欢迎加入站点基地设计开发交流群:30720409
详细内容
.net导出excel文件的方法
发布日期:2010-06-29     点击:5768     字体:[ ]

这里介绍.net利用xsl模板,根据xsl文件定义的样式,导出excel文件,这里引用到了Interop.MSXML2.dllInterop.MSXML2.rar

1。导出文件页PRMjlExport.aspx.cs代码(我这里继承的自己定义BasePage,引用时可直接继承System.Web.UI.Page):

 public partial class PRMjlExport : BasePage
    {
        ProblemJLController pc = new ProblemJLController();

        protected void Page_Load(object sender, EventArgs e)
        {
            try
            {
                if (!this.IsPostBack)
                {
                    //这里是添加临时文件权限,不然有可能导出的文件无法直接打开
                    HttpContext.Current.Response.ClearHeaders();
                    HttpContext.Current.Response.AppendHeader("Expires ", System.DateTime.Now.AddMinutes(30).ToString());
                    HttpContext.Current.Response.AppendHeader("Pragma ", "public ");
                    HttpContext.Current.Response.AppendHeader("Cache-Control ", "must-revalidate,   post-check=0,   pre-check=0 ");
                    HttpContext.Current.Response.AppendHeader("Cache-Control ", "public ");

                    //获取参数
                    if (Request.QueryString["proID"] != null)
                    {
                        ExportProblemList(Request.QueryString["proID"].ToString());
                    }
                }
            }
            catch (Exception err)
            { Response.Write(err.ToString()); }
        }

        private void ExportProblemList(string sProID)
        {
            try
            {
                //获取数据源
                DataSet ds = pc.problemSelect(sProID);

                XmlDocument myXml = new XmlDataDocument(ds);

                MSXML2.DOMDocument pXmlDocShow = new DOMDocumentClass();
                pXmlDocShow.async = false;
                pXmlDocShow.loadXML(myXml.InnerXml);

                XmlDocument pXslDoc = new XmlDocument();

                pXslDoc.Load(Server.MapPath("jlExcel.xsl"));

                MSXML2.DOMDocument pXslDocShow = new DOMDocumentClass();
                pXslDocShow.async = false;
                pXslDocShow.loadXML(pXslDoc.InnerXml);


                string strTemp = pXmlDocShow.transformNode(pXslDocShow);
                int strTempBegin = strTemp.IndexOf("<?xml");
                int strTempEnd = strTemp.LastIndexOf("?>");
                strTemp = strTemp.Remove(strTempBegin, strTempEnd - strTempBegin + 2);
                strTemp = @"<?xml version='1.0' encoding='utf-8'?><?mso-application progid='Excel.Sheet'?>" + strTemp;

                string _fileXls = DateTime.Now.ToString("yyyyMMdd");

                Response.Clear();
                Response.Buffer = true;
                Response.Charset = "utf-8";
                Response.AddHeader("Content-Disposition", "attachment; filename=" + _fileXls + ".xls");

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

                Response.Write(strTemp);
            }
            catch (Exception err)
            { Response.Write(err.ToString()); }

        }
    }

 

