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
