locked
Excel's XML Spreadsheet 2003 format unreadable by XSLT RRS feed

  • Question

  • I'm having an "impossible issue" with Excel's XML Spreadsheet 2003 format. It's an "impossible issue" because it's impossible for what I'm doing to fail, but it does fail.

    I created a test case spreadsheet, shown below. One worksheet, one data entry in cell A1 (fred), Save As XML Spreadsheet 2003. That's it.

    Using XSL, I'm trying to access the data, using <xsl:value-of select="//Data"/>, That has to work. And it does work if I run it against a hand-made version of the same structure. But it doesn't work if Excel creates the file, which makes no sense. It's just a text file. There are no hidden characters to mess things up. So, how is this possible? And how do I work around it? <xsl:value-of select="*/*/*/*/*/*"/> 'works', in the sense that it retrieves the value 'fred', but it's useless.

    Here's the file;

    <?xml version="1.0"?>
    <?mso-application progid="Excel.Sheet"?>
    <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>Dave</Author>
            <LastAuthor>Dave</LastAuthor>
            <Created>2016-09-22T17:55:00Z</Created>
            <Version>15.00</Version>
        </DocumentProperties>
        <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
            <AllowPNG></AllowPNG>
        </OfficeDocumentSettings>
        <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
            <WindowHeight>7755</WindowHeight>
            <WindowWidth>16815</WindowWidth>
            <WindowTopX>0</WindowTopX>
            <WindowTopY>0</WindowTopY>
            <ProtectStructure>False</ProtectStructure>
            <ProtectWindows>False</ProtectWindows>
        </ExcelWorkbook>
        <Styles>
            <Style ss:ID="Default" ss:Name="Normal">
                <Alignment ss:Vertical="Bottom"></Alignment>
                <Borders></Borders>
                <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"></Font>
                <Interior></Interior>
                <NumberFormat></NumberFormat>
                <Protection></Protection>
            </Style>
        </Styles>
        <Worksheet ss:Name="Sheet1">
            <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1" x:FullRows="1" ss:DefaultRowHeight="15">
                <Row>
                    <Cell>
                        <Data ss:Type="String">fred</Data>
                    </Cell>
                </Row>
            </Table>
            <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
                <PageSetup>
                    <Header x:Margin="0.3"></Header>
                    <Footer x:Margin="0.3"></Footer>
                    <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"></PageMargins>
                </PageSetup>
                <Selected></Selected>
                <Panes>
                    <Pane>
                        <Number>3</Number>
                        <ActiveCol>1</ActiveCol>
                    </Pane>
                </Panes>
                <ProtectObjects>False</ProtectObjects>
                <ProtectScenarios>False</ProtectScenarios>
            </WorksheetOptions>
        </Worksheet>
    </Workbook>

    Friday, September 23, 2016 4:39 PM

Answers

All replies

  • Try once removing all namespace. If that solves then you have to focus on handling namespace issues while working with XSLT.

    Best Regards,
    Asadulla Javed,
    Jadavpore & Asansol

    Friday, September 23, 2016 4:47 PM
    Answerer
  • XSLT Namespace

    Refere above link to get detail why that happens.

    Declare xsl:stylesheet like below...

    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:my="urn:schemas-microsoft-com:office:spreadsheet">

    <xsl:value-of select="//my:Data"/>

    Another option is

    <xsl:value-of select="//*[local-name()='Data']"/>


    Best Regards,
    Asadulla Javed,
    Jadavpore & Asansol

    Sunday, September 25, 2016 4:35 PM
    Answerer
  • No need, but you were close enough to point me to the real answer, which was to add the already-declared prefix ss: to the element names. That shouldn't be necessary, given that it's unlikely there is any conflicting namespace in my stylesheet that uses the element names Worksheet, Row, Cell, and Data. Although one would think they would be in the Excel namespace, that doesn't seem to be true, because referring to that namespace doesn't work, while referring to the office-spreadsheet namespace does work.

    So, with my namespace declared as xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet", using ss:Worksheet or ss:Data now works.

    I had taken the lack of prefixes on the elements as evidence that they weren't needed, since the attributes all had prefixes. I assumed that, if they were needed, Microsoft would have output them. Bad assumption.

    Anyway, thanks for the pointer.

    Monday, September 26, 2016 8:20 PM
  • Hi DaveInAZ,

    According to your description, you maybe could refer to similar thread below:

    https://social.msdn.microsoft.com/Forums/en-US/74e5c5f3-020f-4e28-aaf3-555ba9e85405/transform-excel-xml-spreadsheet-using-xslt?forum=xmlandnetfx

    Since this issue is also related to XLST, you could post your issue on MSDN forum for XML, System.Xml, MSXML and XmlLite to seek help

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=xmlandnetfx

    Thanks for your understanding.


    • Edited by David_JunFeng Tuesday, September 27, 2016 9:16 AM
    • Proposed as answer by David_JunFeng Wednesday, October 5, 2016 1:34 PM
    • Marked as answer by David_JunFeng Wednesday, October 5, 2016 1:34 PM
    Tuesday, September 27, 2016 8:52 AM