[[437708]]

本文轉(zhuǎn)載自微信公眾號(hào)「后端Q」，作者conan。轉(zhuǎn)載本文請(qǐng)聯(lián)系后端Q公眾號(hào)。

什么是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的機(jī)子上也可以對(duì)Excel進(jìn)行操作。另外一種方法是使用.NET自帶的excel API，但是這種方法需要運(yùn)行環(huán)境安裝微軟的excel才行。

C#使用NPOI操作excel

將DataTable數(shù)據(jù)導(dǎo)入到excel中

/// <summary> 
      /// 將DataTable數(shù)據(jù)導(dǎo)入到excel中 
      /// </summary> 
      /// <param name="data">要導(dǎo)入的數(shù)據(jù)</param> 
      /// <param name="isColumnWritten">DataTable的列名是否要導(dǎo)入</param> 
      /// <param name="sheetName">要導(dǎo)入的excel的sheet的名稱</param> 
      /// <returns>導(dǎo)入數(shù)據(jù)行數(shù)(包含列名那一行)</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中的數(shù)據(jù)導(dǎo)入到DataTable中

/// <summary> 
      /// 將excel中的數(shù)據(jù)導(dǎo)入到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對(duì)應(yīng)的sheet，則嘗試獲取第一個(gè)sheet 
                  { 
                      sheet = workbook.GetSheetAt(0); 
                  } 
              } 
              else 
              { 
                  sheet = workbook.GetSheetAt(0); 
              } 
              if (sheet != null) 
              { 
                  IRow firstRow = sheet.GetRow(0); 
                  int cellCount = firstRow.LastCellNum; //一行最后一個(gè)cell的編號(hào) 即總的列數(shù) 
                  for (int i = 0; i < cellCount; ++i) 
                  { 
                      var column = new System.Data.DataColumn("column" + i); 
                      data.Columns.Add(column); 
                  } 
                  startRow = sheet.FirstRowNum; 
                  //最后一列的標(biāo)號(hào) 
                  int rowCount = sheet.LastRowNum; 
                  for (int i = startRow; i <= rowCount; ++i) 
                  { 
                      IRow row = sheet.GetRow(i); 
                      if (row == null) continue; //沒有數(shù)據(jù)的行默認(rèn)是null        
 
                      var dataRow = data.NewRow(); 
                      for (int j = row.FirstCellNum; j < cellCount; ++j) 
                      { 
                          if (row.GetCell(j) != null) //同理，沒有數(shù)據(jù)的單元格都默認(rèn)是null 
                              dataRow[j] = row.GetCell(j).ToString(); 
                      } 
                      data.Rows.Add(dataRow); 
                  } 
              } 
 
              return data; 
          } 
          catch (Exception ex) 
          { 
              Console.WriteLine("Exception: " + ex.Message); 
              return null; 
          } 
      }