.net导出excel文件的方法_站点基地[zdbase.com]

详细内容

首页 >> 技术文档 >> .net编程 >> 正文

[关闭窗口]

.net导出excel文件的方法

发布日期：2010-06-29 点击：5935 字体：[大中小]

这里介绍.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:template match="/">

<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
        <Title>
          问题跟踪矩阵()
        </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>

          <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>

        <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>