C# 使用 Npoi 操作Excel文件，你会了吗？

 [[437708]]

本文转载自微信公众号「后端Q」，作者conan。转载本文请联系后端Q公众号。

什么是NPOI

What’s NPOI This project is the .NET version of POI Java project at http://poi.apache.org/. POI is an open source project which can help you read/write xls, doc, ppt files. It has a wide application. For example, you can use it to a. generate a Excel report without Microsoft Office suite installed on your server and more efficient than call Microsoft Excel ActiveX at background; b. extract text from Office documents to help you implement full-text indexing feature (most of time this feature is used to create search engines). c. extract images from Office documents d. generate Excel sheets that contains formulas

在没有安装Microsoft Office Excel的机子上也可以对Excel进行操作。另外一种方法是使用.NET自带的excel API，但是这种方法需要运行环境安装微软的excel才行。

C#使用NPOI操作excel

将DataTable数据导入到excel中

/// <summary> 
      /// 将DataTable数据导入到excel中 
      /// </summary> 
      /// <param name="data">要导入的数据</param> 
      /// <param name="isColumnWritten">DataTable的列名是否要导入</param> 
      /// <param name="sheetName">要导入的excel的sheet的名称</param> 
      /// <returns>导入数据行数(包含列名那一行)</returns> 
      public int DataTableToExcel(System.Data.DataTable data, string sheetName, bool isColumnWritten) 
      { 
          int i = 0; 
          int j = 0; 
          int count = 0; 
          ISheet sheet = null; 
 
          try 
          { 
              fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite); 
              if (fileName.IndexOf(".xls") > 0) // 2003版本 
                  workbook = new HSSFWorkbook(); 
 
              if (workbook != null) 
              { 
                  sheet = workbook.CreateSheet(sheetName); 
              } 
              else 
              { 
                  return -1; 
              } 
 
              if (isColumnWritten == true) //写入DataTable的列名 
              { 
                  IRow row = sheet.CreateRow(0); 
                  for (j = 0; j < data.Columns.Count; ++j) 
                  { 
                      row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName); 
                  } 
                  count = 1; 
              } 
              else 
              { 
                  count = 0; 
              } 
 
              for (i = 0; i < data.Rows.Count; ++i) 
              { 
                  IRow row = sheet.CreateRow(count); 
                  for (j = 0; j < data.Columns.Count; ++j) 
                  { 
                      row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString()); 
                  } 
                  ++count; 
              } 
              workbook.Write(fs); //写入到excel 
              return count; 
          } 
          catch (Exception ex) 
          { 
              Console.WriteLine("Exception: " + ex.Message); 
              return -1; 
          } 
          finally 
          { 
              fs?.Close(); 
          } 
      } 

将excel中的数据导入到DataTable中

/// <summary> 
      /// 将excel中的数据导入到DataTable中 
      /// </summary> 
      /// <param name="sheetName">excel工作薄sheet的名称</param> 
      /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param> 
      /// <returns>返回的DataTable</returns> 
      public System.Data.DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn) 
      { 
          ISheet sheet = null; 
          var data = new System.Data.DataTable(); 
          int startRow = 0; 
          try 
          { 
              fs = new FileStream(fileName, FileMode.Open, FileAccess.Read); 
              if (fileName.IndexOf(".xls") > 0) // 2003版本 
                  workbook = new HSSFWorkbook(fs); 
 
              if (sheetName != null) 
              { 
                  sheet = workbook.GetSheet(sheetName); 
                  if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet 
                  { 
                      sheet = workbook.GetSheetAt(0); 
                  } 
              } 
              else 
              { 
                  sheet = workbook.GetSheetAt(0); 
              } 
              if (sheet != null) 
              { 
                  IRow firstRow = sheet.GetRow(0); 
                  int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数 
                  for (int i = 0; i < cellCount; ++i) 
                  { 
                      var column = new System.Data.DataColumn("column" + i); 
                      data.Columns.Add(column); 
                  } 
                  startRow = sheet.FirstRowNum; 
                  //最后一列的标号 
                  int rowCount = sheet.LastRowNum; 
                  for (int i = startRow; i <= rowCount; ++i) 
                  { 
                      IRow row = sheet.GetRow(i); 
                      if (row == null) continue; //没有数据的行默认是null        
 
                      var dataRow = data.NewRow(); 
                      for (int j = row.FirstCellNum; j < cellCount; ++j) 
                      { 
                          if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null 
                              dataRow[j] = row.GetCell(j).ToString(); 
                      } 
                      data.Rows.Add(dataRow); 
                  } 
              } 
 
              return data; 
          } 
          catch (Exception ex) 
          { 
              Console.WriteLine("Exception: " + ex.Message); 
              return null; 
          } 
      }