NPOI helper

2019/04/14 16:20
阅读数 148
  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&&region.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 }

 

展开阅读全文
加载中
点击引领话题📣 发布并加入讨论🔥
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部