Reference：http://msdn.microsoft.com/zh-tw/ee818993.aspx

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

Codeplex 軟體套件（Package）資訊
套件名稱	NPOI
作者	tonyqus, huseyin, atao
目前版本	1.2.1 Stable（正式版）
URL	http://npoi.codeplex.com/
使用難易度	易
使用此套件時可用的輔助工具	Visual Studio 2008 POIFS Browser 1.2
基礎知識	基本物件導向。 Excel 基礎操作。

在 Server 端控制 Excel 的難處

在今日 Microsoft Excel 被廣為業界接受之際，Excel 已幾乎是每個人必會的工具，不論是在校園或是職場，接觸到 Excel 的機率很高，而且 Excel 靠著簡單易用以及高度容錯的能力，讓使用者可以近乎無痛的操控 Excel，它內建的強大試算以及資料整理的功能，也讓很多使用者樂於使用它，這一點由 Excel 的高市佔率得以印證，用 Excel 來整理與包裝資料已經是司空見慣的事，因此很多的使用者會利用它來處理日常的業務資料或是基本檔等等，不過這可就苦了 IT 人員了。

在論壇上經常會看到一種需求，因為使用者不論如何都要用 Excel 檔來放資料，所以總是要求 IT 人員在系統中直接產出 Excel 檔案直接下載給使用者，目前由官方公布，由伺服器端存取 Excel 表格兩種方法：

使用 Excel 物件模型來存取

這是官方公布最正統的方法，直接將 Excel 物件模型加入專案參考，並使用 COM 的方式來呼叫內含在 Excel 物件庫中的物件，像是 Workbook、Worksheet、Range、Formula、Row、Cell 等等，它的好處是可以精確的控制 Excel 檔案中的各種屬性（儲存格格式、樣式、資料、公式以及條件等等），輸出的檔案也絕對是最正確的 Excel 資料檔，不過它卻有下列缺點：
- 物件模型複雜不易學習。
  在 Excel 物件庫中，擁有數百種物件以及數以百計的列舉常數值，每個物件之間又和數個不同的物件有交互關係，在程式的操控上相對不容易。
- 無法使用資料流方式控制 Excel 檔案。
  Excel 物件模型是基於 EXCEL.exe（Excel 的執行檔）來存取檔案的內容，物件顯露出來的讀取方法只有實體檔案的方式，而無法使用資料流（如 MemoryStream），這會迫使開發人員必須要另外處理產生的實體檔案，對開發人員來說並不方便。同時實體檔案的作業系統 I/O 功能，也會涉及到權限控制的問題。
- 以單機為基礎的執行引擎。
  Excel.exe 本身並不是針對網路環境來設計的，因此它基本上並不支援多人操作與共享的應用程式（簡言之，就是 Web 應用程式），因此經常會在論壇上看到諸如『為什麼 Excel 釋放不掉』的問題，其根本原因就是 Excel 本身的行程特性，在多人產生執行個體以及多行程鎖定的情況下，Excel 行程會被佔住無法釋放，無法放掉的行程愈多，對伺服器的記憶體就愈傷，甚至導致伺服器不穩定。
NOTE

Excel 物件模型可以在 MSDN Excel Developer Center 中找到：
http://msdn.microsoft.com/en-us/office/aa905411.aspx

NOTE

微軟官方並不建議在伺服器端使用直接存取 Excel 物件模型的方式來控制 Excel 檔案，除了上述的資源無法釋放的問題外，還有像是權限的問題，以及安全性問題等等，詳細的資料請參考：
http://support.microsoft.com/default.aspx/kb/257757
使用 OLE DB Provider for Jet 來存取

這是控制 Excel 檔案的另一種作法，經由 Microsoft Jet OLE DB Provider 資料庫引擎來存取，Jet 引擎可以支援多種以檔案為主的資料庫（file-based database），像是 Access、dBase 等等，以 SQL 指令為主的存取能力，在 Excel 上也可以實現，開發人員可以不用特別熟悉 Excel 物件模型，就可以控制 Excel 檔案的內容，不過它也不是一個好的伺服器端 Excel 解決方案，因為：
- 不支援資料流存取。
  Jet 引擎和 Excel.exe 一樣，也只能使用實體檔案，無法使用資料流存取，因此也是要由開發人員自行管理使用過的檔案，若疏於管理的話，會讓伺服器上充斥許多的無用檔案。
