原文地址: 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 表格两种方法:
-
使用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.aspxNOTE
微软官方并不建议在伺服器端使用直接存取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 档案,并且添加三个指定名称的试算表:
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 打开来看,可以看到它的内容确实是以指定的试算表名称所建立:
再试一些程式,我们可以在里面添加资料,例如下列的程式码:
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 开启,即可看到插入的资料值:
这样还不够,我们再设定一些东西,例如设定储存格的背景色:
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 资料档,它的内容是:
然后利用下列的程式码:
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 资料档的小程式供读者自行取用。
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() 方法即可。
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 档案资料流、试算表索引(或名称)以及起始列索引值即可:
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!