使用XML数据结合XSLT导出Excel

XML数据如下：

<?xml version="1.0" encoding="utf-8"?>
<!--<?xml-stylesheet type="text/xsl" href="toexcel_test.xslt"?>-->
<DataSet>
  <Data>
    <Item Name="ID" Type="Hidden" Value="121">121</Item>
    <Item Name="编号" Type="Text" Value="1">1</Item>
    <Item Name="姓名" Type="Text" Title="基本信息" Colspan="6" Value="张三">张三</Item>
    <Item Name="性别" Type="Text" Title="基本信息" Value="男">男</Item>
    <Item Name="出生日期" Type="Text" Title="基本信息" Value="1987-8-22">1987-8-22</Item>
    <Item Name="籍贯" Type="Text" Title="基本信息" Value="湖北武汉">湖北武汉</Item>
    <Item Name="职业" Type="Text" Title="基本信息" Value="销售人员">销售人员</Item>
    <Item Name="现居地" Type="Text" Title="基本信息" Value="深圳">深圳</Item>
  </Data>
  <Data>
    <Item Name="ID" Type="Hidden" Value="122">122</Item>
    <Item Name="编号" Type="Text" Value="2">2</Item>
    <Item Name="姓名" Type="Text" Title="基本信息" Value="李四">李四</Item>
    <Item Name="性别" Type="Text" Title="基本信息" Value="男">男</Item>
    <Item Name="出生日期" Type="Text" Title="基本信息" Value="1988-9-22">1987-8-22</Item>
    <Item Name="籍贯" Type="Text" Title="基本信息" Value="湖北武汉">湖北武汉</Item>
    <Item Name="职业" Type="Text" Title="基本信息" Value="销售人员">销售人员</Item>
    <Item Name="现居地" Type="Text" Title="基本信息" Value="深圳">深圳</Item>
  </Data>
  <Data>
    <Item Name="ID" Type="Hidden" Value="123">123</Item>
    <Item Name="编号" Type="Text" Value="3">3</Item>
    <Item Name="姓名" Type="Text" Title="基本信息" Value="王五">王五</Item>
    <Item Name="性别" Type="Text" Title="基本信息" Value="男">男</Item>
    <Item Name="出生日期" Type="Text" Title="基本信息" Value="1989-10-22">1987-8-22</Item>
    <Item Name="籍贯" Type="Text" Title="基本信息" Value="湖北武汉">湖北武汉</Item>
    <Item Name="职业" Type="Text" Title="基本信息" Value="销售人员">销售人员</Item>
    <Item Name="现居地" Type="Text" Title="基本信息" Value="深圳">深圳</Item>
  </Data>
  <Other>
    <Caption>员工信息</Caption>
  </Other>
</DataSet>

　　

可以通过另存对应的Excel文件为“表格xml”，得到Excel文件对应的XML，如下图：

仔细查看得到的表格XML文件，你会发现Excel文件对应的XML描述，就像HTML+CSS一样，也可以在这个XML文件中设置Excel的各种样式。

使用XSLT结合XML数据源转换其中动态的部分可以得到需要的Excel文件。