- 控制資料的程度有限。
  Jet 引擎雖然可以存取 Excel 檔案內容，但它畢竟不是 Excel 物件模型本身，對於 Excel 的控制無法做到跟物件模型完全相同的能力，而且也受限於 Jet 所支援的 SQL 指令，對於 Excel 檔案只能使用 SQL 指令來操控，因此像是樣式、條件以及高度依賴物件模型的功能，都沒有辦法被 Jet 控制。

由於官方本身所提供的 Excel 資料檔控制的方法基本上不支援伺服端的處理，因此開始有很多第三方軟體廠商開始發展不需要 Excel 就可以處理的方法，像是 Aspose 的 Excel Library（Aspose.Cells）、或是在 ComponentSource 中可以找到的許多元件，多數都可以在不需要安裝 Excel 在伺服端的情況下就可以存取 Excel 檔案，不過這些都是要 $$ 的，所以有部份開發人員（包含開放原始碼陣營）亦發展出一些免費且開放原始碼的套件，像是 ExcelLibrary（on Google code）以及 Java 陣營開發的 Apache POI 專案（Apache POI Project）。

NOTE

Apache POI 專案是為 Java 所設計可支援 Office 系列檔案的存取類別庫，目前已經可以支援到 Excel 97-2003 XLS 與最新的 Excel 2007 XLSX 格式等，可至 Apache POI 專案網站參考它的功能： http://poi.apache.org

在 .NET 上的 POI：NPOI

拜 Apache 的 POI 專案之賜，Java 的開發人員可以輕鬆的存取 Excel 檔案，而反觀 .NET 陣營幾乎只能在 Excel 物件模型以及 Jet 資料庫引擎中打轉，對於 .NET 陣營本身的開發人員似乎也不太公平，所以有幾位佛心來的開發者另外開發可直接存取 Excel 的函式庫，或是將 Java 中好用的函式庫移植到 .NET 環境來，POI 專案就是一例，在 .NET 上被稱為 NPOI。

POI 專案本身是處理 Office 檔案的函式庫，包含 Word、Excel、PowerPoint、Outlook、Visio、Publisher 等檔案，這些檔案都有一個共通的特性，就是它們都是微軟發展的 OLE Compound Document（複合文件），以 OLE Structured Storage（結構化儲存）格式儲存在檔案中，OLE 規範（以及處理 OLE API 呼叫等）對一般的開發人員來說是有相當的難度，因此利用 Excel 本身的物件模型是最容易的一件事。但 POI 專案並沒有使用到 Excel 的任何東西，它直接深入 OLE Compound Document 格式內去存取資料，也可以直接控制到各種儲存格的資訊（顏色，儲存格格式與樣式等），並將它物件導向化，外部開發人員只需要利用 POI 提供的屬性就可以控制 Office 格式的檔案資料。

NOTE

OLE Compound Document 是一種檔案儲存的格式，它是植基在 OLE 結構化儲存（Structured Storage）的基礎上，可以在同一個檔案資料流中儲存多種資料格式，以 Excel 為例，它可以同時儲存試算表（Spreadsheet）、圖表（Chart）、樣式（Style）、圖片（Pictures）以及方程式（Equation）等不同型式的資料，這些不同型式的資料都是由一組獨立格式的 CLSID 識別，再由 CLSID 在檔案區段中找出不同的 CLSID 儲存區，再深入儲存區讀出資料流，即可取回指定的資料。

圖：OLE複合文件結構（來源： http://msdn.microsoft.com/en-us/library/dd942415(PROT.10).aspx）

如果讀者對 Excel 檔案的實際內容有興趣，可以在 NPOI 網站中下載 POIFS Explorer，並用它開啟 Excel 檔案，就可以看到 Excel 檔案的實際組成：

圖：POIFS Explorer

NPOI 函式庫

NPOI 函式庫可以在 http://npoi.codeplex.com 中下載，目前的版本為 1.2.1，有分為 .NET 1.1 與 .NET 2.0 以上版本兩種，支援主要的 POI 專案提供的功能，但專案中的範例程式碼都是以 Excel 為標的，原因應該是 Excel 在伺服器端的處理遠比 Word 和 PowerPoint 等文件要多太多了，故筆者在本篇文章也是以 Excel 檔案為主要說明的標的。

