[NuGet]NPOI初探

從googlesite中搬過來的, 與其說介紹文, 比較像是個人「筆記」之類的存在
公司excel檔案都改成xlsx後, XLS相關就不再使用了
並沒有全面的介紹NPOI, 如果打算深入NPOI, 可以直接參考最底下的外部來源 

另一個與NPOI類似用途的工具：OpenXml 介紹

---

簡介 / 安裝方法

【簡介】
可以操作excel檔案的

Microsoft Developer Network > 學習園地 > CodePlex 教學

在 Server 端存取 Excel 檔案的利器：NPOI Library

【安裝NPOI】

於VS中→右鍵網站或專案→管理NuGet套件→搜尋NOPI→安裝→完成

(沒錯, 就是這麼簡單)

---

XLS範例

使用的NPOI版本 v2.1.3.1 (供參考, 舊版本可能有部分程式碼不相容)

引入的參考

1. using System.IO;  
2. using NPOI.HSSF.UserModel;  
3. using NPOI.HSSF.Util;  
4. using NPOI.SS.UserModel;  

檔案IO

建立一個新活頁簿

1. //建立物件  
2. HSSFWorkbook workbook = new HSSFWorkbook();  
3. workbook.CreateSheet("試算表A");  
4.   
5. //將物件寫到記憶體並產生另存  
6. MemoryStream ms = new MemoryStream();  
7. workbook.Write(ms);  
8. Response.AddHeader("Content-Disposition", string.Format("attchment; filename=EmptyWorkbook.xls"));   
9. Response.BinaryWrite(ms.ToArray());  
10.   
11. //清空  
12. workbook = null;  
13. ms.Close();  
14. ms.Dispose();  

取出Server中已存在的活頁簿

1. string strFilePath = string.Format(Server.MapPath(".") + "\\format_eCRF\\EmptyWorkbook.xls"); //抓取ASP.NET網頁的程式位址    
2. HSSFWorkbook workbook;    
3. using (FileStream fs = new FileStream(strFilePath, FileMode.Open, FileAccess.ReadWrite))    
4. {    
5.     workbook = new HSSFWorkbook(fs);    
6. }  
7.   
8. /*--IT'S SHOW TIME!--*/  
9.     
10. //定番    
11. MemoryStream ms = new MemoryStream();    
12. workbook.Write(ms);    
13. Response.AddHeader("Content-Disposition", string.Format("attchment; filename=EmptyWorkbook.xls"));    
14. Response.BinaryWrite(ms.ToArray());    
15.     
16. workbook = null;    
17. ms.Close();    
18. ms.Dispose();  

cell操作 

建立cell

可以用CreateSheet / CreateRow / CreateCell串燒

(範例待續, 請先參考下方)

找尋目標cell

可以用GetSheet / GetSheetAt / GetRow / GetCell串燒到目標cell
(範例待續, 請先參考下方)


填入值

再用SetCellValue(value)，可容許相當多種type

1. //插入資料。  
2. HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("My Sheet"); //新建資料表同時插入  
3. sheet.CreateRow(0).CreateCell(0).SetCellValue(0);  
4. HSSFSheet at_sheet = (HSSFSheet)workbook.GetSheetAt(0);//用索引取得資料表並插入  
5. at_sheet.CreateRow(0).CreateCell(0).SetCellValue("at sheet");  
6. HSSFSheet _sheet = (HSSFSheet)workbook.GetSheet("試算表 A");   //用資料表名取得資料表並插入  
7. _sheet.GetRow(0).GetCell(0).SetCellValue("get sheet by name"); 

欄位寬度

利用SetColumnWidth(第n攔, 寬度多少)

1. workbook.GetSheetAt(0).SetColumnWidth(3, 2048);  

幫cell上色

物件HSSFCellStyle中的各項方法

