C# NPOI 操作EXCEL文件的讀取和導出

在實際項目中有很多場景遇到需要操作EXCEL文件，而常用到的庫就有NPOI；NPOI是開源的POI 項目的.NET版，POI是一個開源的Java讀寫Excel、WORD等微軟OLE2組件文檔的項目, 使用 NPOI 你就可以在沒有安裝 Office 或者相應環境的機器上對 WORD/EXCEL 文檔進行讀寫。在處理Excel文件上，NPOI 可以同時兼容 xls 和 xlsx。

程序集構成

一、引用

在NuGet裡搜索NPOI，選擇下圖的項目安裝即可；

二、程序示例：

部分代碼如下：

（1）導出EXCEL代碼

<code> public void Export_Excel(string path)
  {   
     string extension= Path.GetExtension(path).ToLower();
     IWorkbook workbook;
     if (extension==".xlsx")
     {
         workbook = new XSSFWorkbook();
      }
      else
       {
          workbook = new HSSFWorkbook();
       }
      ISheet sheet = workbook.CreateSheet("表1");
      using(FileStream fs=new FileStream(path,FileMode.Create,FileAccess.ReadWrite))
      {
         IRow row_Head = sheet.CreateRow(0);
         for (int i = 0; i < dataGridView1.Columns.Count; i++)
          {
             ICell cell = row_Head.CreateCell(i);
             cell.SetCellValue(dataGridView1.Columns[i].HeaderText);
         }
         for (int i = 0; i < dataGridView1.Rows.Count; i++)
          {
             IRow row1 = sheet.CreateRow(i + 1);
             for (int j = 0; j < dataGridView1.Columns.Count; j++)
             {
                ICell cell = row1.CreateCell(j);
                if(dataGridView1.Rows[i].Cells[j].Value==null)
                      cell.SetCellValue("");
                else
                      cell.SetCellValue(dataGridView1.Rows[i].Cells[j].Value.ToString());
                    }
                }
      workbook.Write(fs);
       workbook.Close();
     }  
 }/<code>

（2）讀取EXCEL文件代碼

<code>public void Import_Excel(string path)
        {
            DataTable dt = new DataTable();
            using (FileStream fsRead = new FileStream(path, FileMode.Open, FileAccess.Read))
            {
                string extension = Path.GetExtension(path).ToLower();
                IWorkbook workbook;
                if (extension == ".xlsx")
                {
                    workbook = new XSSFWorkbook(fsRead);
                }
                else
                {
                    workbook = new HSSFWorkbook(fsRead);
                }
                ISheet sheet = workbook.GetSheetAt(0);
                IRow currentRow = sheet.GetRow(0);
                //最後一個方格的編號 即總的列數
                int cellCount = currentRow.LastCellNum;
                dataGridView1.DataSource = null;
                for (int i = 0; i  
< cellCount; i++)
                {
                    //DataColumn dc = new DataColumn();
                    dt.Columns.Add( currentRow.GetCell(i).ToString());//增加列
                }
                for (int j = 0; j <= sheet.LastRowNum - 1; j++)
                {
                    
                    dt.Rows.Add();//增加行
                    IRow currentRow1 = sheet.GetRow(j + 1);
                    if (currentRow1 != null)
                    {
                        for (int c = 0; c < currentRow1.LastCellNum; c++)
                        {
                            ICell cell = currentRow1.GetCell(c);
                            if (cell == null || cell.CellType == CellType.Blank)
                            {
                                dt.Rows[j][c] = DBNull.Value;
                            }
                            else
                            {
                                //如果當前單元格不為空，則根據單元格的類型獲取數據
                                switch (cell.CellType)
                                {
                                    //只有當單元格的數據類型是數字類型的時候使用cell.NumericCellValue來獲取值。其餘類型都使用字符串來獲取.日期類型數據插入單元格後也是CellType.NUMERIC
                                    case CellType.Numeric:
                                        //cell.NumericCellValue;
                                        dt.Rows[j][c] = cell.NumericCellValue;
                                        break;
                                    default:
                                        //cell.ToString();
                                        dt.Rows[j][c] = cell.ToString();
                                        break;
                                }
                            }
                        }
                    }
                }
                dataGridView1.DataSource = dt;
                workbook.Close();
            }
        }/<code>

三、感謝您的閱讀，完整代碼已上傳，關注點贊後發送私信“NPOI”即可獲取。