2。下面为定义的jlExcel.xsl模板,主要是定义的excel文件显示样式,内容如下:

 

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
 xmlns:xs="http://www.w3.org/2001/XMLSchema"
 xmlns:fn="http://www.w3.org/2005/xpath-functions"
 xmlns:xdt="http://www.w3.org/2005/xpath-datatypes"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns="urn:schemas-microsoft-com:office:spreadsheet">

  <!--xsl:output method="xml" version="1.0" encoding="GB2312" indent="yes"/-->

  <xsl:template match="/">

    <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"
      xmlns:xsd="http://www.w3.org/2001/XMLSchema"
      xmlns:fn="http://www.w3.org/2005/xpath-functions"
      xmlns:xdt="http://www.w3.org/2005/xpath-datatypes">


      <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
        <Title>
          问题跟踪矩阵(<!--xsl:value-of select="/QuestionList/@ProjectName"/-->)
        </Title>
        <LastAuthor>ITQA_Administrator</LastAuthor>
        <Created>2008-01-14T07:14:03Z</Created>
        <LastSaved>2008-01-15T01:20:52Z</LastSaved>
        <Version>11.5606</Version>
      </DocumentProperties>
      <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
        <WindowHeight>13050</WindowHeight>
        <WindowWidth>18180</WindowWidth>
        <WindowTopX>480</WindowTopX>
        <WindowTopY>105</WindowTopY>
        <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="12"/>
          <Interior/>
          <NumberFormat/>
          <Protection/>
        </Style>
        <Style ss:ID="s21">
          <Font ss:FontName="Simsun" ss:Size="9"/>
        </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="2"
            ss:Color="#000000"/>
          </Borders>
          <Font ss:FontName="Simsun" ss:Size="9" ss:Bold="1"/>
        </Style>
        <Style ss:ID="s24">
          <Alignment ss:Vertical="Center" ss:WrapText="1"/>
          <Font ss:FontName="Simsun" ss:Size="9"/>
        </Style>
        <Style ss:ID="s26">
          <Alignment ss:Vertical="Center" ss:WrapText="1"/>
          <Font ss:FontName="Simsun" ss:Size="9"/>
          <NumberFormat ss:Format="General Date"/>
        </Style>
      </Styles>


      <Worksheet ss:Name="Question">
        <Table ss:ExpandedColumnCount="25" x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25">

          <Column ss:StyleID="s21" ss:AutoFitWidth="0" ss:Width="30"/>
          <Column ss:AutoFitWidth="0" ss:Width="50"/>
          <Column ss:AutoFitWidth="0" ss:Width="50"/>
          <Column ss:AutoFitWidth="0" ss:Width="60"/>
          <Column ss:AutoFitWidth="0" ss:Width="60"/>
         
          <Column ss:AutoFitWidth="0" ss:Width="60"/>
          <Column ss:AutoFitWidth="0" ss:Width="300"/>
          <Column ss:AutoFitWidth="0" ss:Width="80"/>
          <Column ss:AutoFitWidth="0" ss:Width="150"/>
          <Column ss:AutoFitWidth="0" ss:Width="50"/>
         
          <Column ss:AutoFitWidth="0" ss:Width="60"/>
          <Column ss:AutoFitWidth="0" ss:Width="150"/>
          <Column ss:AutoFitWidth="0" ss:Width="50"/>
          <Column ss:AutoFitWidth="0" ss:Width="60"/>
          <Column ss:AutoFitWidth="0" ss:Width="150"/>

          <Column ss:AutoFitWidth="0" ss:Width="50"/>
          <xsl/>
         
          <Row ss:Height="22.5" ss:StyleID="s21">
            <Cell ss:StyleID="s23"><Data ss:Type="String">序号</Data></Cell>
            <Cell ss:StyleID="s23"><Data ss:Type="String">重要性</Data></Cell>
            <Cell ss:StyleID="s23"> <Data ss:Type="String">发现者</Data></Cell>
            <Cell ss:StyleID="s23"> <Data ss:Type="String">发现时间</Data> </Cell>
            <Cell ss:StyleID="s23"> <Data ss:Type="String">来源</Data></Cell>
           
            <Cell ss:StyleID="s23"><Data ss:Type="String">阶段</Data></Cell>
            <Cell ss:StyleID="s23"><Data ss:Type="String">问题描述</Data></Cell>
            <Cell ss:StyleID="s23"><Data ss:Type="String">期望解决时间</Data> </Cell>
            <Cell ss:StyleID="s23"><Data ss:Type="String">备注</Data> </Cell>
            <Cell ss:StyleID="s23"><Data ss:Type="String">确认者</Data> </Cell>
           
            <Cell ss:StyleID="s23"> <Data ss:Type="String">确认时间</Data></Cell> 
            <Cell ss:StyleID="s23"><Data ss:Type="String">意见反馈</Data> </Cell>
            <Cell ss:StyleID="s23"><Data ss:Type="String">解决者</Data></Cell>
            <Cell ss:StyleID="s23"><Data ss:Type="String">解决时间</Data></Cell>
            <Cell ss:StyleID="s23"><Data ss:Type="String">解决办法</Data></Cell>
             
            <Cell ss:StyleID="s23"><Data ss:Type="String">状态</Data></Cell>
           
             
          </Row>
         
          <xsl:for-each select="NewDataSet/ds">
            <Row ss:Height="22.5" ss:StyleID="s21">
              <Cell ss:StyleID="s24"><Data ss:Type="String"><xsl:value-of select="CycNO"/></Data></Cell>
              <Cell ss:StyleID="s24">
                <Data ss:Type="String">
                  <xsl:choose>
                    <xsl:when test="Cyc = 1">高</xsl:when>
                    <xsl:when test="Cyc = 2">中</xsl:when>
                    <xsl:when test="Cyc = 3">低</xsl:when>
                    <xsl:otherwise>未定义</xsl:otherwise>
                  </xsl:choose>
                </Data>
              </Cell>
              <Cell ss:StyleID="s24"> <Data ss:Type="String"> <xsl:value-of select="CycWriterName"/></Data></Cell>
              <Cell ss:StyleID="s26"><Data ss:Type="String"><xsl:value-of select="CycWriteDate"/> </Data>
              </Cell>
              <Cell ss:StyleID="s24"><Data ss:Type="String">
                  <xsl:choose>
                    <xsl:when test="CycSource = 1">文档资料</xsl:when>
                    <xsl:when test="CycSource = 2">应用系统</xsl:when>
                    <xsl:when test="CycSource = 3">沟通协调</xsl:when>
                    <xsl:otherwise>未定义</xsl:otherwise>
                  </xsl:choose>
                </Data>
              </Cell>
              <Cell ss:StyleID="s24"> <Data ss:Type="String">
                  <xsl:choose>
                    <xsl:when test="CycPhase = 1">需求调研</xsl:when>
                    <xsl:when test="CycPhase = 2">系统设计</xsl:when>
                    <xsl:when test="CycPhase = 3">系统实施</xsl:when>
                    <xsl:when test="CycPhase = 4">调试运行</xsl:when>
                    <xsl:when test="CycPhase = 5">竣工验收</xsl:when>
                    <xsl:otherwise>未定义</xsl:otherwise>
                  </xsl:choose>
                </Data>
              </Cell>

              <Cell ss:StyleID="s24"><Data ss:Type="String"><xsl:value-of select="CycRemark"/> </Data></Cell>
              <Cell ss:StyleID="s26"><Data ss:Type="String"> <xsl:value-of select="CycPlanFinishDate"/></Data></Cell>
              <Cell ss:StyleID="s24"><Data ss:Type="String"><xsl:value-of select="CycMemo"/> </Data></Cell>
              <Cell ss:StyleID="s24"><Data ss:Type="String"> <xsl:value-of select="CycConfirmerName"/></Data></Cell>
              <Cell ss:StyleID="s26"><Data ss:Type="String"> <xsl:value-of select="CycConfirmDate"/></Data> </Cell>

              <Cell ss:StyleID="s24"><Data ss:Type="String"> <xsl:value-of select="CycFeedBack"/></Data> </Cell>
              <Cell ss:StyleID="s24"><Data ss:Type="String"><xsl:value-of select="CycFinishName"/> </Data></Cell>
              <Cell ss:StyleID="s26"><Data ss:Type="String"><xsl:value-of select="CycFinishDate"/></Data> </Cell>
              <Cell ss:StyleID="s24"><Data ss:Type="String"> <xsl:value-of select="CycFinishMethod"/></Data> </Cell>
              <Cell ss:StyleID="s24">
                <Data ss:Type="String">
                  <xsl:choose>
                    <xsl:when test="CycStatus = 1">未修改</xsl:when>
                    <xsl:when test="CycStatus = 2">处理中</xsl:when>
                    <xsl:when test="CycStatus = 3">待定</xsl:when>
                    <xsl:when test="CycStatus = 4">已修改</xsl:when>
                    <xsl:otherwise>未定义</xsl:otherwise>
                  </xsl:choose>
                </Data>
              </Cell>
            </Row>
          </xsl:for-each>
        </Table>

        <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
          <Print>
            <ValidPrinterInfo/>
            <PaperSizeIndex>9</PaperSizeIndex>
            <HorizontalResolution>300</HorizontalResolution>
            <VerticalResolution>300</VerticalResolution>
          </Print>
          <Selected/>
          <DoDisplayGridlines/>
          <Panes>
            <Pane>
              <Number>3</Number>
              <ActiveRow>12</ActiveRow>
              <ActiveCol>4</ActiveCol>
            </Pane>
          </Panes>
          <ProtectObjects>False</ProtectObjects>
          <ProtectScenarios>False</ProtectScenarios>
        </WorksheetOptions>
      </Worksheet>
    </Workbook>

  </xsl:template>

</xsl:stylesheet>

用户评论
昵称 
内容  *
验证码   
   
Copyright © 2010 zdbase.com All Rights Reserved. 苏ICP备15039389号-1 可人软件设计