1. //建立儲存格樣式  
2. HSSFSheet teststyle=(HSSFSheet)workbook.CreateSheet("sheet1");  
3. HSSFCellStyle style1 = (HSSFCellStyle)workbook.CreateCellStyle();  
4. style1.FillForegroundColor = HSSFColor.Pink.Index;  
5. style1.FillPattern = FillPattern.SolidForeground; //版本2.0的操作方法  
6.   
7. HSSFCell cell = (HSSFCell)teststyle.GetRow(0).GetCell(0);  
8. cell.CellStyle = style1;  

函數應用

在cell中產生與欄位相呼應的值

利用LastRowNum和LastCellNum取得陣列範圍

1. for (int i = 0; i < workbook.GetSheetAt(0).LastRowNum ; i++)  
2. {  
3.     int n = 65;  
4.     for (int j = 0; j < workbook.GetSheetAt(0).GetRow(1).LastCellNum; j++)  
5.     {  
6.         workbook.GetSheetAt(0).GetRow(i).GetCell(j).SetCellValue(Convert.ToChar(n + j).ToString() + (i+1));  
7.     }  
8. }  

---

Q&A

有些程式碼無法起作用

如果範例是以2.0以前的版本去實作，可能會遇到這些問題

【需要強制轉型】HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("My Sheet");

【變更調用方法】style1.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;

建議下載官方範本去看該如何在C#中使用

CreateRow 和 GetRow 有何差異

相CreateRow想像成初始化Row會更容易理解

同：Sheet層的設定維持不變, 會在同一列(不會產生插入列的效果)

異：前者移除style後者保留, 前者Cell需要重新create cell後者維持

---

XLSX範例

基本上跟XLS很像, 遇到特別的再說

讀取樣板, 並另存新檔

只是能用而不太講究的話, 將HSSF全部改成XSSF就可以了

缺點是開啟檔案時, 會彈出警告視窗

參考官方範例並加以修改後(改成檔案是在記憶體中產生, 不留檔), 只要將下面這段

  Response.AddHeader("Content-Disposition", string.Format("attchment; filename=EmptyWorkbook.xlsx"));

  Response.BinaryWrite(ms.ToArray());

  workbook = null;

  ms.Close();

  ms.Dispose();

替換成這段就ok了(前半仍需完成HSSF替換成XSSF)

  Response.Clear();

  Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

  Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "editedWorkbook.xlsx"));

  Response.BinaryWrite(ms.ToArray());

  Response.Flush();

  Response.End();

將DataSet的資料用NPOI倒入Excel

如果有要特定格式, 可善用「string.format()」

例如日期：

• 將「SetCellValue(column.ColumnName);」
• 替換成「SetCellValue(string.Format("{0:yyyy/MM/dd HH:mm:ss}", row[column]));」

DataSet相關請參考：DataSet.Tables 屬性

/*--此處開始編輯檔案*/

  XSSFSheet sheet = (XSSFSheet)workbook.GetSheetAt(2);

  for (int i = 0; i <= 2;i++ )

  {

      int rowIndex = i*3;

      

      dt = ds.Tables.Count > 0 ? ds.Tables[i] : new DataTable();

      XSSFRow headerRow = (XSSFRow)sheet.CreateRow(rowIndex);

      foreach (DataColumn column in dt.Columns)

    headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);

      foreach (DataRow row in dt.Rows)

      {

    XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex+1);

    foreach (DataColumn column in dt.Columns)

    {

        dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());

    }

    rowIndex++;

      }

  }

---

外部資料：

E.K 開發紀事 [C#][NPOI系列]

• 用NPOI讀取Excel資料
• 用NPOI匯出EXCEL檔案並讓EXCEL顯示樞紐分析報表
• 匯出EXCEL檔案有欄位篩選功能及自動調整欄位寬度
• 匯出EXCEL檔案能垂直合併欄位
• 搬移資料列和Copy Sheet資料
• 用NPOI作分割視窗與凍結視窗
• 在欄位中放入公式自動去計算結果

饅頭小舖【ASP.NET MVC】

• 在您的網站上加入匯入或匯出 Excel 功能(一)
• 在您的網站上加入匯入或匯出 Excel 功能(二)

林大貓

• excel讀寫使用NPOI Library --(2.0之後的版本)