1 using NPOI.HSSF.UserModel;
2 using NPOI.HSSF.Util;
3 using NPOI.SS.UserModel;
4 using NPOI.XSSF.UserModel;
5 using System;
6 using System.Collections.Generic;
7 using System.ComponentModel;
8 using System.Data;
9 using System.Globalization;
10 using System.IO;
11 using System.Linq;
12 using System.Text;
13 using System.Threading.Tasks;
14 using NPOI.SS.Util;
15
16 namespace POICommen
17 {
18 public class NpoiHelper
19 {
20 #region 属性
21
22 private readonly int _perSheetCount = 40000; //每个sheet要保存的条数
23
24 public NpoiHelper()
25 {
26 }
27
28 /// <summary>
29 /// 最大接收5万条每页，大于5万时，使用系统默认的值(4万)
30 /// </summary>
31 /// <param name="perSheetCounts"></param>
32 public NpoiHelper(int perSheetCounts)
33 {
34 if (_perSheetCount <= 50000)
35 _perSheetCount = perSheetCounts;
36 }
37
38 #endregion
39
40 #region IExcelProvider 成员
41
42 public DataTable Import(Stream fs, string ext, out string msg, List<string> validates = null)
43 {
44 msg = string.Empty;
45 var dt = new DataTable();
46 try
47 {
48 IWorkbook workbook;
49 if (ext == ".xls")
50 workbook = new HSSFWorkbook(fs);
51 else
52 workbook = new XSSFWorkbook(fs);
53 const int num = 0;
54 var sheet = workbook.GetSheetAt(num);
55 dt.TableName = sheet.SheetName;
56 var rowCount = sheet.LastRowNum;
57 const int firstNum = 0;
58 var headerRow = sheet.GetRow(0);
59 int cellCount = headerRow.LastCellNum;
60 if (validates != null)
61 {
62 var validateCount = validates.Count;
63 if (validateCount > cellCount)
64 {
65 msg = "上传EXCEL文件格式不正确";
66 return null;
67 }
68 for (var i = 0; i < validateCount; i++)
69 {
70 var columnName = headerRow.GetCell(i).StringCellValue;
71 if (validates[i] == columnName) continue;
72 msg = "上传EXCEL文件格式不正确";
73 return null;
74 }
75 }
76 for (int i = headerRow.FirstCellNum; i < cellCount; i++)
77 {
78 var column = new DataColumn(headerRow.GetCell(i).StringCellValue);
79 dt.Columns.Add(column);
80 }
81 for (var i = firstNum + 1; i <= rowCount; i++)
82 {
83 var row = sheet.GetRow(i);
84 var dataRow = dt.NewRow();
85 if (row != null)
86 for (int j = row.FirstCellNum; j < cellCount; j++)
87 if (row.GetCell(j) != null)
88 dataRow[j] = GetCellValue(row.GetCell(j), ext);
89 dt.Rows.Add(dataRow);
90 }
91 return dt;
92 }
93 catch (Exception ex)
94 {
95 throw new Exception(ex.Message);
96 }
97 }
98
99 private static IFont GetFont(IWorkbook workbook, HSSFColor color)
100 {
101 var font = workbook.CreateFont();
102 font.Color = color.Indexed;
103 font.FontHeightInPoints = 10;
104 font.Boldweight = 700;
105 //font.FontName = "楷体";
106 font.IsItalic = true;
107 return font;
108 }
109
110 public static void SetCellValues(ICell cell, string cellType, string cellValue)
111 {
112 switch (cellType)
113 {
114 case "System.String": //字符串类型
115 double result;
116 if (double.TryParse(cellValue, out result))
117 cell.SetCellValue(result);
118 else
119 cell.SetCellValue(cellValue);
120 break;
121 case "System.DateTime": //日期类型
122 DateTime dateV;
123 DateTime.TryParse(cellValue, out dateV);
124 cell.SetCellValue(dateV);
125 break;
126 case "System.Boolean": //布尔型
127 bool boolV;
128 bool.TryParse(cellValue, out boolV);
129 cell.SetCellValue(boolV);
130 break;
131 case "System.Int16": //整型
132 case "System.Int32":
133 case "System.Int64":
134 case "System.Byte":
135 int intV;
136 int.TryParse(cellValue, out intV);
137 cell.SetCellValue(intV);
138 break;
139 case "System.Decimal": //浮点型
140 case "System.Double":
141 double doubV;
142 double.TryParse(cellValue, out doubV);
143 cell.SetCellValue(doubV);
144 break;
145 case "System.DBNull": //空值处理
146 cell.SetCellValue("");
147 break;
148 default:
149 cell.SetCellValue("");
150 break;
151 }
152 }
153
154 public string Export(string excelFileName, DataTable dtIn)
155 {
156 var workbook = new HSSFWorkbook();
157 ICell cell;
158 var sheetCount = 1; //当前的sheet数量
159 var currentSheetCount = 0; //循环时当前保存的条数，每页都会清零
160
161 //表头样式
162 var style = workbook.CreateCellStyle();
163 style.Alignment = HorizontalAlignment.Center;
164 var green = new HSSFColor.Green();
165 style.SetFont(GetFont(workbook, green));
166
167 //内容样式
168 style = workbook.CreateCellStyle();
169 style.Alignment = HorizontalAlignment.Center;
170 var blue = new HSSFColor.Blue();
171 style.SetFont(GetFont(workbook, blue));
172
173 var sheet = workbook.CreateSheet("Sheet" + sheetCount);
174 //填充表头
175 var row = sheet.CreateRow(0);
176 for (var i = 0; i < dtIn.Columns.Count; i++)
177 {
178 cell = row.CreateCell(i);
179 cell.SetCellValue(dtIn.Columns[i].ColumnName);
180 cell.CellStyle = style;
181 }
182 //填充内容
183 for (var i = 0; i < dtIn.Rows.Count; i++)
184 {
185 if (currentSheetCount >= _perSheetCount)
186 {
187 sheetCount++;
188 currentSheetCount = 0;
189 sheet = workbook.CreateSheet("Sheet" + sheetCount);
190 }
191 row = sheetCount == 1 ? sheet.CreateRow(currentSheetCount + 1) : sheet.CreateRow(currentSheetCount);
192 currentSheetCount++;
193 for (var j = 0; j < dtIn.Columns.Count; j++)
194 {
195 cell = row.CreateCell(j);
196 cell.CellStyle = style;
197 SetCellValues(cell, dtIn.Columns[j].DataType.ToString(), dtIn.Rows[i][j].ToString());
198 }
199 }
200 var fs = new FileStream(excelFileName, FileMode.CreateNew, FileAccess.Write);
201 workbook.Write(fs);
202 fs.Close();
203 return excelFileName;
204 }
205
206 public DataTable Import(string filepath, string key, string sheetName, string endKey)
207 {
208 var table = new DataTable();
209 try
210 {
211 using (var excelFileStream = new FileStream(filepath, FileMode.Open, FileAccess.Read))
212 {
213 var file = Path.GetExtension(filepath);
214 if (file != null)
215 {
216 var type = file.Replace(".", "");
217 IWorkbook workbook;
218 if (type == "xls")
219 workbook = new HSSFWorkbook(excelFileStream);
220 else
221 workbook = new XSSFWorkbook(excelFileStream);
222
223 for (var num = 0; num < workbook.NumberOfSheets; num++)
224 {
225 var sheet = workbook.GetSheetAt(num);
226 if (sheet.SheetName != sheetName)
227 continue;
228 table.TableName = sheet.SheetName;
229 var rowCount = sheet.LastRowNum;
230 IRow headerRow = null;
231 var cellCount = 0;
232 var firstNum = 0;
233
234 for (var i = 0; i <= rowCount; i++)
235 {
236 if (sheet.GetRow(i).GetCell(0).StringCellValue != key) continue;
237 headerRow = sheet.GetRow(i);
238 cellCount = headerRow.LastCellNum;
239 firstNum = i;
240 break;
241 }
242
243 //列名
244
245 //handling header.
246 if (headerRow != null)
247 for (int i = headerRow.FirstCellNum; i < cellCount; i++)
248 {
249 var column = new DataColumn(headerRow.GetCell(i).StringCellValue);
250 table.Columns.Add(column);
251 }
252
253 for (var i = firstNum + 1; i <= rowCount; i++)
254 {
255 var row = sheet.GetRow(i);
256 var dataRow = table.NewRow();
257 var isEnd = false;
258 if (row != null)
259 for (int j = row.FirstCellNum; j < cellCount; j++)
260 {
261 if (row.GetCell(j) != null)
262 dataRow[j] = GetCellValue(row.GetCell(j), type);
263 if (dataRow[j].ToString() != endKey) continue;
264 isEnd = true;
265 break;
266 }
267 if (isEnd)
268 break;
269 table.Rows.Add(dataRow);
270 }
271 return table;
272 }
273 }
274 }
275 }
276 catch (Exception)
277 {
278 return null;
279 }
280 return table;
281 }
282
283 private static string GetCellValue(ICell cell, string type)
284 {
285 if (cell == null)
286 return string.Empty;
287 switch (cell.CellType)
288 {
289 case CellType.Blank:
290 return string.Empty;
291 case CellType.Boolean:
292 return cell.BooleanCellValue.ToString();
293 case CellType.Error:
294 return cell.ErrorCellValue.ToString();
295 case CellType.Numeric:
296 var format = cell.CellStyle.DataFormat;
297 if (format == 14 || format == 31 || format == 57 || format == 58)
298 {
299 var date = cell.DateCellValue;
300 var re = date.ToString("yyy-MM-dd");
301 return re;
302 }
303 return cell.ToString();
304
305 case CellType.String:
306 return cell.StringCellValue;
307
308 case CellType.Formula:
309 try
310 {
311 if (type == "xls")
312 {
313 var e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
314 e.EvaluateInCell(cell);
315 return cell.ToString();
316 }
317 else
318 {
319 var e = new XSSFFormulaEvaluator(cell.Sheet.Workbook);
320 e.EvaluateInCell(cell);
321 return cell.ToString();
322 }
323 }
324 catch
325 {
326 return cell.NumericCellValue.ToString(CultureInfo.InvariantCulture);
327 }
328 case CellType.Unknown:
329 return cell.ToString();
330 default:
331 return cell.ToString();
332 }
333 }
334
335 #endregion
336
337 #region 辅助导入
338
339 /// <summary>
340 /// </summary>
341 /// <typeparam name="T"></typeparam>
342 /// <param name="datatable"></param>
343 /// <returns></returns>
344 public IEnumerable<T> ConvertTo<T>(DataTable datatable) where T : new()
345 {
346 var temp = new List<T>();
347 try
348 {
349 var columnsNames =
350 (from DataColumn dataColumn in datatable.Columns select dataColumn.ColumnName).ToList();
351 temp = datatable.AsEnumerable().ToList().ConvertAll(row => GetObject<T>(row, columnsNames));
352 return temp;
353 }
354 catch
355 {
356 return temp;
357 }
358 }
359
360 /// <summary>
361 /// 根据DataTable生成对象，对象的属性与列同名
362 /// </summary>
363 /// <typeparam name="T"></typeparam>
364 /// <param name="row"></param>
365 /// <param name="columnsName"></param>
366 /// <returns></returns>
367 public T GetObject<T>(DataRow row, List<string> columnsName) where T : new()
368 {
369 var obj = new T();
370 try
371 {
372 var properties = typeof(T).GetProperties();
373 foreach (var objProperty in properties)
374 {
375 var attrs = objProperty.GetCustomAttributes(typeof(DisplayNameAttribute), false);
376 if (!attrs.Any()) continue;
377 var displayName = ((DisplayNameAttribute)attrs.First()).DisplayName;
378
379 var columnname = columnsName.Find(s => s == displayName);
380 if (string.IsNullOrEmpty(columnname)) continue;
381 var value = row[columnname].ToString();
382 if (string.IsNullOrEmpty(value)) continue;
383 if (Nullable.GetUnderlyingType(objProperty.PropertyType) != null)
384 {
385 value = row[columnname].ToString().Replace("$", "").Replace(",", "");
386 objProperty.SetValue(obj,
387 Convert.ChangeType(value,
388 Type.GetType(Nullable.GetUnderlyingType(objProperty.PropertyType).ToString())), null);
389 }
390 else
391 {
392 value = row[columnname].ToString().Replace("%", "");
393 objProperty.SetValue(obj,
394 Convert.ChangeType(value, Type.GetType(objProperty.PropertyType.ToString())), null);
395 }
396 }
397 return obj;
398 }
399 catch
400 {
401 return obj;
402 }
403 }
404
405 public static void CopyRow(int startRow, int endRow, int pPosition, ISheet sheet)
406 {
407 int pStartRow = startRow - 1;
408 int pEndRow = endRow - 1;
409 int targetRowFrom;
410 int targetRowTo;
411 int cloumnCount;
412
413 CellRangeAddress region = null;
414
415
416 if (pStartRow==-1||pEndRow==-1)
417 {
418 return;
419 }
420
421
422 //拷贝合并的单元格
423 for (int k = 0; k < sheet.NumMergedRegions; k++)
424 {
425 region = sheet.GetMergedRegion(k);
426 if (region.FirstRow>=pStartRow&®ion.LastRow<=pEndRow)
427 {
428 targetRowFrom = region.FirstRow - pStartRow + pPosition;
429 targetRowTo = region.LastRow - pStartRow + pPosition;
430 CellRangeAddress newRegion = region.Copy();
431 newRegion.FirstRow = targetRowFrom;
432 newRegion.FirstColumn = region.FirstColumn;
433 newRegion.LastRow = targetRowTo;
434 newRegion.LastColumn = region.LastColumn;
435 sheet.AddMergedRegion(newRegion);
436 }
437
438 }
439
440 //设置列宽
441 for (int k = pStartRow; k <=pEndRow; k++)
442 {
443 IRow sourceRow = sheet.GetRow(k);
444 cloumnCount = sourceRow.LastCellNum;
445 if (sourceRow!=null)
446 {
447 IRow newRow = sheet.CreateRow(pPosition - pStartRow + k);
448 newRow.Height = sourceRow.Height;
449 for (int l = 0; l < cloumnCount; l++)
450 {
451 ICell templateCell = sourceRow.GetCell(l);
452 if (templateCell!=null)
453 {
454 ICell newCell = newRow.CreateCell(l);
455 CopyCell(templateCell,newCell);
456 }
457 }
458 }
459
460 }
461
462
463 }
464
465 private static void CopyCell(ICell srcCell, ICell distCell)
466 {
467 distCell.CellStyle=srcCell.CellStyle;
468 if (srcCell.CellComment!= null)
469 {
470 distCell.CellComment=srcCell.CellComment;
471 }
472
473 CellType srcCellType = srcCell.CellType;
474 distCell.SetCellType(srcCellType);
475
476 string cellValue = GetCellValue(srcCell, "xlsx");
477 SetCellValues(distCell, "System.String", cellValue);
478 }
479
480 #endregion
481 }
482 }
