[[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; 
          } 
      }