Экспорт данных из DataTable(ADO.Net) с помощью XSLT. Часть 3 - Excel XML

При всех плюсах использования csv, следует сказать, что возможности его более чем скромны, особенно если сравнивать с возможностями книги Excel. Для того же, чтобы успешно использовать и другие возможности лучше обратиться к Таблице XML2003. Это словарь XML, разработанный специально для описания документов Excel. Для того, чтобы понять как он устроен надо создать документ нужной структуры в программе Excel, и сохранить его при помощи функции Сохранить как, а в диалоговом окне сохранения выбрать формат Таблица XML 2003. Документ будет сохранен как XML документ, далее его можно изучать. О возможностях Excel, которые не поддерживаются этим форматом можно прочитать на странице Поддерживаемые Excel форматы файлов (список неподдерживаемых в данном формате возможностей Excel в конце документа).

Возьмем для примера HTML таблицу, полученную в первой части, откроем ее в Excel и сохраним в означенном формате. Даже беглого просмотра документа вполне достаточно для того, чтобы понять смысл многих его элементов, поэтому возиться с ним долго не придется. Корневой элемент называется Workbook(рабочая книга), в нем список пространств имен, о назначении которых тоже несложно догадаться. Далее мы видим блок свойств документа

1:  <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
2:   <Title>DataTable with schema</Title>
3:   <Author>diadiavova</Author>
4:   <LastAuthor>diadiavova</LastAuthor>
5:   <Created>2011-06-04T17:46:26Z</Created>
6:   <Version>14.00</Version>
7:  </DocumentProperties>

Здесь тоже все ясно: заголовок, автор, последний автор, дата создания, версия.

Далее идут настройки положения и размеров окна и другая малоинтересная в данный момент информация. Список стилей, которые будут использоваться в документе, если понадобится отобразить таблицу каким-то особым образом, то на этот раздел следует обратить внимание. Но самое интересное – это элемент Table. Понятно, что это и есть наша таблица. Понимание значения таких элементов как Column, Row и Cell тоже не вызывает затруднений. Элементы Cell ссылаются на стили, описанные выше, при помощи атрибута ss:StyleID.

Данные в каждой ячейке содержатся в элементе Data, в котором можно указать тип данных(чего нельзя было сделать при использовании форматов, которые я описывал ранее) с помощью атрибута ss:Type.

Само преобразование ничего сложного из себя не представляет. Общий принцип его создания таков: создается книга Excel, в которой будет определена таблица нужного вида, далее это сохраняется в формате XML таблицы и этот документ переносится в преобразование почти без изменений, за исключением той области, где должны содержаться уникальные данные(строки таблицы). Разместить его надо в шаблоне, обрабатывающем корневой элемент исходного документа( match=“/”). В корневоЙ элемент преобразования надо скопировать пространства имен этой Excel таблицы с теми же префиксами. В нашем случае это будет выглядеть примерно так:

