1

I want to generate a excel .xls file with xls. I've already figured out how to generate the file. Only when opening the file in MS Excel i get the following errors.

The file is broken and can not be opened.

This is the output;

 enterFile: Order_2013.xls <?xml version="1.0"?> <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"><Styles><Style xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" ss:ID="Default" ss:Name="Normal"><Alignment ss:Vertical="Bottom"/><Borders/><Font/><Interior/><NumberFormat/><Protection/></Style><Style xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" ss:ID="s21"><Font ss:Size="22" ss:Bold="1"/></Style><Style xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" ss:ID="s22"><Font ss:Size="14" ss:Bold="1"/></Style><Style xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" ss:ID="s23"><Font ss:Size="12" ss:Bold="1"/></Style><Style xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" ss:ID="s24"><Font ss:Size="10" ss:Bold="1"/></Style></Styles><Worksheet xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" ss:Name="order"><Table><Column ss:AutoFitWidth="0" ss:Width="85"/><Column ss:AutoFitWidth="0" ss:Width="115"/><Column ss:AutoFitWidth="0" ss:Width="115"/><Column ss:AutoFitWidth="0" ss:Width="160"/><Column ss:AutoFitWidth="0" ss:Width="115"/><Column ss:AutoFitWidth="0" ss:Width="85"/><Column ss:AutoFitWidth="0" ss:Width="85"/><Column ss:AutoFitWidth="0" ss:Width="160"/><Row ss:AutoFitHeight="0" ss:Height="27.75"><Cell ss:StyleID="s21"><Data ss:Type="String">Example Spreadsheet</Data></Cell></Row><Row ss:AutoFitHeight="0" ss:Height="18"><Cell ss:StyleID="s22"><Data ss:Type="String">Vuurvlinderronde 15</Data></Cell></Row><Row><Cell><Data ss:Type="String"> test </Data></Cell></Row><Row xmlns:html="http://www.w3.org/TR/REC-html40" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" ss:AutoFitHeight="0" ss:Height="18"><Cell ss:StyleID="s23"><Data ss:Type="String"> Collumn 1 </Data></Cell></Row><Row xmlns:html="http://www.w3.org/TR/REC-html40" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel"><Cell ss:StyleID="s24"><Data ss:Type="String"> Collumn 2 </Data></Cell><Cell ss:StyleID="s24"><Data ss:Type="String"> Collumn 3 </Data></Cell><Cell ss:StyleID="s24"><Data ss:Type="String"> Collumn 4 </Data></Cell><Cell ss:StyleID="s24"><Data ss:Type="String"> Collumn 5 </Data></Cell></Row></Table></Worksheet></Workbook> code here

This is the xsl stylesheet

