C#读写Excel（NPOI）

      NPOI 是 POI 项目的 .NET 版本。POI是一个开源的Java读写Excel、WORD等微软OLE2组件文档的项目。使用 NPOI 你就可以在没有安装 Office 或者相应环境的机器上对 WORD/EXCEL 文档进行读写。需要在NuGet下载NPOI包。
       优点：读取Excel速度较快，读取方式操作灵活性。

读取Excel

       /// <summary>
       /// NPOI读Excel至DataTable
       /// </summary>
       /// <param name="fileName">Excel路径</param>
       /// <param name="sheetName">工作表名默认读取第一个工作表</param>
       /// <returns></returns>
       public static System.Data.DataTable NPExcelToDataTable(string fileName,string sheetName="")
       {
           FileStream fs = null;
           NPOI.SS.UserModel.IWorkbook workbook = null;
           NPOI.SS.UserModel.ISheet sheet = null;
           System.Data.DataTable dt = new System.Data.DataTable();
           try
           {
               fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
               if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                   workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(fs);
               else if (fileName.IndexOf(".xls") > 0) // 97-2003版本
                   workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(fs);
               //workbook = NPOI.SS.UserModel.WorkbookFactory.Create(fs);
               if (sheetName != "")                              //是否有传入表名
               {
                   sheet = workbook.GetSheet(sheetName);
               }
               else
               {
                   sheet = workbook.GetSheetAt(0);    //读取第一个sheet
               }
               int startRow = 0;   //开始读取行数
               if (sheet == null)
                   throw new Exception("未找到工作表");
               dt.TableName = sheet.SheetName;
               NPOI.SS.UserModel.IRow firstRow = sheet.GetRow(startRow);     //第一行 
               int cellCount = firstRow.LastCellNum;     //一行最后一个cell的编号 即总的列数

               for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
               {
                   NPOI.SS.UserModel.ICell cell = firstRow.GetCell(i);
                   if (cell != null)
                   {
                       string cellValue = cell.StringCellValue;
                       if (cellValue != null)
                       {
                           if (dt.Columns.IndexOf(cellValue) > 0)              //是否有重复列名
                           {
                               System.Data.DataColumn column = new System.Data.DataColumn(Convert.ToString("重复列名" + cellValue + i));
                               dt.Columns.Add(column);
                           }
                           else
                           {
                               System.Data.DataColumn column = new System.Data.DataColumn(cellValue);
                               dt.Columns.Add(column);
                           }
                       }
                   }
               }
               startRow = startRow + 1;

               int rowCount = sheet.LastRowNum;      //总行数 
               //填充行
               for (int i = startRow; i <= rowCount; ++i)
               {
                   NPOI.SS.UserModel.IRow row = sheet.GetRow(i);
                   if (row == null) continue; //没有数据的行默认是null

                   System.Data.DataRow dataRow = dt.NewRow();
                   for (int j = row.FirstCellNum; j < cellCount; ++j)
                   {
                       NPOI.SS.UserModel.ICell cell = row.GetCell(j);
                       if (cell != null)        //同理，没有数据的单元格都默认是null
                       {
                           if (row.GetCell(j).CellType == NPOI.SS.UserModel.CellType.FORMULA)   //是否为公式
                           {
                               try
                               {
                                   dataRow[j] = cell.StringCellValue;                              
                               }
                               catch
                               {
                                   if (NPOI.SS.UserModel.DateUtil.IsCellDateFormatted(cell))   //是否是日期
                                   {
                                       dataRow[j] = cell.DateCellValue;
                                   }
                                   else { dataRow[j] = cell.NumericCellValue; }
                               }
                           }
                           else { dataRow[j] = row.GetCell(j).ToString(); }
                       }
                   }
                   dt.Rows.Add(dataRow);
               }
               fs.Close();
               return dt;
           }
           catch (Exception ex)
           {
               if (fs != null)
               {
                   fs.Close();
               }
               throw new Exception(ex.Message);
           }
       }

 

测试读取1000*23Excel数据耗时2.5秒左右

 

