基于 DocumentFormat.OpenXml 操作 Excel （3）-- 导出数据

　　前两节已经大概了解了 OpenXML SDK 的一些主要类型，以及Excel文档内部的结构。接下来开始尝试第一个Excel文档导出的实现。其实操作OpenXML SDK, 大部分情况下，和操作XML是差不多的，大部分类型都是继承于OpenXmlElement这个元素，一般大致了解XML的结构，对照来操作，都不是很难。

　　我们来生成一个简单的文档，设置第一行为表头，共五列，分别为：序号，学生姓名，学生年龄，学生班级，辅导老师， 同时输出2行具体的数据。

　　具体导出结果图 如下图所示：

　　

　　

　--》项目准备

　　通过 Visual Studio 这个开发工具来创建一个控制台项目，也可以直接用 Visual Studio Code ,或者终端命令行来创建等等。　

　　首先安装OpenXml SDK，通过 Visual Studio 开发工具的Nuget管理安装，也可以直接在终端通过nuget包管理命令安装：

　　 Install-Package DocumentFormat.OpenXml -Version 2.11.3 

　　命名空间，一般会用到以下几个：　　

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

　　

　--》创建Excel文档，工作簿部件 WorkbookPart

　　从前面章节我们了解到我们要涉及的几个主要部件（Part）的类型，有SpreadsheetDocument ， workbookPart， WorksheetPart 。这三个部件是必须的，同时我们这里增加一个SharedStringTablePart 类型。

　　大致流程：

　　（1）创建 SpreadsheetDocument 对象，表示一个Excel文档包，通过它进行下一步操作。

　　（2）SpreadsheetDocument 对象下，提供AddNewPart，增加 WorkbookPart对象，相当于给文档包插入一个工作簿。

　　（3）初始化 WorkbookPart 对象中代表其描述的XML根元素节点： Workbook 对象。

　　（4）通过WorkbookPart 对象，先创建 SharedStringTablePart 对象，表示这个工作簿中，统一共享字符串相关的部件。

　　（5）有了工作簿，则需要插入工作表了；通过WorkbookPart对象的AddNewPart，方法为其增加子部件，增加WorksheetPart 对象

　　（6）建立工作簿和工作表的关联，Workbook 下创建 Sheets， 再创建Sheet， 该对象，承接的任务就是建立工作簿和上一步创建的WorksheetPart 对象。

　　（7）接下来的核心就都在工作表了，初始化Worksheet对象

　　（8）创建表格表头信息

　　（9）创建表格数据内容的信息

　　（10）保存工作簿并且持久化到磁盘

　　以下代码是Main方法，其中 初始化Worksheet，创建表头，创建表格数据 单独放一个方法 。

public static void Main(string[] args)
{
    //构建一个MemoryStream
    var ms = new MemoryStream();

    //创建Workbook, 指定为Excel Workbook (*.xlsx).
    var document = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook);

    //创建WorkbookPart（工作簿）
    var workbookPart = document.AddWorkbookPart();
    workbookPart.Workbook = new Workbook();

    //构建SharedStringTablePart
    var shareStringPart = workbookPart.AddNewPart<SharedStringTablePart>();
    shareStringPart.SharedStringTable = new SharedStringTable(); //创建根元素

    //创建WorksheetPart（工作簿中的工作表）
    var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
    
    //Workbook 下创建Sheets节点, 建立一个子节点Sheet，关联工作表WorksheetPart
    var sheets = document.WorkbookPart.Workbook.AppendChild<Sheets>(
        new Sheets(new Sheet()
        {
            Id = document.WorkbookPart.GetIdOfPart(worksheetPart),
            SheetId = 1,
            Name = "myFirstSheet"
        }));

    //初始化Worksheet
    InitWorksheet(worksheetPart);

    //创建表头 （序号，学生姓名，学生年龄，学生班级，辅导老师）
    CreateTableHeader(worksheetPart, shareStringPart);

    //创建内容数据
    CreateTableBody(worksheetPart, shareStringPart);

    workbookPart.Workbook.Save();
    document.Close();

    //保存到文件
    SaveToFile(ms);
    Console.WriteLine("End.");
}

　　

