Главная » XSLT » Работа с электронными таблицами в формате Excel XML

0

Задача

Требуется экспортировать данные из Excel в XML, но не в том формате, кото­рый предлагает Microsoft.

Решение XSLT 1.0

Пусть есть такая электронная таблица Excel:

Date                      Price                                  Volume

20010817       61.88                                260163

20010820                        62.7                                  241859

20010821                        6 0.78                              233989

2001082                           2____________________ 60.66____________________________ 387444           

При выводе в формате XML (в версиях Excel XP или 2003) получается следу­ющий результат:

<?xml version="1.0"?>

<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas- microsoft-com:office:excel"

xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http:// www.w3.org/TR/REC-html40">

<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"> <Author>Salvatore R. Mangano</Author> <LastAuthor>Salvatore R. Mangano</LastAuthor> <Created>2002-08-18T00:43:49Z</Created> <LastSaved>2 0 02-08-18T02:19:21Z</LastSaved> <Company>Descriptix</Company> <Version>10.35 01</Version> </DocumentProperties>

<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office"> <DownloadComponents/> <LocationOfComponents HRef="/"/> </OfficeDocumentSettings>

<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel"> <WindowHeight>9 915</WindowHeight> <WindowWidth>1014 0</WindowWidth> <WindowTopX>2 4 0</WindowTopX> <WindowTopY>25 5</WindowTopY> <ProtectStructure>False</ProtectStructure> <ProtectWindows>False</ProtectWindows> </ExcelWorkbook> <Styles>

<Style ss:ID="Default" ss:Name="Normal"> <Alignment ss:Vertical="Bottom"/> <Borders/> <Font/>

<Interior/> <NumberFormat/> <Protection/> </Style> </Styles>

<Worksheet ss:Name="msft"> <Table ss:ExpandedColumnCount="3" ss:ExpandedRowCount="5" x:FullColumns="1" x:FullRows="1">

<Row> <Cell>

<Data ss:Type="String">Date</Data> </Cell> <Cell>

<Data ss:Type="String">Price</Data> </Cell> <Cell>

<Data ss:Type="String">Volume</Data>

</Cell> </Row> <Row> <Cell>

<Data ss:Type="Number">2 0 010 817</Data> </Cell> <Cell>

<Data ss:Type="Number">61.88</Data> </Cell> <Cell>

<Data ss:Type="Number">260163</Data> </Cell> </Row> <Row> <Cell>

<Data ss:Type="Number">2 0 010 82 0</Data> </Cell> <Cell>

<Data ss:Type="Number">62.7</Data> </Cell> <Cell>

<Data ss:Type="Number">2 4185 9</Data> </Cell> </Row> <Row> <Cell>

<Data ss:Type="Number">2 0 010 821</Data>

</Cell> <Cell>

<Data ss:Type="Number">60.78</Data> </Cell> <Cell>

<Data ss:Type="Number">2 3 3 98 9</Data> </Cell> </Row> <Row> <Cell>

<Data ss:Type="Number">2 0 010 822</Data> </Cell> <Cell>

<Data ss:Type="Number">60.66</Data> </Cell> <Cell>

<Data ss:Type="Number">38 7 444</Data> </Cell> </Row> </Table>

<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"> <Selected/> <Panes>

<Pane>

<Number>3</Number>

<ActiveRow>11</ActiveRow>

<ActiveCol>5</ActiveCol>

</Pane> </Panes>

<ProtectObjects>False</ProtectObjects> <ProtectScenarios>False</ProtectScenarios> </WorksheetOptions> </Worksheet>

</Workbook>

Но это совершенно не то, что вам нужно!

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

Очевидное решение – преобразовать названия колонок в имена элементов, а остальные ячейки – в содержимое этих элементов. Не хватает только имен эле­мента верхнего уровня и элементов, содержащих данные каждой строки. В следу­ющей таблице стилей эти имена будут передаваться в качестве параметров с оче­видными умолчаниями. Некоторые полезные метаданные будут преобразованы в комментарии, а специфичная для Excel информация отброшена. Мы введем еще

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

<?xml version="1.0" encoding="UTF-8"?>

<xsl:stylesheet version="1.0"

xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">

<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>

<!– Имя элемента верхнего уровня –>

<xsl:param name="topLevelName" select=" ‘Table’ "/>

<!– Имя строки –>

<xsl:param name="rowName" select=" ‘Row’ "/> <!– Название используемого пространства имен –> <xsl:param name="namespace"/>

<!– Префикс используемого пространства имен –> <xsl:param name="namespacePrefix"/>