NPOI 函式庫檔案有七個，分別是：

NPOI.DLL：NPOI 核心函式庫。
NPOI.DDF.DLL：NPOI 繪圖區讀寫函式庫。
NPOI.HPSF.DLL：NPOI 文件摘要資訊讀寫函式庫。
NPOI.HSSF.DLL：NPOI Excel BIFF 檔案讀寫函式庫。
NPOI.Util.DLL：NPOI 工具函式庫。
NPOI.POIFS.DLL：NPOI OLE 格式存取函式庫。
ICSharpCode.SharpZipLib.DLL：檔案壓縮函式庫。

一般需要存取 Excel 97-2003 格式（.xls）的檔案時，需要使用 NPOI、NPOI.HSSF、NPOI.POIFS 與 NPOI.Util 函式庫，因此專案中要引用這四個 DLL，若要一併存取文件摘要資訊時，則也要引用 NPOI.HPSF.DLL 檔案，以取得必要的類別宣告。開發人員通常只要集中精神在 NPOI.HSSF.UserModel 命名空間即可，它包含了控制 Excel 資料的各式類別物件供開發人員取用。

例如下列的 ASP.NET 程式碼可以生成一個空白的 Excel 檔案，並且添加三個指定名稱的試算表：

[C#]


HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
 
// 新增試算表。
workbook.CreateSheet("試算表 A");
workbook.CreateSheet("試算表 B");
workbook.CreateSheet("試算表 C");
 
workbook.Write(ms);
Response.AddHeader("Content-Disposition", string.Format("attachment; filename=EmptyWorkbook.xls"));
Response.BinaryWrite(ms.ToArray());
 
workbook = null;
ms.Close();
ms.Dispose();

其執行結果就有如一般的檔案下載般，不過它的資料卻是一個完整的 Excel 資料檔：

將它用 Excel 打開來看，可以看到它的內容確實是以指定的試算表名稱所建立：

再試一些程式，我們可以在裡面添加資料，例如下列的程式碼：

[C#]


HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
 
// 新增試算表。
HSSFSheet sheet = workbook.CreateSheet("My Sheet");
// 插入資料值。
sheet.CreateRow(0).CreateCell(0).SetCellValue("0");
sheet.CreateRow(1).CreateCell(0).SetCellValue("1");
sheet.CreateRow(2).CreateCell(0).SetCellValue("2");
sheet.CreateRow(3).CreateCell(0).SetCellValue("3");
sheet.CreateRow(4).CreateCell(0).SetCellValue("4");
sheet.CreateRow(5).CreateCell(0).SetCellValue("5");
 
workbook.Write(ms);
Response.AddHeader("Content-Disposition", string.Format("attachment; filename=EmptyWorkbook.xls"));
Response.BinaryWrite(ms.ToArray());
 
workbook = null;
ms.Close();
ms.Dispose();

將它下載下來，用 Excel 開啟，即可看到插入的資料值：

這樣還不夠，我們再設定一些東西，例如設定儲存格的背景色：

[C#]


HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
 
// 新增試算表。
HSSFSheet sheet = workbook.CreateSheet("My Sheet");
// 建立儲存格樣式。
HSSFCellStyle style1 = workbook.CreateCellStyle();
style1.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.BLUE.index2;
style1.FillPattern = HSSFCellStyle.SOLID_FOREGROUND;
HSSFCellStyle style2 = workbook.CreateCellStyle();
style2.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.YELLOW.index2;
style2.FillPattern = HSSFCellStyle.SOLID_FOREGROUND;
// 設定儲存格樣式與資料。
HSSFCell cell = sheet.CreateRow(0).CreateCell(0);
cell.CellStyle = style1;
cell.SetCellValue(0);
 
cell = sheet.CreateRow(1).CreateCell(0);
cell.CellStyle = style2;
cell.SetCellValue(1);
 
cell = sheet.CreateRow(2).CreateCell(0);
cell.CellStyle = style1;
cell.SetCellValue(2);
 
cell = sheet.CreateRow(3).CreateCell(0);
cell.CellStyle = style2;
cell.SetCellValue(3);
 
cell = sheet.CreateRow(4).CreateCell(0);
cell.CellStyle = style1;
cell.SetCellValue(4);
 
workbook.Write(ms);
Response.AddHeader("Content-Disposition", string.Format("attachment; filename=EmptyWorkbook.xls"));
Response.BinaryWrite(ms.ToArray());
 
workbook = null;
ms.Close();
ms.Dispose();

將它下載下來，用 Excel 開啟，即可看到設定樣式的試算表：

輸出沒有問題，那麼輸入呢？當然也沒有問題啦。例如目前手上有一個 Datas.xls 資料檔，它的內容是：

然後利用下列的程式碼：

[C#]


if (this.fuUpload.HasFile)
{
    HSSFWorkbook workbook = new HSSFWorkbook(this.fuUpload.FileContent);
    HSSFSheet sheet = workbook.GetSheetAt(0);
 
    DataTable table = new DataTable();
 
    HSSFRow headerRow = sheet.GetRow(0);
    int cellCount = headerRow.LastCellNum;
 
    for (int i = headerRow.FirstCellNum; i < cellCount; i++)
    {
        DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
        table.Columns.Add(column);
    }
 
    int rowCount = sheet.LastRowNum;
 
    for (int i = (sheet.FirstRowNum + 1); i < sheet.LastRowNum; i++)
    {
        HSSFRow row = sheet.GetRow(i);
        DataRow dataRow = table.NewRow();
 
        for (int j = row.FirstCellNum; j < cellCount; j++)
        {
            if (row.GetCell(j) != null)
                dataRow[j] = row.GetCell(j).ToString();
        }
 
        table.Rows.Add(dataRow);
    }
 
    workbook = null;
    sheet = null;
 
    this.gvExcel.DataSource = table;
    this.gvExcel.DataBind();
}

執行結果如下：

實例應用：將 DataTable 和 Excel 檔案間互轉

有了 NPOI 的支持，在伺服端將資料轉換成 Excel 檔案的功能將不再是大問題，也無須再使用匯出 HTML 表格的方式來模擬 Excel 檔案的暫行方案來解決，只要使用 NPOI 就可以得到正規的 Excel 資料檔，筆者也特別撰寫了一個簡單的由 DataTable 物件自動轉成 Excel 資料檔的小程式供讀者自行取用。

[C#]


using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Web;
using NPOI;
using NPOI.HPSF;
using NPOI.HSSF;
using NPOI.HSSF.UserModel;
using NPOI.POIFS;
using NPOI.Util;
 
public class DataTableRenderToExcel 
{
    public static Stream RenderDataTableToExcel(DataTable SourceTable)
    {
        HSSFWorkbook workbook = new HSSFWorkbook();
        MemoryStream ms = new MemoryStream();
        HSSFSheet sheet = workbook.CreateSheet();
        HSSFRow headerRow = sheet.CreateRow(0);
 
        // handling header.
        foreach (DataColumn column in SourceTable.Columns)
            headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
 
        // handling value.
        int rowIndex = 1;
 
        foreach (DataRow row in SourceTable.Rows)
        {
            HSSFRow dataRow = sheet.CreateRow(rowIndex);
 
            foreach (DataColumn column in SourceTable.Columns)
            {
    　　　　　　dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
            }
 
            rowIndex++;
        }
 
        workbook.Write(ms);
        ms.Flush();
        ms.Position = 0;
 
        sheet = null;
        headerRow = null;
        workbook = null;
 
        return ms;
    }
 
    public static void RenderDataTableToExcel(DataTable SourceTable, string FileName)
    {
        MemoryStream ms = RenderDataTableToExcel(SourceTable) as MemoryStream;
        FileStream fs = new FileStream(FileName, FileMode.Create, FileAccess.Write);
        byte[] data = ms.ToArray();
 
        fs.Write(data, 0, data.Length);
        fs.Flush();
        fs.Close();
 
        data = null;
        ms = null;
        fs = null;
    }
 
    public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, string SheetName, int HeaderRowIndex)
    {
        HSSFWorkbook workbook = new HSSFWorkbook(ExcelFileStream);
        HSSFSheet sheet = workbook.GetSheet(SheetName);
 
        DataTable table = new DataTable();
 
        HSSFRow headerRow = sheet.GetRow(HeaderRowIndex);
        int cellCount = headerRow.LastCellNum;
 
        for (int i = headerRow.FirstCellNum; i < cellCount; i++)
        {
            DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
            table.Columns.Add(column);
        }
 
        int rowCount = sheet.LastRowNum;
 
        for (int i = (sheet.FirstRowNum + 1); i < sheet.LastRowNum; i++)
        {
            HSSFRow row = sheet.GetRow(i);
            DataRow dataRow = table.NewRow();
 
            for (int j = row.FirstCellNum; j < cellCount; j++)
                dataRow[j] = row.GetCell(j).ToString();
        }
 
        ExcelFileStream.Close();
        workbook = null;
        sheet = null;
        return table;
    }
 
    public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, int SheetIndex, int HeaderRowIndex)
    {
        HSSFWorkbook workbook = new HSSFWorkbook(ExcelFileStream);
        HSSFSheet sheet = workbook.GetSheetAt(SheetIndex);
 
        DataTable table = new DataTable();
 
        HSSFRow headerRow = sheet.GetRow(HeaderRowIndex);
        int cellCount = headerRow.LastCellNum;
 
        for (int i = headerRow.FirstCellNum; i < cellCount; i++)
        {
            DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
            table.Columns.Add(column);
        }
 
        int rowCount = sheet.LastRowNum;
 
        for (int i = (sheet.FirstRowNum + 1); i < sheet.LastRowNum; i++)
        {
            HSSFRow row = sheet.GetRow(i);
            DataRow dataRow = table.NewRow();
 
            for (int j = row.FirstCellNum; j < cellCount; j++)
            {
                if (row.GetCell(j) != null)
                    dataRow[j] = row.GetCell(j).ToString();
            }
 
            table.Rows.Add(dataRow);
        }
 
        ExcelFileStream.Close();
        workbook = null;
        sheet = null;
        return table;
    }
}

它的呼叫方法很簡單，若是要將 DataTable 輸出到 Excel 檔案，只要將 DataTable 丟給 RenderDataTableToExcel() 方法即可。

[C#]


DataTable table = new DataTable();
 
// 填充資料（由讀者自行撰寫）
 
// 產生 Excel 資料流。
MemoryStream ms = DataTableRenderToExcel.RenderDataTableToExcel(table) as MemoryStream;
// 設定強制下載標頭。
Response.AddHeader("Content-Disposition", string.Format("attachment; filename=Download.xls"));
// 輸出檔案。
Response.BinaryWrite(ms.ToArray());
 
ms.Close();
ms.Dispose();

若是要讀取 Excel 檔案並存到 DataTable，只要設定上傳的 Excel 檔案資料流、試算表索引（或名稱）以及起始列索引值即可：

[C#]


if (this.fuUpload.HasFile)
{
    // 讀取 Excel 資料流並轉換成 DataTable。
    DataTable table = DataTableRenderToExcel.RenderDataTableFromExcel(this.fuUpload.FileContent, 1, 0);
    this.gvExcel.DataSource = table;
    this.gvExcel.DataBind();
}

結語

NPOI 是一個好用又簡單的函式庫，可以幫助開發人員解決長久以來在伺服端的 Excel 檔案產生與存取的問題，它還有很多特別的功能可以利用（像是讀寫摘要資料），正等著讀者發掘它呢。

Enjoy it!

William's Book

2013年12月23日星期一

[ASP.NET C# ]在 Server 端存取 Excel 檔案的利器：NPOI Library

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

在 Server 端控制 Excel 的難處

在 .NET 上的 POI：NPOI

NPOI 函式庫

實例應用：將 DataTable 和 Excel 檔案間互轉

結語

沒有留言:

張貼留言

2013年12月23日 星期一

[ASP.NET C# ]在 Server 端存取 Excel 檔案的利器：NPOI Library

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

在 Server 端控制 Excel 的難處

在 .NET 上的 POI：NPOI

NPOI 函式庫

實例應用：將 DataTable 和 Excel 檔案間互轉

結語

沒有留言:

張貼留言

2013年12月23日星期一