如何使用 Openxml 创建大型 Excel 文件

• 下载源代码 - 3 MB

引言

本文将向您展示如何使用 Openxml SDK 2.5 创建 Excel 文件。有很多文章介绍了如何使用 DOM 创建文件，但我将向您展示一种不同的方法，这种方法更快、内存效率更高。这是一份完整的指南，它向您展示了如何使用这种类似 SAX 的方法创建样式、共享 string 表，它使用多工作表，并向您展示如何将不同的数据类型写入文件，包括 datetime 格式。

背景

在我最近的一个项目中，该系统使用了一个第三方 Excel 创建器，它附带了很多我们使用的工具，而且这个 Excel 创建器速度非常慢。它需要 17 分钟才能创建 15,000 行。在一个周末，我探索了另一种创建 Excel 文件的方法，即使用 OpenXml，并找到了不同的解决方案。本文将结合我通过数小时的谷歌搜索学到的关于该主题的内容。

当然，市面上也有一些第三方工具可以使这项任务更容易，例如 ClosedXML，但我只想专注于从头开始仅使用 OpenXml 创建 Excel，因为我认为这是创建文件的最快方法。我的主要关注点是

1. 速度
2. 内存使用情况

Using the Code

这是解决方案中的一个控制台项目。该解决方案是 .NET Framework 4 的 VS 2012 解决方案。我在项目中添加了 DocumentFormat.OpenXml 的 nuget 包，它会自动为我添加 DocumentFormat.OpenXml，我需要手动添加对 WindowsBase 的引用。

您所要做的就是解压缩文件并运行控制台应用程序。它会在您的桌面上创建一个名为 test.xlsx 的 Excel 文件。如果遇到权限问题，您可能需要手动更改路径，或者以管理员身份启动 Visual Studio。

string path = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
var fileName = Path.Combine(path,  @"test.xlsx");
if (File.Exists(fileName))
{
    File.Delete(fileName);
}

我将为 Excel 文件创建 2 个工作表。第一个工作表将演示将不同的数据类型写入文件。我需要将 .NET 类型映射到 Excel 类型。正在创建的 .NET 类型有 bool、int、DateTime 和 string。Excel 类型将是 SharedString、InlineString、Boolean、Number 和 Date （实际上是带格式的 Number ）。我还创建了 2 个工作表来向您展示如何将数据拆分到多个工作表中。Excel 2010 最多支持 1,048,576 行。因此，如果您的数据大于此，您可以将数据拆分到多个工作表中。

我在 Main() 的开头定义了不同类型的 .NET 数组，然后为 Excel 文件设置了不同的部分。Excel 文件只包含 1 个 SharedStringTablePart 和 1 个 WorkbookStylesPart。我们将稍后创建它们，我们将首先使用辅助类创建 WorkbookStylesPart。

OpenXmlWriterHelper.SaveCustomStylesheet(WorkbookPart);

SaveCustomStylesheet 调用 CreateDefaultStylesheet()，该函数来自此文章 http://polymathprogrammer.com/2009/11/09/how-to-create-stylesheet-in-excel-open-xml/ ，以创建由空 Excel 文件生成的默认样式表。根据我搜索到的信息，您很可能不想更改此默认样式表创建的任何内容，因为 Excel 会保留某些内容作为默认值。不过，您可以向默认样式表添加格式或样式，只是不要删除默认样式表创建的部分。

例如，我的标题行有浅蓝色背景，要实现这一点，我向 stylesheet 添加了一个 Fill。

//header fills background color
var fill = new Fill();
var patternFill = new PatternFill();
patternFill.PatternType = PatternValues.Solid;
patternFill.ForegroundColor = new ForegroundColor { Rgb = HexBinaryValue.FromString("C8EEFF") };
//patternFill.BackgroundColor = new BackgroundColor() { Indexed = 64 };
fill.PatternFill = patternFill;
fills.AppendChild(fill);
fills.Count = (uint)fills.ChildElements.Count;

我还希望以特定格式显示 Date，所以我添加了这个数字格式。

// *************************** numbering formats ***********************
var nfs = stylesheet.NumberingFormats;
//number less than 164 is reserved by excel for default formats
uint iExcelIndex = 165;
NumberingFormat nf;
nf = new NumberingFormat();
nf.NumberFormatId = iExcelIndex++;
nf.FormatCode = @"[$-409]m/d/yy\ h:mm\ AM/PM;@";
nfs.Append(nf);

nfs.Count = (uint)nfs.ChildElements.Count;

为了让您的单元格能够引用这些特殊格式或样式，您需要创建一个不同的 CellFormat 来标识不同的 NumberingFormat、Font、Fill、Border、Format... 这样您的单元格就可以使用 StyleIndex 引用这些 CellFormat 。StyleIndex 是基于 0 的，其中 0 用于默认样式。您定义的任何自定义样式都从 1 开始。

//************************** cell formats ***********************************
var cfs = stylesheet.CellFormats;//this should already contain a default StyleIndex of 0
       