比如一个转换XML成“Excel表格数据XML”的XSLT如下：

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" 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"
                xmlns:html="http://www.w3.org/TR/REC-html40">
  <xsl:template match="/DataSet">
    <Workbook>
      <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
        <Created>1996-12-17T01:32:42Z</Created>
        <LastSaved>2000-11-18T06:53:49Z</LastSaved>
        <Version>11.6568</Version>
      </DocumentProperties>
      <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
        <RemovePersonalInformation/>
      </OfficeDocumentSettings>
      <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
        <WindowHeight>4530</WindowHeight>
        <WindowWidth>8505</WindowWidth>
        <WindowTopX>480</WindowTopX>
        <WindowTopY>120</WindowTopY>
        <ActiveSheet>0</ActiveSheet>
        <AcceptLabelsInFormulas/>
        <ProtectStructure>False</ProtectStructure>
        <ProtectWindows>False</ProtectWindows>
      </ExcelWorkbook>
      <Styles>
        <Style ss:ID="Default" ss:Name="Normal">
          <Alignment ss:Vertical="Bottom"/>
          <Borders/>
          <Font ss:FontName="宋体" x:CharSet="134" ss:Size="12"/>
          <Interior/>
          <NumberFormat/>
          <Protection/>
        </Style>
        <Style ss:ID="s28">
          <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
          <Font ss:FontName="宋体" x:CharSet="134" ss:Size="16" ss:Bold="1"/>
        </Style>
        <Style ss:ID="s30">
          <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
          <Borders>
            <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
            <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
            <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
            <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
          </Borders>
        </Style>
        <Style ss:ID="s31">
          <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
          <Borders>
            <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
            <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
            <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
            <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
          </Borders>
          <Font ss:FontName="宋体" x:CharSet="134" ss:Size="12" ss:Color="#000000"/>
        </Style>
        <Style ss:ID="s34">
          <Alignment ss:Horizontal="Right" ss:Vertical="Bottom"/>
          <Borders>
            <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
            <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
            <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
            <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
          </Borders>
          <NumberFormat ss:Format="@"/>
        </Style>
        <Style ss:ID="s35">
          <Borders>
            <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
            <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
            <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
            <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
          </Borders>
        </Style>
        <Style ss:ID="s36">
          <Alignment ss:Horizontal="Right" ss:Vertical="Bottom"/>
          <Borders>
            <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
            <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
            <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
            <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
          </Borders>
        </Style>
      </Styles>
      <Worksheet ss:Name="sheet">
        <Table x:FullColumns="1"  x:FullRows="1" ss:DefaultColumnWidth="100" ss:DefaultRowHeight="14.25">
          <Row ss:Height="20.25">
            <Cell ss:StyleID="s28">
              <xsl:attribute name="ss:MergeAcross">
                <xsl:value-of select="count(//Data[1]/Item[@Type!='Hidden'])"/>
              </xsl:attribute>
              <Data ss:Type="String">
                <xsl:value-of select="//Caption"/>
              </Data>
            </Cell>
          </Row>
          <Row>
            <xsl:for-each select="//DataSet/Data[1]/Item[@Type!='Hidden']">
              <xsl:choose>
                <xsl:when test="@Title!=''">
                  <xsl:if test="@Colspan&gt;0">
                    <Cell ss:StyleID="s31">
                      <xsl:attribute name="ss:MergeAcross">
                        <xsl:value-of select="number(@Colspan)-1"/>
                      </xsl:attribute>
                      <Data ss:Type="String">
                        <xsl:value-of select="@Title"/>
                      </Data>
                    </Cell>
                  </xsl:if>
                </xsl:when>
                <xsl:otherwise>
                  <Cell ss:StyleID="s31" ss:MergeDown="1">
                    <Data ss:Type="String">
                      <xsl:value-of select="@Name"/>
                    </Data>
                  </Cell>
                </xsl:otherwise>
              </xsl:choose>
            </xsl:for-each>
          </Row>

          <Row>
            <xsl:for-each select="//DataSet/Data[1]/Item[@Type!='Hidden'][@Title!='']">
              <Cell ss:StyleID="s31">
                <xsl:if test="position()=1">
                  <xsl:attribute name="ss:Index">2</xsl:attribute>
                </xsl:if>
                <Data ss:Type="String">
                  <xsl:value-of select="@Name"/>
                </Data>
              </Cell>
            </xsl:for-each>
          </Row>

          <xsl:for-each select="//DataSet/Data">
            <Row>
              <xsl:for-each select="Item[@Type!='Hidden']">
                <Cell ss:StyleID="s31">
                  <Data ss:Type="String">
                    <xsl:value-of select="substring(@Value , 1 , 80)"/>
                  </Data>
                </Cell>
              </xsl:for-each>
            </Row>
          </xsl:for-each>
        </Table>

        <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
          <Unsynced/>
          <Print>
            <ValidPrinterInfo/>
            <PaperSizeIndex>9</PaperSizeIndex>
            <HorizontalResolution>300</HorizontalResolution>
            <VerticalResolution>300</VerticalResolution>
          </Print>
          <Selected/>
          <FreezePanes/>
          <FrozenNoSplit/>
          <SplitHorizontal>4</SplitHorizontal>
          <TopRowBottomPane>7</TopRowBottomPane>
          <SplitVertical>1</SplitVertical>
          <LeftColumnRightPane>1</LeftColumnRightPane>
          <ActivePane>0</ActivePane>
          <Panes>
            <Pane>
              <Number>3</Number>
            </Pane>
            <Pane>
              <Number>1</Number>
            </Pane>
            <Pane>
              <Number>2</Number>
              <ActiveRow>4</ActiveRow>
            </Pane>
            <Pane>
              <Number>0</Number>
              <ActiveRow>4</ActiveRow>
            </Pane>
          </Panes>
          <ProtectObjects>False</ProtectObjects>
          <ProtectScenarios>False</ProtectScenarios>
        </WorksheetOptions>
      </Worksheet>

      <Worksheet ss:Name="Sheet3">
        <Table ss:ExpandedColumnCount="0" ss:ExpandedRowCount="0" x:FullColumns="1"
         x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25"/>
        <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
          <ProtectObjects>False</ProtectObjects>
          <ProtectScenarios>False</ProtectScenarios>
        </WorksheetOptions>
      </Worksheet>
    </Workbook>
  </xsl:template>
</xsl:stylesheet>

　　

使用XslCompiledTransform.Transform 方法即可调用XSLT文件转换指定的XML。

详见MSDN：http://msdn.microsoft.com/zh-cn/library/system.xml.xsl.xslcompiledtransform.transform(v=VS.80).aspx

得到XML之后向前台Response即可实现导出效果，C#的方法如下：

          Response.ClearContent();

          Response.ClearHeaders();

          Response.ContentType = "application/ms-excel";

          Response.AddHeader("Content-Disposition", "inline;filename='前台看到的文件名'");

          Response.Write(xmlStr);//xmlStr为转换之后得到的XML字符串

          Response.Flush();

          Response.Close();

得到的Excel效果如下(第一列及前三行是固定的)：

Excel第一列及前三行是固定这一效果对应的XML是：

 <SplitHorizontal>3</SplitHorizontal>

 <TopRowBottomPane>3</TopRowBottomPane>

 <SplitVertical>1</SplitVertical>

 <LeftColumnRightPane>1</LeftColumnRightPane>