　--》初始化Worksheet

　　初始化 WorksheetPart 对象下的 Worksheet 对象，Worksheet 是对应描述工作表的XML的根元素。Worksheet 下的子对象中，SheetData 是表示单元格数据部分，SheetFormatProperties 是可以设置一些属性，Columns 是定义列的一些属性。

　　这里初始化工作表，设置默认行高度，宽度分别为15个单位长度， 而第一列宽度为 5个单位长度，而第2列~第4列为 30个单位长度。Excel里面，高度，宽度的单位是什么，没有说明，网络上查阅的资料是：

　　　　Excel 行高所使用单位为磅（ 1厘米 = 28.6磅），列宽使用单位为 1/10英寸（既 1个单位为 2.54毫米)

　　　　Excel 行高：1毫米（mm）＝2.7682个单位长度，则 1厘米（cm）＝27.682个单位长度；1个单位长度＝0.3612 毫米（mm）

　　　　Excel 列宽：1毫米（mm）＝0.4374个单位长度，则 1厘米（cm）＝4.374 个单位长度；1个单位长度＝2.2862 毫米（mm）

　　Column类型，其中属性 Min 和 Max 是一个区间，表示连续多列，所以设置 第2列~第4列为 30个单位长度，只需要实则Min 为2， Max为 4， 要注意的是这里是从1开始计算，不是从0开始。

具体初始化代码如下：

 

/// <summary>
/// 初始化工作表
/// </summary>
/// <param name="worksheetPart"></param>
public static void InitWorksheet(WorksheetPart worksheetPart)
{
    //构建Worksheet根节点，同时追加子节点SheetData
    worksheetPart.Worksheet = new Worksheet(new SheetData());
    //获取Worksheet对象
    var worksheet = worksheetPart.Worksheet;

    //SheetFormatProperties, 设置默认行高度，宽度， 值类型是Double类型。
    var sheetFormatProperties = new SheetFormatProperties()
    {
        DefaultColumnWidth = 15d,
        DefaultRowHeight = 15d
    };

    //插入SheetFormatProperties，插入到SheetData的前面。通过InsertBefore方法，而不是Append
    //顺序不能错误，否则会导致office打开提示错误，所以一般最好提前在一个列表或者数组，放好顺序再一次性加入
    worksheet.InsertBefore(sheetFormatProperties, worksheet.GetFirstChild<SheetData>());

    //初始化列宽 第一列 5个单位， 第二列~第四列 30个单位
    var columns = new Columns();
    //列，从1开始算起。
    var column1 = new Column
    {
        Min = 1, Max = 1, Width = 5d, CustomWidth = true
    };
    var column2 = new Column
    {
        Min = 2, Max =3, Width = 30d, CustomWidth = true
    };

    columns.Append(column1, column2);

    //插入Column1对象， 它的位置是在SheetFormatProperties 的后面，但是在SheetData的前面。
    //worksheet.Append(columns); 直接追加在后面，office打开提示错误
    worksheet.InsertAfter(columns, worksheet.GetFirstChild<SheetFormatProperties>());

    //最好是前面弄好对象，再一次性插入，或者初始化时先创建对象，用的时候直接拿出来。
    //worksheet.Append(new OpenXmlElement[]
    //{
    //    new SheetFormatProperties(),
    //    new Columns(),
    //    new SheetData()
    //});
}

　　这里需要注意的一点 SheetData，SheetFormatProperties ，Columns 三个类型在Worksheet 下的顺序，是有要求的。本身XML上，通过XSD 是可以约束 子元素 出现的顺序。按照这三个的顺序是： SheetFormatProperties最前， 中间Columns ， 最后SheetData。

　　如果说不按顺序的话，会怎么样呢？ 执行代码，导出成功了，但是通过office excel 打开文件的时候，会提示有问题，提示如下图所示：

　　可以点击是，尝试恢复。 修复后会提示 是sheet1.xml 文件有问题，已经删除或者修复了不可读取的内容，如下图所示：

