.net使用免费开源类库操作Excel 通吃所有Excel
主要找到以下类库:MyXls(http://)
Koogra(http://)
ExcelLibrary(http://code.)
ExcelPackage(http://excelpackage.)
EPPlus(http://epplus.)
LinqToExcel(http://code.)
NetOffice(http://netoffice.) 需安装Office Excel
从1-6的类库均不需要安装Office,不使用Office COM组件;而NetOffice需要安装Office,它提供的是与Office COM组件差不多的功能。
注:本文仅简单演示读取与创建Excel。
准备测试代码
首先,为这些类库准备一些测试代码,用于之后的测试。
aspx主要代码如下:
程序代码:
<asp:FileUpload ID="FileUpload1" runat="server" /> <asp:Button ID="Button1" runat="server" Text="上传Excel" onclick="Button1_Click" /> <asp:Button ID="Button2" runat="server" Text="下载Excel" onclick="Button2_Click" /> <asp:GridView ID="GridView2" runat="server"> </asp:GridView>aspx.cs主要代码如下:
程序代码:
private void RenderToBrowser(MemoryStream ms, string fileName) { if (Request.Browser.Browser == "IE") fileName = HttpUtility.UrlEncode(fileName); Response.AddHeader("Content-Disposition", "attachment;fileName=" + fileName); Response.BinaryWrite(ms.ToArray()); } protected void Button1_Click(object sender, EventArgs e) { if (FileUpload1.HasFile) {//读取上传的文件绑定到GridView GridView1.DataSource = ReadByXXX(FileUpload1.FileContent); GridView1.DataBind(); } } protected void Button2_Click(object sender, EventArgs e) { DataTable table = new DataTable(); table.Columns.Add("aa", typeof(string)); table.Columns.Add("bb", typeof(string)); table.Columns.Add("cc", typeof(string)); for (int i = 0; i < 10; i++) { string a = DateTime.Now.Ticks.ToString(); Thread.Sleep(1); string b = DateTime.Now.Ticks.ToString(); Thread.Sleep(1); string c = DateTime.Now.Ticks.ToString(); Thread.Sleep(1); table.Rows.Add(a, b, c); } //从DataTable创建Excel并下载 RenderToBrowser(CreateByXXX(table), "test.xls"); }MyXls
MyXls支持Office Excel 97-2003格式(Biff8格式),但目前并不支持formula即公式;网上流传的支持2007是错误的说法。
使用它还需要注意的是,它与Office PIA一样,索引号是从1开始的。
另外不得不说的是,它的构造函数、Save方法、属性中的FileName让人看的眼花瞭乱,无所适从呐-_-。
主要使用的类型都位于org.in2bits.MyXls空间下,主要测试代码如下:
程序代码:
MemoryStream CreateByMyXls(DataTable table) { XlsDocument doc = new XlsDocument(); Worksheet sheet = doc.Workbook.Worksheets.Add("Sheet1"); int colCount = table.Columns.Count; for (int i = 1; i <= colCount; i++) { sheet.Cells.Add(1, i, table.Columns[i - 1].Caption); } int k = 2; foreach (DataRow row in table.Rows) { for (int i = 1; i <= colCount; i++) { sheet.Cells.Add(k, i, row[i - 1]); } k++; } MemoryStream ms = new MemoryStream(); doc.Save(ms); return ms; } DataTable ReadByMyXls(Stream xlsStream) { XlsDocument doc = new XlsDocument(xlsStream); DataTable table = new DataTable(); Worksheet sheet = doc.Workbook.Worksheets[0]; int colCount = sheet.Rows[1].CellCount; int rowCount = sheet.Rows.Count; for (ushort j = 1; j <= colCount; j++) { table.Columns.Add(new DataColumn(sheet.Rows[1].GetCell(j).Value.ToString())); } for (ushort i = 2; i < rowCount; i++) { DataRow row = table.NewRow(); for (ushort j = 1; j <= colCount; j++) { row[j - 1] = sheet.Rows[i].GetCell(j).Value; } table.Rows.Add(row); } return table; }Koogra
Koogra支持Office 97-2003(Biff8)以及Office 2007以上(Xlsx)格式,但它仅提供读取功能,没有相关的创建Excel功能;另需要注意它的索引号又是从0开始的。
我在几台机器上测试不太稳定,即有的机器直接不能运行,没有深究什么问题。
操作xls格式的类型主要位于Net.SourceForge.Koogra.Excel空间,主要测试代码如下:
程序代码:
public static DataTable ReadByKoogra(Stream xlsStream) { DataTable table = new DataTable(); Workbook book = new Workbook(xlsStream); Worksheet sheet = book.Sheets[0]; Row headerRow = sheet.Rows[0]; uint colCount = headerRow.Cells.MaxCol; uint rowCount = sheet.Rows.MaxRow; Row tempr = null; Cell tempc = null; for (ushort j = 0; j <= colCount; j++) { tempc = headerRow.Cells[j]; if (tempc != null) table.Columns.Add(new DataColumn((tempc.Value ?? string.Empty).ToString())); } for (ushort i = 0; i <= rowCount; i++) { DataRow row = table.NewRow(); tempr = sheet.Rows[i]; for (ushort j = 0; j <= colCount; j++) { tempc = tempr.Cells[j]; if (tempc != null) row[j] = tempc.Value; } table.Rows.Add(row); } return table; }操作XLSX格式的类型主要位于Net.SourceForge.Koogra.Excel2007空间,主要测试代码如下:
程序代码:
public static DataTable ReadByKoogra(Stream xlsStream) { DataTable table = new DataTable(); Workbook book = new Workbook(xlsStream); Worksheet sheet = book.GetWorksheet(0); Row headerRow = sheet.GetRow(0); uint colCount = sheet.CellMap.LastCol; uint rowCount = sheet.CellMap.LastRow; Row tempr = null; ICell tempc = null; for (ushort j = 0; j <= colCount; j++) { tempc = headerRow.GetCell(j); if (tempc != null) table.Columns.Add(new DataColumn((tempc.Value ?? string.Empty).ToString())); } for (ushort i = 0; i <= rowCount; i++) { DataRow row = table.NewRow(); tempr = sheet.GetRow(i); for (ushort j = 0; j <= colCount; j++) { tempc = tempr.GetCell(j); if (tempc != null) row[j] = tempc.Value; } table.Rows.Add(row); } return table; }ExcelLibrary
听说这是国人开发的,目前支持97-2003(biff8)格式,未来可能会支持xlsx格式。它使用二维数组的方式来操作,这种方式比较接近Office PIA,另外,它的索引号是从0开始的。
在测试时,创建出的Excel有时内容是空的,可能存在bug。
它提供了一个DataSetHelper的工具类,用于从DataTable/DataSet和WorkBook之间的转换,但这个工具类不支持对流的操作,所以还是自己写测试代码(ExcelLibrary空间):
程序代码:
MemoryStream CreateByExcelLibrary(DataTable table) { Workbook book = new Workbook(); Worksheet sheet = new Worksheet("Sheet123"); int colCount = table.Columns.Count; for (int i = 0; i < colCount; i++) { sheet.Cells[0, i] = new Cell(table.Columns[i].Caption); } int k = 1; foreach (DataRow row in table.Rows) { for (int i = 0; i < colCount; i++) { sheet.Cells[k, i] = new Cell(row[i]); } k++; } book.Worksheets.Add(sheet); MemoryStream ms = new MemoryStream(); book.Save(ms); return ms; } DataTable ReadByExcelLibrary(Stream xlsStream) { DataTable table = new DataTable(); Workbook book = Workbook.Load(xlsStream); Worksheet sheet = book.Worksheets[0]; int colCount = sheet.Cells.LastColIndex; int rowCount = sheet.Cells.LastRowIndex; for (ushort j = 0; j <= colCount; j++) { table.Columns.Add(new DataColumn(sheet.Cells[0, j].StringValue)); } for (ushort i = 1; i <= rowCount; i++) { DataRow row = table.NewRow(); for (ushort j = 0; j <= colCount; j++) { row[j] = sheet.Cells[i, j].Value; } table.Rows.Add(row); } return table; }
ExcelPackage与EPPlus
ExcelPackage它主要支持OOXML即Office Open XML标准,Office 2007以上XLSX格式的读写;但它不支持对流的操作,仅支持对实体文件的操作。
EPPlus全称应该是ExcelPackage Plus,即ExcelPackage的增强版,它在ExcelPackage的基础上,增强了许多功能包括对流、Linq的支持,可以说相当不错。
它的索引号是从1开始的,主要使用的类型位于OfficeOpenXml空间,具体测试代码如下:
程序代码:
MemoryStream CreateByExcelLibrary(DataTable table) { using (ExcelPackage package = new ExcelPackage()) { ExcelWorksheet sheet = package.Workbook.Worksheets.Add("sheet111"); int colCount = table.Columns.Count; for (int i = 0; i < colCount; i++) { sheet.Cells[1, i + 1].Value = table.Columns[i].Caption; } int k = 2; foreach (DataRow row in table.Rows) { for (int i = 0; i < colCount; i++) { sheet.Cells[k, i + 1].Value = row[i]; } k++; } MemoryStream ms = new MemoryStream(); package.SaveAs(ms); return ms; } } DataTable ReadByExcelLibrary(Stream xlsStream) { DataTable table = new DataTable(); using (ExcelPackage package = new ExcelPackage(xlsStream)) { ExcelWorksheet sheet = package.Workbook.Worksheets[1]; int colCount = sheet.Dimension.End.Column; int rowCount = sheet.Dimension.End.Row; for (ushort j = 1; j <= colCount; j++) { table.Columns.Add(new DataColumn(sheet.Cells[1, j].Value.ToString())); } for (ushort i = 2; i <= rowCount; i++) { DataRow row = table.NewRow(); for (ushort j = 1; j <= colCount; j++) { row[j - 1] = sheet.Cells[i, j].Value; } table.Rows.Add(row); } } return table; }
LinqToExcel,NetOffice…
至于LinqToExcel,只能说是颗糖而已,不支持对流的操作,实在是无爱啊,不多说。
NetOffice提供与Office PIA相似的功能,又需要安装Office,实在不适合在web场景中使用,所以也不多说。
结尾
对于Excel 97-2003格式,还是用NPOI最好,API设计比较好(上面这些类库又是0又是1的索引号和二维数组实在让人好晕);而对于2007(xlsx)以上版本,可以使用EPPlus;这样基本所有的Excel格式通吃了。