var cf = new CellFormat();// Date time format is defined as StyleIndex = 1
cf.NumberFormatId = nf.NumberFormatId;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = true;
cfs.Append(cf);
       
cf = new CellFormat();// Header format is defined as StyleINdex = 2
cf.NumberFormatId = 0;
cf.FontId = 0;
cf.FillId = 2;
cf.ApplyFill = true;
cf.BorderId = 0;
cf.FormatId = 0;
cfs.Append(cf);
      
cfs.Count = (uint)cfs.ChildElements.Count;

然后，我只是使用简单的 DOM 方法保存 Style，因为与单元格值相比，stylesheet 相对较小。

var workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>();
var style = workbookStylesPart.Stylesheet = stylesheet;
style.Save();

创建完 Stylesheet 后，我创建了一个 Workbook 并将 Sheets 元素添加到其中，然后开始将 Sheet1 添加到 Sheets 类中。到目前为止，一切都是 DOM。

var workbook = workbookPart.Workbook = new Workbook();
var sheets = workbook.AppendChild<Sheets>(new Sheets());


// create worksheet 1
var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
var sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Sheet1" };
sheets.Append(sheet);

现在进入有趣的部分，我使用 WorksheetPart 创建了一个 OpenXmlWriter，然后开始将 XML 元素写入文件。在详细介绍之前，我需要解释一下 xlsx 文件是什么。xlsx 文件本质上是一个 zip 文件，其中包含一堆 XML 文件，这些文件按照特定的文件夹结构排列，以及一堆不重要的东西。您实际上可以右键单击 xlsx 文件并将其解压缩到一个文件夹中。然后您会看到一个名为 xl 的文件夹。在此文件夹中，您可能会找到以下内容

styles.xml 包含 xlsx 文件中单元格的所有样式和格式，sharedStrings.xml 包含所有指定为共享 string 的单元格的单元格值。

在 worksheets 文件夹内，您会找到 0 个或多个 XML 文件；每个文件代表一个工作表。大多数重要信息都定义在这些文件中。

您可以创建一个简单的 Excel 文件并检查这些文件的内容。您可能会在文件部分中找到以下内容

<sheetData>
<row>
<c t="b"><v>1</v></c>
<c t="inlineStr"><is><t>this is an inline string</t></is></c>
<c t="s"><v>0</v></c>
<c t="n"><v>2</v></c>
<c s="1"><v>42053</v></c>
</row>
</sheetData>

这些元素实际上代表了一个包含 1 行 5 个单元格的工作表。

• 第一个单元格的 DataType 是 Boolean，值为 TRUE。
• 第二个单元格是内联 string，值为 "this is an inline string"。
• 第三个单元格是共享 string 类型，值为存储在共享 string 表的 0 索引处。
• 第四个单元格是数字类型，值为 2。
• 第五个单元格是样式索引为 1、值为 42053 的单元格，样式索引和值将其转换为日期时间值。

这就是 OpenXmlWriter 的作用。它能够将强类型的 OpenXmlElement 转换为 XML 文件和标签。

要创建 XML 和标签而不加载整个 DOM，您需要创建一个 OpenXmlWriter。我实例化它，然后使用 WriteStartElement() 或 WriteElement() 添加必要的标签。