　　但是我们发现，修复成功之后，里面的数据内容不见，表头也没有了，表格数据也没有了。所以通过OpenXML SDK 来操作Excel，是需要很小心的。

　　这里另外一个有趣的地方是，如果你用WPS office 来打开那个错误的Excel（直接导出，没有经过office修复的）， 它实际上是可以打开的， 也就是说WPS对这种有错误格式的Excel文件，是有一定的兼容性的，打开如下图所示：

　　但是我们导出，做测试的时候，还是需要以office 软件打得开为准，毕竟不知道使用者的软件安装的是office 还是 wps。

 

　--》创建工作表的表头部分

　　初始化工作表部分，接下来就是开始导出数据了，首先是录入表头数据， 来看CreateTableHeader这个方法的实现。

　　单元格的数据，都是存放在SheetData 下面的，从结构上很容易理解，一个Row对象，表示一行， Row对象下面的每个 Cell对象，表示一行中的一格， CellValue 表示单元格的值。

　　这里同时使用了 SharedStringTablePart 对象，这个部件代表共享字符串信息，属于WorkbookPart下面的，表示整个工作簿下的工作表都可以用这个来共享字符串，以便于减少整个文档的大小。SharedStringTablePart 对象下代表其对应XML文件的根元素，是 SharedStringTable对象（xml根节点元素是 sst ）， 而其对象下的 SharedStringItem（xml根节点元素是 si ）表示一个要用于共享的字符串项，new SharedStringItem(new Text("文本信息")) 就表示一个字符串项，这个对象也不是只用于普通文档，像一个单元格文本里面附带多种字体，多种颜色，也是可以的，但是相对来说构建起来会很复杂。而单元格的值 CellValue 对象，是通过 这个共享字符串SharedStringItem 在 SharedStringTable下面的第几个元素来引用的，用索引值（0开始计算的）。 比如是第二个子元素，则其对应的索引值就是 1（0开始计算的）， 所以就是构建对象的时候就是 new CellValue("1") , 同时 Cell对象下的属性，DataType属性，值为枚举类CellValues 指定的 SharedString。

具体CreateTableHeader这个方法代码如下，同时创建表头单元格的方法，也抽了一个CreateTableHeaderCell方法，具体如下：

/// <summary>
/// 创建表头。 （序号，学生姓名，学生年龄，学生班级，辅导老师）
/// </summary>
/// <param name="worksheetPart">WorksheetPart 对象</param>
/// <param name="shareStringPart">SharedStringTablePart 对象</param>
public static void CreateTableHeader(WorksheetPart worksheetPart, SharedStringTablePart shareStringPart)
{
    //获取Worksheet对象
    var worksheet = worksheetPart.Worksheet;

    //获取表格的数据对象，SheetData
    var sheetData = worksheet.GetFirstChild<SheetData>();

    //插入第一行数据，作为表头数据 创建 Row 对象，表示一行
    var row = new Row
    {
        //设置行号，从1开始，不是从0
        RowIndex = 1
    };
   
    //Row下面，追加Cell对象
    row.AppendChild(CreateTableHeaderCell("序号", shareStringPart));
    row.AppendChild(CreateTableHeaderCell("学生姓名", shareStringPart));
    row.AppendChild(CreateTableHeaderCell("学生年龄", shareStringPart));
    row.AppendChild(CreateTableHeaderCell("学生班级", shareStringPart));
    row.AppendChild(CreateTableHeaderCell("辅导老师", shareStringPart));

    sheetData.AppendChild(row);
}

/// <summary>
/// 创建表头的单元格
/// </summary>
public static Cell CreateTableHeaderCell(string headerStr, SharedStringTablePart shareStringPart)
{
    //共享字符串表
    var sharedStringTable = shareStringPart.SharedStringTable;

    //把字符串追加到共享
    sharedStringTable.AppendChild(new SharedStringItem(new Text(headerStr)));
    var index = sharedStringTable.ChildElements.Count - 1; //获取索引

    var cell = new Cell
    {
        //设置值，这里的值是引用 共享字符串里面的对应的索引，就是上面添加的SharedStringItem的子元素的位置。
        CellValue = new CellValue(index.ToString()),
        //设置值类型是共享字符串
        DataType = new EnumValue<CellValues>(CellValues.SharedString)
    };

    return cell;
}

　　

