NPOI Excel导入、导出 

  1 /// <summary>
  2         /// 将excel导入到datatable
  3         /// </summary>
  4         /// <param name="filePath">excel路径</param>
  5         /// <param name="isColumnName">第一行是否是列名</param>
  6         /// <param name="sheetIndex">第几个sheet(从0开始)</param>
  7         /// <returns>返回datatable</returns>
  8         public DataTable ExcelToDataTable(string filePath, bool isColumnName, string sheetIndex)
  9         {
 10             DataTable dataTable = null;
 11             FileStream fs = null;
 12             DataColumn column = null;
 13             DataRow dataRow = null;
 14             IWorkbook workbook = null;
 15             ISheet sheet = null;
 16             IRow row = null;
 17             ICell cell = null;
 18             int startRow = 0;
 19             try
 20             {
 21                 using (fs = File.OpenRead(filePath))
 22                 {
 23                     // 2007版本
 24                     if (filePath.IndexOf(".xlsx") > 0)
 25                         workbook = new XSSFWorkbook(fs);
 26                     // 2003版本
 27                     else if (filePath.IndexOf(".xls") > 0)
 28                         workbook = new HSSFWorkbook(fs);
 29 
 30                     if (workbook != null)
 31                     {
 32                         if (string.IsNullOrWhiteSpace(sheetIndex))
 33                         {
 34                             sheet = workbook.GetSheetAt(0);//读取第一个sheet，当然也可以循环读取每个sheet
 35                         }
 36                         else
 37                         {
 38                             sheet = workbook.GetSheetAt(int.Parse(sheetIndex));
 39                         }
 40                         dataTable = new DataTable();
 41                         if (sheet != null)
 42                         {
 43                             int rowCount = sheet.LastRowNum;//总行数
 44                             if (rowCount > 0)
 45                             {
 46                                 IRow firstRow = sheet.GetRow(0);//第一行
 47                                 int cellCount = 5;//列数
 48 
 49                                 //构建datatable的列
 50                                 if (isColumnName)
 51                                 {
 52                                     startRow = 1;//如果第一行是列名，则从第二行开始读取
 53                                     for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
 54                                     {
 55                                         cell = firstRow.GetCell(i);
 56                                         if (cell != null)
 57                                         {
 58                                             if (cell.StringCellValue != null)
 59                                             {
 60                                                 column = new DataColumn(cell.StringCellValue);
 61                                                 dataTable.Columns.Add(column);
 62                                             }
 63                                         }
 64                                     }
 65                                 }
 66                                 else
 67                                 {
 68                                     for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
 69                                     {
 70                                         column = new DataColumn("column" + (i + 1));
 71                                         dataTable.Columns.Add(column);
 72                                     }
 73                                 }
 74 
 75                                 //填充行
 76                                 for (int i = startRow; i <= rowCount; ++i)
 77                                 {
 78                                     row = sheet.GetRow(i);
 79                                     if (row == null) continue;
 80                                     if (row.GetCell(0)==null)
 81                                         continue;
 82                                     dataRow = dataTable.NewRow();
 83                                     for (int j = row.FirstCellNum; j < cellCount; ++j)
 84                                     {
 85                                         cell = row.GetCell(j);
 86                                         if (cell == null)
 87                                         {
 88                                             dataRow[j] = "";
 89                                         }
 90                                         else
 91                                         {
 92                                             //CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)
 93                                             switch (cell.CellType)
 94                                             {
 95                                                 case CellType.Blank:
 96                                                     dataRow[j] = "";
 97                                                     break;
 98                                                 case CellType.Numeric:
 99                                                     short format = cell.CellStyle.DataFormat;
100                                                     //对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理
101                                                     if (format == 14 || format == 31 || format == 57 || format == 58)
102                                                         dataRow[j] = cell.DateCellValue;
103                                                     else
104                                                         dataRow[j] = cell.NumericCellValue;
105                                                     break;
106                                                 case CellType.String:
107                                                     dataRow[j] = cell.StringCellValue;
108                                                     break;
109                                             }
110                                         }
111                                     }
112                                     dataTable.Rows.Add(dataRow);
113                                 }
114                             }
115                         }
116                     }
117                 }
118                 return dataTable;
119             }
120             catch (Exception ex)
121             {
122                 if (fs != null)
123                 {
124                     fs.Close();
125                 }
126                 return null;
127             }
128         }
 1         /// <summary>
 2         /// Datable导出成Excel
 3         /// </summary>
 4         /// <param name="dt"></param>
 5         /// <param name="file">导出路径(包括文件名与扩展名)</param>
 6         public static void TableToExcel(DataTable dt, string file)
 7         {
 8             IWorkbook workbook;
 9             string fileExt = Path.GetExtension(file).ToLower();
10             if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(); } else { workbook = null; }
11             if (workbook == null) { return; }
12             ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName);
13             //数据  
14             for (int i = 0; i < dt.Rows.Count; i++)
15             {
16                 var error = false;
17                 IRow row1 = sheet.CreateRow(i);
18                 for (int j = 0; j < dt.Columns.Count; j++)
19                 {
20                     ICell cell = row1.CreateCell(j);
21                     cell.SetCellValue(dt.Rows[i][j].ToString());
22                 }
23             }
24 
25             //转为字节数组  
26             MemoryStream stream = new MemoryStream();
27             workbook.Write(stream);
28             var buf = stream.ToArray();
29 
30             //保存为Excel文件  
31             using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
32             {
33                 fs.Write(buf, 0, buf.Length);
34                 fs.Flush();
35             }
36         }

单元格颜色

ICellStyle s = workbook.CreateCellStyle();  //创建一个单元格样式
s.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Red.Index;  //填充前景色
s.FillPattern = FillPattern.SolidForeground;  //填充样式
row1.GetCell(0).CellStyle = s;  //设置给单元格

posted @ 2020-11-06 16:15  怀星  阅读(101)  评论(0)    收藏  举报