XSLT - XML to XLS

Question

I have an XSLT template that gets applied to an XML file of data. The template generates an Excel Spreadsheet XML file.

Everything works wonderful except for one minor issue....

When I open the spreadsheet in Excel, it treats it as an XML import and asks me whether the stylesheet should be applied. I would like to have it so it automatically applies any XSLT template that is associated with the file.

Below is some sample code....thanks in advance....

XML Document...

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="spreadstyle.xsl"?>

<ResponseBody>
    <ActivityDetails>
        <ActivityDetail>
            <TranCodeDesc>LateChargeAssessment</TranCodeDesc>
        </ActivityDetail>
    </ActivityDetails>
</ResponseBody>

XSLT Template...

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"&gt;

<xsl:template match="/">


    <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"&gt;

     <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
      <Author>tbarbedo</Author>
      <Created>2009-05-29T18:21:48Z</Created>
      <Version>12.00</Version>
     </DocumentProperties>
     <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
      <WindowHeight>8895</WindowHeight>
      <WindowWidth>18015</WindowWidth>
      <WindowTopX>0</WindowTopX>
      <WindowTopY>105</WindowTopY>
      <ProtectStructure>False</ProtectStructure>
      <ProtectWindows>False</ProtectWindows>
     </ExcelWorkbook>
     <Styles>
      <Style ss:ID="Default" ss:Name="Normal">
       <Alignment ss:Vertical="Bottom"/>
       <Borders/>
       <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
       <Interior/>
       <NumberFormat/>
       <Protection/>
      </Style>
     </Styles>
     <Worksheet ss:Name="Sheet1">
      <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="5000" x:FullColumns="1"
       x:FullRows="1" ss:DefaultRowHeight="15">
            <Row><Cell><Data ss:Type="String">Transaction Code Descriptions (MAGIC) </Data></Cell></Row>
            <xsl:for-each select="ResponseBody/ActivityDetails/ActivityDetail">     
                <Row> 
                    <Cell>
                        <Data ss:Type="String">
                            <xsl:value-of select="TranCodeDesc" />
                        </Data>
                    </Cell>

                </Row>
            </xsl:for-each>

      </Table>
      <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
       <PageSetup>
        <Header x:Margin="0.3"/>
        <Footer x:Margin="0.3"/>
        <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
       </PageSetup>
       <Selected/>
       <Panes>
        <Pane>
         <Number>3</Number>
         <ActiveRow>1</ActiveRow>
        </Pane>
       </Panes>
       <ProtectObjects>False</ProtectObjects>
       <ProtectScenarios>False</ProtectScenarios>
      </WorksheetOptions>
     </Worksheet>
    </Workbook>

</xsl:template>
</xsl:stylesheet>

Answer 1

From my understanding, there is no way around the Import XML... dialog being shown. The reason is explained in this MSDN article (Transform XML Files with XSLT When Importing into Microsoft Excel 2002):

XML is a markup language used for describing structured data (such as that in a worksheet) so that it can be read by a variety of applications. Designers can create customized XML elements, enabling the definition, transmission, validation, and interpretation of data between applications and between organizations. This versatility provides many opportunities for data interchange, but it also has at least one inherent challenge. By manipulating the elements of an XML document, designers can create any number of nested element dimensions within a document. These parent/child element relationships can lead to ambiguity in the two-dimensional row-and-column paradigm of an Excel worksheet.

To deal with this ambiguity, Excel forces a format to any XML data it receives, unless the data comes with a style sheet that predefines another format. This forced format is created by using a flattening algorithm to populate the rows and columns that comprise a worksheet. Yet while this format is effective in forcing multidimensional data into a two-dimensional format, it doesn't always present the data in the most optimal format for a human reader.

So how does Excel know when to use either the flattening algorithm or the XSLT style sheet? When Excel opens or imports an XML data file, it looks for an element inside the file that points to an XSLT style sheet. If that element is present, Excel displays a dialog box that prompts you to apply the style sheet or to open the file without a style sheet.

Answer 2

divo,

Thank you very much for your response. Saved me a lot of time and frustration...lol