<!– Какой символ использовать вместо пробела в названиях колонок –>

<xsl:param name="wsSub" select="’_’"/>

<!– Какая строка содержит названия колонок –>

<xsl:param name="colNamesRow" select="1"/>

<!– В какой строке начинаются данные –>

<xsl:param name="dataRowStart" select="2"/>

<!– Если false, то ячейки, не содержащие данных или содержащие –>

<!– только пробелы, пропускаются –>

<xsl:param name="includeEmpty" select="true( )"/>

<!– Если false, то не выводится комментарий, содержащий метаданные —> <!– об авторе и дате создания–>

<xsl:param name="includeComment" select="true( )"/>

<!– Нормализовать namespacePrefix –> <xsl:variable name="nsp">

<xsl:if test="$namespace">

<!— Использовать префикс только, если задано пространство имен –> <xsl:choose>

<xsl:when test="contains($namespacePrefix,':’)"> <xsl:value-of

select="concat(translate(substring-before(

$namespacePrefix,

</xsl:when>

<xsl:when test="translate($namespacePrefix,’ ‘,”)"> <xsl:value-of

select="concat(translate($namespacePrefix,’ ,,,,),':,)"/> </xsl:when> <xsl:otherwise/> </xsl:choose> </xsl:if> </xsl:variable>

<!— Получить названия колонок, заменив в них пробелы —> <xsl:variable name="COLS" select="/*/*/*/ss:Row[$colNamesRow]/ss:Cell"/>

<xsl:template match="o:DocumentProperties"> <xsl:if test="$includeComment"> <xsl:text>&#xa;</xsl:text> <xsl:comment>

<xsl:text>&#xa;</xsl:text>

<xsl:if test="normalize-space(o:Company)"> <xsl:text>Kомпания: </xsl:text> <xsl:value-of select="o:Company"/> <xsl:text>&#xa;</xsl:text> </xsl:if>

<xsl:text>Aвтор: </xsl:text> <xsl:value-of select="o:Author"/> <xsl:text>&#xa;</xsl:text> <xsl:text>Дата создания: </xsl:text>

<xsl:value-of select="translate(o:Created,’TZ’,’ ‘)"/>

<xsl:text>&#xa;</xsl:text>

<xsl:text>Последний автор: </xsl:text>

<xsl:value-of select="o:LastAuthor"/>

<xsl:text>&#xa;</xsl:text>

<xsl:text>Дата сохранения:</xsl:text>

<xsl:value-of select="translate(o:LastSaved,’TZ’,’ ‘)"/> <xsl:text>&#xa;</xsl:text> </xsl:comment> </xsl:if> </xsl:template>

<xsl:template match="ss:Table"> <xsl:element

name="{concat($nsp,translate($topLevelName, ‘&#x2 0;&#x9;&#xA;’,$wsSub))}" namespace="{$namespace}">

<xsl:apply-templates select="ss:Row[position( ) >= $dataRowStart] </xsl:element> </xsl:template>

<xsl:template match="ss:Row"> <xsl:element

name="{concat($nsp,translate($rowName,

‘&#x20;&#x9;&#xA;’,$wsSub))}" namespace="{$namespace}"> <xsl:for-each select="ss:Cell">

<xsl:variable name="pos" select="position( )"/>

<!– Получить правильное название колонки, даже если в исходной –> <!– электронной таблице были пустые колонки –> <xsl:variable name="colName"> <xsl:choose>

<xsl:when test="@ss:Index and

$COLS[@ss:Index = current( )/@ss:Index]">

<xsl:value-of

select="$COLS[@ss:Index = current( )/@ss:Index]/ss:Data"/> </xsl:when>

<xsl:when test="@ss:Index"> <xsl:value-of

select="$COLS[number(current( )/@ss:Index)]/ss:Data"/> </xsl:when> <xsl:otherwise>

<xsl:value-of select="$COLS[$pos]/ss:Data"/> </xsl:otherwise> </xsl:choose> </xsl:variable>

<xsl:if test="$includeEmpty or

translate(ss:Data,’&#x20;&#x9;&#xA;’,,,)">

<xsl:element

name="{concat($nsp,translate($colName,

‘&#x2 0;&#x9;&#xA;’,$wsSub))}" namespace="{$namespace}"> <xsl:value-of select="ss:Data"/> </xsl:element> </xsl:if>

</xsl:for-each> </xsl:element> </xsl:template>

<xsl:template match="text( )"/> </xsl:stylesheet>

Если заданы параметры по умолчанию, то получается гораздо более есте­ственное XML-представление:

<Table> <Row>

<Date>2 0 010 817</Date> <Price>61.8 8</Price> <Volume>2 60163</Volume> </Row> <Row>

<Date>20010820</Date> <Price>62.7</Price> <Volume>2 4185 9</Volume> </Row> <Row>

<Date>20010821</Date> <Price>6 0.7 8</Price> <Volume>2 33 98 9</Volume> </Row> <Row>

<Date>20010822</Date> <Price>6 0.6 6</Price> <Volume>387 4 4 4</Volume> </Row> </Table>

XSLT 2.0

Улучшения за счет использования XSLT 2.0 сводятся в основном к нескольким вспомогательным функциям, которые позволяют убрать избыточный код, и к при­менению более лаконичных конструкций XPath 2.0.

<xsl:stylesheet version="2.0"

xmlns:xsl="http://www.w3.org/1999/XSL/Transform"

xmlns:xs="http://www.w3.org/2001/XMLSchema"

xmlns:fn="http://www.w3.org/2005/02/xpath-functions"

xmlns:o="urn:schemas-microsoft-com:office:office"

xmlns:x="urn:schemas-microsoft-com:office:excel"

xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"

xmlns:ckbk="http://www.oreilly.com/xsltckbk">

<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/> <!– Имя элемента верхнего уровня –>

<xsl:param name="topLevelName" select=" ‘Table’ " as="xs:string"/> <!– Имя строки –>

<xsl:param name="rowName" select=" ‘Row’ " as="xs:string"/> <!– Название используемого пространства имен –> <xsl:param name="namespace" select=" ” " as="xs:string"/> <!– Префикс используемого пространства имен –>

<xsl:param name="namespacePrefix" select=" ” " as="xs:string" />

<!– Какой символ использовать вместо пробела в названиях колонок –>

<xsl:param name="wsSub" select="’_’" as="xs:string"/>

<!– Какая строка содержит названия колонок –>

<xsl:param name="colNamesRow" select="1" as="xs:integer"/>

<!– В какой строке начинаются данные –>

<xsl:param name="dataRowStart" select="2" as="xs:integer"/>

<!– Если false, то ячейки, не содержащие данных или содержащие –>

<!– только пробелы, пропускаются –>

<xsl:param name="includeEmpty" select="true( )" as="xs:boolean"/> <!– Если false, то не выводится комментарий, содержащий метаданные –> <!– об авторе и дате создания–>

<xsl:param name="includeComment" select="true( )" as="xs:boolean"/>

<!– Нормализовать namespacePrefix –> <xsl:variable name="nsp" as="xs:string"

select="if (contains($namespacePrefix,':’)) then concat(translate(substring-before($namespacePrefix, ‘:’),’ ‘,”),':’) else

if (matches($namespacePrefix,’\W’))

then concat(translate($namespacePrefix,’ ‘,”),':’) else ” "/>

<!– Получить названия всех колонок –>

<xsl:variable name="COLS" select="/*/*/*/ss:Row[$colNamesRow]/ss:Cell"/>

<xsl:template match="o:DocumentProperties"> <xsl:if test="$includeComment"> <xsl:text>&#xa;</xsl:text> <xsl:comment select="concat(‘&#xa;’,

ckbk:comment(o:Company), ckbk:comment(o:Author), ckbk:comment(o:Created,’Created on’), ckbk:comment(o:LastAuthor,’Last Author’), ckbk:comment(o:LastSaved,’Saved on’))"/>

</xsl:if>

<xsl:text>&#xa;</xsl:text> </xsl:template>

<xsl:template match="ss:Table"> <xsl:element

name="{ckbk:makeName($nsp,$topLevelName,$wsSub)}" namespace="{$namespace}"> <xsl:apply-templates select="ss:Row[position() ge $dataRowStart]"/> </xsl:element> </xsl:template>

<xsl:template match="ss:Row"> <xsl:element

name="{ckbk:makeName($nsp,$rowName,$wsSub)}" namespace="{$namespace}"> <xsl:for-each select="ss:Cell">

<xsl:variable name="pos" select="position()"/>

<!– Получить правильное название колонки, даже если в исходной –> <!– электронной таблице были пустые колонки –> <xsl:variable name="colName" as="xs:string"

select="if (@ss:Index and $COLS[@ss:Index = current()/@ss:Index]) then $COLS[@ss:Index = current()/@ss:Index]/ss:Datae else

if (@ss:Index)

then $COLS[number(current()/@ss:Index)]/ss:Data else $COLS[$pos]/ss:Data"/>

<xsl:if test="$includeEmpty or

translate(ss:Data,’&#x20;&#x9;&#xA;’,,,)">

<xsl:element

name="{ckbk:makeName($nsp,$colName,$wsSub)}" namespace="{$namespace}"> <xsl:value-of select="ss:Data"/> </xsl:element> </xsl:if> </xsl:for-each> </xsl:element> </xsl:template>

<xsl:template match="text()"/>

<xsl:function name="ckbk:makeName" as="xs:string"> <xsl:param name="nsp" as="xs:string"/> <xsl:param name="name" as="xs:string"/> <xsl:param name="wsSub" as="xs:string"/> <xsl:sequence select="concat($nsp,translate($name,

‘&#x2 0;&#x9;&#xA;’,$wsSub))"/>

</xsl:function>

<xsl:function name="ckbk:comment" as="xs:string"> <xsl:param name="elem"/>

<xsl:sequence select="ckbk:comment($elem, local-name($elem))"/> </xsl:function>

<xsl:function name="ckbk:comment" as="xs:string"> <xsl:param name="elem"/>

<xsl:param name="label" as="xs:string"/> <xsl:sequence select="if (normalize-space($elem))

then concat($label,': ‘,$elem,’&#xa;’) else ” "/>

</xsl:function>

</xsl:stylesheet>

Обсуждение

Я уже почти решил не включать этот рецепт в книгу, сочтя его тривиальным. Но потом понял, что для корректного решения нужно рассматривать ряд особых случаев, а во многих реализациях (включая и мою первую версию) это не делает­ся. Например, часто в электронные таблицы включают пустые колонки в качестве разделителей. Как это обрабатывается, можете увидеть, поискав в тексте атрибут @ss:Index. Кроме того, в первой редакции этой книги в код были «зашиты» мно­гие значения, которые теперь передаются параметрами.

Напрашивается по меньшей мере одно обобщение этой таблицы стилей – об­работка случая, когда есть несколько элементов ss:Worksheet. Для этого мож­но было бы передать номер рабочего листа в качестве параметра.

<xsl:param name="WSNum" select="1"/>

<xsl:variable name="COLS"

select="/*/ss:Worksheet[$WSNum]/*/ss:Row[$colNamesRow]/ss:Cell"/>

<xsl:template match="ss:Workbook"> <xsl:element name="{concat($nsp,translate($topLevelName, ‘&#x20;&#x9;&#xA;’,$wsSub))}" namespace="{$namespace}"> <xsl:apply-templates select="ss:Worksheet[number($WSNum)]/ss:Table"/> </xsl:element>

</xsl:template>

Но интереснее было бы преобразовать каждый элемент Worksheet в доку­менте с несколькими рабочими листами в отдельный элемент в результирующем

документе. При таком подходе названия колонок уже нельзя хранить в глобаль­ной переменной:

<xsl:template match="ss:Workbook"> <xsl:element name="{concat($nsp,translate($topLevelName, ‘&#x20;&#x9;&#xA;’,$wsSub))}" namespace="{$namespace}"> <xsl:choose>

<xsl:when test="number($WSNum) > 0"> <xsl:apply-templates

select="ss:Worksheet[number($WSNum)]/ss:Table"> <xsl:with-param name="COLS"

select="ss:Worksheet[number($WSNum)]

/*/ss:Row[$colNamesRow]/ss:Cell"/>

</xsl:apply-templates> </xsl:when> <xsl:otherwise>

<xsl:for-each select="ss:Worksheet"> <xsl:element

name="{concat($nsp,translate(@ss:Name,

‘&#x2 0;&#x9;&#xA;’,$wsSub))}" namespace="{$namespace}"> <xsl:apply-templates select="ss:Table"> <xsl:with-param name="COLS"

select="*/ss:Row[$colNamesRow]/ss:Cell"/> </xsl:apply-templates> </xsl:element> </xsl:for-each> </xsl:otherwise> </xsl:choose> </xsl:element> </xsl:template>

<xsl:template match="ss:Table"> <xsl:param name="COLS"/>

<xsl:apply-templates select="ss:Row[position( ) >= $dataRowStart]"> <xsl:with-param name="COLS" select="$COLS"/> </xsl:apply-templates> </xsl:template>

<xsl:template match="ss:Row"> <xsl:param name="COLS"/>

<!– Все остальное не меняется … –>

Единственный недостаток этого решения – предположение о том, что назва­ния колонок в каждом рабочем листе должны находиться в одной и той же строке.

Мангано Сэл  XSLT. Сборник рецептов. – М.: ДМК Пресс, СПБ.: БХВ-Петербург, 2008. – 864 с.: ил.

По теме:

  • Комментарии