欢迎光临
我的个人博客网站

基于 DocumentFormat.OpenXml 操作 Excel (3)– 导出数据

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

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

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

  基于 DocumentFormat.OpenXml 操作 Excel (3)-- 导出数据

  

 –》项目准备

  通过 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 workbookPartWorksheetPart 。这三个部件是必须的,同时我们这里增加一个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 }

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

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

基于 DocumentFormat.OpenXml 操作 Excel (3)-- 导出数据

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

基于 DocumentFormat.OpenXml 操作 Excel (3)-- 导出数据

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

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

基于 DocumentFormat.OpenXml 操作 Excel (3)-- 导出数据

  但是我们导出,做测试的时候,还是需要以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文件, 因为我们在代码中,还没有涉及到 样式类型。

基于 DocumentFormat.OpenXml 操作 Excel (3)-- 导出数据

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

基于 DocumentFormat.OpenXml 操作 Excel (3)-- 导出数据

  打开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文件,这个时候你大致可以了解到你想要的信息。

 

赞(0) 打赏
未经允许不得转载:张拓的天空 » 基于 DocumentFormat.OpenXml 操作 Excel (3)– 导出数据
分享到: 更多 (0)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

专业的IT技术经验分享 更专业 更方便

联系我们本站主机

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