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:	emp";

        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