<?xml version="1.0" encoding="ISO-8859-1"?> <?mso-application progid="Excel.Sheet"?> <files> <file filename="Order_%Y%.xls"> <xsl:stylesheet version="1.0" xmlns:html="http://www.w3.org/TR/REC-html40" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 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"> <xsl:template match="/"> <xsl:for-each select="orders/order"> <Workbook> <Styles> <Style ss:ID="Default" ss:Name="Normal"> <Alignment ss:Vertical="Bottom" /> <Borders /> <Font /> <Interior /> <NumberFormat /> <Protection /> </Style> <Style ss:ID="s21"> <Font ss:Size="22" ss:Bold="1" /> </Style> <Style ss:ID="s22"> <Font ss:Size="14" ss:Bold="1" /> </Style> <Style ss:ID="s23"> <Font ss:Size="12" ss:Bold="1" /> </Style> <Style ss:ID="s24"> <Font ss:Size="10" ss:Bold="1" /> </Style> </Styles> <Worksheet ss:Name="order"> <Table> <Column ss:AutoFitWidth="0" ss:Width="85" /> <Column ss:AutoFitWidth="0" ss:Width="115" /> <Column ss:AutoFitWidth="0" ss:Width="115" /> <Column ss:AutoFitWidth="0" ss:Width="160" /> <Column ss:AutoFitWidth="0" ss:Width="115" /> <Column ss:AutoFitWidth="0" ss:Width="85" /> <Column ss:AutoFitWidth="0" ss:Width="85" /> <Column ss:AutoFitWidth="0" ss:Width="160" /> <Row ss:AutoFitHeight="0" ss:Height="27.75"> <Cell ss:StyleID="s21"> <Data ss:Type="String">Voorbeeld</Data> </Cell> </Row> <Row ss:AutoFitHeight="0" ss:Height="18"> <Cell ss:StyleID="s22"> <Data ss:Type="String"> <xsl:value-of select="shipping/street" /> </Data> </Cell> </Row> <Row> <Cell> <Data ss:Type="String"> test </Data> </Cell> </Row> <xsl:call-template name="orders" /> </Table> </Worksheet> </Workbook> </xsl:for-each> </xsl:template> <xsl:template name="orders"> <Row ss:AutoFitHeight="0" ss:Height="18"> <Cell ss:StyleID="s23"> <Data ss:Type="String"> Collumn 1 </Data> </Cell> </Row> <Row> <Cell ss:StyleID="s24"> <Data ss:Type="String"> Collumn 2 </Data> </Cell> <Cell ss:StyleID="s24"> <Data ss:Type="String"> Collumn 3 </Data> </Cell> <Cell ss:StyleID="s24"> <Data ss:Type="String"> Collumn 4 </Data> </Cell> <Cell ss:StyleID="s24"> <Data ss:Type="String"> Collumn 5 </Data> </Cell> </Row> <xsl:for-each select="orders/order"> <Row> <Cell> <Data ss:Type="String"> <xsl:value-of select="order_id" /> </Data> </Cell> <Cell> <Data ss:Type="String"> <xsl:value-of select="order_id" /> </Data> </Cell> <Cell> <Data ss:Type="String"> <xsl:value-of select="order_id" /> </Data> </Cell> <Cell> <Data ss:Type="String"> <xsl:value-of select="order_id" /> </Data> </Cell> </Row> </xsl:for-each> </xsl:template> </xsl:stylesheet> </file> </files>

I hope someone can point me to the right solution.

Thanks in advance!

J

Improve this question
1
  • Could you show a sample of your the input XML you are using? Thanks! Commented Mar 14, 2013 at 14:24

2 Answers 2

Reset to default
1

Excel files in XML format have the extension .xlsx otherwise Excel expects BIFF format.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

If you save as .xml, Excel 2007 generates an error file on opening, which might help. When I look at an Excel-created .xml file, the columns are inside the rows. You file has rows inside columns.
0

In your XSLT sample, you have enclosed the xsl:stylesheet element within a file element.

<files> <file filename="Order_%Y%.xls"> <xsl:stylesheet version="1.0" .... <!-- XSLT Code.... --> </xsl:stylesheet> </file> </files>

Maybe you didn't mean to put them in your question, but the files and file elements should definitely not be there. I don't think files is a valid Excel XML at all. I would expect a Workbook element to be the top most element in an Excel XML file.

Your current output also includes a line of text enterFile: Order_2013.xls at the top. Again, maybe this is just an issue with your question, but it shouldn't be there in the output. The output should be a well-formed XML document.

One other thing to note is the use of the processing instruction

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

Although this is correct for Excel XML, it does not need to be placed in the XSLT file. As it stands this will be a processing instruction for the XSLT processor, which will ignore it! You need your XSLT to write out the processing instruction in your output. To do this, add this command to within the first template

<xsl:template match="/"> <xsl:processing-instruction name="mso-application"> <xsl:text>progid="Excel.Sheet"</xsl:text> </xsl:processing-instruction> <xsl:for-each select="orders/order">

When I tried the XSLT on an empty XML file, I was able to generate an Excel XML file that opened successfully.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.