Xml to Excel Via Xsl

there is a nice trick in XSL that allows us to Create XLS documents. therefor taking any object, serializing it to XML and then using the right XSL we will get our XLS just as we want it, so there is the XSL.

notice that if you serialize objects that comes from services the usually come with some
[System.Xml.Serialization.XmlTypeAttribute(Namespace="http://xmlns.bla/bla/yourType")]

and if so the XML nodes will look like this
<node xmlns="http://xmlns.bla/bla/yourType">20:52:00</node>

and for some reason it wont read the nodes, so the solution is
allXmlStr = allXmlStr.Replace("xmlns=\"http://xmlns.bla/bla/yourType\"", "");

<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:user="urn:my-scripts"
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:fo="http://www.w3.org/1999/XSL/Format">

<xsl:template match="rootElement">
<ss: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">

<ss:Styles>
  <ss:Style ss:ID="Default" ss:Name="Normal">
    <ss:Alignment ss:Vertical="Bottom" ss:Horizontal="Center" ss:ReadingOrder="LeftToRight"/>
    <ss:Borders>
      <ss:Border ss:Position="Left" ss:Weight="1"/>
      <ss:Border ss:Position="Right" ss:Weight="1"/>
      <ss:Border ss:Position="Top" ss:Weight="1"/>
      <ss:Border ss:Position="Bottom" ss:Weight="1"/>
    </ss:Borders>
    <ss:Font/>
    <ss:Interior/>
    <ss:NumberFormat/>
    <ss:Protection/>
</ss:Style>

  <ss:Style ss:ID="BoldColumn">
     <ss:Font x:Family="Swiss" ss:Bold="1"/>
   </ss:Style>
   <ss:Style ss:ID="MergeColumn">
      <ss:Font x:Family="Swiss" ss:Bold="1" ss:Color="#ff0000"/>
   </ss:Style>
   <ss:Style ss:ID="StringLiteral">
      <ss:Alignment ss:Vertical="Bottom" ss:ReadingOrder="LeftToRight"/>
   <ss:NumberFormat ss:Format="@"/>
   </ss:Style>
   <ss:Style ss:ID="Decimal">
      <ss:Alignment ss:Vertical="Bottom" ss:ReadingOrder="LeftToRight"/>
      <ss:NumberFormat ss:Format="#,##0.00"/>
   </ss:Style>
   <ss:Style ss:ID="Integer">
      <ss:Alignment ss:Vertical="Bottom" ss:ReadingOrder="LeftToRight"/>
      <ss:NumberFormat ss:Format="#,##0"/>
   </ss:Style>
   <ss:Style ss:ID="DateLiteral">
      <ss:Alignment ss:Vertical="Bottom" ss:ReadingOrder="LeftToRight"/>
      <ss:NumberFormat ss:Format="dd/mm/yyyy;@"/>
   </ss:Style>
</ss:Styles>

  <ss:Worksheet ss:Name="Worksheet 1">
     <ss:Table>
         <ss:Column ss:AutoFitWidth="1" ss:Width="150"/>
         <ss:Column ss:AutoFitWidth="1" ss:Width="100"/>
         <ss:Row>
             <ss:Cell ss:MergeAcross="1" ss:StyleID="MergeColumn">
                 <ss:Data ss:Type="String">Main Header</ss:Data>
              </ss:Cell>
         </ss:Row>
         <ss:Row>
             <ss:Cell ss:StyleID="BoldColumn">
                 <ss:Data ss:Type="String">Secondery Header</ss:Data>
             </ss:Cell>
             <ss:Cell ss:StyleID="BoldColumn">
                 <ss:Data ss:Type="String">Secondery Header</ss:Data>
             </ss:Cell>
         </ss:Row>

         <xsl:for-each select="./Items/Item">
             <ss:Row>
                 <ss:Cell ss:StyleID="StringLiteral">
                     <ss:Data ss:Type="String">
                         <xsl:value-of select="Name"/>
                     </ss:Data>
                 </ss:Cell>
                 <ss:Cell ss:StyleID="Integer">
                     <ss:Data ss:Type="Number">
                         <xsl:value-of select="Value"/>
                     </ss:Data>
                 </ss:Cell>
             </ss:Row>
         </xsl:for-each>
</ss:Table>
</ss:Worksheet>

<ss:Worksheet ss:Name="Worksheet 2">
</ss:Worksheet>

</ss:Workbook>
</xsl:template>
</xsl:stylesheet>

the result is the content of the excel file (XLS)

Comments

Popular posts from this blog

c# Service Play Sound with NAudio example by Moshe

SOLVED The item could not be indexed successfully because the item failed in the indexing subsystem

JS/JQ simulate Enter event