CODEWITHSUNDEEP
c#.netexcelxsltxslt-1.0
m27
m27

Reputation: 79

Xslt 1.0 XML to excel transformation issue

I'm using the .Net framework's XslCompiledTransform class as the XSLT processor (in other words Xslt 1.0).

My requirement is that I want to convert an XML file to an Excel file (xls file) using XSLT 1.0 and .Net [4.0].

For simplicity and since this is just test code, I'm just considering some simple hard-coding on my Xsl file.

Specifically, my two questions are:

  1. I've tried a bunch of things, yet I can't see my Excel sheet's worksheet named to "wAbc" which is what I'm trying to name it. I see the worksheet name as my file name. Additional worksheets are not generated as well. Below is my XSL file

  2. Additionally, since this is test code, I can't see the data outputted on different rows unless I use HTML tags . What I see is: AbcNext line instead of

    Abc Nextline

So what am I doing wrong? Thank you in advance for your time and help.

I set the content type for my file response as

application/vnd.ms-excel

Here is my test Xsl file:

<?xml version="1.0"?>
    <xsl:stylesheet version="1.0"
        xmlns="urn:schemas-microsoft-com:office:spreadsheet"
        xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
        xmlns:msxsl="urn:schemas-microsoft-com:xslt"
        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">


      <xsl:template match="/">
        <xsl:processing-instruction name="mso-application">progid='Excel.Sheet'</xsl:processing-instruction>
        <Workbook>
            <Worksheet ss:Name="wAbc">
              <Table ss:ExpandedColumnCount="2" x:FullColumns="1" x:FullRows="1" ss:DefaultRowHeight="15">
                <Column ss:AutoFitWidth="20" ss:AutoWidth="65"></Column>
                <Row>
                  <Cell>
                    <Data ss:Type="String">Abc</Data>
                  </Cell>
                </Row>
                <Row>
                  <Cell>
                    <Data ss:Type="String">Next line</Data>
                  </Cell>
                </Row>
              </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>
              </WorksheetOptions>
            </Worksheet>
        </Workbook>
      </xsl:template>
    </xsl:stylesheet>

Upvotes: 0

Views: 2070

Answers (2)

Tim C
Tim C

Reputation: 70618

You are using Excel XML as the format here (so, not strictly an XLS file, which is binary).

But in any case, I can see two issues with the Excel XML you are currently outputting. Firstly, I think the ss:AutoFitWidth attribute on the Column element needs to be set to 1 (or 0). I don't think 20 is a valid value.

See http://msdn.microsoft.com/en-us/library/aa140066(v=office.10).aspx for the list of possible values.

Addtionally, I think you may (although I am not 100% sure on this) need to set the ss:ExpandedRowCount on the Table element as well as the ss:ExpandedColumnCount.

See if changing one or both of these fixes your errors.

Upvotes: 1

MiMo
MiMo

Reputation: 11953

Excel XLS files use a Microsoft proprietary binary format - no way to generate that with an XSLT.

The (relatively) new XSLX format is a set of XML files that are ZIPped together, so in principle you can generate these files with an XSLT, and then ZIP them to generate the final file - still not very easy. To see these files simply un-ZIP an XSLX file.

I suggest you generate a simpler format - either a CSV or an intermediate XML- and then import it into Excel.

Upvotes: 0

Related Questions