Open-Xml SDK使用介绍
Office Open XML
简称为 ooxml ，是Microsoft 在 Office 2007 之后推行的标准格式，用在 Excel, Word, PPT 等文件。已确定为国际标准。
Open-Xml SDK是Microsoft提供操作ooxml格式的接口类库，是c#实现的，2014年开源的，
open-xml sdk开源项目地址：https://github.com/OfficeDev/Open-XML-SDK
open-xml sdk官方介绍文档：https://docs.microsoft.com/en-us/office/open-xml/open-xml-sdk
和其他操作ooxml类库相比，如NPOI，EPPlus等比较，网上说NPOI速度更快些，但是我测试后open-xml sdk更快，只能说其他类库接口封装比较容易使用
以下是用open-xml sdk 读写的示例
using DocumentFormat.OpenXml; using DocumentFormat.OpenXml.Packaging; using DocumentFormat.OpenXml.Spreadsheet; using System; using System.Data; using System.Linq; namespace ExcelExport { public class ExcelOpenXml { /* * excel 对象结构 * SpreadsheetDocument * 》WorkbookPart * 》WorksheetPart * 》Worksheet * 》SheetData * 》WorksheetPart * 》Worksheet * 》SheetData1 * 》Workbook * 》Sheets * 》Sheet */ public static void Create(string filename, DataSet ds) { SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook); WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart(); Workbook workbook = new Workbook(); Sheets sheets = new Sheets(); #region 创建多个 sheet 页 //创建多个sheet for (int s = 0; s < ds.Tables.Count; s++) { DataTable dt = ds.Tables[s]; var tname = dt.TableName; WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>(); Worksheet worksheet = new Worksheet(); SheetData sheetData = new SheetData(); //创建 sheet 页 Sheet sheet = new Sheet() { //页面关联的 WorksheetPart Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = UInt32Value.FromUInt32((uint)s + 1), Name = tname }; sheets.Append(sheet); #region 创建sheet 行 Row row; uint rowIndex = 1; //添加表头 row = new Row() { RowIndex = UInt32Value.FromUInt32(rowIndex++) }; sheetData.Append(row); for (int i = 0; i < dt.Columns.Count; i++) { Cell newCell = new Cell(); newCell.CellValue = new CellValue(dt.Columns[i].ColumnName); newCell.DataType = new EnumValue<CellValues>(CellValues.String); row.Append(newCell); } //添加内容 object val = null; for (int i = 0; i < dt.Rows.Count; i++) { row = new Row() { RowIndex = UInt32Value.FromUInt32(rowIndex++) }; sheetData.Append(row); for (int j = 0; j < dt.Columns.Count; j++) { Cell newCell = new Cell(); val = dt.Rows[i][j]; newCell.CellValue = new CellValue(val.ToString()); newCell.DataType = new EnumValue<CellValues>(CellValues.String); row.Append(newCell); } } #endregion worksheet.Append(sheetData); worksheetPart.Worksheet = worksheet; worksheetPart.Worksheet.Save(); } #endregion workbook.Append(sheets); workbookpart.Workbook = workbook; workbookpart.Workbook.Save(); spreadsheetDocument.Close(); } public static DataTable GetSheet(string filename, string sheetName) { DataTable dt = new DataTable(); using (SpreadsheetDocument document = SpreadsheetDocument.Open(filename, false)) { WorkbookPart wbPart = document.WorkbookPart; //通过sheet名查找 sheet页 Sheet sheet = wbPart .Workbook .Descendants<Sheet>() .Where(s => s.Name == sheetName) .FirstOrDefault(); if (sheet == null) { throw new ArgumentException("未能找到" + sheetName + " sheet 页"); } //获取Excel中共享表 SharedStringTablePart sharedStringTablePart = wbPart .GetPartsOfType<SharedStringTablePart>() .FirstOrDefault(); SharedStringTable sharedStringTable = null; if (sharedStringTablePart != null) sharedStringTable = sharedStringTablePart.SharedStringTable; #region 构建datatable //添加talbe列,返回列数 Func<Row, int> addTabColumn = (r) => { //遍历单元格 foreach (Cell c in r.Elements<Cell>()) { dt.Columns.Add(GetCellVal(c, sharedStringTable)); } return dt.Columns.Count; }; //添加行 Action<Row> addTabRow = (r) => { DataRow dr = dt.NewRow(); int colIndex = 0; int colCount = dt.Columns.Count; //遍历单元格 foreach (Cell c in r.Elements<Cell>()) { if (colIndex >= colCount) break; dr[colIndex++] = GetCellVal(c, sharedStringTable); } dt.Rows.Add(dr); }; #endregion //通过 sheet.id 查找 WorksheetPart WorksheetPart worksheetPart = wbPart.GetPartById(sheet.Id) as WorksheetPart; //查找 sheetdata SheetData sheetData = worksheetPart.Worksheet.Elements<SheetData>().First(); //遍历行 foreach (Row r in sheetData.Elements<Row>()) { //构建table列 if (r.RowIndex == 1) { addTabColumn(r); continue; } //构建table行 addTabRow(r); } } return dt; } /// <summary> /// 获取单元格值 /// </summary> /// <param name="cell"></param> /// <param name="sharedStringTable"></param> /// <returns></returns> static string GetCellVal(Cell cell, SharedStringTable sharedStringTable) { var val = cell.InnerText; if (cell.DataType != null) { switch (cell.DataType.Value) { //从共享表中获取值 case CellValues.SharedString: if (sharedStringTable != null) val = sharedStringTable .ElementAt(int.Parse(val)) .InnerText; break; default: val = string.Empty; break; } } return val; } } }
数据接口
using System; using System.Collections.Generic; using System.Data; using System.IO; using System.Linq; using System.Text; namespace TextExcelExport { public class TestData { private static string _exportDir = @"D:\temp"; public static string GetNewExcelFileName(string name) { //return Path.Combine(_exportDir, DateTime.Now.ToString("yyMMdd-HHmmss") + suffix); return Path.Combine(_exportDir, name); } public static string GetFileName(string fileName) { return Path.Combine(_exportDir , fileName); } public static DataTable GetDataTable(int cols = 100, int rows = 1000, string tabName = "mytable") { DataTable dt = new DataTable(tabName); for (int i = 0; i < cols; i++) { dt.Columns.Add("col" + i.ToString("D3")); } DataRow dr = null; for (int i = 0; i < rows; i++) { dr = dt.NewRow(); for (int j = 0; j < dt.Columns.Count; j++) { dr[j] = "val-" + i + "-" + j; } dt.Rows.Add(dr); } return dt; } } }
单元测试接口代码
using System; using System.Data; using System.IO; using ExcelExport; using Microsoft.VisualStudio.TestTools.UnitTesting; namespace TextExcelExport { [TestClass] public class TestCreateExcel { #region openxml [TestMethod] public void TestOpenXmlCrate() { var fname = TestData.GetNewExcelFileName("TestOpenXmlCrate.xlsx"); var dt1 = TestData.GetDataTable(tabName: "tab1"); var dt2 = TestData.GetDataTable(tabName: "tab2"); DataSet ds = new DataSet(); ds.Tables.Add(dt1); ds.Tables.Add(dt2); ExcelOpenXml.Create(fname, ds); Assert.IsTrue(File.Exists(fname)); } [TestMethod] public void TestOpenXmlRead() { var fname = TestData.GetFileName("TestOpenXmlCrate.xlsx"); var dt = ExcelOpenXml.GetSheet(fname, "tab1"); Assert.IsTrue(File.Exists(fname)); } #endregion } }
测试发现，写两张sheet表，1000行，100列的数据创建需要2秒多，读取只需433ms
详细可以查看git仓库代码：https://github.com/marblemm/UtilsHelper
