Databases
StackOverflow2013
Postsdbo
POApply XSLT on XML before importing into Excel
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POApply XSLT on XML before importing into Excel
    text
    copied!<p>I have an XSLT template that gets applied to an XML file of data. The template generates an Excel Spreadsheet XML file.</p> <p>Everything works wonderful except for one minor issue....</p> <p>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.</p> <p>Below is some sample code....thanks in advance....</p> <p><strong>XML Document...</strong></p> <pre><code>&lt;?xml version="1.0"?&gt; &lt;?xml-stylesheet type="text/xsl" href="spreadstyle.xsl"?&gt; &lt;ResponseBody&gt; &lt;ActivityDetails&gt; &lt;ActivityDetail&gt; &lt;TranCodeDesc&gt;LateChargeAssessment&lt;/TranCodeDesc&gt; &lt;/ActivityDetail&gt; &lt;/ActivityDetails&gt; &lt;/ResponseBody&gt; </code></pre> <p><strong>XSLT Template...</strong></p> <pre><code>&lt;?xml version="1.0"?&gt; &lt;?mso-application progid="Excel.Sheet"?&gt; &lt;xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"&gt; &lt;xsl:template match="/"&gt; &lt;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; &lt;DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"&gt; &lt;Author&gt;tbarbedo&lt;/Author&gt; &lt;Created&gt;2009-05-29T18:21:48Z&lt;/Created&gt; &lt;Version&gt;12.00&lt;/Version&gt; &lt;/DocumentProperties&gt; &lt;ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel"&gt; &lt;WindowHeight&gt;8895&lt;/WindowHeight&gt; &lt;WindowWidth&gt;18015&lt;/WindowWidth&gt; &lt;WindowTopX&gt;0&lt;/WindowTopX&gt; &lt;WindowTopY&gt;105&lt;/WindowTopY&gt; &lt;ProtectStructure&gt;False&lt;/ProtectStructure&gt; &lt;ProtectWindows&gt;False&lt;/ProtectWindows&gt; &lt;/ExcelWorkbook&gt; &lt;Styles&gt; &lt;Style ss:ID="Default" ss:Name="Normal"&gt; &lt;Alignment ss:Vertical="Bottom"/&gt; &lt;Borders/&gt; &lt;Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/&gt; &lt;Interior/&gt; &lt;NumberFormat/&gt; &lt;Protection/&gt; &lt;/Style&gt; &lt;/Styles&gt; &lt;Worksheet ss:Name="Sheet1"&gt; &lt;Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="5000" x:FullColumns="1" x:FullRows="1" ss:DefaultRowHeight="15"&gt; &lt;Row&gt;&lt;Cell&gt;&lt;Data ss:Type="String"&gt;Transaction Code Descriptions (MAGIC) &lt;/Data&gt;&lt;/Cell&gt;&lt;/Row&gt; &lt;xsl:for-each select="ResponseBody/ActivityDetails/ActivityDetail"&gt; &lt;Row&gt; &lt;Cell&gt; &lt;Data ss:Type="String"&gt; &lt;xsl:value-of select="TranCodeDesc" /&gt; &lt;/Data&gt; &lt;/Cell&gt; &lt;/Row&gt; &lt;/xsl:for-each&gt; &lt;/Table&gt; &lt;WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"&gt; &lt;PageSetup&gt; &lt;Header x:Margin="0.3"/&gt; &lt;Footer x:Margin="0.3"/&gt; &lt;PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/&gt; &lt;/PageSetup&gt; &lt;Selected/&gt; &lt;Panes&gt; &lt;Pane&gt; &lt;Number&gt;3&lt;/Number&gt; &lt;ActiveRow&gt;1&lt;/ActiveRow&gt; &lt;/Pane&gt; &lt;/Panes&gt; &lt;ProtectObjects&gt;False&lt;/ProtectObjects&gt; &lt;ProtectScenarios&gt;False&lt;/ProtectScenarios&gt; &lt;/WorksheetOptions&gt; &lt;/Worksheet&gt; &lt;/Workbook&gt; &lt;/xsl:template&gt; &lt;/xsl:stylesheet&gt; </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload