How can I parse this Excel XML export file with th

2019-09-03 14:31发布

站内文章 / 后端开发
6 0 0

问题:

We can parse this test XML file with this XSL file fine:

Test XML:

<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet href="newrows.xsl" type="text/xsl"?>
<Workbook>
    <Worksheet>
        <Table>
            <Row>
                <Cell></Cell>
                <Cell>(info...)</Cell>
                <Cell></Cell>
            </Row>
            <Row>
                <Cell>first name</Cell>
                <Cell>last name</Cell>
                <Cell>age</Cell>
            </Row>
            <Row>
                <Cell>Jim</Cell>
                <Cell>Smith</Cell>
                <Cell>34</Cell>
            </Row>
            <Row>
                <Cell>Roy</Cell>
                <Cell>Rogers</Cell>
                <Cell>22</Cell>
            </Row>
            <Row>
                <Cell>(info...)</Cell>
                <Cell></Cell>
                <Cell>(info...)</Cell>
            </Row>

            <Row>
                <Cell>Sally</Cell>
                <Cell>Cloud</Cell>
                <Cell>26</Cell>
            </Row>

            <Row>
                <Cell>John</Cell>
                <Cell>Randall</Cell>
                <Cell>44</Cell>
            </Row>  

        </Table>
    </Worksheet>
</Workbook>

XSL:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"  version="1.0">

    <xsl:output method="xml" indent="yes"/>

    <xsl:param name="range-1-begin"  select="1"/>
    <xsl:param name="range-1-end"  select="3"/>

    <xsl:param name="range-2-begin"  select="5"/>
    <xsl:param name="range-2-end"  select="6"/>

    <xsl:template match="Table">
        <test>
            <xsl:for-each select="Row">
                <xsl:if test="(position() &gt;= $range-1-begin and position() &lt;= $range-1-end)
                    or (position() &gt;= $range-2-begin and position() &lt;= $range-2-end)">
                    <Row>
                       <xsl:for-each select="Cell">
                            <xsl:if test="position() = 1 or position() = 3">
                                <Cell>
                                    <xsl:value-of select="."/>
                                </Cell>
                            </xsl:if>
                        </xsl:for-each>
                    </Row>
                </xsl:if>
            </xsl:for-each>
        </test>
    </xsl:template>

</xsl:stylesheet>

However, when we try to parse this similar XML file exported from Excel, it exports the content of every field with no XML element tags. We can even type in kksljflskdjf instead of Table and it outputs the content of every XML element.

What do I have to change in the XML/XSL file so that the XSL file correctly parses the XML?

Excel XML (exceprts):

<?xml version="1.0"?>
<?xml-stylesheet href="blackbox.xsl" type="text/xsl"?>
<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>MM</Author>
        <LastAuthor>xx</LastAuthor>
        ...
<Worksheet ss:Name="OFFSET Individual">
        <Names>
            <NamedRange ss:Name="_FilterDatabase" ss:RefersTo="='OFFSET Individual'!R3C2:R3C12" ss:Hidden="1"/>
            <NamedRange ss:Name="Print_Area" ss:RefersTo="='OFFSET Individual'!R4C2:R435C15"/>
            <NamedRange ss:Name="Muster" ss:RefersTo="='OFFSET Individual'!C1:C9"/>
            <NamedRange ss:Name="PAP" ss:RefersTo="='OFFSET Individual'!C2"/>
        </Names>
        <Table ss:ExpandedColumnCount="31" ss:ExpandedRowCount="443" x:FullColumns="1" x:FullRows="1" ss:StyleID="s90" ss:DefaultColumnWidth="59" ss:DefaultRowHeight="15">
            <Column ss:StyleID="s416" ss:Hidden="1" ss:AutoFitWidth="0" ss:Width="61"/>
            <Column ss:StyleID="s91" ss:AutoFitWidth="0" ss:Width="287"/>
            <Column ss:StyleID="s547" ss:AutoFitWidth="0" ss:Width="216"/>
            <Column ss:StyleID="s91" ss:AutoFitWidth="0" ss:Width="87"/>
            <Column ss:StyleID="s92" ss:AutoFitWidth="0" ss:Width="202"/>
            <Column ss:StyleID="s90" ss:AutoFitWidth="0" ss:Width="87"/>
            <Column ss:StyleID="s101" ss:AutoFitWidth="0" ss:Width="284"/>
            <Column ss:StyleID="s132" ss:Hidden="1" ss:AutoFitWidth="0" ss:Width="52"/>
            <Column ss:StyleID="s137" ss:Hidden="1" ss:AutoFitWidth="0" ss:Width="47"/>
            <Column ss:StyleID="s90" ss:Hidden="1" ss:AutoFitWidth="0" ss:Width="42"/>
            <Column ss:StyleID="s90" ss:Hidden="1" ss:AutoFitWidth="0" ss:Width="39"/>
            <Column ss:StyleID="s90" ss:Hidden="1" ss:AutoFitWidth="0" ss:Width="37"/>
            <Column ss:StyleID="s113" ss:AutoFitWidth="0" ss:Width="47"/>
            <Column ss:StyleID="s87" ss:Hidden="1" ss:AutoFitWidth="0" ss:Width="275"/>
            <Column ss:StyleID="s458" ss:AutoFitWidth="0" ss:Width="89"/>
            <Column ss:StyleID="s179" ss:AutoFitWidth="0" ss:Span="1"/>
            <Column ss:Index="18" ss:StyleID="s168" ss:Hidden="1" ss:AutoFitWidth="0"/>
            <Column ss:StyleID="s90" ss:Hidden="1" ss:AutoFitWidth="0"/>
            <Column ss:StyleID="s377" ss:AutoFitWidth="0" ss:Width="202" ss:Span="2"/>
            <Column ss:Index="23" ss:StyleID="s377" ss:AutoFitWidth="0" ss:Width="203"/>
            <Row ss:AutoFitHeight="0" ss:Height="23">
                <Cell ss:Index="2" ss:StyleID="s142">
                    <Data ss:Type="String">Paper Overview</Data>
                    <NamedCell ss:Name="PAP"/>
                    <NamedCell ss:Name="Muster"/>
                </Cell>
            </Row>
            <Row ss:AutoFitHeight="0">
                <Cell ss:Index="2" ss:StyleID="s141">
                    <Data ss:Type="String">Stand: 10.03.2011; 13:00 Uhr</Data>
                    <NamedCell ss:Name="PAP"/>
                    <NamedCell ss:Name="Muster"/>
                </Cell>
            </Row>
                        ...

Here is an example of the resulting "XML" file:

Addendum

This is the full solution which now works, thanks @Dimitre!

<xsl:stylesheet version="1.0"
 xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns="urn:schemas-microsoft-com:office:spreadsheet" 
    xmlns:y="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"
  exclude-result-prefixes="y o x ss html"
 >

 <xsl:strip-space elements="*"/>
    <xsl:output method="xml" indent="yes"/>

    <xsl:param name="range-1-begin"  select="1"/>
    <xsl:param name="range-1-end"  select="3"/>

    <xsl:param name="range-2-begin"  select="5"/>
    <xsl:param name="range-2-end"  select="6"/>

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

    <xsl:template match="y:Table">
        <test>
            <xsl:for-each select="y:Row">
                <xsl:if test="(position() &gt;= $range-1-begin and position() &lt;= $range-1-end)
                    or (position() &gt;= $range-2-begin and position() &lt;= $range-2-end)">
                    <Row>
                       <xsl:for-each select="y:Cell">
                            <xsl:if test="position() = 1 or position() = 3">
                                <Cell>
                                    <xsl:value-of select="."/>
                                </Cell>
                            </xsl:if>
                        </xsl:for-each>
                    </Row>
                </xsl:if>
            </xsl:for-each>
        </test>
    </xsl:template>

</xsl:stylesheet>

回答1:

What do I have to change in the XML/XSL file so that the XSL file correctly parses the XML?

First of all, your terminology is quite incorrect. An XSLT transformation is applied on an already parsed XML document. The parsing (by an XML parser) is a prerequisit for being able to apply a transformation.

This is the most FAQ on XML, XPath and in XSLT:

The reason for not being able to select any element by name the second document is because there is a default namesace defined in it (xmlns="urn:schemas-microsoft-com:office:spreadsheet").

In XPath any unprefixed name is considered to be in "no namespace". Therefore the template matching Table and the <xsl:for-each> selecting Row elements will not match/select any element, because in the XML document there are no such elements that are in "no namespace".

The most readable solution is to define the same namespaces in the XSLT stylesheet and to use prefixed names in any XPath expression/match-pattern.

Thus, in the corrected XSLT stylesheet you will have:

<xsl:stylesheet version="1.0"
 xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
 xmlns:y="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"
  exclude-result-prefixes="y o x ss html"
 >
    <xsl:output method="xml" indent="yes"/>

    <xsl:param name="range-1-begin"  select="1"/>
    <xsl:param name="range-1-end"  select="3"/>
    <xsl:param name="range-2-begin"  select="5"/>
    <xsl:param name="range-2-end"  select="6"/>

    <xsl:template match="y:Table">
        <test>
            <xsl:for-each select="y:Row">
                <xsl:if test="(position() &gt;= $range-1-begin and position() &lt;= $range-1-end)                     or (position() &gt;= $range-2-begin and position() &lt;= $range-2-end)">
                    <Row>
                        <xsl:for-each select="Cell">
                            <xsl:if test="position() = 1 or position() = 3">
                                <Cell>
                                    <xsl:value-of select="."/>
                                </Cell>
                            </xsl:if>
                        </xsl:for-each>
                    </Row>
                </xsl:if>
            </xsl:for-each>
        </test>
    </xsl:template>
</xsl:stylesheet>


回答2:

Your Test Xml and Xsl do not declare and use any namespaces whereas the Excel Xml export defines various namespaces:

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"


标签： xml excel xslt
举报

收藏的人(0)

Ta的文章 更多文章
登录 后发表评论
0条评论
还没有人评论过~