.net XSLT to create worksheets

I am trying to export a lot of data to excel.

I need a possibility to separate the data between different wor开发者_如何学运维ksheets.

I prefer not to be depend on packages like this, so I thought about XSLT.

Does anyone knows how to create worksheets with XSLT?

---

It is possible to use XSLT for this, but you will have to output the data in Microsoft Excel XML format, which is supported from versions of Microsoft Excel 2002 onwards.

Microsoft XML Spreadsheet Reference

You can also find a useful sample on Wikipedia

Microsoft Office XML Formats

How the XSLT is coding generally depends on the structure of the XML you wish to transform. Here is a very simple example, which transforms to one sheet:

<data>
   <row>
      <cell>1.1</cell><cell>12</cell>
   </row>
   <row>
      <cell>2.1</cell><cell>2.2</cell>
   </row>
</data>

Then, use the following XSLT to transform it to Excel XML

<xsl:stylesheet version="1.0"
   xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
   xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
   <xsl:template match="/data">
      <xsl:processing-instruction name="mso-application">
         <xsl:text>progid="Excel.Sheet"</xsl:text>
      </xsl:processing-instruction>

      <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>Bob</Author>
            <Created>2001-01-01T12:00:00Z</Created>
            <Version>1.0</Version>
         </DocumentProperties>
         <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
            <WindowHeight>8835</WindowHeight>
            <WindowWidth>11340</WindowWidth>
            <WindowTopX>480</WindowTopX>
            <WindowTopY>120</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"/>
               <Interior/>
               <NumberFormat ss:Format="#,##0.00"/>
               <Protection/>
            </Style>
         </Styles>
         <Worksheet ss:Name="Sheet1">
            <ss:Table x:FullColumns="1" x:FullRows="1">
               <xsl:attribute name="ss:ExpandedColumnCount">
                  <xsl:value-of select="count(row[1]/cell)"/>
               </xsl:attribute>
               <xsl:attribute name="ss:ExpandedRowCount">
                  <xsl:value-of select="count(row)"/>
               </xsl:attribute>
               <xsl:apply-templates select="row"/>
            </ss: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>
               <Print>
                  <ValidPrinterInfo/>
                  <PaperSizeIndex>9</PaperSizeIndex>
                  <HorizontalResolution>600</HorizontalResolution>
                  <VerticalResolution>0</VerticalResolution>
               </Print>
               <Selected/>
               <ProtectObjects>False</ProtectObjects>
               <ProtectScenarios>False</ProtectScenarios>
            </WorksheetOptions>
         </Worksheet>
      </Workbook>
   </xsl:template>

   <xsl:template match="row" xmlns="urn:schemas-microsoft-com:office:spreadsheet">
      <Row>
         <xsl:apply-templates select="cell"/>
      </Row>
   </xsl:template>

   <xsl:template match="cell" xmlns="urn:schemas-microsoft-com:office:spreadsheet">
      <Cell>
         <Data>
            <xsl:choose>
               <xsl:when test="number(.) =.">
                  <xsl:attribute name="ss:Type">Number</xsl:attribute>
               </xsl:when>
               <xsl:otherwise>
                  <xsl:attribute name="ss:Type">String</xsl:attribute>
               </xsl:otherwise>
            </xsl:choose>
            <xsl:value-of select="."/>
         </Data>
      </Cell>
   </xsl:template>
</xsl:stylesheet>

Although this example only creates one worksheet, you should be able to see how straight-forward it is to create multiple sheets. Simply output another Worksheet element for each work sheet within the document you need.

It can be useful to create a spreadsheet manually in Microsoft Excel, and then Save As Mircosoft Excel XML format, and then have a look at it in Notepad to see the XML it creates. This could be useful for seeing how it does things like formatting, or column widths.