1: <?xml version="1.0" encoding="utf-8"?>
2: <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
3:     xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl"
4:     xmlns:xsd="http://www.w3.org/2001/XMLSchema"
5:     xmlns="urn:schemas-microsoft-com:office:spreadsheet"
6:     xmlns:o="urn:schemas-microsoft-com:office:office"
7:     xmlns:x="urn:schemas-microsoft-com:office:excel"
8:     xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
9:     xmlns:html="http://www.w3.org/TR/REC-html40">
10:   <xsl:output method="xml" indent="yes"/>
11: 
12:   <xsl:template match="/">
13:     <?mso-application progid="Excel.Sheet"?>
14:     <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
15:      xmlns:o="urn:schemas-microsoft-com:office:office"
16:      xmlns:x="urn:schemas-microsoft-com:office:excel"
17:      xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
18:      xmlns:html="http://www.w3.org/TR/REC-html40">
19:       <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
20:         <Title>DataTable with schema</Title>
21:         <Author>diadiavova</Author>
22:         <LastAuthor>diadiavova</LastAuthor>
23:         <Created>2011-06-04T17:46:26Z</Created>
24:         <Version>14.00</Version>
25:       </DocumentProperties>
26:       <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
27:         <AllowPNG/>
28:       </OfficeDocumentSettings>
29:       <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
30:         <WindowHeight>4680</WindowHeight>
31:         <WindowWidth>10515</WindowWidth>
32:         <WindowTopX>360</WindowTopX>
33:         <WindowTopY>120</WindowTopY>
34:         <ProtectStructure>False</ProtectStructure>
35:         <ProtectWindows>False</ProtectWindows>
36:       </ExcelWorkbook>
37:       <Styles>
38:         <Style ss:ID="Default" ss:Name="Normal">
39:           <Alignment ss:Vertical="Bottom"/>
40:           <Borders/>
41:           <Font ss:FontName="Calibri" x:CharSet="204" x:Family="Swiss" ss:Size="11"
42:            ss:Color="#000000"/>
43:           <Interior/>
44:           <NumberFormat/>
45:           <Protection/>
46:         </Style>
47:         <Style ss:ID="s63">
48:           <Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>
49:           <Borders>
50:             <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"
51:              ss:Color="#000000"/>
52:             <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"
53:              ss:Color="#000000"/>
54:             <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"
55:              ss:Color="#000000"/>
56:             <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"
57:              ss:Color="#000000"/>
58:           </Borders>
59:           <Font ss:FontName="Calibri" x:CharSet="204" x:Family="Swiss" ss:Color="#000000"
60:            ss:Bold="1"/>
61:         </Style>
62:         <Style ss:ID="s64">
63:           <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>
64:           <Borders>
65:             <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"
66:              ss:Color="#000000"/>
67:             <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"
68:              ss:Color="#000000"/>
69:             <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"
70:              ss:Color="#000000"/>
71:             <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"
72:              ss:Color="#000000"/>
73:           </Borders>
74:         </Style>
75:         <Style ss:ID="s65">
76:           <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>
77:           <Borders>
78:             <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"
79:              ss:Color="#000000"/>
80:             <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"
81:              ss:Color="#000000"/>
82:             <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"
83:              ss:Color="#000000"/>
84:             <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"
85:              ss:Color="#000000"/>
86:           </Borders>
87:           <Font ss:FontName="Calibri" x:CharSet="204" x:Family="Swiss" ss:Color="#000000"/>
88:         </Style>
89:         <Style ss:ID="s68">
90:           <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>
91:           <Borders>
92:             <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"
93:              ss:Color="#000000"/>
94:             <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"
95:              ss:Color="#000000"/>
96:             <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"
97:              ss:Color="#000000"/>
98:             <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"
99:              ss:Color="#000000"/>
100:           </Borders>
101:           <Font ss:FontName="Calibri" x:CharSet="204" x:Family="Swiss" ss:Color="#000000"/>
102:           <NumberFormat ss:Format="#,##0"/>
103:         </Style>
104:       </Styles>
105:       <Worksheet ss:Name="Customers_schema">
106:         <Table ss:ExpandedColumnCount="11" ss:ExpandedRowCount="92" x:FullColumns="1"
107:          x:FullRows="1" ss:DefaultRowHeight="15">
108:           <xsl:for-each select="/*/xsd:schema//xsd:element[count(ancestor::xsd:element) = 2]">
109:             <!--
110:             Здесь описать определение колонок
111:             -->
112:             <Column>
113:               <xsl:attribute name="Width" namespace="urn:schemas-microsoft-com:office:spreadsheet">
114: 
115:               </xsl:attribute>
116:             </Column>
117:           </xsl:for-each>
118: 
119:           <Column ss:Width="54"/>
120:           <Column ss:Width="158.25"/>
121:           <Column ss:Width="103.5"/>
122:           <Column ss:Width="132.75"/>
123:           <Column ss:Width="192"/>
124:           <Column ss:Width="66.75"/>
125:           <Column ss:Width="64.5"/>
126:           <Column ss:Width="51"/>
127:           <Column ss:Width="53.25"/>
128:           <Column ss:Width="78.75" ss:Span="1"/>
129:           <xsl:apply-templates select="/*/*[position() &gt; 1]"/>
130:         </Table>
131:         <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
132:           <Print>
133:             <ValidPrinterInfo/>
134:             <PaperSizeIndex>9</PaperSizeIndex>
135:             <HorizontalResolution>600</HorizontalResolution>
136:             <VerticalResolution>600</VerticalResolution>
137:           </Print>
138:           <Selected/>
139:           <DoNotDisplayGridlines/>
140:           <ProtectObjects>False</ProtectObjects>
141:           <ProtectScenarios>False</ProtectScenarios>
142:         </WorksheetOptions>
143:       </Worksheet>
144:     </Workbook>
145: 
146:   </xsl:template>
147: 
148: 
149:   <xsl:template match="/*/*[position() &gt; 1]">
150:     <Row>
151:       <xsl:for-each select="/*/xsd:schema//xsd:element[count(ancestor::xsd:element) = 2]">
152:         <!--
153:             Здесь описать определение каждой ячейки
154:             -->
155:       </xsl:for-each>
156:     </Row>
157:   </xsl:template>
158: </xsl:stylesheet>
159: 

Там, где должны вставляться индивидуальные данные, я вставил комментарии.

В любом случае, думаю, что принцип понятен и долго говорить об этом нет смысла. Есть однако же несколько вещей, о которых упомянуть стоит. Во-первых, это определение типов данных. В схеме типы описываются по разному: иногда это атрибут type; иногда тип определяется прямо в определении элемента, по всей видимости ожидать приходится, что это будет SimpleType, но даже с ним не все так просто, поскольку способов описания простого типа несколько и там может быть множество нюансов с его извлечением из схемы. Но даже если эта проблема будет решена, то полученные сведения о типе надо будет еще обработать таким образом, чтобы это было понятно Excel, поскольку там свой собственный набор форматов ячеек. В наиболее сложных случаях, по всей видимости возможностями чистых XSLT и XPath обойтись не удастся и придется снова прибегать к внедрению скриптов в преобразование.

Другая проблема – форматирование данных при записи таблицы в XML. При преобразовании некоторые типы данных нуждаются в учете культуры. Формат даты, десятичный разделитель чисел и прочие тонкости совсем необязательно должны совпадать с форматами аналогичных данный в Excel, поэтому на эти аспекты следует обратить особое внимание.

Ну и что касается формул, то тут задача даже несколько упрощается по сравнению с CSV, поскольку можно указывать формулы с относительной адресацией, то есть во всех строках формула будет одинаковой, применительно к примеру из прошлой записи она будет выглядеть так

RC[-2]+RC[-1]

То есть в отличие от A2+B2 и т.п. здесь указывается на то, что ссылка ведется на ячейку в той же строке, а номер колонки вычисляется по числу в квадратных скобках, оно должно прибавиться к номеру текущей ячейки(в данном случае оба числа – отрицательные). Формулу надо вставлять в атрибут ss:Formula ячейки. А вычисляется она просто как

RC[<индекс целевой ячейки> – <индекс текущей ячейки>] 

Это проще, чем вычислять буквенное обозначение ячейки, да и номер строки указывать не надо.