1 using System;
2 using System.Collections.Generic;
3 using System.Data;
4 using System.IO;
5 using System.Text;
6 using System.Web;
7 using NPOI;
8 using NPOI.HPSF;
9 using NPOI.HSSF;
10 using NPOI.HSSF.Record;
11 using NPOI.HSSF.UserModel;
12 using NPOI.HSSF.Util;
13 using NPOI.XSSF;
14 using NPOI.XSSF.UserModel;
15 using NPOI.XSSF.Util;
16 using NPOI.POIFS;
17 using NPOI.SS.UserModel;
18 using NPOI.Util;
19 using NPOI.SS;
20 using NPOI.DDF;
21 using NPOI.SS.Util;
22 using System.Collections;
23 using System.Text.RegularExpressions;
24
25 public class ExcelHelper
26 {
27 #region 从datatable中将数据导出到excel
28 public static HSSFWorkbook ExportWorkbook(DataTable dtSource, string SheetName, string ColumnProperty)
29 {
30 HSSFWorkbook workbook = new HSSFWorkbook();
31 if (SheetName == null || SheetName == "") SheetName = "Sheet1";
32 HSSFSheet sheet = workbook.CreateSheet(SheetName) as HSSFSheet;
33
34 // 标题样式
35 HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
36 headStyle.Alignment = HorizontalAlignment.Center;
37 headStyle.VerticalAlignment = VerticalAlignment.Center;
38 headStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
39 headStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
40 headStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
41 headStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
42 headStyle.WrapText = true;
43 HSSFFont font = workbook.CreateFont() as HSSFFont;
44 font.Boldweight = (short)FontBoldWeight.Bold;
45 headStyle.SetFont(font);
46
47 //内容样式
48 HSSFCellStyle cellStyle = workbook.CreateCellStyle() as HSSFCellStyle;
49 cellStyle.CloneStyleFrom(headStyle);
50 cellStyle.Alignment = HorizontalAlignment.Center;
51 font = workbook.CreateFont() as HSSFFont;
52 font.Boldweight = (short)FontBoldWeight.Normal;
53 cellStyle.SetFont(font);
54 HSSFCellStyle cellStyleLeft = workbook.CreateCellStyle() as HSSFCellStyle;
55 cellStyleLeft.CloneStyleFrom(cellStyle);
56 cellStyleLeft.Alignment = HorizontalAlignment.Left;
57 HSSFCellStyle cellStyleRight = workbook.CreateCellStyle() as HSSFCellStyle;
58 cellStyleRight.CloneStyleFrom(cellStyle);
59 cellStyleRight.Alignment = HorizontalAlignment.Right;
60
61 int rowIndex = 0;
62 #region 列头及样式
63 HSSFRow headerRow = sheet.CreateRow(rowIndex) as HSSFRow;
64 foreach (DataColumn column in dtSource.Columns)
65 {
66 headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
67 headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
68 }
69 #endregion
70 rowIndex++;
71 foreach (DataRow row in dtSource.Rows)
72 {
73 #region 填充内容
74 HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
75 foreach (DataColumn column in dtSource.Columns)
76 {
77 HSSFCell newCell = dataRow.CreateCell(column.Ordinal) as HSSFCell;
78 newCell.CellStyle = cellStyle;
79 AutoSetType(newCell, column, row);
80 }
81
82 #endregion
83 rowIndex++;
84 }
85 AutoSizeColumns(sheet);
86
87 if (ColumnProperty != null)
88 {
89 string[] ColumnArray = ColumnProperty.Split('|');
90 foreach (string Column in ColumnArray)
91 {
92 string[] ValueArray = Column.Split(',');
93 if (ValueArray.Length > 2)
94 {
95 int cols = int.Parse(ValueArray[1]);
96 switch (ValueArray[0].ToString())
97 {
98 case "Merge":
99 int precols = 0;
100 int.TryParse(ValueArray[2], out precols);
101 GroupCol(sheet, 1, cols, precols);
102 break;
103 case "Width":
104 int columnWidth = 0;
105 int.TryParse(ValueArray[2], out columnWidth);
106 sheet.SetColumnWidth(cols, columnWidth * 256);
107 break;
108 case "Align":
109 string Value = ValueArray[2].ToString();
110 for (int j = 1; j <= sheet.LastRowNum; j++)
111 {
112 ICell currentCell = sheet.GetRow(j).GetCell(cols);
113 switch (Value)
114 {
115 case "center":
116 currentCell.CellStyle = cellStyle;
117 break;
118 case "left":
119 currentCell.CellStyle = cellStyleLeft;
120 break;
121 case "right":
122 currentCell.CellStyle = cellStyleRight;
123 break;
124 }
125 }
126 break;
127 case "DataType":
128 string Value2 = ValueArray[2].ToString();
129 for (int j = 1; j <= sheet.LastRowNum; j++)
130 {
131 ICell currentCell = sheet.GetRow(j).GetCell(cols);
132 string drValue = currentCell.ToString();
133 switch (Value2)
134 {
135 case "double":
136 double result;
137 if (double.TryParse(drValue, out result))
138 currentCell.SetCellValue(result);
139 break;
140 case "string":
141 currentCell.SetCellValue(drValue.ToString());
142 break;
143 }
144 }
145 break;
146 }
147 }
148 }
149 }
150 return workbook;
151 }
152
153 /// <summary>
154 /// DataTable导出到Excel的MemoryStream
155 /// </summary>
156 /// <param name="dtSource">源DataTable</param>
157 public static MemoryStream ExportDT(DataTable dtSource, string SheetName, string ColumnProperty)
158 {
159 using (MemoryStream ms = new MemoryStream())
160 {
161 HSSFWorkbook workbook = ExportWorkbook(dtSource, SheetName, ColumnProperty);
162 workbook.Write(ms);
163 ms.Flush();
164 ms.Position = 0;
165 return ms;
166 }
167 }
168
169 public static void ExportDTtoExcel(DataTable dtSource, string strFileName, bool SaveFile)
170 {
171 ExportDTtoExcel(dtSource, strFileName, SaveFile, null, null);
172 }
173
174 /// <summary>
175 /// DataTable导出到Excel文件
176 /// </summary>
177 /// <param name="dtSource">源DataTable</param>
178 /// <param name="sFileName">文件名</param>
179 /// <param name="SaveFile">是否需保存</param>
180 /// <param name="SheetName">Sheet名</param>
181 /// <param name="ColumnProperty">列属性集,多个属性集以"|"分隔,值以","分隔,范例:"Merge,0,0|Align,2,right|Width,4,100|DataType,5,double"</param>
182 public static void ExportDTtoExcel(DataTable dtSource, string sFileName, bool SaveFile, string SheetName, string ColumnProperty)
183 {
184 using (MemoryStream ms = ExportDT(dtSource, SheetName, ColumnProperty))
185 {
186 if (SaveFile)
187 {
188 using (FileStream fs = new FileStream(sFileName, FileMode.Create, FileAccess.Write))
189 {
190 byte[] data = ms.ToArray();
191 fs.Write(data, 0, data.Length);
192 fs.Flush();
193 }
194 System.IO.FileInfo fileInfo = new System.IO.FileInfo(sFileName);
195 sFileName = fileInfo.Name;
196 }
197 System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename=" + HttpUtility.UrlEncode(sFileName)));
198 System.Web.HttpContext.Current.Response.AddHeader("Content-Length", ms.ToArray().Length.ToString());
199 System.Web.HttpContext.Current.Response.BinaryWrite(ms.ToArray());
200 System.Web.HttpContext.Current.Response.Flush();
201 ms.Close();
202 ms.Dispose();
203 System.Web.HttpContext.Current.Response.End();
204 }
205 }
206
207 /// <summary>
208 /// 导出EXCEL,可以导出多个sheet(不设置列宽)
209 /// </summary>
210 /// <param name="dtSources">原始数据表</param>
211 /// <param name="strFileName">文件名</param>
212 /// <param name="SaveFile">是否需要保存此文件</param>
213 /// <param name="NeedMargeColumns">需要合并的列，如：3,5</param>
214 public static void ExportDTtoExcelNoWidth(DataTable[] dtSources, string strFileName, bool SaveFile, string NeedMargeColumns)
215 {
216 HSSFWorkbook workbook = new HSSFWorkbook();
217 if (NeedMargeColumns.Length > 0)
218 {
219 NeedMargeColumns = "," + NeedMargeColumns + ",";
220 }
221 HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle;
222 HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat;
223 dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
224 for (int k = 0; k < dtSources.Length; k++)
225 {
226 int[] margeColIndex = new int[dtSources[k].Columns.Count];//保存合并列的起始行
227 string[] margeColValue = new string[dtSources[k].Columns.Count];//保存合并列对应的上一个值
228 HSSFSheet sheet = workbook.CreateSheet(dtSources[k].TableName.ToString()) as HSSFSheet;
229 //填充表头
230 HSSFRow dataRow = sheet.CreateRow(0) as HSSFRow;
231 ICellStyle Headstyle = workbook.CreateCellStyle();
232 //设置单元格的样式：水平对齐居中
233 Headstyle.Alignment = HorizontalAlignment.Center;
234 //新建一个字体样式对象
235 IFont font = workbook.CreateFont();
236 //设置字体加粗样式
237 font.Boldweight = short.MaxValue;
238 //使用SetFont方法将字体样式添加到单元格样式中
239 Headstyle.SetFont(font);
240 foreach (DataColumn column in dtSources[k].Columns)
241 {
242 margeColIndex[column.Ordinal] = 1;
243 margeColValue[column.Ordinal] = "";
244 ICell cell = dataRow.CreateCell(column.Ordinal);//创建单元格
245 cell.SetCellValue(column.ColumnName);//设置单元格的值
246 dataRow.GetCell(column.Ordinal).CellStyle = Headstyle; //将新的样式赋给单元格
247 }
248 //填充内容
249 for (int i = 0; i < dtSources[k].Rows.Count; i++)
250 {
251 dataRow = sheet.CreateRow(i + 1) as HSSFRow;
252 for (int j = 0; j < dtSources[k].Columns.Count; j++)
253 {
254 ICell newCell = dataRow.CreateCell(j);
255 bool canSetValue = false;
256 if (NeedMargeColumns.Length > 0 && (NeedMargeColumns.IndexOf("," + j.ToString() + ",") != -1)) //有需要合并的列,并且当前列需要合并
257 {
258 if (margeColValue[j] == "")
259 {
260 canSetValue = true;
261 margeColIndex[j] = i + 1;
262 margeColValue[j] = dtSources[k].Rows[i][j].ToString();
263 }
264 else if (margeColValue[j] != dtSources[k].Rows[i][j].ToString())//不同的时候需要更新原来储存的值，并重新设置合并
265 {
266 canSetValue = true;
267 //CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
268 sheet.AddMergedRegion(new CellRangeAddress(margeColIndex[j], i, j, j));
269 margeColIndex[j] = i + 1;
270 margeColValue[j] = dtSources[k].Rows[i][j].ToString();
271 }
272 }
273 else
274 {
275 canSetValue = true;
276 }
277 if (canSetValue)
278 {
279 //需要合并的列，1.第一行/最后一行,2.上一行值不同，下一行值相同，则给当前单元格赋值
280 switch (dtSources[k].Columns[j].DataType.ToString())
281 {
282 case "System.String"://字符串类型
283 newCell.SetCellValue(dtSources[k].Rows[i][j].ToString());
284 break;
285 case "System.DateTime"://日期类型
286 DateTime dateV;
287 DateTime.TryParse(dtSources[k].Rows[i][j].ToString(), out dateV);
288 newCell.SetCellValue(dateV);
289 newCell.CellStyle = dateStyle;//格式化显示
290 break;
291 case "System.Boolean"://布尔型
292 bool boolV = false;
293 bool.TryParse(dtSources[k].Rows[i][j].ToString(), out boolV);
294 newCell.SetCellValue(boolV);
295 break;
296 case "System.Int16"://整型
297 case "System.Int32":
298 case "System.Int64":
299 case "System.Byte":
300 int intV = 0;
301 int.TryParse(dtSources[k].Rows[i][j].ToString(), out intV);
302 newCell.SetCellValue(intV);
303 break;
304 case "System.Decimal"://浮点型
305 case "System.Double":
306 double doubV = 0;
307 double.TryParse(dtSources[k].Rows[i][j].ToString(), out doubV);
308 newCell.SetCellValue(doubV);
309 break;
310 case "System.DBNull"://空值处理
311 newCell.SetCellValue("");
312 break;
313 default:
314 newCell.SetCellValue("");
315 break;
316 }
317 }
318 }
319 }
320 }
321 //保存
322 using (MemoryStream ms = new MemoryStream())
323 {
324 workbook.Write(ms);
325 ms.Flush();
326 ms.Position = 0;
327 if (SaveFile)
328 {
329 using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
330 {
331 byte[] data = ms.ToArray();
332 fs.Write(data, 0, data.Length);
333 fs.Flush();
334 }
335 System.IO.FileInfo fileInfo = new System.IO.FileInfo(strFileName);
336 strFileName = fileInfo.Name;
337 }
338 else
339 {
340 System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename=" + HttpUtility.UrlEncode(strFileName)));
341 System.Web.HttpContext.Current.Response.AddHeader("Content-Length", ms.ToArray().Length.ToString());
342 System.Web.HttpContext.Current.Response.BinaryWrite(ms.ToArray());
343 System.Web.HttpContext.Current.Response.Flush();
344 ms.Close();
345 ms.Dispose();
346 System.Web.HttpContext.Current.Response.End();
347 }
348 }
349 }
350 /// <summary>
351 /// 导出EXCEL,可以导出多个sheet
352 /// </summary>
353 /// <param name="dtSources">原始数据表</param>
354 /// <param name="strFileName">文件名</param>
355 /// <param name="SaveFile">是否需要保存此文件</param>
356 /// <param name="NeedMargeColumns">需要合并的列，如：3,5（表示第三列和第五列各自合并单元格）</param>
357 public static void ExportDTtoExcel(DataTable[] dtSources, string strFileName, bool SaveFile, string NeedMargeColumns)
358 {
359 HSSFWorkbook workbook = new HSSFWorkbook();
360 if (NeedMargeColumns.Length > 0)
361 {
362 NeedMargeColumns = "," + NeedMargeColumns + ",";
363 }
364 HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle;
365 HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat;
366 dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
367 for (int k = 0; k < dtSources.Length; k++)
368 {
369 int[] margeColIndex = new int[dtSources[k].Columns.Count];//保存合并列的起始行
370 string[] margeColValue = new string[dtSources[k].Columns.Count];//保存合并列对应的上一个值
371 HSSFSheet sheet = workbook.CreateSheet(dtSources[k].TableName.ToString()) as HSSFSheet;
372 //填充表头
373 HSSFRow dataRow = sheet.CreateRow(0) as HSSFRow;
374 ICellStyle Headstyle = workbook.CreateCellStyle();
375 //设置单元格的样式：水平对齐居中
376 Headstyle.Alignment = HorizontalAlignment.Center;
377 //新建一个字体样式对象
378 IFont font = workbook.CreateFont();
379 //设置字体加粗样式
380 font.Boldweight = short.MaxValue;
381 //使用SetFont方法将字体样式添加到单元格样式中
382 Headstyle.SetFont(font);
383
384 ICellStyle Rowstyle = workbook.CreateCellStyle();
385 //设置单元格的样式：垂直对齐居中
386 Rowstyle.VerticalAlignment = VerticalAlignment.Justify;
387
388 //取得列宽
389 int[] arrColWidth = new int[dtSources[k].Columns.Count];
390 foreach (DataColumn item in dtSources[k].Columns)
391 {
392 arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
393 margeColIndex[item.Ordinal] = 1;
394 margeColValue[item.Ordinal] = "";
395 }
396 for (int i = 0; i < dtSources[k].Rows.Count; i++)
397 {
398 for (int j = 0; j < dtSources[k].Columns.Count; j++)
399 {
400 int intTemp = Encoding.GetEncoding(936).GetBytes(dtSources[k].Rows[i][j].ToString()).Length;
401 if (intTemp > arrColWidth[j])
402 {
403 arrColWidth[j] = intTemp;
404 }
405 }
406 }
407 foreach (DataColumn column in dtSources[k].Columns)
408 {
409 ICell cell = dataRow.CreateCell(column.Ordinal);//创建单元格
410 cell.SetCellValue(column.ColumnName);//设置单元格的值
411 dataRow.GetCell(column.Ordinal).CellStyle = Headstyle; //将新的样式赋给单元格
412 //设置列宽
413 //sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
414 if (arrColWidth[column.Ordinal] > 255)
415 {
416 arrColWidth[column.Ordinal] = 254;
417 }
418 else
419 {
420 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
421 }
422 }
423 //填充内容
424 for (int i = 0; i < dtSources[k].Rows.Count; i++)
425 {
426 dataRow = sheet.CreateRow(i + 1) as HSSFRow;
427 for (int j = 0; j < dtSources[k].Columns.Count; j++)
428 {
429 ICell newCell = dataRow.CreateCell(j);
430 bool canSetValue = false;
431 if (NeedMargeColumns.Length > 0 && (NeedMargeColumns.IndexOf("," + j.ToString() + ",") != -1)) //有需要合并的列,并且当前列需要合并
432 {
433 if (margeColValue[j] == "")
434 {
435 canSetValue = true;
436 margeColIndex[j] = i + 1;
437 margeColValue[j] = dtSources[k].Rows[i][j].ToString();
438 }
439 else if (margeColValue[j] != dtSources[k].Rows[i][j].ToString())//不同的时候需要更新原来储存的值，并重新设置合并
440 {
441 canSetValue = true;
442 //CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
443 sheet.AddMergedRegion(new CellRangeAddress(margeColIndex[j], i, j, j));
444 margeColIndex[j] = i + 1;
445 margeColValue[j] = dtSources[k].Rows[i][j].ToString();
446 newCell.CellStyle = Rowstyle;
447 }
448 else if (i == dtSources[k].Rows.Count - 1 && i != 0)//最后一行强制合并
449 {
450 //值相同本来是不用做任何事，但是最后一行，则需要合并
451 sheet.AddMergedRegion(new CellRangeAddress(margeColIndex[j], i + 1, j, j));
452 newCell.CellStyle = Rowstyle;
453 }
454 }
455 else
456 {
457 canSetValue = true;
458 }
459 if (canSetValue)
460 {
461 //需要合并的列，1.第一行/最后一行,2.上一行值不同，下一行值相同，则给当前单元格赋值
462 switch (dtSources[k].Columns[j].DataType.ToString())
463 {
464 case "System.String"://字符串类型
465 newCell.SetCellValue(dtSources[k].Rows[i][j].ToString());
466 break;
467 case "System.DateTime"://日期类型
468 DateTime dateV;
469 DateTime.TryParse(dtSources[k].Rows[i][j].ToString(), out dateV);
470 newCell.SetCellValue(dateV);
471 newCell.CellStyle = dateStyle;//格式化显示
472 break;
473 case "System.Boolean"://布尔型
474 bool boolV = false;
475 bool.TryParse(dtSources[k].Rows[i][j].ToString(), out boolV);
476 newCell.SetCellValue(boolV);
477 break;
478 case "System.Int16"://整型
479 case "System.Int32":
480 case "System.Int64":
481 case "System.Byte":
482 int intV = 0;
483 int.TryParse(dtSources[k].Rows[i][j].ToString(), out intV);
484 newCell.SetCellValue(intV);
485 break;
486 case "System.Decimal"://浮点型
487 case "System.Double":
488 double doubV = 0;
489 double.TryParse(dtSources[k].Rows[i][j].ToString(), out doubV);
490 newCell.SetCellValue(doubV);
491 break;
492 case "System.DBNull"://空值处理
493 newCell.SetCellValue("");
494 break;
495 default:
496 newCell.SetCellValue("");
497 break;
498 }
499 }
500 }
501 }
502 }
503 //保存
504 using (MemoryStream ms = new MemoryStream())
505 {
506 workbook.Write(ms);
507 ms.Flush();
508 ms.Position = 0;
509 if (SaveFile)
510 {
511 using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
512 {
513 byte[] data = ms.ToArray();
514 fs.Write(data, 0, data.Length);
515 fs.Flush();
516 }
517 System.IO.FileInfo fileInfo = new System.IO.FileInfo(strFileName);
518 strFileName = fileInfo.Name;
519 }
520 System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename=" + HttpUtility.UrlEncode(strFileName)));
521 System.Web.HttpContext.Current.Response.AddHeader("Content-Length", ms.ToArray().Length.ToString());
522 System.Web.HttpContext.Current.Response.BinaryWrite(ms.ToArray());
523 System.Web.HttpContext.Current.Response.Flush();
524 ms.Close();
525 ms.Dispose();
526 System.Web.HttpContext.Current.Response.End();
527 }
528 }
529 /// <summary>
530 /// 导出EXCEL,可以导出多个sheet
531 /// </summary>
532 /// <param name="dtSources">原始数据表</param>
533 /// <param name="strFileName">文件名</param>
534 /// <param name="SaveFile">是否需要保存此文件</param>
535 /// <param name="NeedMargeColumns">需要合并的列，如：3,5（表示第三列和第五列各自合并单元格）或者3,3:5（表示第三列合并，然后第五列合并时参考第三列再合并）</param>
536 public static void ExportDTtoExcelExt(DataTable[] dtSources, string strFileName, bool SaveFile, string NeedMargeColumns)
537 {
538 HSSFWorkbook workbook = new HSSFWorkbook();
539 if (NeedMargeColumns.Length > 0)
540 {
541 NeedMargeColumns = "," + NeedMargeColumns + ",";
542 }
543 HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle;
544 HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat;
545 dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
546 for (int k = 0; k < dtSources.Length; k++)
547 {
548 int[] margeColIndex = new int[dtSources[k].Columns.Count];//保存合并列的起始行
549 string[] margeColValue = new string[dtSources[k].Columns.Count];//保存合并列对应的上一个值
550 HSSFSheet sheet = workbook.CreateSheet(dtSources[k].TableName.ToString()) as HSSFSheet;
551 //填充表头
552 HSSFRow dataRow = sheet.CreateRow(0) as HSSFRow;
553 ICellStyle Headstyle = workbook.CreateCellStyle();
554 //设置单元格的样式：水平对齐居中
555 Headstyle.Alignment = HorizontalAlignment.Center;
556 //新建一个字体样式对象
557 IFont font = workbook.CreateFont();
558 //设置字体加粗样式
559 font.Boldweight = short.MaxValue;
560 //使用SetFont方法将字体样式添加到单元格样式中
561 Headstyle.SetFont(font);
562
563 ICellStyle Rowstyle = workbook.CreateCellStyle();
564 //设置单元格的样式：垂直对齐居中
565 Rowstyle.VerticalAlignment = VerticalAlignment.Justify;
566
567 //取得列宽
568 int[] arrColWidth = new int[dtSources[k].Columns.Count];
569 foreach (DataColumn item in dtSources[k].Columns)
570 {
571 arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
572 margeColIndex[item.Ordinal] = 1;
573 margeColValue[item.Ordinal] = "";
574 }
575 for (int i = 0; i < dtSources[k].Rows.Count; i++)
576 {
577 for (int j = 0; j < dtSources[k].Columns.Count; j++)
578 {
579 int intTemp = Encoding.GetEncoding(936).GetBytes(dtSources[k].Rows[i][j].ToString()).Length;
580 if (intTemp > arrColWidth[j])
581 {
582 arrColWidth[j] = intTemp;
583 }
584 }
585 }
586 foreach (DataColumn column in dtSources[k].Columns)
587 {
588 ICell cell = dataRow.CreateCell(column.Ordinal);//创建单元格
589 cell.SetCellValue(column.ColumnName);//设置单元格的值
590 dataRow.GetCell(column.Ordinal).CellStyle = Headstyle; //将新的样式赋给单元格
591 //设置列宽
592 //sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
593
594 if (arrColWidth[column.Ordinal] >= 255)
595 {
596 arrColWidth[column.Ordinal] = 254;
597 }
598 else
599 {
600 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
601 }
602 }
603 //填充内容
604 for (int i = 0; i < dtSources[k].Rows.Count; i++)
605 {
606 dataRow = sheet.CreateRow(i + 1) as HSSFRow;
607 for (int j = 0; j < dtSources[k].Columns.Count; j++)
608 {
609 ICell newCell = dataRow.CreateCell(j);
610 bool canSetValue = false;
611 if (NeedMargeColumns.Length > 0 && (NeedMargeColumns.IndexOf("," + j.ToString() + ",") != -1 || NeedMargeColumns.IndexOf(":" + j.ToString() + ",") != -1)) //有需要合并的列,并且当前列需要合并
612 {
613 if (margeColValue[j] == "")
614 {
615 canSetValue = true;
616 margeColIndex[j] = i + 1;
617 margeColValue[j] = dtSources[k].Rows[i][j].ToString();
618 }
619 else if (NeedMargeColumns.IndexOf("," + j.ToString() + ",") != -1)//单独列合并
620 {
621 if (margeColValue[j] != dtSources[k].Rows[i][j].ToString())//不同的时候需要更新原来储存的值，并重新设置合并
622 {
623 canSetValue = true;
624 //CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
625 sheet.AddMergedRegion(new CellRangeAddress(margeColIndex[j], i, j, j));
626 margeColIndex[j] = i + 1;
627 margeColValue[j] = dtSources[k].Rows[i][j].ToString();
628 newCell.CellStyle = Rowstyle;
629 }
630 else if (i == dtSources[k].Rows.Count - 1 && i != 0)//最后一行强制合并
631 {
632 //值相同本来是不用做任何事，但是最后一行，则需要合并
633 sheet.AddMergedRegion(new CellRangeAddress(margeColIndex[j], i + 1, j, j));
634 newCell.CellStyle = Rowstyle;
635 }
636 }
637 else//参考前列合并
638 {
639 //获取前列
640 int refercol = 0;
641 string[] MargeCols = NeedMargeColumns.Split(',');
642 for (int dd = 0; dd < MargeCols.Length; dd++)
643 {
644 if (MargeCols[dd].Length > 0 && ("," + MargeCols[dd] + ",").IndexOf(":" + j.ToString() + ",") != -1)
645 {
646 refercol = Convert.ToInt32(MargeCols[dd].Substring(0, MargeCols[dd].IndexOf(":")));
647 break;
648 }
649 }
650 if (dtSources[k].Rows[i - 1][refercol].ToString() != dtSources[k].Rows[i][refercol].ToString())
651 {
652 canSetValue = true;
653 //CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
654 sheet.AddMergedRegion(new CellRangeAddress(margeColIndex[j], i, j, j));
655 margeColIndex[j] = i + 1;
656 margeColValue[j] = dtSources[k].Rows[i][j].ToString();
657 newCell.CellStyle = Rowstyle;
658 }
659 else if (margeColValue[j] != dtSources[k].Rows[i][j].ToString())//不同的时候需要更新原来储存的值，并重新设置合并
660 {
661 canSetValue = true;
662 //CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
663 sheet.AddMergedRegion(new CellRangeAddress(margeColIndex[j], i, j, j));
664 margeColIndex[j] = i + 1;
665 margeColValue[j] = dtSources[k].Rows[i][j].ToString();
666 newCell.CellStyle = Rowstyle;
667 }
668 else if (i == dtSources[k].Rows.Count - 1 && i != 0)//最后一行强制合并
669 {
670 //值相同本来是不用做任何事，但是最后一行，则需要合并
671 sheet.AddMergedRegion(new CellRangeAddress(margeColIndex[j], i + 1, j, j));
672 newCell.CellStyle = Rowstyle;
673 }
674 }
675 }
676 else
677 {
678 canSetValue = true;
679 }
680 if (canSetValue)
681 {
682 #region 需要合并的列，1.第一行/最后一行,2.上一行值不同，下一行值相同，则给当前单元格赋值
683 switch (dtSources[k].Columns[j].DataType.ToString())
684 {
685 case "System.String"://字符串类型
686 newCell.SetCellValue(dtSources[k].Rows[i][j].ToString());
687 break;
688 case "System.DateTime"://日期类型
689 DateTime dateV;
690 DateTime.TryParse(dtSources[k].Rows[i][j].ToString(), out dateV);
691 newCell.SetCellValue(dateV);
692 newCell.CellStyle = dateStyle;//格式化显示
693 break;
694 case "System.Boolean"://布尔型
695 bool boolV = false;
696 bool.TryParse(dtSources[k].Rows[i][j].ToString(), out boolV);
697 newCell.SetCellValue(boolV);
698 break;
699 case "System.Int16"://整型
700 case "System.Int32":
701 case "System.Int64":
702 case "System.Byte":
703 int intV = 0;
704 int.TryParse(dtSources[k].Rows[i][j].ToString(), out intV);
705 newCell.SetCellValue(intV);
706 break;
707 case "System.Decimal"://浮点型
708 case "System.Double":
709 double doubV = 0;
710 double.TryParse(dtSources[k].Rows[i][j].ToString(), out doubV);
711 newCell.SetCellValue(doubV);
712 break;
713 case "System.DBNull"://空值处理
714 newCell.SetCellValue("");
715 break;
716 default:
717 newCell.SetCellValue("");
718 break;
719 }
720 #endregion
721 }
722 }
723 }
724 }
725 //保存
726 using (MemoryStream ms = new MemoryStream())
727 {
728 workbook.Write(ms);
729 ms.Flush();
730 ms.Position = 0;
731 if (SaveFile)
732 {
733 using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
734 {
735 byte[] data = ms.ToArray();
736 fs.Write(data, 0, data.Length);
737 fs.Flush();
738 }
739 System.IO.FileInfo fileInfo = new System.IO.FileInfo(strFileName);
740 strFileName = fileInfo.Name;
741 }
742 System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename=" + HttpUtility.UrlEncode(strFileName)));
743 System.Web.HttpContext.Current.Response.AddHeader("Content-Length", ms.ToArray().Length.ToString());
744 System.Web.HttpContext.Current.Response.BinaryWrite(ms.ToArray());
745 System.Web.HttpContext.Current.Response.Flush();
746 ms.Close();
747 ms.Dispose();
748 System.Web.HttpContext.Current.Response.End();
749 }
750 }
751 #endregion
752
753 #region 从excel中将数据导出到datatable
754 /// <summary>读取excel
755 /// 默认第一行为标头
756 /// </summary>
757 /// <param name="strFileName">excel文档路径</param>
758 /// <returns></returns>
759 public static DataTable ImportExceltoDt(string strFileName)
760 {
761 DataTable dt = new DataTable();
762 IWorkbook wk = null;
763 using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
764 {
765 if (strFileName.EndsWith(".xlsx"))
766 wk = new XSSFWorkbook(file);
767 else
768 wk = new HSSFWorkbook(file);
769 }
770 ISheet sheet = wk.GetSheetAt(0) as ISheet;
771 dt = ImportDt(sheet, 0, true);
772 return dt;
773 }
774
775 /// <summary>
776 /// 读取excel
777 /// </summary>
778 /// <param name="strFileName">excel文件路径</param>
779 /// <param name="sheet">需要导出的sheet</param>
780 /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
781 /// <returns></returns>
782 public static DataTable ImportExceltoDt(string strFileName, string SheetName, int HeaderRowIndex)
783 {
784 DataTable dt = new DataTable();
785 IWorkbook wk = null;
786 using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
787 {
788 if (strFileName.EndsWith(".xlsx"))
789 wk = new XSSFWorkbook(file);
790 else
791 wk = new HSSFWorkbook(file);
792 }
793 ISheet sheet = wk.GetSheet(SheetName) as ISheet;
794 dt = ImportDt(sheet, HeaderRowIndex, true);
795 //ExcelFileStream.Close();
796 wk = null;
797 sheet = null;
798 return dt;
799 }
800
801 /// <summary>
802 /// 读取excel
803 /// </summary>
804 /// <param name="strFileName">excel文件路径</param>
805 /// <param name="sheet">需要导出的sheet序号</param>
806 /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
807 /// <returns></returns>
808 public static DataTable ImportExceltoDt(string strFileName, int SheetIndex, int HeaderRowIndex)
809 {
810 DataTable dt = new DataTable();
811 IWorkbook wk = null;
812 using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
813 {
814 if (strFileName.EndsWith(".xlsx"))
815 wk = new XSSFWorkbook(file);
816 else
817 wk = new HSSFWorkbook(file);
818 }
819 ISheet sheet = wk.GetSheetAt(SheetIndex) as ISheet;
820 dt = ImportDt(sheet, HeaderRowIndex, true);
821 //ExcelFileStream.Close();
822 wk = null;
823 sheet = null;
824 return dt;
825 }
826
827 /// <summary>
828 /// 读取excel
829 /// </summary>
830 /// <param name="strFileName">excel文件路径</param>
831 /// <param name="sheet">需要导出的sheet</param>
832 /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
833 /// <returns></returns>
834 public static DataTable ImportExceltoDt(string strFileName, string SheetName, int HeaderRowIndex, bool needHeader)
835 {
836 DataTable dt = new DataTable();
837 IWorkbook wk = null;
838 using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
839 {
840 if (strFileName.EndsWith(".xlsx"))
841 wk = new XSSFWorkbook(file);
842 else
843 wk = new HSSFWorkbook(file);
844 }
845 ISheet sheet = wk.GetSheet(SheetName) as ISheet;
846 dt = ImportDt(sheet, HeaderRowIndex, needHeader);
847 wk = null;
848 sheet = null;
849 return dt;
850 }
851
852 /// <summary>
853 /// 读取excel
854 /// </summary>
855 /// <param name="strFileName">excel文件路径</param>
856 /// <param name="sheet">需要导出的sheet序号</param>
857 /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
858 /// <returns></returns>
859 public static DataTable ImportExceltoDt(string strFileName, int SheetIndex, int HeaderRowIndex, bool needHeader)
860 {
861 DataTable dt = new DataTable();
862 IWorkbook wk = null;
863 using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
864 {
865 if (strFileName.EndsWith(".xlsx"))
866 wk = new XSSFWorkbook(file);
867 else
868 wk = new HSSFWorkbook(file);
869 }
870 ISheet sheet = wk.GetSheetAt(SheetIndex) as ISheet;
871 dt = ImportDt(sheet, HeaderRowIndex, needHeader);
872 wk = null;
873 sheet = null;
874 return dt;
875 }
876
877 /// <summary>
878 /// 将制定sheet中的数据导出到datatable中
879 /// </summary>
880 /// <param name="sheet">需要导出的sheet</param>
881 /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
882 /// <returns></returns>
883 static DataTable ImportDt(ISheet sheet, int HeaderRowIndex, bool needHeader)
884 {
885 DataTable table = new DataTable();
886 IRow headerRow;
887 int cellCount;
888 try
889 {
890 if (HeaderRowIndex < 0 || !needHeader)
891 {
892 headerRow = sheet.GetRow(0) as IRow;
893 cellCount = headerRow.LastCellNum;
894
895 for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
896 {
897 DataColumn column = new DataColumn(Convert.ToString("F" + (i + 1).ToString()));
898 table.Columns.Add(column);
899 }
900 }
901 else
902 {
903 headerRow = sheet.GetRow(HeaderRowIndex) as IRow;
904 cellCount = headerRow.LastCellNum;
905
906 for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
907 {
908 if (headerRow.GetCell(i) == null)
909 {
910 if (table.Columns.IndexOf(Convert.ToString(i)) > 0)
911 {
912 DataColumn column = new DataColumn(Convert.ToString("重复列名" + i));
913 table.Columns.Add(column);
914 }
915 else
916 {
917 DataColumn column = new DataColumn(Convert.ToString(i));
918 table.Columns.Add(column);
919 }
920
921 }
922 else if (table.Columns.IndexOf(headerRow.GetCell(i).ToString()) > 0)
923 {
924 DataColumn column = new DataColumn(Convert.ToString("重复列名" + i));
925 table.Columns.Add(column);
926 }
927 else
928 {
929 DataColumn column = new DataColumn(headerRow.GetCell(i).ToString());
930 table.Columns.Add(column);
931 }
932 }
933 }
934 int rowCount = sheet.LastRowNum;
935 for (int i = (HeaderRowIndex + 1); i <= sheet.LastRowNum; i++)
936 {
937 try
938 {
939 IRow row;
940 if (sheet.GetRow(i) == null)
941 {
942 row = sheet.CreateRow(i) as IRow;
943 }
944 else
945 {
946 row = sheet.GetRow(i) as IRow;
947 }
948
949 DataRow dataRow = table.NewRow();
950
951 for (int j = row.FirstCellNum; j <= cellCount; j++)
952 {
953 try
954 {
955 if (row.GetCell(j) != null)
956 {
957 switch (row.GetCell(j).CellType)
958 {
959 case CellType.String:
960 string str = row.GetCell(j).StringCellValue;
961 if (str != null && str.Length > 0)
962 {
963 dataRow[j] = str.ToString();
964 }
965 else
966 {
967 dataRow[j] = null;
968 }
969 break;
970 case CellType.Numeric:
971 if (DateUtil.IsCellDateFormatted(row.GetCell(j)))
972 {
973 dataRow[j] = DateTime.FromOADate(row.GetCell(j).NumericCellValue);
974 }
975 else
976 {
977 dataRow[j] = Convert.ToDouble(row.GetCell(j).NumericCellValue);
978 }
979 break;
980 case CellType.Boolean:
981 dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
982 break;
983 case CellType.Error:
984 dataRow[j] = row.GetCell(j).ErrorCellValue;
985 break;
986 case CellType.Formula:
987 switch (row.GetCell(j).CachedFormulaResultType)
988 {
989 case CellType.String:
990 string strFORMULA = row.GetCell(j).StringCellValue;
991 if (strFORMULA != null && strFORMULA.Length > 0)
992 {
993 dataRow[j] = strFORMULA.ToString();
994 }
995 else
996 {
997 dataRow[j] = null;
998 }
999 break;
1000 case CellType.Numeric:
1001 dataRow[j] = Convert.ToString(row.GetCell(j).NumericCellValue);
1002 break;
1003 case CellType.Boolean:
1004 dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
1005 break;
1006 case CellType.Error:
1007 dataRow[j] = row.GetCell(j).ErrorCellValue;
1008 break;
1009 default:
1010 dataRow[j] = "";
1011 break;
1012 }
1013 break;
1014 default:
1015 dataRow[j] = "";
1016 break;
1017 }
1018 }
1019 }
1020 catch (Exception)
1021 {
1022 //wl.WriteLogs(exception.ToString());
1023 }
1024 }
1025 table.Rows.Add(dataRow);
1026 }
1027 catch (Exception)
1028 {
1029 //wl.WriteLogs(exception.ToString());
1030 }
1031 }
1032 }
1033 catch (Exception)
1034 {
1035 //wl.WriteLogs(exception.ToString());
1036 }
1037 return table;
1038 }
1039 #endregion
1040
1041 #region 更新excel中的数据
1042 /// <summary>
1043 /// 更新Excel表格
1044 /// </summary>
1045 /// <param name="outputFile">需更新的excel表格路径</param>
1046 /// <param name="sheetname">sheet名</param>
1047 /// <param name="updateData">需更新的数据</param>
1048 /// <param name="coluid">需更新的列号</param>
1049 /// <param name="rowid">需更新的开始行号</param>
1050 public static void UpdateExcel(string outputFile, int sheetIndex, string[] updateData, int coluid, int rowid)
1051 {
1052 FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);
1053
1054 HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);
1055 ISheet sheet1 = hssfworkbook.GetSheetAt(sheetIndex);
1056 for (int i = 0; i < updateData.Length; i++)
1057 {
1058 try
1059 {
1060 if (sheet1.GetRow(i + rowid) == null)
1061 {
1062 sheet1.CreateRow(i + rowid);
1063 }
1064 if (sheet1.GetRow(i + rowid).GetCell(coluid) == null)
1065 {
1066 sheet1.GetRow(i + rowid).CreateCell(coluid);
1067 }
1068
1069 sheet1.GetRow(i + rowid).GetCell(coluid).SetCellValue(updateData[i]);
1070 }
1071 catch (Exception ex)
1072 {
1073 // wl.WriteLogs(ex.ToString());
1074 throw;
1075 }
1076 }
1077 try
1078 {
1079 readfile.Close();
1080 FileStream writefile = new FileStream(outputFile, FileMode.Create, FileAccess.Write);
1081 hssfworkbook.Write(writefile);
1082 writefile.Close();
1083 }
1084 catch (Exception ex)
1085 {
1086 // wl.WriteLogs(ex.ToString());
1087 }
1088
1089 }
1090
1091 /// <summary>
1092 /// 更新Excel表格
1093 /// </summary>
1094 /// <param name="outputFile">需更新的excel表格路径</param>
1095 /// <param name="sheetname">sheet名</param>
1096 /// <param name="updateData">需更新的数据</param>
1097 /// <param name="coluids">需更新的列号</param>
1098 /// <param name="rowid">需更新的开始行号</param>
1099 public static void UpdateExcel(string outputFile, int sheetIndex, string[][] updateData, int[] coluids, int rowid)
1100 {
1101 FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);
1102
1103 HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);
1104 readfile.Close();
1105 ISheet sheet1 = hssfworkbook.GetSheetAt(sheetIndex);
1106 for (int j = 0; j < coluids.Length; j++)
1107 {
1108 for (int i = 0; i < updateData[j].Length; i++)
1109 {
1110 try
1111 {
1112 if (sheet1.GetRow(i + rowid) == null)
1113 {
1114 sheet1.CreateRow(i + rowid);
1115 }
1116 if (sheet1.GetRow(i + rowid).GetCell(coluids[j]) == null)
1117 {
1118 sheet1.GetRow(i + rowid).CreateCell(coluids[j]);
1119 }
1120 sheet1.GetRow(i + rowid).GetCell(coluids[j]).SetCellValue(updateData[j][i]);
1121 }
1122 catch (Exception ex)
1123 {
1124 // wl.WriteLogs(ex.ToString());
1125 }
1126 }
1127 }
1128 try
1129 {
1130 FileStream writefile = new FileStream(outputFile, FileMode.Create);
1131 hssfworkbook.Write(writefile);
1132 writefile.Close();
1133 }
1134 catch (Exception ex)
1135 {
1136 //wl.WriteLogs(ex.ToString());
1137 }
1138 }
1139
1140 /// <summary>
1141 /// 更新Excel表格
1142 /// </summary>
1143 /// <param name="outputFile">需更新的excel表格路径</param>
1144 /// <param name="sheetname">sheet名</param>
1145 /// <param name="updateData">需更新的数据</param>
1146 /// <param name="coluid">需更新的列号</param>
1147 /// <param name="rowid">需更新的开始行号</param>
1148 public static void UpdateExcel(string outputFile, int sheetIndex, double[] updateData, int coluid, int rowid)
1149 {
1150 FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);
1151
1152 HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);
1153 ISheet sheet1 = hssfworkbook.GetSheetAt(sheetIndex);
1154 for (int i = 0; i < updateData.Length; i++)
1155 {
1156 try
1157 {
1158 if (sheet1.GetRow(i + rowid) == null)
1159 {
1160 sheet1.CreateRow(i + rowid);
1161 }
1162 if (sheet1.GetRow(i + rowid).GetCell(coluid) == null)
1163 {
1164 sheet1.GetRow(i + rowid).CreateCell(coluid);
1165 }
1166
1167 sheet1.GetRow(i + rowid).GetCell(coluid).SetCellValue(updateData[i]);
1168 }
1169 catch (Exception ex)
1170 {
1171 //wl.WriteLogs(ex.ToString());
1172 throw;
1173 }
1174 }
1175 try
1176 {
1177 readfile.Close();
1178 FileStream writefile = new FileStream(outputFile, FileMode.Create, FileAccess.Write);
1179 hssfworkbook.Write(writefile);
1180 writefile.Close();
1181 }
1182 catch (Exception ex)
1183 {
1184 //wl.WriteLogs(ex.ToString());
1185 }
1186
1187 }
1188
1189 /// <summary>
1190 /// 更新Excel表格
1191 /// </summary>
1192 /// <param name="outputFile">需更新的excel表格路径</param>
1193 /// <param name="sheetname">sheet名</param>
1194 /// <param name="updateData">需更新的数据</param>
1195 /// <param name="coluids">需更新的列号</param>
1196 /// <param name="rowid">需更新的开始行号</param>
1197 public static void UpdateExcel(string outputFile, int sheetIndex, double[][] updateData, int[] coluids, int rowid)
1198 {
1199 FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);
1200
1201 HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);
1202 readfile.Close();
1203 ISheet sheet1 = hssfworkbook.GetSheetAt(sheetIndex);
1204 for (int j = 0; j < coluids.Length; j++)
1205 {
1206 for (int i = 0; i < updateData[j].Length; i++)
1207 {
1208 try
1209 {
1210 if (sheet1.GetRow(i + rowid) == null)
1211 {
1212 sheet1.CreateRow(i + rowid);
1213 }
1214 if (sheet1.GetRow(i + rowid).GetCell(coluids[j]) == null)
1215 {
1216 sheet1.GetRow(i + rowid).CreateCell(coluids[j]);
1217 }
1218 sheet1.GetRow(i + rowid).GetCell(coluids[j]).SetCellValue(updateData[j][i]);
1219 }
1220 catch (Exception ex)
1221 {
1222 //wl.WriteLogs(ex.ToString());
1223 }
1224 }
1225 }
1226 try
1227 {
1228 FileStream writefile = new FileStream(outputFile, FileMode.Create);
1229 hssfworkbook.Write(writefile);
1230 writefile.Close();
1231 }
1232 catch (Exception ex)
1233 {
1234 //wl.WriteLogs(ex.ToString());
1235 }
1236 }
1237
1238 #endregion
1239
1240 public static int GetSheetNumber(string outputFile)
1241 {
1242 int number = 0;
1243 try
1244 {
1245 IWorkbook wk = null;
1246 FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);
1247 if (outputFile.EndsWith(".xlsx"))
1248 wk = new XSSFWorkbook(readfile);
1249 else
1250 wk = new HSSFWorkbook(readfile);
1251 number = wk.NumberOfSheets;
1252 }
1253 catch (Exception)
1254 {
1255 //wl.WriteLogs(exception.ToString());
1256 }
1257 return number;
1258 }
1259
1260 public static ArrayList GetSheetName(string outputFile)
1261 {
1262 ArrayList arrayList = new ArrayList();
1263 try
1264 {
1265 IWorkbook wk = null;
1266 FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);
1267 if (outputFile.EndsWith(".xlsx"))
1268 wk = new XSSFWorkbook(readfile);
1269 else
1270 wk = new HSSFWorkbook(readfile);
1271
1272 for (int i = 0; i < wk.NumberOfSheets; i++)
1273 {
1274 arrayList.Add(wk.GetSheetName(i));
1275 }
1276
1277 //var newFile2 = @"newbook.core.docx";
1278 //using (var fs = new FileStream(newFile2, FileMode.Create, FileAccess.Write))
1279 //{
1280 // XWPFDocument doc = new XWPFDocument();
1281 // var p0 = doc.CreateParagraph();
1282 // p0.Alignment = ParagraphAlignment.CENTER;
1283 // XWPFRun r0 = p0.CreateRun();
1284 // r0.FontFamily = "microsoft yahei";
1285 // r0.FontSize = 18;
1286 // r0.IsBold = true;
1287 // r0.SetText("This is title");
1288
1289 // var p1 = doc.CreateParagraph();
1290 // p1.Alignment = ParagraphAlignment.LEFT;
1291 // p1.IndentationFirstLine = 500;
1292 // XWPFRun r1 = p1.CreateRun();
1293 // r1.FontFamily = "·ÂËÎ";
1294 // r1.FontSize = 12;
1295 // r1.IsBold = true;
1296 // r1.SetText("This is content, content content content content content content content content content");
1297
1298 // doc.Write(fs);
1299 //}
1300 }
1301 catch (Exception ex)
1302 {
1303 throw ex;
1304 }
1305 return arrayList;
1306 }
1307
1308 public static bool isNumeric(String message, out double result)
1309 {
1310 Regex rex = new Regex(@"^[-]?\d+[.]?\d*$");
1311 result = -1;
1312 if (rex.IsMatch(message))
1313 {
1314 result = double.Parse(message);
1315 return true;
1316 }
1317 else
1318 return false;
1319 }
1320
1321 /// <summary>
1322 /// 根据其他行的内容合并ISheet中某列相同信息的行（单元格）,2列相等
1323 /// </summary>
1324 /// <param name="sheet"></param>
1325 /// <param name="StartRow">合并的开始行</param>
1326 /// <param name="cols">合并列</param>
1327 /// <param name="precols">参考列</param>
1328 public static void GroupCol(ISheet sheet, int StartRow, int cols, int precols)
1329 {
1330 if (sheet.PhysicalNumberOfRows < StartRow + 1 || cols > sheet.GetRow(0).PhysicalNumberOfCells - 1)
1331 {
1332 return;
1333 }
1334 int RowNum = 0;
1335 ICell oldPreCell = sheet.GetRow(StartRow).GetCell(precols);
1336 ICell oldCell = sheet.GetRow(StartRow).GetCell(cols);
1337 int i = StartRow + 1;
1338 while (i < sheet.PhysicalNumberOfRows)
1339 {
1340
1341 ICell PreCell = sheet.GetRow(i).GetCell(precols);
1342 ICell Cell = sheet.GetRow(i).GetCell(cols);
1343 if (oldCell.ToString() == Cell.ToString() && oldPreCell.ToString() == PreCell.ToString())
1344 {
1345 //Cell.SetCellValue("");
1346 RowNum++;
1347 }
1348 else
1349 {
1350 if (RowNum > 0)
1351 {
1352 CellRangeAddress region = new CellRangeAddress(StartRow, StartRow + RowNum, cols, cols);
1353 sheet.AddMergedRegion(region);
1354 StartRow += RowNum;
1355 }
1356 oldPreCell = PreCell;
1357 oldCell = Cell;
1358 StartRow += 1;
1359 RowNum = 0;
1360 }
1361 i++;
1362 }
1363 if (RowNum > 0 && i == sheet.PhysicalNumberOfRows)
1364 {
1365 CellRangeAddress region = new CellRangeAddress(StartRow, StartRow + RowNum, cols, cols);
1366 sheet.AddMergedRegion(region);
1367 StartRow += RowNum;
1368 }
1369 }
1370
1371 /// <summary>
1372 /// 自动设置Excel列宽，行高
1373 /// </summary>
1374 /// <param name="sheet">Excel表</param>
1375 public static void AutoSizeColumns(ISheet sheet)
1376 {
1377 int RowNum = sheet.LastRowNum;
1378 int ColumnNum = sheet.GetRow(0).LastCellNum;
1379 int HeightValue = 0;
1380 for (int i = 0; i < ColumnNum; i++)
1381 {
1382 int columnWidth = sheet.GetColumnWidth(i) / 256;//获取当前列宽度
1383 for (int j = 1; j <= RowNum; j++)//在这一列上循环行
1384 {
1385 IRow currentRow = sheet.GetRow(j);
1386 ICell currentCell = currentRow.GetCell(i);
1387 int length = Encoding.UTF8.GetBytes(currentCell.ToString()).Length;//获取当前单元格的内容宽度
1388 if (columnWidth < length + 1)
1389 columnWidth = length + 1;
1390 if (columnWidth > 40)
1391 columnWidth = 40;
1392
1393 if (length > HeightValue) HeightValue = length;
1394 currentRow.HeightInPoints = 20 * (HeightValue / 60 + 1);//高度自适应
1395 }
1396 sheet.SetColumnWidth(i, columnWidth * 256);
1397 }
1398 }
1399
1400 public static void AutoSetType(ICell newCell, DataColumn column, DataRow row)
1401 {
1402 string drValue = row[column].ToString();
1403 switch (column.DataType.ToString())
1404 {
1405 case "System.String"://字符串类型
1406 //double result;
1407 //if (double.TryParse(drValue, out result) && result.ToString().Length != 15)
1408 // newCell.SetCellValue(result);
1409 //else
1410 newCell.SetCellValue(drValue);
1411 break;
1412 case "System.DateTime"://日期类型
1413 DateTime dateV;
1414 DateTime.TryParse(drValue, out dateV);
1415 newCell.SetCellValue(dateV);
1416 break;
1417 case "System.Boolean"://布尔型
1418 bool boolV = false;
1419 bool.TryParse(drValue, out boolV);
1420 newCell.SetCellValue(boolV);
1421 break;
1422 case "System.Int16"://整型
1423 case "System.Int32":
1424 case "System.Int64":
1425 case "System.Byte":
1426 int intV = 0;
1427 int.TryParse(drValue, out intV);
1428 if (intV != 0)
1429 newCell.SetCellValue(intV);
1430 break;
1431 case "System.Decimal"://浮点型
1432 case "System.Double":
1433 double doubV = 0;
1434 double.TryParse(drValue, out doubV);
1435 if (doubV != 0)
1436 newCell.SetCellValue(doubV);
1437 break;
1438 case "System.DBNull"://空值处理
1439 newCell.SetCellValue("");
1440 break;
1441 default:
1442 newCell.SetCellValue("");
1443 break;
1444 }
1445 }
1446
1447
1448
1449 /// <summary>
1450 /// DataTable导出到Excel文件 两行表头
1451 /// </summary>
1452 /// <param name="dt_Title">表头表格</param>
1453 /// <param name="dtSource">源DataTable</param>
1454 /// <param name="sFileName">文件名</param>
1455 /// <param name="SaveFile">是否需保存</param>
1456 /// <param name="SheetName">Sheet名</param>
1457 /// <param name="ColumnProperty">列属性集,多个属性集以"|"分隔,值以","分隔,范例:"Merge,0,0|Align,2,right|Width,4,100|DataType,5,double"</param>
1458
1459 public static void ExportDTtoExcel_2Title(DataTable dt_Title, DataTable dtSource, string sFileName, bool SaveFile, string SheetName, string ColumnProperty)
1460 {
1461 using (MemoryStream ms = ExportDT_2Title(dt_Title, dtSource, SheetName, ColumnProperty))
1462 {
1463 if (SaveFile)
1464 {
1465 using (FileStream fs = new FileStream(sFileName, FileMode.Create, FileAccess.Write))
1466 {
1467 byte[] data = ms.ToArray();
1468 fs.Write(data, 0, data.Length);
1469 fs.Flush();
1470 }
1471 System.IO.FileInfo fileInfo = new System.IO.FileInfo(sFileName);
1472 sFileName = fileInfo.Name;
1473 }
1474 System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename=" + HttpUtility.UrlEncode(sFileName)));
1475 System.Web.HttpContext.Current.Response.AddHeader("Content-Length", ms.ToArray().Length.ToString());
1476 System.Web.HttpContext.Current.Response.BinaryWrite(ms.ToArray());
1477 System.Web.HttpContext.Current.Response.Flush();
1478 ms.Close();
1479 ms.Dispose();
1480 System.Web.HttpContext.Current.Response.End();
1481 }
1482 }
1483 /// <summary>
1484 /// DataTable导出到Excel的MemoryStream
1485 /// </summary>
1486 /// <param name="dtSource">源DataTable</param>
1487 public static MemoryStream ExportDT_2Title(DataTable dt_Title, DataTable dtSource, string SheetName, string ColumnProperty)
1488 {
1489 using (MemoryStream ms = new MemoryStream())
1490 {
1491 HSSFWorkbook workbook = ExportWorkbook_2Title(dt_Title, dtSource, SheetName, ColumnProperty);
1492 workbook.Write(ms);
1493 ms.Flush();
1494 ms.Position = 0;
1495 return ms;
1496 }
1497 }
1498 public static HSSFWorkbook ExportWorkbook_2Title(DataTable dt_Title, DataTable dtSource, string SheetName, string ColumnProperty)
1499 {
1500 HSSFWorkbook workbook = new HSSFWorkbook();
1501 if (SheetName == null || SheetName == "") SheetName = "Sheet1";
1502 HSSFSheet sheet = workbook.CreateSheet(SheetName) as HSSFSheet;
1503
1504 // 标题样式
1505 HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
1506 headStyle.Alignment = HorizontalAlignment.Center;
1507 headStyle.VerticalAlignment = VerticalAlignment.Center;
1508 headStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
1509 headStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
1510 headStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
1511 headStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
1512 headStyle.WrapText = true;
1513 HSSFFont font = workbook.CreateFont() as HSSFFont;
1514 font.Boldweight = (short)FontBoldWeight.Bold;
1515 headStyle.SetFont(font);
1516
1517 //内容样式
1518 HSSFCellStyle cellStyle = workbook.CreateCellStyle() as HSSFCellStyle;
1519 cellStyle.CloneStyleFrom(headStyle);
1520 cellStyle.Alignment = HorizontalAlignment.Center;
1521 font = workbook.CreateFont() as HSSFFont;
1522 font.Boldweight = (short)FontBoldWeight.Normal;
1523 cellStyle.SetFont(font);
1524 HSSFCellStyle cellStyleLeft = workbook.CreateCellStyle() as HSSFCellStyle;
1525 cellStyleLeft.CloneStyleFrom(cellStyle);
1526 cellStyleLeft.Alignment = HorizontalAlignment.Left;
1527 HSSFCellStyle cellStyleRight = workbook.CreateCellStyle() as HSSFCellStyle;
1528 cellStyleRight.CloneStyleFrom(cellStyle);
1529 cellStyleRight.Alignment = HorizontalAlignment.Right;
1530
1531 int rowIndex = 0;
1532 #region 列头及样式
1533
1534 for (int i = 0; i < dt_Title.Rows.Count; i++)
1535 {
1536 HSSFRow headerRow = sheet.CreateRow(rowIndex) as HSSFRow;
1537 for (int j = 0; j < dt_Title.Columns.Count; j++)
1538 {
1539 headerRow.CreateCell(dt_Title.Columns[j].Ordinal).SetCellValue(dt_Title.Rows[i][j].ToString());
1540 headerRow.GetCell(dt_Title.Columns[j].Ordinal).CellStyle = headStyle;
1541
1542 }
1543 rowIndex++;
1544 }
1545 for (int i = 0; i < dt_Title.Rows.Count; i++)
1546 {
1547 for (int m = 0; m < dt_Title.Columns.Count; m++)
1548 {
1549 int n = m + 1;
1550 while (n < dt_Title.Columns.Count)
1551 {
1552 if (dt_Title.Rows[i][m].ToString() == dt_Title.Rows[i][n].ToString())
1553 {
1554 n++;
1555 }
1556 else if (n == dt_Title.Columns.Count)
1557 {
1558 break;
1559 }
1560 else
1561 {
1562 //合并同一行相同的列表头
1563 sheet.AddMergedRegion(new CellRangeAddress(i, i, m, n - 1));
1564 m = n - 1;
1565 break;
1566 }
1567 }
1568 if (n == dt_Title.Columns.Count)
1569 {
1570 sheet.AddMergedRegion(new CellRangeAddress(i, i, m, n - 1));
1571
1572 }
1573 }
1574
1575 }
1576 for (int i = 0; i < dt_Title.Columns.Count; i++)
1577 {
1578 for (int m = 0; m < dt_Title.Rows.Count; m++)
1579 {
1580 int n = m + 1;
1581 while (n < dt_Title.Rows.Count)
1582 {
1583 if (dt_Title.Rows[m][i].ToString() == dt_Title.Rows[n][i].ToString())
1584 {
1585 n++;
1586 }
1587 else if (n == dt_Title.Rows.Count)
1588 {
1589 break;
1590 }
1591 else
1592 {
1593 //合并同一列相同的行表头
1594 sheet.AddMergedRegion(new CellRangeAddress(m, n - 1, i, i));
1595 m = n - 1;
1596 break;
1597 }
1598 }
1599 if (n == dt_Title.Rows.Count)
1600 {
1601 sheet.AddMergedRegion(new CellRangeAddress(m, n - 1, i, i));
1602
1603 }
1604 }
1605 }
1606
1607
1608
1609
1610 //sheet.AddMergedRegion(new CellRangeAddress(0, 1, 1, 1));
1611 //sheet.AddMergedRegion(new CellRangeAddress(0, 1, 2, 2));
1612 #endregion
1613
1614 foreach (DataRow row in dtSource.Rows)
1615 {
1616 #region 填充内容
1617 HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
1618 foreach (DataColumn column in dtSource.Columns)
1619 {
1620 HSSFCell newCell = dataRow.CreateCell(column.Ordinal) as HSSFCell;
1621 newCell.CellStyle = cellStyle;
1622 AutoSetType(newCell, column, row);
1623 }
1624
1625 #endregion
1626 rowIndex++;
1627 }
1628 AutoSizeColumns(sheet);
1629
1630 if (ColumnProperty != null)
1631 {
1632 string[] ColumnArray = ColumnProperty.Split('|');
1633 foreach (string Column in ColumnArray)
1634 {
1635 string[] ValueArray = Column.Split(',');
1636 if (ValueArray.Length > 2)
1637 {
1638 int cols = int.Parse(ValueArray[1]);
1639 switch (ValueArray[0].ToString())
1640 {
1641 case "Merge":
1642 int precols = 0;
1643 int.TryParse(ValueArray[2], out precols);
1644 GroupCol(sheet, 1, cols, precols);
1645 break;
1646 case "Width":
1647 int columnWidth = 0;
1648 int.TryParse(ValueArray[2], out columnWidth);
1649 sheet.SetColumnWidth(cols, columnWidth * 256);
1650 break;
1651 case "Align":
1652 string Value = ValueArray[2].ToString();
1653 for (int j = 1; j <= sheet.LastRowNum; j++)
1654 {
1655 ICell currentCell = sheet.GetRow(j).GetCell(cols);
1656 switch (Value)
1657 {
1658 case "center":
1659 currentCell.CellStyle = cellStyle;
1660 break;
1661 case "left":
1662 currentCell.CellStyle = cellStyleLeft;
1663 break;
1664 case "right":
1665 currentCell.CellStyle = cellStyleRight;
1666 break;
1667 }
1668 }
1669 break;
1670 case "DataType":
1671 string Value2 = ValueArray[2].ToString();
1672 for (int j = 1; j <= sheet.LastRowNum; j++)
1673 {
1674 ICell currentCell = sheet.GetRow(j).GetCell(cols);
1675 string drValue = currentCell.ToString();
1676 switch (Value2)
1677 {
1678 case "double":
1679 double result;
1680 if (double.TryParse(drValue, out result))
1681 currentCell.SetCellValue(result);
1682 break;
1683 case "string":
1684 currentCell.SetCellValue(drValue.ToString());
1685 break;
1686 }
1687 }
1688 break;
1689 }
1690 }
1691 }
1692 }
1693 return workbook;
1694 }
1695
1696 }
