2013年4月30日星期二

C# xsl stylesheet for excel template

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:template match="/">
    <xsl:processing-instruction name="mso-application">progid="Excel.Sheet"</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>Tester</Author>
        <LastAuthor>Tester</LastAuthor>
        <Created>2013-04-29T00:40:35Z</Created>
        <LastSaved>2013-04-29T00:40:35Z</LastSaved>
        <Company>My Company</Company>
        <Version>1.0</Version>
      </DocumentProperties>
      <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
        <WindowHeight>8955</WindowHeight>
        <WindowWidth>11115</WindowWidth>
        <WindowTopX>720</WindowTopX>
        <WindowTopY>315</WindowTopY>
        <ProtectStructure>False</ProtectStructure>
        <ProtectWindows>False</ProtectWindows>
        <!--Define styles-->
      </ExcelWorkbook>
      <Styles>
        <Style ss:ID="Default" ss:Name="Normal">
          <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
          <Borders/>
          <Font x:Family="Swiss"/>
          <Interior/>
          <NumberFormat/>
          <Protection/>
        </Style>
        <Style ss:ID="s11">
          <Alignment ss:Horizontal="Left" ss:Vertical="Center"/>
          <Font x:Family="Swiss"/>
        </Style>
        <Style ss:ID="s12">
          <Alignment ss:Horizontal="Left" ss:Vertical="Center" ss:WrapText="1"/>
          <Borders>
            <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"
             ss:Color="#000000"/>
            <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"
             ss:Color="#000000"/>
            <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"
             ss:Color="#000000"/>
            <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"
             ss:Color="#000000"/>
          </Borders>
          <Font x:Family="Swiss" ss:Size="8" ss:Color="#000000" ss:Bold="1"/>
          <Interior ss:Color="#9999FF" ss:Pattern="Solid"/>
        </Style>
        <Style ss:ID="s21">
          <Alignment ss:Vertical="Bottom"/>
          <Borders/>
          <Font x:Family="Swiss"/>
          <Interior/>
          <NumberFormat ss:Format="d/m/yyyy;@"/>
          <Protection/>
        </Style>
        <Style ss:ID="s22">
          <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
          <NumberFormat ss:Format="dd-mmm-yyyy;@"/>
        </Style>
        <Style ss:ID="s23">
          <Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>
          <Borders>
            <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"
             ss:Color="#000000"/>
            <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"
             ss:Color="#000000"/>
            <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"
             ss:Color="#000000"/>
            <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"
             ss:Color="#000000"/>
          </Borders>
          <Font x:Family="Swiss" ss:Size="8" ss:Color="#000000" ss:Bold="1"/>
          <Interior ss:Color="#9999FF" ss:Pattern="Solid"/>
        </Style>
        <Style ss:ID="s24">
          <Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>
          <Borders>
            <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"
             ss:Color="#000000"/>
            <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"
             ss:Color="#000000"/>
            <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"
             ss:Color="#000000"/>
            <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"
             ss:Color="#000000"/>
          </Borders>
          <Font x:Family="Swiss" ss:Size="8" ss:Color="#000000" ss:Bold="1"/>
          <Interior ss:Color="#9999FF" ss:Pattern="Solid"/>
          <NumberFormat ss:Format="d/m/yyyy;@"/>
        </Style>
        <Style ss:ID="s25">
          <Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>
          <Borders>
            <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"
             ss:Color="#000000"/>
            <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"
             ss:Color="#000000"/>
            <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"
             ss:Color="#000000"/>
            <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"
             ss:Color="#000000"/>
          </Borders>
          <Font x:Family="Swiss" ss:Size="8" ss:Color="#000000" ss:Bold="1"/>
          <Interior ss:Color="#9999FF" ss:Pattern="Solid"/>
          <NumberFormat ss:Format="&quot;$&quot;#,##0.00"/>
        </Style>
        <Style ss:ID="s26">
          <Alignment ss:Horizontal="Right" ss:Vertical="Center" ss:WrapText="1"/>
          <NumberFormat ss:Format="&quot;$&quot;#,##0.00"/>
        </Style>
      </Styles>
      <Worksheet ss:Name="Sheet1">
        <Table ss:ExpandedColumnCount="20" ss:ExpandedRowCount="65532" x:FullColumns="1"
         x:FullRows="1">
          <Column ss:AutoFitWidth="0" ss:Width="95"/>
          <Column ss:AutoFitWidth="0" ss:Width="65"/>
          <Column ss:AutoFitWidth="0" ss:Width="70"/>
          <Column ss:AutoFitWidth="0" ss:Width="71"/>
          <Column ss:Index="5" ss:AutoFitWidth="0" ss:Width="35"/>
          <Row ss:AutoFitHeight="0" ss:Height="15.5">
            <Cell ss:StyleID="s12">
              <Data ss:Type="String">TITLE 1:</Data>
            </Cell>
            <Cell ss:StyleID="s11">
              <Data ss:Type="String">ABC</Data>
            </Cell>
          </Row>
          <Row ss:AutoFitHeight="0" ss:Height="15.5">
            <Cell ss:StyleID="s12">
              <Data ss:Type="String">TITLE 2:</Data>
            </Cell>
            <Cell ss:StyleID="s11">
              <Data ss:Type="String"></Data>
            </Cell>
          </Row>
          <Row ss:AutoFitHeight="0" ss:Height="15.5">
            <Cell ss:StyleID="s12">
              <Data ss:Type="String">Date:</Data>
            </Cell>
            <Cell ss:StyleID="s22">
              <Data ss:Type="String"></Data>
            </Cell>
          </Row>
          <Row ss:AutoFitHeight="0" ss:Height="15.5">
            <Cell>
              <Data ss:Type="String"></Data>
            </Cell>
          </Row>
          <!--Table Header Section-->
          <Row ss:AutoFitHeight="0" ss:Height="22.5">
            <Cell ss:StyleID="s23">
              <Data ss:Type="String">HEADER1</Data>
            </Cell>
            <Cell ss:StyleID="s23">
              <Data ss:Type="String">HEADER2</Data>
            </Cell>
            <Cell ss:StyleID="s23">
              <Data ss:Type="String">HEADER2</Data>
            </Cell>
            <Cell ss:StyleID="s23">
              <Data ss:Type="String">HEADER3</Data>
            </Cell>
            <Cell ss:StyleID="s24">
              <Data ss:Type="String">DATE</Data>
            </Cell>
            <Cell ss:StyleID="s25">
              <Data ss:Type="String">COST</Data>
            </Cell>
          </Row>
          <!--Table Body Section-->
          <xsl:for-each select="/NewDataSet/Table">
            <Row ss:AutoFitHeight="0" ss:Height="15">
              <Cell ss:StyleID="Default">
                <Data ss:Type="String">
                  <xsl:value-of select="COLUMN_TEXT_A"/>
                </Data>
              </Cell>
              <Cell ss:StyleID="Default">
                <Data ss:Type="String">
                  <xsl:value-of select="COLUMN_TEXT_B"/>
                </Data>
              </Cell>
              <Cell ss:StyleID="Default">
                <Data ss:Type="String">
                  <xsl:value-of select="COLUMN_TEXT_C"/>
                </Data>
              </Cell>
              <Cell ss:StyleID="Default">
                <Data ss:Type="String">
                  <xsl:value-of select="COLUMN_TEXT_D"/>
                </Data>
              </Cell>
              <Cell ss:StyleID="s22">
                <Data ss:Type="String">
                  <xsl:value-of select="COLUMN_DATE"/>
                </Data>
              </Cell>
              <Cell ss:StyleID="s26">
                <Data ss:Type="Number">
                  <xsl:value-of select="COLUMN_NUMBER"/>
                </Data>
              </Cell>
            </Row>
          </xsl:for-each>
          <!--Summary Section-->
          <Row ss:AutoFitHeight="1">
            <Cell ss:StyleID="DefBold" ss:MergeAcross="4">
                <Data ss:Type="String">Total</Data>
            </Cell>
            <Cell ss:StyleID="NumBold">
                <Data ss:Type="Number"><xsl:value-of select="sum(//Table/COLUMN_NUMBER)"/></Data>
            </Cell>
        </Row>
        </Table>
        <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
          <PageSetup>
            <Header x:Margin="0.25" x:Data="&amp;R Date: &amp;D Time: &amp;T"/>
            <Footer x:Margin="0.25" x:Data="&amp;L Private and Confidential &amp;R Page &amp;P of &amp;N"/>
            <Layout x:Orientation="Landscape"/>
            <PageMargins x:Bottom="0.5" x:Left="0.5" x:Right="0.5" x:Top="0.5"/>
          </PageSetup>
          <!--Fit all columns to one page-->
          <FitToPage/>
          <Print>
            <FitWidth>1</FitWidth>
            <FitHeight>1000</FitHeight>
            <ValidPrinterInfo/>
            <PaperSizeIndex>9</PaperSizeIndex>
            <HorizontalResolution>600</HorizontalResolution>
            <VerticalResolution>600</VerticalResolution>
          </Print>
          <Selected/>
          <Panes>
            <Pane>
              <Number>3</Number>
              <ActiveCol>5</ActiveCol>
              <RangeSelection>C6</RangeSelection>
            </Pane>
          </Panes>
          <ProtectObjects>False</ProtectObjects>
          <ProtectScenarios>False</ProtectScenarios>
        </WorksheetOptions>
      </Worksheet>
      <Worksheet ss:Name="Sheet2">
        <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
          <ProtectObjects>False</ProtectObjects>
          <ProtectScenarios>False</ProtectScenarios>
        </WorksheetOptions>
      </Worksheet>
      <Worksheet ss:Name="Sheet3">
        <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
          <ProtectObjects>False</ProtectObjects>
          <ProtectScenarios>False</ProtectScenarios>
        </WorksheetOptions>
      </Worksheet>
    </Workbook>
  </xsl:template>
</xsl:stylesheet>