　--》创建表格数据内容的信息

　　创建了表头部分，接下来就是开始创建表格内容数据了，其实方法和创建表头是一样，只是这里不使用 SharedStringTablePart 对象，换另外一种方式尝试下和对比，就是直接输出字符串， Cell对象下的属性，DataType属性，值为枚举类CellValues 指定的 String。 CellValue 对象构建的时候，输入的就不是索引值，而是具体的内容字符串了。

具体代码如下：

public static void CreateTableBody(WorksheetPart worksheetPart)
{
    //获取Worksheet对象
    var worksheet = worksheetPart.Worksheet;

    //获取表格的数据对象，SheetData
    var sheetData = worksheet.GetFirstChild<SheetData>();

    //插入第一行数据，作为表头数据 创建 Row 对象，表示一行
    var row1 = new Row
    {
        RowIndex = 2
    };

    row1.Append(new OpenXmlElement[]
    {
        new Cell()
        {
            CellValue = new CellValue("1"),
            DataType = new EnumValue<CellValues>(CellValues.String) 
        },
        new Cell()
        {
            CellValue = new CellValue("王同学"),
            DataType = new EnumValue<CellValues>(CellValues.String) 
        },
        new Cell()
        {
            CellValue = new CellValue("18岁"),
            DataType = new EnumValue<CellValues>(CellValues.String) 
        },
        new Cell()
        {
            CellValue = new CellValue("一班"),
            DataType = new EnumValue<CellValues>(CellValues.String) 
        },
        new Cell()
        {
            CellValue = new CellValue("林老师"),
            DataType = new EnumValue<CellValues>(CellValues.String) 
        }
    });

    sheetData.AppendChild(row1);

    var row2 = new Row
    {
        RowIndex = 3
    };

    row2.Append(new OpenXmlElement[]
    {
        new Cell()
        {
            CellValue = new CellValue("2"),
            DataType = new EnumValue<CellValues>(CellValues.String) 
        },
        new Cell()
        {
            CellValue = new CellValue("李同学"),
            DataType = new EnumValue<CellValues>(CellValues.String) 
        },
        new Cell()
        {
            CellValue = new CellValue("19岁"),
            DataType = new EnumValue<CellValues>(CellValues.String) 
        },
        new Cell()
        {
            CellValue = new CellValue("二班"),
            DataType = new EnumValue<CellValues>(CellValues.String) 
        },
        new Cell()
        {
            CellValue = new CellValue("林老师"),
            DataType = new EnumValue<CellValues>(CellValues.String) 
        }
    });

    sheetData.AppendChild(row2);
}

 

　--》保存工作簿，持久化到文件

　　调用Workbook的save方法，和关闭文档对象（document.Close()方法）。由于创建文档的时候，并不是指定一个文件路径，而是通过一个Stream流, 所以还需要将流转换为文件流持久化，通过SaveToFile方法。

以下SaveToFile方法代码：

/// <summary>
/// 保存到文件
/// </summary>
public static void SaveToFile(MemoryStream ms)
{
    //当前运行时路径
    var directoryInfo = new DirectoryInfo(Directory.GetCurrentDirectory());
    var fileName = $@"PracticePart1-{DateTime.Now:yyyyMMddHHmmss}.xlsx";

    //文件路径，保存在运行时路径下
    var filepath = Path.Combine(directoryInfo.ToString(), fileName);

    var bytes = ms.ToArray();
    var fileStream = new FileStream(filepath, FileMode.Create, FileAccess.Write, FileShare.Read);
    fileStream.Write(bytes, 0, bytes.Length);
    fileStream.Flush();

    Console.WriteLine($"Save Path: {filepath}");
}

 

　--》执行代码生成Excel，解压文件对比

