-
基于 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，创建表头，创建表格数据 单独放一个方法 。
1 public static void Main(string[] args) 2 { 3 //构建一个MemoryStream 4 var ms = new MemoryStream(); 5 6 //创建Workbook, 指定为Excel Workbook (*.xlsx). 7 var document = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook); 8 9 //创建WorkbookPart（工作簿） 10 var workbookPart = document.AddWorkbookPart(); 11 workbookPart.Workbook = new Workbook(); 12 13 //构建SharedStringTablePart 14 var shareStringPart = workbookPart.AddNewPart<SharedStringTablePart>(); 15 shareStringPart.SharedStringTable = new SharedStringTable(); //创建根元素 16 17 //创建WorksheetPart（工作簿中的工作表） 18 var worksheetPart = workbookPart.AddNewPart<WorksheetPart>(); 19 20 //Workbook 下创建Sheets节点, 建立一个子节点Sheet，关联工作表WorksheetPart 21 var sheets = document.WorkbookPart.Workbook.AppendChild<Sheets>( 22 new Sheets(new Sheet() 23 { 24 Id = document.WorkbookPart.GetIdOfPart(worksheetPart), 25 SheetId = 1, 26 Name = "myFirstSheet" 27 })); 28 29 //初始化Worksheet 30 InitWorksheet(worksheetPart); 31 32 //创建表头 （序号，学生姓名，学生年龄，学生班级，辅导老师） 33 CreateTableHeader(worksheetPart, shareStringPart); 34 35 //创建内容数据 36 CreateTableBody(worksheetPart, shareStringPart); 37 38 workbookPart.Workbook.Save(); 39 document.Close(); 40 41 //保存到文件 42 SaveToFile(ms); 43 Console.WriteLine("End."); 44 }
--》初始化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开始。
具体初始化代码如下：
1 /// <summary> 2 /// 初始化工作表 3 /// </summary> 4 /// <param name="worksheetPart"></param> 5 public static void InitWorksheet(WorksheetPart worksheetPart) 6 { 7 //构建Worksheet根节点，同时追加子节点SheetData 8 worksheetPart.Worksheet = new Worksheet(new SheetData()); 9 //获取Worksheet对象 10 var worksheet = worksheetPart.Worksheet; 11 12 //SheetFormatProperties, 设置默认行高度，宽度， 值类型是Double类型。 13 var sheetFormatProperties = new SheetFormatProperties() 14 { 15 DefaultColumnWidth = 15d, 16 DefaultRowHeight = 15d 17 }; 18 19 //插入SheetFormatProperties，插入到SheetData的前面。通过InsertBefore方法，而不是Append 20 //顺序不能错误，否则会导致office打开提示错误，所以一般最好提前在一个列表或者数组，放好顺序再一次性加入 21 worksheet.InsertBefore(sheetFormatProperties, worksheet.GetFirstChild<SheetData>()); 22 23 //初始化列宽 第一列 5个单位， 第二列~第四列 30个单位 24 var columns = new Columns(); 25 //列，从1开始算起。 26 var column1 = new Column 27 { 28 Min = 1, Max = 1, Width = 5d, CustomWidth = true 29 }; 30 var column2 = new Column 31 { 32 Min = 2, Max =3, Width = 30d, CustomWidth = true 33 }; 34 35 columns.Append(column1, column2); 36 37 //插入Column1对象， 它的位置是在SheetFormatProperties 的后面，但是在SheetData的前面。 38 //worksheet.Append(columns); 直接追加在后面，office打开提示错误 39 worksheet.InsertAfter(columns, worksheet.GetFirstChild<SheetFormatProperties>()); 40 41 //最好是前面弄好对象，再一次性插入，或者初始化时先创建对象，用的时候直接拿出来。 42 //worksheet.Append(new OpenXmlElement[] 43 //{ 44 // new SheetFormatProperties(), 45 // new Columns(), 46 // new SheetData() 47 //}); 48 }
这里需要注意的一点 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方法，具体如下：
1 /// <summary> 2 /// 创建表头。 （序号，学生姓名，学生年龄，学生班级，辅导老师） 3 /// </summary> 4 /// <param name="worksheetPart">WorksheetPart 对象</param> 5 /// <param name="shareStringPart">SharedStringTablePart 对象</param> 6 public static void CreateTableHeader(WorksheetPart worksheetPart, SharedStringTablePart shareStringPart) 7 { 8 //获取Worksheet对象 9 var worksheet = worksheetPart.Worksheet; 10 11 //获取表格的数据对象，SheetData 12 var sheetData = worksheet.GetFirstChild<SheetData>(); 13 14 //插入第一行数据，作为表头数据 创建 Row 对象，表示一行 15 var row = new Row 16 { 17 //设置行号，从1开始，不是从0 18 RowIndex = 1 19 }; 20 21 //Row下面，追加Cell对象 22 row.AppendChild(CreateTableHeaderCell("序号", shareStringPart)); 23 row.AppendChild(CreateTableHeaderCell("学生姓名", shareStringPart)); 24 row.AppendChild(CreateTableHeaderCell("学生年龄", shareStringPart)); 25 row.AppendChild(CreateTableHeaderCell("学生班级", shareStringPart)); 26 row.AppendChild(CreateTableHeaderCell("辅导老师", shareStringPart)); 27 28 sheetData.AppendChild(row); 29 } 30 31 /// <summary> 32 /// 创建表头的单元格 33 /// </summary> 34 public static Cell CreateTableHeaderCell(string headerStr, SharedStringTablePart shareStringPart) 35 { 36 //共享字符串表 37 var sharedStringTable = shareStringPart.SharedStringTable; 38 39 //把字符串追加到共享 40 sharedStringTable.AppendChild(new SharedStringItem(new Text(headerStr))); 41 var index = sharedStringTable.ChildElements.Count - 1; //获取索引 42 43 var cell = new Cell 44 { 45 //设置值，这里的值是引用 共享字符串里面的对应的索引，就是上面添加的SharedStringItem的子元素的位置。 46 CellValue = new CellValue(index.ToString()), 47 //设置值类型是共享字符串 48 DataType = new EnumValue<CellValues>(CellValues.SharedString) 49 }; 50 51 return cell; 52 }
--》创建表格数据内容的信息
创建了表头部分，接下来就是开始创建表格内容数据了，其实方法和创建表头是一样，只是这里不使用 SharedStringTablePart 对象，换另外一种方式尝试下和对比，就是直接输出字符串， Cell对象下的属性，DataType属性，值为枚举类CellValues 指定的 String。 CellValue 对象构建的时候，输入的就不是索引值，而是具体的内容字符串了。
具体代码如下：
1 public static void CreateTableBody(WorksheetPart worksheetPart) 2 { 3 //获取Worksheet对象 4 var worksheet = worksheetPart.Worksheet; 5 6 //获取表格的数据对象，SheetData 7 var sheetData = worksheet.GetFirstChild<SheetData>(); 8 9 //插入第一行数据，作为表头数据 创建 Row 对象，表示一行 10 var row1 = new Row 11 { 12 RowIndex = 2 13 }; 14 15 row1.Append(new OpenXmlElement[] 16 { 17 new Cell() 18 { 19 CellValue = new CellValue("1"), 20 DataType = new EnumValue<CellValues>(CellValues.String) 21 }, 22 new Cell() 23 { 24 CellValue = new CellValue("王同学"), 25 DataType = new EnumValue<CellValues>(CellValues.String) 26 }, 27 new Cell() 28 { 29 CellValue = new CellValue("18岁"), 30 DataType = new EnumValue<CellValues>(CellValues.String) 31 }, 32 new Cell() 33 { 34 CellValue = new CellValue("一班"), 35 DataType = new EnumValue<CellValues>(CellValues.String) 36 }, 37 new Cell() 38 { 39 CellValue = new CellValue("林老师"), 40 DataType = new EnumValue<CellValues>(CellValues.String) 41 } 42 }); 43 44 sheetData.AppendChild(row1); 45 46 var row2 = new Row 47 { 48 RowIndex = 3 49 }; 50 51 row2.Append(new OpenXmlElement[] 52 { 53 new Cell() 54 { 55 CellValue = new CellValue("2"), 56 DataType = new EnumValue<CellValues>(CellValues.String) 57 }, 58 new Cell() 59 { 60 CellValue = new CellValue("李同学"), 61 DataType = new EnumValue<CellValues>(CellValues.String) 62 }, 63 new Cell() 64 { 65 CellValue = new CellValue("19岁"), 66 DataType = new EnumValue<CellValues>(CellValues.String) 67 }, 68 new Cell() 69 { 70 CellValue = new CellValue("二班"), 71 DataType = new EnumValue<CellValues>(CellValues.String) 72 }, 73 new Cell() 74 { 75 CellValue = new CellValue("林老师"), 76 DataType = new EnumValue<CellValues>(CellValues.String) 77 } 78 }); 79 80 sheetData.AppendChild(row2); 81 }
--》保存工作簿，持久化到文件
调用Workbook的save方法，和关闭文档对象（document.Close()方法）。由于创建文档的时候，并不是指定一个文件路径，而是通过一个Stream流, 所以还需要将流转换为文件流持久化，通过SaveToFile方法。
以下SaveToFile方法代码：
1 /// <summary> 2 /// 保存到文件 3 /// </summary> 4 public static void SaveToFile(MemoryStream ms) 5 { 6 //当前运行时路径 7 var directoryInfo = new DirectoryInfo(Directory.GetCurrentDirectory()); 8 var fileName = $@"PracticePart1-{DateTime.Now:yyyyMMddHHmmss}.xlsx"; 9 10 //文件路径，保存在运行时路径下 11 var filepath = Path.Combine(directoryInfo.ToString(), fileName); 12 13 var bytes = ms.ToArray(); 14 var fileStream = new FileStream(filepath, FileMode.Create, FileAccess.Write, FileShare.Read); 15 fileStream.Write(bytes, 0, bytes.Length); 16 fileStream.Flush(); 17 18 Console.WriteLine($"Save Path: {filepath}"); 19 }
--》执行代码生成Excel，解压文件对比
执行代码生成了Excel文件之后，打开展示就如文章第一图所展示的是一样的。 修改后缀名为zip解压后，打开文件夹，包含了workbook.xml 和 sharedStrings.xml 两个xml文件，和worksheets文件。跟上一节解压的文件对比， 没有style.xml文件， 因为我们在代码中，还没有涉及到 样式类型。
打开worksheets文件，有一个sheet1.xml文件，工作表文件。
打开sheet1.xml文件来看看，我们导出的数据，生成是怎么样的。
以下是sheet1.xml文件代码：
1 <?xml version="1.0" encoding="utf-8"?> 2 <x:worksheet xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main"> 3 <x:sheetFormatPr defaultColWidth="15" defaultRowHeight="15" /> 4 <x:cols> 5 <x:col min="1" max="1" width="5" customWidth="1" /> 6 <x:col min="2" max="3" width="30" customWidth="1" /> 7 </x:cols> 8 <x:sheetData> 9 <x:row r="1"> 10 <x:c t="s"> 11 <x:v>0</x:v> 12 </x:c> 13 <x:c t="s"> 14 <x:v>1</x:v> 15 </x:c> 16 <x:c t="s"> 17 <x:v>2</x:v> 18 </x:c> 19 <x:c t="s"> 20 <x:v>3</x:v> 21 </x:c> 22 <x:c t="s"> 23 <x:v>4</x:v> 24 </x:c> 25 </x:row> 26 <x:row r="2"> 27 <x:c t="str"> 28 <x:v>1</x:v> 29 </x:c> 30 <x:c t="str"> 31 <x:v>王同学</x:v> 32 </x:c> 33 <x:c t="str"> 34 <x:v>18岁</x:v> 35 </x:c> 36 <x:c t="str"> 37 <x:v>一班</x:v> 38 </x:c> 39 <x:c t="str"> 40 <x:v>林老师</x:v> 41 </x:c> 42 </x:row> 43 <x:row r="3"> 44 <x:c t="str"> 45 <x:v>2</x:v> 46 </x:c> 47 <x:c t="str"> 48 <x:v>李同学</x:v> 49 </x:c> 50 <x:c t="str"> 51 <x:v>19岁</x:v> 52 </x:c> 53 <x:c t="str"> 54 <x:v>二班</x:v> 55 </x:c> 56 <x:c t="str"> 57 <x:v>林老师</x:v> 58 </x:c> 59 </x:row> 60 </x:sheetData> 61 </x:worksheet>
从上面代码看，和前一节对比展示的对比，有一点不一样，就是元素节点前面加上了命名空间。 XML命名空间 主要是为了避免命名冲突而起，所以这里加上了命名空间则是更为严谨了一些而已。 从上面的代码可以看出，除了表头一行用的是共享字符串（<x:c t="s">），表格数据内容则是直接字符串内容（<x:c t="str">）。
对比看下sharedStrings.xml 文件的代码：
1 <?xml version="1.0" encoding="utf-8"?> 2 <x:sst xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main"> 3 <x:si> 4 <x:t>序号</x:t> 5 </x:si> 6 <x:si> 7 <x:t>学生姓名</x:t> 8 </x:si> 9 <x:si> 10 <x:t>学生年龄</x:t> 11 </x:si> 12 <x:si> 13 <x:t>学生班级</x:t> 14 </x:si> 15 <x:si> 16 <x:t>辅导老师</x:t> 17 </x:si> 18 </x:sst>
针对一些常用，并且可能重复性出现很多次的文本，是可以通过共享字符串来统一存储和访问的，其它部分可以直接放在各自的工作表里面。一方面主要是在代码逻辑处理上会比较麻烦，因为值的引用，是通过共享字符串在其子元素的位置索引。如果一般不是预先设置好，很难知道其要插入的字符串，是否在共享字符串列表里面存在了。 除非每次插入的时候，都去判断一下，不存在则插入，返回索引，若存在则直接返回索引。
文中源代码可以查阅Github: https://github.com/QingGuangWang/OpenXMLForExcelPractices/tree/master/PracticePart2
以上便是本节的内容，其中需要再次强调的是操作各个子元素的时候，需要注意其顺序，若有时候不知道什么顺序，或者要用什么元素，简单的情况下，就是先用office软件创建一个excel，设置你要的格式和数据，然后解压出来看看其中的XML文件，这个时候你大致可以了解到你想要的信息。