using (var writer = OpenXmlWriter.Create(worksheetPart))
{
    writer.WriteStartElement(new Worksheet());
    writer.WriteStartElement(new SheetData());

要创建 Row 标签，您只需这样做

writer.WriteStartElement(new Row()); //<Row>
//...... write your cell tags using OpenXmlWriter
writer.WriteEndElement();  //this will close the Row Tag : </Row>

WriteStartElement 有不同的重载，如果您不想在运行时创建太多对象，实际上可以传递标签 <Row> 而不是使用新的 Row()。这可能会减小应用程序的内存占用。如果您有时间，请随意尝试。

我喜欢在不下载任何东西的情况下查看代码，这就是为什么我在这里发布我的代码中的重要部分。下面的代码根据您指定的 CellValues 以不同的方式写出单元格 (<c>) 及其子元素。它接受 OpenXmlWriter 实例、CellValues datatype 和 OpenXmlAttribute 列表。

public void WriteCellValueSax(OpenXmlWriter writer, string cellValue, 
CellValues dataType, List<OpenXmlAttribute> attributes = null)
{
    switch (dataType)
    {
        case CellValues.InlineString:
            {
                if (attributes == null)
                {
                    attributes = new List<OpenXmlAttribute>();
                }
                attributes.Add(new OpenXmlAttribute("t", null, "inlineStr"));
                writer.WriteStartElement(new Cell(), attributes);
                writer.WriteElement(new InlineString(new Text(cellValue)));
                writer.WriteEndElement();
                break;
            }
        case CellValues.SharedString:
            {
                if (attributes == null)
                {
                    attributes = new List<OpenXmlAttribute>();
                }
                attributes.Add(new OpenXmlAttribute("t", null, "s"));//shared string type
                writer.WriteStartElement(new Cell(), attributes);
                if (!_shareStringDictionary.ContainsKey(cellValue))
                {
                    _shareStringDictionary.Add(cellValue, _shareStringMaxIndex);
                    _shareStringMaxIndex++;
                }

                //writing the index as the cell value
                writer.WriteElement(new CellValue(_shareStringDictionary[cellValue].ToString()));

                writer.WriteEndElement();//cell

                break;
            }
        case CellValues.Date:
            {
                if (attributes == null)
                {
                    writer.WriteStartElement(new Cell() { DataType = CellValues.Number });
                }
                else
                {
                    writer.WriteStartElement(new Cell() { DataType = CellValues.Number }, attributes);
                }

                writer.WriteElement(new CellValue(cellValue));

                writer.WriteEndElement();

                break;
            }
        case CellValues.Boolean:
            {
                if (attributes == null)
                {
                    attributes = new List<OpenXmlAttribute>();
                }
                attributes.Add(new OpenXmlAttribute("t", null, "b"));//boolean type
                writer.WriteStartElement(new Cell(), attributes);
                writer.WriteElement(new CellValue(cellValue == "True" ? "1" : "0"));
                writer.WriteEndElement();
                break;
            }
        default:
            {
                if (attributes == null)
                {
                    writer.WriteStartElement(new Cell() { DataType = dataType });
                }
                else
                {
                    writer.WriteStartElement(new Cell() { DataType = dataType }, attributes);
                }
                writer.WriteElement(new CellValue(cellValue));

                writer.WriteEndElement();

                break;
            }
    }
}

OpenXmlAttribute 用于写出 XML 元素的属性。例如，要写出 <c t="inlineStr"> 中的 "t" 属性，您可以这样做

attributes.Add(new OpenXmlAttribute("t", null, "inlineStr"));
writer.WriteStartElement(new Cell(), attributes);

在我的例子中，Attribute 用于指定单元格的格式，例如，指定一个单元格具有在自定义 stylesheet 中定义的特定 StyleIndex。我在项目中用它来指示一个单元格是否应具有特定的背景颜色或特定的 datetime 格式。

String vs InlineString vs SharedString

你们中的一些人可能不知道这些 CellValues enum 值之间的区别，所以我也将包含这一部分。

• String 应​​用于表示单元格中的公式（我实际上没有处理这种情况）
• InlineString 将被视为富文本
• SharedString 表示在 Excel 文件中的所有单元格/工作表中共享的 string。根据 MSDN：一个工作簿可能包含成千上万个包含 string（非数字）数据的单元格。此外，这些数据很可能在许多行或列中重复。实现一个在整个工作簿中共享的单个 string 表的目标是通过只读取和写入重复信息一次来提高打开和保存文件的性能。

我测试了使用 InlineString 与 SharedString 创建 100 万行，发现如果数据长度较短且重复值不多，使用 InlineString 生成的 Excel 文件要小得多，19.7 MB 对比 26 MB。但是，如果数据重复很多次，使用 SharedString 实际上更有效。在我测试的案例中，使用 149,000 个唯一的 5 个字母字符串组成 400 万个单元格时，19.7 MB 对比 15MB。如果 string 很长且重复，您将看到使用共享 string 的好处。

对于 SharedString 实现，我简单地使用了一个 Dictionary<string, int>，其中键是单元格的值，字典的值是该值在 SharedStringTable 中的索引。

/// <summary>
/// contains the shared string as the key, and the index as the value.  index is 0 base
/// </summary>
private readonly Dictionary<string, int> _shareStringDictionary = new Dictionary<string, int>();
private int _shareStringMaxIndex = 0;

微软在将 SharedString 插入 SharedStringTable 时有不同的实现方式，但我相信它的速度会比我在这里实现的方式慢得多。我这种方法唯一的缺点是 Dictionary 的内存使用。最终，如果内存对您来说是一个问题，您可以选择使用微软的方法。

由于所有单元格值都保存在 Dictionary 中，因此在完成写入工作表后，我们需要将所有值写入 sharedStringTable。我选择再次使用 OpenXmlWriter 来完成这一部分。

/// <summary>
/// write out the share string xml.  Call this after writing out all shared string values in sheet
/// </summary>
/// <param name="workbookPart"></param>
public void CreateShareStringPart(WorkbookPart workbookPart)
{
    if (_shareStringMaxIndex > 0)
    {
        var sharedStringPart = workbookPart.AddNewPart<SharedStringTablePart>();
        using (var writer = OpenXmlWriter.Create(sharedStringPart))
        {
            writer.WriteStartElement(new SharedStringTable());
            foreach (var item in _shareStringDictionary)
            {
                writer.WriteStartElement(new SharedStringItem());
                writer.WriteElement(new Text(item.Key));
                writer.WriteEndElement();
            }
            writer.WriteEndElement();
        }
    }
}

结论

根据配置，在我的机器上，写入 400 万个单元格值大约需要 11 秒到 19 秒。我认为这很快。

额外的鸣谢

• http://polymathprogrammer.com/2012/08/06/how-to-properly-use-openxmlwriter-to-write-large-excel-files/