　　执行代码生成了Excel文件之后，打开展示就如文章第一图所展示的是一样的。 修改后缀名为zip解压后，打开文件夹，包含了workbook.xml 和 sharedStrings.xml 两个xml文件，和worksheets文件。跟上一节解压的文件对比， 没有style.xml文件， 因为我们在代码中，还没有涉及到 样式类型。

　　打开worksheets文件，有一个sheet1.xml文件，工作表文件。

　　打开sheet1.xml文件来看看，我们导出的数据，生成是怎么样的。

以下是sheet1.xml文件代码：

<?xml version="1.0" encoding="utf-8"?>
<x:worksheet xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
    <x:sheetFormatPr defaultColWidth="15" defaultRowHeight="15" />
    <x:cols>
        <x:col min="1" max="1" width="5" customWidth="1" />
        <x:col min="2" max="3" width="30" customWidth="1" />
    </x:cols>
    <x:sheetData>
        <x:row r="1">
            <x:c t="s">
                <x:v>0</x:v>
            </x:c>
            <x:c t="s">
                <x:v>1</x:v>
            </x:c>
            <x:c t="s">
                <x:v>2</x:v>
            </x:c>
            <x:c t="s">
                <x:v>3</x:v>
            </x:c>
            <x:c t="s">
                <x:v>4</x:v>
            </x:c>
        </x:row>
        <x:row r="2">
            <x:c t="str">
                <x:v>1</x:v>
            </x:c>
            <x:c t="str">
                <x:v>王同学</x:v>
            </x:c>
            <x:c t="str">
                <x:v>18岁</x:v>
            </x:c>
            <x:c t="str">
                <x:v>一班</x:v>
            </x:c>
            <x:c t="str">
                <x:v>林老师</x:v>
            </x:c>
        </x:row>
        <x:row r="3">
            <x:c t="str">
                <x:v>2</x:v>
            </x:c>
            <x:c t="str">
                <x:v>李同学</x:v>
            </x:c>
            <x:c t="str">
                <x:v>19岁</x:v>
            </x:c>
            <x:c t="str">
                <x:v>二班</x:v>
            </x:c>
            <x:c t="str">
                <x:v>林老师</x:v>
            </x:c>
        </x:row>
    </x:sheetData>
</x:worksheet>

　　从上面代码看，和前一节对比展示的对比，有一点不一样，就是元素节点前面加上了命名空间。 XML命名空间 主要是为了避免命名冲突而起，所以这里加上了命名空间则是更为严谨了一些而已。 从上面的代码可以看出，除了表头一行用的是共享字符串（<x:c t="s">），表格数据内容则是直接字符串内容（<x:c t="str">）。

对比看下sharedStrings.xml 文件的代码：

<?xml version="1.0" encoding="utf-8"?>
<x:sst xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
    <x:si>
        <x:t>序号</x:t>
    </x:si>
    <x:si>
        <x:t>学生姓名</x:t>
    </x:si>
    <x:si>
        <x:t>学生年龄</x:t>
    </x:si>
    <x:si>
        <x:t>学生班级</x:t>
    </x:si>
    <x:si>
        <x:t>辅导老师</x:t>
    </x:si>
</x:sst>

　　针对一些常用，并且可能重复性出现很多次的文本，是可以通过共享字符串来统一存储和访问的，其它部分可以直接放在各自的工作表里面。一方面主要是在代码逻辑处理上会比较麻烦，因为值的引用，是通过共享字符串在其子元素的位置索引。如果一般不是预先设置好，很难知道其要插入的字符串，是否在共享字符串列表里面存在了。 除非每次插入的时候，都去判断一下，不存在则插入，返回索引，若存在则直接返回索引。

　　文中源代码可以查阅Github: https://github.com/QingGuangWang/OpenXMLForExcelPractices/tree/master/PracticePart2

　　以上便是本节的内容，其中需要再次强调的是操作各个子元素的时候，需要注意其顺序，若有时候不知道什么顺序，或者要用什么元素，简单的情况下，就是先用office软件创建一个excel，设置你要的格式和数据，然后解压出来看看其中的XML文件，这个时候你大致可以了解到你想要的信息。