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

原文地址： http://msdn.microsoft.com/zh-tw/ee818993.aspx

原文是繁体中文，google翻译

在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 表格两种方法：

1. 使用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

    

2. 使用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!