写入Excel

       在Office2007版使用AutoSizeColumn(int列号)时会报错。就用了NPOI自身自带为列宽指定宽度函数SetColumnWidth(int 列号，int宽度)设置了固定宽度。也可以遍历一下每一列的文字，求出文字的宽度，算出这一列的最大值，重新设置本列的宽度即可，跟自适应列宽的效果是一样的。

       /// <summary>
       /// NPOI将datatable写入Excel
       /// </summary>
       /// <param name="data">DataTable</param>
       /// <param name="fileName">路径</param>
       /// <param name="isColumnWritten">是否写入列名</param>
       public static void NPDataTableToExcel(System.Data.DataTable data, string fileName, bool isColumnWritten)
       {
           NPOI.SS.UserModel.IWorkbook workbook = null;
           NPOI.SS.UserModel.ISheet sheet = null;

           FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
           if (fileName.IndexOf(".xlsx") > 0) // 2007版本
               workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
           else if (fileName.IndexOf(".xls") > 0) // 2003版本
               workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
           int rowIndex = 0;     //行号
           try
           {
               sheet = workbook.CreateSheet(data.TableName);

               if (isColumnWritten == true) //写入DataTable的列名
               {
                   NPOI.SS.UserModel.IRow row = sheet.CreateRow(rowIndex);

                   #region 设置样式
                   row.HeightInPoints = 25;  //行高

                   NPOI.SS.UserModel.ICellStyle headStyle = workbook.CreateCellStyle();        //创建样式对象 
                   headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;                 //水平居中
                   headStyle.FillForegroundColor = new NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT().GetIndex();  //具体数字代表的颜色看NPOI颜色对照表,全部在HSSFColor下面
                   headStyle.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND;  //前景色
                   headStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;     //单元格边框类型
                   headStyle.BottomBorderColor = new NPOI.HSSF.Util.HSSFColor.BLUE().GetIndex();  //单元格边框颜色

                   NPOI.SS.UserModel.IFont font = workbook.CreateFont();                    //创建一个字体样式对象
                   font.FontHeightInPoints = 12;   //字体大小
                   font.IsItalic = false;      //斜体
                   //font.Boldweight = short.MaxValue;   //字体加粗
                   font.Color = new NPOI.HSSF.Util.HSSFColor.BLACK().GetIndex();//颜色参考NPOI的颜色对照表(替换掉PINK())
                   headStyle.SetFont(font);   //将字体样式赋给样式对象
                   #endregion 

                   for (int j = 0; j < data.Columns.Count; ++j)
                   {
                       row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
                       row.GetCell(j).CellStyle = headStyle;
                   }
                   rowIndex++;
               }

               // handling value.            
               for (int i = 0; i < data.Rows.Count; ++i)
               {
                   NPOI.SS.UserModel.IRow row = sheet.CreateRow(rowIndex);
                   for (int j = 0; j < data.Columns.Count; ++j)
                   {
                       string a = data.Rows[i][j].ToString();
                       row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
                   }
                   rowIndex++;
               }
               
               for (int cl = 0; cl < data.Columns.Count; cl++)
               {
                   //sheet.AutoSizeColumn(cl);               //自适应列宽,会报错
                   sheet.SetColumnWidth(cl, 13 * 256);  //列宽为13 (注意的是列的宽度为 列宽*256)
               }
               //CellRangeAddress四个参数为：起始行，结束行，起始列，结束列，单元格合并后，样式以左上角的单元格为准
               //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 10));    //合并单元格
              
               workbook.Write(fs);                       //向打开的这个文件中写入数据 
           }
           catch (Exception ex)
           {
               throw new Exception(ex.Message);
           }
           finally
           {
               if (fs != null)
                   fs.Close();
           }
       }

调整列宽的代码：

    //获取当前列的宽度，然后对比本列的长度，取最大值  
               for (int columnNum = 0; columnNum <= data.Columns.Count; columnNum++)
               {
                   int columnWidth = sheet.GetColumnWidth(columnNum) / 256;
                   for (int rowNum = 0; rowNum <= sheet.LastRowNum; rowNum++)
                   {
                       NPOI.SS.UserModel.IRow currentRow;
                       //当前行未被使用过  
                       if (sheet.GetRow(rowNum) == null)
                       {
                           currentRow = sheet.CreateRow(rowNum);
                       }
                       else
                       {
                           currentRow = sheet.GetRow(rowNum);
                       }

                       if (currentRow.GetCell(columnNum) != null)
                       {
                           NPOI.SS.UserModel.ICell currentCell = currentRow.GetCell(columnNum);
                           int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;
                           if (columnWidth < length)
                           {
                               columnWidth = length;
                           }
                       }
                   }
                   sheet.SetColumnWidth(columnNum, columnWidth * 256);
               }

测试写入24000*23的数据Ofice97-2003版耗时2秒左右，Office2007版耗时1分多钟，未加上调整列宽。