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 读写的示例
1using DocumentFormat.OpenXml; 2using DocumentFormat.OpenXml.Packaging; 3using DocumentFormat.OpenXml.Spreadsheet; 4using System; 5using System.Data; 6using System.Linq; 7 8namespace ExcelExport 9{ 10 public class ExcelOpenXml 11 { 12 /* 13 * excel 对象结构 14 * SpreadsheetDocument 15 * 》WorkbookPart 16 * 》WorksheetPart 17 * 》Worksheet 18 * 》SheetData 19 * 》WorksheetPart 20 * 》Worksheet 21 * 》SheetData1 22 * 》Workbook 23 * 》Sheets 24 * 》Sheet 25 */ 26 public static void Create(string filename, DataSet ds) 27 { 28 SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook); 29 30 WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart(); 31 Workbook workbook = new Workbook(); 32 Sheets sheets = new Sheets(); 33 34 #region 创建多个 sheet 页 35 36 //创建多个sheet 37 for (int s = 0; s < ds.Tables.Count; s++) 38 { 39 DataTable dt = ds.Tables[s]; 40 var tname = dt.TableName; 41 42 WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>(); 43 Worksheet worksheet = new Worksheet(); 44 SheetData sheetData = new SheetData(); 45 46 //创建 sheet 页 47 Sheet sheet = new Sheet() 48 { 49 //页面关联的 WorksheetPart 50 Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), 51 SheetId = UInt32Value.FromUInt32((uint)s + 1), 52 Name = tname 53 }; 54 sheets.Append(sheet); 55 56 #region 创建sheet 行 57 Row row; 58 uint rowIndex = 1; 59 //添加表头 60 row = new Row() 61 { 62 RowIndex = UInt32Value.FromUInt32(rowIndex++) 63 }; 64 sheetData.Append(row); 65 for (int i = 0; i < dt.Columns.Count; i++) 66 { 67 Cell newCell = new Cell(); 68 newCell.CellValue = new CellValue(dt.Columns[i].ColumnName); 69 newCell.DataType = new EnumValue<CellValues>(CellValues.String); 70 row.Append(newCell); 71 } 72 //添加内容 73 object val = null; 74 for (int i = 0; i < dt.Rows.Count; i++) 75 { 76 row = new Row() 77 { 78 RowIndex = UInt32Value.FromUInt32(rowIndex++) 79 }; 80 sheetData.Append(row); 81 82 for (int j = 0; j < dt.Columns.Count; j++) 83 { 84 Cell newCell = new Cell(); 85 val = dt.Rows[i][j]; 86 newCell.CellValue = new CellValue(val.ToString()); 87 newCell.DataType = new EnumValue<CellValues>(CellValues.String); 88 89 row.Append(newCell); 90 } 91 92 } 93 #endregion 94 95 worksheet.Append(sheetData); 96 worksheetPart.Worksheet = worksheet; 97 worksheetPart.Worksheet.Save(); 98 } 99 #endregion 100 101 workbook.Append(sheets); 102 workbookpart.Workbook = workbook; 103 104 workbookpart.Workbook.Save(); 105 spreadsheetDocument.Close(); 106 } 107 108 public static DataTable GetSheet(string filename, string sheetName) 109 { 110 DataTable dt = new DataTable(); 111 using (SpreadsheetDocument document = SpreadsheetDocument.Open(filename, false)) 112 { 113 WorkbookPart wbPart = document.WorkbookPart; 114 //通过sheet名查找 sheet页 115 Sheet sheet = wbPart 116 .Workbook 117 .Descendants<Sheet>() 118 .Where(s => s.Name == sheetName) 119 .FirstOrDefault(); 120 121 if (sheet == null) 122 { 123 throw new ArgumentException("未能找到" + sheetName + " sheet 页"); 124 } 125 126 //获取Excel中共享表 127 SharedStringTablePart sharedStringTablePart = wbPart 128 .GetPartsOfType<SharedStringTablePart>() 129 .FirstOrDefault(); 130 SharedStringTable sharedStringTable = null; 131 if (sharedStringTablePart != null) 132 sharedStringTable = sharedStringTablePart.SharedStringTable; 133 #region 构建datatable 134 135 //添加talbe列,返回列数 136 Func<Row, int> addTabColumn = (r) => 137 { 138 //遍历单元格 139 foreach (Cell c in r.Elements<Cell>()) 140 { 141 dt.Columns.Add(GetCellVal(c, sharedStringTable)); 142 } 143 return dt.Columns.Count; 144 }; 145 //添加行 146 Action<Row> addTabRow = (r) => 147 { 148 DataRow dr = dt.NewRow(); 149 int colIndex = 0; 150 int colCount = dt.Columns.Count; 151 //遍历单元格 152 foreach (Cell c in r.Elements<Cell>()) 153 { 154 if (colIndex >= colCount) 155 break; 156 dr[colIndex++] = GetCellVal(c, sharedStringTable); 157 } 158 dt.Rows.Add(dr); 159 }; 160 #endregion 161 162 163 //通过 sheet.id 查找 WorksheetPart 164 WorksheetPart worksheetPart 165 = wbPart.GetPartById(sheet.Id) as WorksheetPart; 166 //查找 sheetdata 167 SheetData sheetData = worksheetPart.Worksheet.Elements<SheetData>().First(); 168 169 //遍历行 170 foreach (Row r in sheetData.Elements<Row>()) 171 { 172 //构建table列 173 if (r.RowIndex == 1) 174 { 175 addTabColumn(r); 176 continue; 177 } 178 //构建table行 179 addTabRow(r); 180 } 181 182 } 183 return dt; 184 } 185 186 /// <summary> 187 /// 获取单元格值 188 /// </summary> 189 /// <param name="cell"></param> 190 /// <param name="sharedStringTable"></param> 191 /// <returns></returns> 192 static string GetCellVal(Cell cell, SharedStringTable sharedStringTable) 193 { 194 var val = cell.InnerText; 195 196 if (cell.DataType != null) 197 { 198 switch (cell.DataType.Value) 199 { 200 //从共享表中获取值 201 case CellValues.SharedString: 202 if (sharedStringTable != null) 203 val = sharedStringTable 204 .ElementAt(int.Parse(val)) 205 .InnerText; 206 break; 207 default: 208 val = string.Empty; 209 break; 210 } 211 212 } 213 return val; 214 } 215 } 216}
数据接口
1using System; 2using System.Collections.Generic; 3using System.Data; 4using System.IO; 5using System.Linq; 6using System.Text; 7 8namespace TextExcelExport 9{ 10 public class TestData 11 { 12 private static string _exportDir = @"D:\temp"; 13 14 public static string GetNewExcelFileName(string name) 15 { 16 //return Path.Combine(_exportDir, DateTime.Now.ToString("yyMMdd-HHmmss") + suffix); 17 return Path.Combine(_exportDir, name); 18 } 19 public static string GetFileName(string fileName) 20 { 21 return Path.Combine(_exportDir 22 , fileName); 23 } 24 25 public static DataTable GetDataTable(int cols = 100, int rows = 1000, string tabName = "mytable") 26 { 27 DataTable dt = new DataTable(tabName); 28 for (int i = 0; i < cols; i++) 29 { 30 dt.Columns.Add("col" + i.ToString("D3")); 31 } 32 33 DataRow dr = null; 34 for (int i = 0; i < rows; i++) 35 { 36 dr = dt.NewRow(); 37 for (int j = 0; j < dt.Columns.Count; j++) 38 { 39 dr[j] = "val-" + i + "-" + j; 40 } 41 dt.Rows.Add(dr); 42 } 43 44 return dt; 45 } 46 } 47}
单元测试接口代码
1using System; 2using System.Data; 3using System.IO; 4using ExcelExport; 5using Microsoft.VisualStudio.TestTools.UnitTesting; 6 7namespace TextExcelExport 8{ 9 [TestClass] 10 public class TestCreateExcel 11 { 12 #region openxml 13 [TestMethod] 14 public void TestOpenXmlCrate() 15 { 16 var fname = TestData.GetNewExcelFileName("TestOpenXmlCrate.xlsx"); 17 18 var dt1 = TestData.GetDataTable(tabName: "tab1"); 19 var dt2 = TestData.GetDataTable(tabName: "tab2"); 20 DataSet ds = new DataSet(); 21 ds.Tables.Add(dt1); 22 ds.Tables.Add(dt2); 23 24 ExcelOpenXml.Create(fname, ds); 25 26 Assert.IsTrue(File.Exists(fname)); 27 } 28 [TestMethod] 29 public void TestOpenXmlRead() 30 { 31 var fname = TestData.GetFileName("TestOpenXmlCrate.xlsx"); 32 var dt = ExcelOpenXml.GetSheet(fname, "tab1"); 33 34 Assert.IsTrue(File.Exists(fname)); 35 } 36 #endregion 37 } 38}
测试发现，写两张sheet表，1000行，100列的数据创建需要2秒多，读取只需433ms
详细可以查看git仓库代码：https://github.com/marblemm/UtilsHelper