В первой части я рассмотрел преобразование XML документа, сохраненного из DataTable в формат HTML. Это преобразование, по моему мнению, следовало рассмотреть в первую очередь, в силу того, что HTML обладает массой преимуществ перед другими форматами и в этом смысле является наиболее важным и востребованным. Перечислю некоторые из преимуществ экспорта в HTML:
- Это универсальный формат, с помощью которого можно отобразить практически все, что может понадобиться и даже больше.
- Кроме возможностей представления он обладает еще и интерактивностью и другими возможностями, свойственными скорей приложениям, нежели документам(благодаря встроенным javascript и css, конечно же).
- Формат прост и знаком многим, что дает возможность легко обрабатывать документы, написанные на нем(да и для создания документов это тоже важно).
- HTML документ может быть прочитан на любом компьютере, не зависимо от того, какой браузер установлен и даже какая операционная система.
- Практически любая программа, предназначенная для обработки документов, понимает этот формат, а это и Word, и Excel, и Writer, и Calc( последние две – из OpenOffice) и многие другие. Поэтому во многих случаях в экспорте в форматы этих программ просто нет никакой необходимости. К примеру документ, созданный в первой части, прекрасно отображается во всех перечисленных программах, а когда он открыт в одной из них, он может быть сохранен в любой другой формат, поддерживаемый этой программой.
Не смотря на вышеперечисленные плюсы, следует сказать и о том, что HTML – формат представления данных, а не их описания. В силу этого обстоятельства в нем отсутствует ряд возможностей, которые могут понадобиться для правильного понимания содержимого документа некоторыми программами. Если говорить об Excel, то есть как минимум два обстоятельства, из-за которых HTML может оказаться не вполне подходящим форматом экспорта, а именно: в HTML отсутствует возможность указать (каким-то универсальным способом) тип данных; а так же, в Excel некоторые ячейки могут содержать формулы, что так же может оказаться востребованным, в силу того, что в DataTable они тоже поддерживаются в свойстве Expression столбцов таблиц.В силу вышеозначенных причин, необходимость в экспорте в более “родной” для этой программы формат все таки есть. Но как подобраться к двоичному формату Excel?
Если речь идет об Excel 2007 и более поздним версиям, то можно добраться до XML данных xlsx файла, поскольку он представляет из себя zip-архив, в котором данные хранятся именно в XML документах. Но это тема для отдельного разговора и здесь речь не об этом. Решение проблемы в том, что программа поддерживает целый ряд форматов, некоторые из которых совсем не сложны.
CSV
Для начала можно рассмотреть формат csv. Он не дает возможности описать все, что может присутствовать в документе Excel, но, к примеру, формулы в csv файл вводить можно. Кроме того, этот формат обладает еще одним плюсом, а именно – он более компактен, чем любой другой формат(если не считать архивов, разумеется, но и его тоже можно сжать). Структура csv файла предельно проста: ячейки отделяются друг от друга точкой с запятой(;), строки – каждая пишется на отдельной строке текстового документа. То есть csv файл(а они имеют расширение csv) следующей структуры
Фамилия;Имя;ОтчествоИванов;Иван;ИвановичПетров;Петр;Петрович
Прекрасно отобразится в Excel. Мало того, если в csv файл мы введем
1;2;=A1+B13;4;=A2+B2
то, открыв этот документ в программе, мы увидим
1;2;33;4;7
поскольку в последней колонке будут содержаться формулы, а не просто текст.
Теперь о самом преобразовании… Для начала скажу, что выполнить экспорт в коде совсем несложно. Можно сделать это к примеру так
1: Sub SaveTableAsCsv(table As DataTable, fileName As String)2: Using sw As New IO.StreamWriter(fileName)3: sw.WriteLine(String.Join(";", From col As DataColumn In table.Columns Select col.Caption))4: For Each r As DataRow In table.Rows5: sw.WriteLine(String.Join(";", From cell In r.ItemArray Select (cell.ToString)))6: Next7: End Using8: End Sub
Правда тут сразу возникают проблемы, например cell.ToString может неправильно отформатировать значение, поэтому нужна возможность как-то управлять этим процессом. И опять таки формулы : иногда они нужны, иногда – нет, а если учитывать все, то и код окажется более сложным. Поэтому лучше сделать все единообразным способом, код будет универсальным и логика экспорта будет вынесена за его пределы.
XSLT мы можем получить из преобразования, написанного для HTML, если слегка его подправим. Для этого надо указать метод выходных данных text
<xsl:output method="text" indent="no"/>
А так же убрать HTML разметку и заменить ее разделителями свойственные csv, то есть точками с запятыми и новыми строками. Вот, собственно, как это выглядит
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:xsd="http://www.w3.org/2001/XMLSchema">4: <xsl:output method="text" indent="no"/>5:6: <xsl:template match="/">7: <xsl:for-each select="//xsd:element[count(ancestor::xsd:element) = 2]">8: <xsl:value-of select="@name"/>9: <xsl:if test="following-sibling::xsd:element">10: <xsl:text>;</xsl:text>11: </xsl:if>12: </xsl:for-each>13: <xsl:text> </xsl:text>14: <xsl:apply-templates15: select="/*/*[namespace-uri() != 'http://www.w3.org/2001/XMLSchema']"/>16: </xsl:template>17:18: <xsl:template match="/*/*[namespace-uri() != 'http://www.w3.org/2001/XMLSchema']">19: <xsl:variable name="currentrow" select="."/>20: <xsl:for-each select="//xsd:element[count(ancestor::xsd:element) = 2]">21: <xsl:variable name="column" select="@name"/>22: <xsl:value-of select="$currentrow/*[name() = $column]"/>23: <xsl:if test="following-sibling::xsd:element">24: <xsl:text>;</xsl:text>25: </xsl:if>26: </xsl:for-each>27: <xsl:text> </xsl:text>28: </xsl:template>29: </xsl:stylesheet>
Формулы
Учитывая, что я упомянул возможность использования в csv как одну из причин экспорта в этот формат, скажу так же и о том, как это сделать. В качестве примера использую простейшую таблицу из трех колонок, в певых двух будут числа, в третей выражение суммы чисел из первых двух столбцов. То есть таблица будет выглядеть так
| ColumnFirst | ColumnSecond | ColumnSum |
|---|---|---|
| 1 | 2 | ColumnFirst+ColumnSecond |
| 3 | 4 | ColumnFirst+ColumnSecond |
В третьей колонке, естественно, ничего нет, но свойству Expression колонки присвоено выражение, которое я отобразил во всех ее ячейках. Вот XML определение этой таблицы вместе со схемой
1: <?xml version="1.0" standalone="yes"?>2: <NewDataSet>3: <xs:schema id="NewDataSet" xmlns=""4: xmlns:xs="http://www.w3.org/2001/XMLSchema"5: xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">6: <xs:element name="NewDataSet" msdata:IsDataSet="true"7: msdata:MainDataTable="Table1"8: msdata:UseCurrentLocale="true">9: <xs:complexType>10: <xs:choice minOccurs="0" maxOccurs="unbounded">11: <xs:element name="Table1">12: <xs:complexType>13: <xs:sequence>14: <xs:element name="ColumnFirst" type="xs:int" minOccurs="0" />15: <xs:element name="ColumnSecond" type="xs:int" minOccurs="0" />16: <xs:element name="ColumnSum" msdata:ReadOnly="true"17: msdata:Expression="ColumnFirst+ColumnSecond"18: type="xs:int" minOccurs="0" />19: </xs:sequence>20: </xs:complexType>21: </xs:element>22: </xs:choice>23: </xs:complexType>24: </xs:element>25: </xs:schema>26: <Table1>27: <ColumnFirst>1</ColumnFirst>28: <ColumnSecond>2</ColumnSecond>29: <ColumnSum>3</ColumnSum>30: </Table1>31: <Table1>32: <ColumnFirst>3</ColumnFirst>33: <ColumnSecond>4</ColumnSecond>34: <ColumnSum>7</ColumnSum>35: </Table1>36: </NewDataSet>
Документ csv, который нам надо получить выглядит так
ColumnFirst;ColumnSecond;ColumnSum
1;2;=A2+B2
3;4;=A3+B3
Казалось бы – ничего сложного и это мало чем отличается от того, что было сделано в предыдущем примере, за исключением того, что из схемы надо извлечь формулу. Но не все так просто: во-первых, выражение нашей таблицы ссылается на другие колонки по именам этих колонок, а не по принятой в Excel системе адресации; во-вторых, само выражение придется изменять, а для этого его на до сначала распарсить. К сожалению, решить эти проблемы исключительно средствами XSLT, если и можно, то решение окажется настолько сложным и корявым, что использование этого формата в принципе потеряет смысл, поскольку его главными преимуществами являются: простота, понятность и декларативность. Здесь же нам придется для выполнения операций обыденных с точки зрения других языков, написать большое количество кода, в котором потом и разобраться-то будет ой как непросто.
Решить проблему можно, используя расширение формата собственными функциями, написанными на одном из языков .Net Framework. Существует два механизма, позволяющих это сделать: в коде или прямо в преобразовании. Преимуществом первого механизма является лучшая производительность и то, что большое количество кода не потребуется писать в тексте xslt. Но у него есть и недостаток – сложно предусмотреть в коде все, что может понадобиться. И хотя существуют библиотеки с готовыми решениям на все случаи жизни, я, тем не менее, приведу пример с внедренным кодом на VB.Net.
Для начала решим проблему обозначений колонок. Для этого нам понадобится вспомогательная функция, которая будет принимать индекс колонки в нашей таблице и возвращать буквенное обозначение в нотации Excel. Сначала колонки обозначаются буквами латинского алфавита, потом(когда алфавит заканчивается) обозначения становятся двухбуквенными.
1: Function GetExcelCode(ByVal num As Integer)2: Dim aChar = Asc("A")3: Dim sec As Integer = num Mod 26 + aChar4: Dim frst As Integer = num \ 26 + aChar - 15: Return If(num < 26, "", Chr(frst)) & Chr(sec)6: End Function
Следующая вспомогательная функция принимает коллекцию имен столбцов, текст выражения как оно определено в свойствах столбца таблицы и номер строки. Номер строки нам нужен потому что он присутствует в выражении( например в выражении A8+B8, 8 – как раз и есть номер строки). Замена производится с помощью регулярного выражения
(?<! [\'"])\b(col1|col2|col3)\b(?! [\'"])
данном случае col1, col2 и col3 на самом деле заменяют настоящие имена колонок, то есть паттерн выражения формируется программно. Далее при замене определяется индекс колонки, он передается методу GetExcelCode и к полученному коду колонки Excel добавляется номер строки.
1: Function GetExcelExpr(2: columnNames As IEnumerable(Of String),3: expression As String, rowNumber As Integer) As String4: Static columnRegex As New Regex(5: String.Format("(?<! [\'""])\b({0})\b(?! [\'""])",6: String.Join("|", columnNames.ToArray)),7: RegexOptions.IgnorePatternWhitespace Or RegexOptions.Compiled)8:9: Dim columnChars As New Dictionary(Of String, String)10: For i As Integer = 0 To columnNames.Count - 111: columnChars.Add(columnNames(i), GetExcelCode(i) & rowNumber)12: Next13: Return columnRegex.Replace(14: expression,15: Function(x As Match) As String16: Return columnChars(x.Value)17: End Function)18: End Function
Обе вспомогательных функции не будут вызываться непосредственно из преобразования, оттуда мы будем вызывать следующую функцию. Сигнатура этой функции очень похожа на предыдущую, только первый параметр представляет из себя узел схемы. Из этого узла извлекается список столбцов и дальше все это передается функции GetExcelExpr, описанной выше. При извлечении следует использовать NamespaceManager для того, чтобы указать какому пространству имен соответствует какой префикс. Например в данном случае, для пространства схемы мы используем разные префиксы в исходном документе и в преобразовании. Вот для того чтобы не было разночтений в интерпретации префиксов требуется этот объект.
1: Function GetExcelExpression(2: ByVal schema As XPathNavigator,3: ByVal expression As String, rowNumber As Integer) As String4: If schema IsNot Nothing Then5: Dim xpath = "//xsd:element[count(ancestor::xsd:element) = 2]/@name"6: Dim nsmng As New Xml.XmlNamespaceManager(schema.NameTable)7: nsmng.AddNamespace("xsd", "http://www.w3.org/2001/XMLSchema")8: Dim columnNames = From cn As XPathNavigator In schema.Select(xpath,nsmng)9: Select cn.Value10:11: Return GetExcelExpr(columnNames, expression, rowNumber)12: Else13: Return expression14: End If15: End Function
Теперь, когда вспомогательные функции готовы, можно приступить к написанию преобразования. Сразу хочу заметить, что код проверялся только на простейших примерах и не претендует на исчерпанность. Регулярное выражение может работать только с определенным набором имен, хотя правила именования столбцов в таблице позволяют больше. При извлечении предполагается, что имена по крайней мере начинаются и кончаются алфавитно-цифровыми символами( хотя это ограничение легко убрать, достаточно удалить последовательности \b из выражения) , если они не заключены в кавычки(одинарные или двойные. хотя, строго говоря речь не о заключении в кавычки, а о том, что слово не должно граничить с кавычками и апострофами). Тут не учтено множество частных случаев, таких, например, как совпадение имен колонок с именами функций и т. д. Естественно, для более общего случая пример пришлось бы несколько усложнить, но это уже частности и скорей всего усложнению следует подвергнуть регулярное выражение, а не весь код. Кроме того, следует помнить так же и о том, что в выражениях могут использоваться функции, а имена функций в выражениях свойства Expression совсем не обязательно должны совпадать с именами аналогичных функций в 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" xmlns:custom="urn:schemas-custom"5: xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">6: <xsl:output method="text" indent="no"/>7:8: <xsl:template match="/">9: <xsl:for-each select="//xsd:element[count(ancestor::xsd:element) = 2]">10: <xsl:value-of select="@name"/>11: <xsl:if test="following-sibling::xsd:element">12: <xsl:text>;</xsl:text>13: </xsl:if>14: </xsl:for-each>15: <xsl:text> </xsl:text>16: <xsl:apply-templates17: select="/*/*[namespace-uri() != 'http://www.w3.org/2001/XMLSchema']"/>18: </xsl:template>19:20: <xsl:template match="/*/*[namespace-uri() != 'http://www.w3.org/2001/XMLSchema']">21: <xsl:variable name="tablename" select="name()"/>22: <xsl:variable name="rownumber" select="count(preceding-sibling::*[name() = $tablename]) + 2"/>23: <xsl:variable name="schema" select="//xsd:schema"/>24: <xsl:variable name="currentrow" select="."/>25: <xsl:for-each select="//xsd:element[count(ancestor::xsd:element) = 2]">26: <xsl:variable name="column" select="@name"/>27: <xsl:choose>28: <xsl:when test="@msdata:Expression">29: <xsl:text>=</xsl:text>30: <xsl:value-of select="custom:GetExcelExpression($schema, @msdata:Expression, $rownumber)"/>31: </xsl:when>32: <xsl:otherwise>33: <xsl:value-of select="$currentrow/*[name() = $column]"/>34: </xsl:otherwise>35: </xsl:choose>36: <xsl:if test="following-sibling::xsd:element">37: <xsl:text>;</xsl:text>38: </xsl:if>39: </xsl:for-each>40: <xsl:text> </xsl:text>41: </xsl:template>42:43:44: <msxsl:script language="vb" implements-prefix="custom">45: <msxsl:assembly name="System"/>46: <msxsl:assembly name="System.Core"/>47: <msxsl:using namespace="System.Linq"/>48: <msxsl:using namespace="System.Text.RegularExpressions"/>49: <msxsl:using namespace="System.Xml.Xpath"/>50: <msxsl:using namespace="System.Collections.Generic"/>51: <![CDATA[52:53: Function GetExcelExpression(54: ByVal schema As XPathNavigator,55: ByVal expression As String, rowNumber As Integer) As String56: If schema IsNot Nothing Then57: Dim xpath = "//xsd:element[count(ancestor::xsd:element) = 2]/@name"58: Dim nsmng As New Xml.XmlNamespaceManager(schema.NameTable)59: nsmng.AddNamespace("xsd", "http://www.w3.org/2001/XMLSchema")60: Dim columnNames = From cn As XPathNavigator In schema.Select(xpath,nsmng)61: Select cn.Value62:63: Return GetExcelExpr(columnNames, expression, rowNumber)64: Else65: Return expression66: End If67: End Function68:69: Function GetExcelExpr(70: columnNames As IEnumerable(Of String),71: expression As String, rowNumber As Integer) As String72: Static columnRegex As New Regex(73: String.Format("(?<! [\'""])\b({0})\b(?! [\'""])",74: String.Join("|", columnNames.ToArray)),75: RegexOptions.IgnorePatternWhitespace Or RegexOptions.Compiled)76:77: Dim columnChars As New Dictionary(Of String, String)78: For i As Integer = 0 To columnNames.Count - 179: columnChars.Add(columnNames(i), GetExcelCode(i) & rowNumber)80: Next81: Return columnRegex.Replace(82: expression,83: Function(x As Match) As String84: Return columnChars(x.Value)85: End Function)86: End Function87:88: Function GetExcelCode(ByVal num As Integer)89: Dim aChar = Asc("A")90: Dim sec As Integer = num Mod 26 + aChar91: Dim frst As Integer = num \ 26 + aChar - 192: Return If(num < 26, "", Chr(frst)) & Chr(sec)93: End Function94: ]]>95: </msxsl:script>96: </xsl:stylesheet>97:
Комментариев нет :
Отправить комментарий