利用xslt导出复杂样式的excel,支持多个worksheet

在项目里新建文件XsltHeader.txt,该文件保存excel的通用样式，我们可以理解为excel的css文件。
打开excel,编辑好各个worksheet复杂样式的excel,然后另存为"XML 电子表格 "的XML格式，名称为book.xml。
用记事本打开刚才后另存的xml文件，可以看到excel自动生成的xml格式，这里我们把样式和数据部分进行分开，以偏复用。

剪切book.xml第一行到"</Style>"标签，把内容粘贴到XsltHeader.txt文件，保存txt文件，这样就建好excel的格式文件了，后面可以用到这些格式。

XsltHeader.txt:

<?xml version="1.0" encoding="UTF-8"?>
<?mso-application progid="Excel.Sheet"?>
<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>Kevin</Author>
  <LastAuthor>Ke boxun</LastAuthor>
  <Created>2013-09-26T03:08:53Z</Created>
  <LastSaved>2013-09-26T03:16:59Z</LastSaved>
  <Company>xxxx</Company>
  <Version>12.00</Version>
 </DocumentProperties>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>12090</WindowHeight>
  <WindowWidth>19200</WindowWidth>
  <WindowTopX>0</WindowTopX>
  <WindowTopY>75</WindowTopY>
  <DoNotCalculateBeforeSave/>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Center"/>
   <Borders/>
   <Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#000000"/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
  <Style ss:ID="s63">
   <Font ss:FontName="宋体" x:CharSet="134" ss:Size="9" ss:Color="#000000"/>
   <NumberFormat ss:Format="#,##0.0000_ "/>
  </Style>
  <Style ss:ID="s66">
   <Font ss:FontName="宋体" x:CharSet="134" ss:Size="9" ss:Color="#000000"
    ss:Bold="1"/>
  </Style>
  <Style ss:ID="s67">
   <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
   <Font ss:FontName="宋体" x:CharSet="134" ss:Size="9" ss:Color="#000000"
    ss:Bold="1"/>
  </Style>
 </Styles>

View Code

 

去掉book.xml多余的代码，只保留WorkSheet之间的内容。

进一步修改book.xml,在头和尾加上xslt语法，使之成为excel模板，

更改book.xml为book.xslt，这样就建好了，最后xslt格式如下:

<?xml version="1.0" encoding="UTF-8"?>
<!-- edited with XMLSpy v2005 sp2 U (http://www.altova.com) by wind (Ru-Board) -->
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:ms="urn:schemas-microsoft-com:xslt" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
  <xsl:output method="html"/>
  <xsl:decimal-format NaN=""/>
  <xsl:template match="/">
<Worksheet ss:Name="BOOK1">
  <Table ss:DefaultColumnWidth="54" ss:DefaultRowHeight="13.5">
    <Column ss:Width="100"/>
    <Column ss:Width="60"/>   
    <Row>
      <Cell ss:StyleID="s67">
        <Data ss:Type="String">名称</Data>
      </Cell>
      <Cell ss:StyleID="s67">
        <Data ss:Type="String">代码</Data>
      </Cell>
      
    </Row>
    <xsl:for-each select="DsBrief/TradeBrief">
    <Row ss:AutoFitHeight="0">      
      <Cell ss:StyleID="s63">
        <Data ss:Type="String">         
          <xsl:value-of select="_month"/>
        </Data>
      </Cell>
      <Cell ss:StyleID="s63">
        <Data ss:Type="Number">
          <xsl:if test="format-number(_rateLimit,'##.##')!=''">
            <xsl:value-of select="format-number(_rateLimit,'##.##')"/>
          </xsl:if>
        </Data>
      </Cell>     
    </Row>
    </xsl:for-each>
  </Table>
  
</Worksheet>
<Worksheet ss:Name="BOOK2">
  <Table ss:DefaultColumnWidth="54" ss:DefaultRowHeight="13.5">
    <Column ss:AutoFitWidth="0" ss:Width="126"/>
    <Column ss:AutoFitWidth="0" ss:Width="192"/>
    <Column ss:AutoFitWidth="0" ss:Width="111.75"/>
    <Column ss:AutoFitWidth="0" ss:Width="176.25"/>
    <Row>
      <Cell ss:StyleID="s66">
        <Data ss:Type="String">名称</Data>
      </Cell>
      <Cell ss:StyleID="s63">
        <Data ss:Type="String">
          <xsl:value-of select="DsBrief/StandardContact/_type"/>
        </Data>
      </Cell>
      <Cell ss:StyleID="s66">
        <Data ss:Type="String">日期</Data>
      </Cell>
      <Cell ss:StyleID="s63">
        <Data ss:Type="String">
          <xsl:value-of select="DsBrief/StandardContact/_termDate"/>
        </Data>
      </Cell>
    </Row>    
  </Table>
  
</Worksheet>

  </xsl:template>
</xsl:stylesheet>

View Code

 

页面导出:

DataSet ds = tb.GetPageData(windcode, startdate, market);
 ExportHelper.exportDataSet(ds, "PeriodDiff", "Book导出测试", this);

 ds包含两个datatable,每一个为一个worksheet对应的datatable,DataSet名称为"DsBrief",两个DataTable为"TradeBrief","StandardContact"

  public static void exportDataSet(DataSet ds, string xsltName,string cnName, System.Web.UI.Page page)
        {
            MemoryStream dataStream = new MemoryStream();
            ds.Namespace = "";
            ds.WriteXml(dataStream);
            doExport(dataStream, xsltName, cnName, page);
        }

        public static void doExport(Stream dataStream, string xsltName, string cnName, System.Web.UI.Page page)
        {
            MemoryStream outStream = new MemoryStream();
            dataStream.Position = 0;
            XmlReader reader = XmlReader.Create(dataStream);
            XslCompiledTransform xTrans = new XslCompiledTransform();
            xTrans.Load(ExportHelper.xsltFilePath + "\\" + xsltName + ".xslt");
            xTrans.Transform(reader, null, outStream);

            outStream.Position = 0;
            StreamReader srContent = null;
            StreamReader srHeader = null;
            try
            {
                srContent = new StreamReader(outStream, System.Text.Encoding.UTF8);
                srHeader = new StreamReader(ExportHelper.xsltFilePath + "\\XsltHeader.txt", System.Text.Encoding.UTF8);
                page.Response.Clear();
                string fileName = page.Server.UrlEncode(cnName) + DateTime.Now.Date.ToString(ExportHelper.DefaultDateTimeFormat) + ".xls";
                page.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
                page.Response.ContentType = "application/vnd.ms-excel";
                page.Response.ContentEncoding = System.Text.Encoding.UTF8;
                page.Response.Charset = "gb2312";
                page.Response.Write(srHeader.ReadToEnd() + srContent.ReadToEnd() + "</Workbook>");
                page.Response.End();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (srContent != null)
                {
                    try
                    {
                        srContent.Close();
                    }
                    catch { }
                }
                if (srHeader != null)
                {
                    try
                    {
                        srHeader.Close();
                    }
                    catch { }
                }
            }
        }

View Code

 

转载于:https://www.cnblogs.com/kevinke/p/3346864.html