.Net导出Excel的两种方法,导出Word亦类似

网页上的数据导出有很多种导出方法，其中有的是真正格式的Excel，也有XML文档格式的Excel，还有table格式的。真正导出Excel格式的打开是没有任何问题，但是有的要求机器上需要安装Office Excel，借用Microsoft.Office.Interop.Excel,dll

。现在要说的是，用XML格式导出成Excel文件，打开的时候会出现如下提示，点击后打开没有问题。

一般的客户也接受这种提示，尽管有瑕疵，但是对于开发比较简单，不用考虑部署的服务器上是否安装excel，不用借用第三方控件。下面就说一下具体实现，两种方式。

 

具体实现                                                                                                       

第一种，直接导出DataGrid中的数据：

代码：

 public void DGToExcel(System.Web.UI.Control ctl)   

        {  
            HttpContext.Current.Response.AppendHeader( " Content-Disposition ", " attachment;filename=Excel.xls "); 
            HttpContext.Current.Response.Charset = " gb2312 ";     
            HttpContext.Current.Response.ContentEncoding =System.Text.Encoding.GetEncoding( " gb2312 ");
            HttpContext.Current.Response.ContentType = " application/ms-excel ";
            ctl.Page.EnableViewState = false;    
            System.IO.StringWriter tw =  new System.IO.StringWriter() ; 
            System.Web.UI.HtmlTextWriter hw =  new System.Web.UI.HtmlTextWriter (tw); 
            ctl.RenderControl(hw); 
            HttpContext.Current.Response.Write(tw.ToString()); 
            HttpContext.Current.Response.End(); 
        }

调用就直接用即可：DGToExcel(this.DataGrid1);

第二种，导出XML格式的数据：

原理：

就是通过Excel另存为一个XML格式，然后将这个XML格式的文件拆分成head，content，bottom三个部分，咱们就是处理中间的content，将需要的数据遍历出来，放在content中，最后组合成一个Excel文档认的XML文件即可。

代码：

 

#region 写Excel头
///   <summary>
         ///
         ///   </summary>
         ///   <param name="OutFileContent"></param>
         ///   <returns></returns>
         private StringBuilder AddHeadFile(StringBuilder OutFileContent)
        {
            OutFileContent.Append( " <?xml version=\"1.0\"?>\r\n ");
            OutFileContent.Append( " <?mso-application progid=\"Excel.Sheet\"?>\r\n ");
            OutFileContent.Append( " <Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n ");
            OutFileContent.Append( "  xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n ");
            OutFileContent.Append( "  xmlns:x=\"urn:schemas-microsoft-com:office:excel\"\r\n ");
            OutFileContent.Append( "  xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n ");
            OutFileContent.Append( "  xmlns:html=\"http://www.w3.org/TR/REC-html40\">\r\n ");
            OutFileContent.Append( "  <DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">\r\n ");
            OutFileContent.Append( "   <Author>panss</Author>\r\n ");
            OutFileContent.Append( "   <LastAuthor>Оґ¶ЁТе</LastAuthor>\r\n ");
            OutFileContent.Append( "   <Created>2004-12-31T03:40:31Z</Created>\r\n ");
            OutFileContent.Append( "   <Company>Prcedu</Company>\r\n ");
            OutFileContent.Append( "   <Version>12.00</Version>\r\n ");
            OutFileContent.Append( "  </DocumentProperties>\r\n ");
            OutFileContent.Append( "  <OfficeDocumentSettings xmlns=\"urn:schemas-microsoft-com:office:office\">\r\n ");
            OutFileContent.Append( "   <DownloadComponents/>\r\n ");
            OutFileContent.Append( "   <LocationOfComponents HRef=\"file:///F:\\Tools\\OfficeXP\\OfficeXP\\\"/>\r\n ");
            OutFileContent.Append( "  </OfficeDocumentSettings>\r\n ");
            OutFileContent.Append( "  <ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">\r\n ");
            OutFileContent.Append( "   <WindowHeight>9000</WindowHeight>\r\n ");
            OutFileContent.Append( "   <WindowWidth>10620</WindowWidth>\r\n ");
            OutFileContent.Append( "   <WindowTopX>480</WindowTopX>\r\n ");
            OutFileContent.Append( "   <WindowTopY>45</WindowTopY>\r\n ");
            OutFileContent.Append( "   <ProtectStructure>False</ProtectStructure>\r\n ");
            OutFileContent.Append( "   <ProtectWindows>False</ProtectWindows>\r\n ");
            OutFileContent.Append( "  </ExcelWorkbook>\r\n ");
            OutFileContent.Append( "  <Styles>\r\n ");
            OutFileContent.Append( "   <Style ss:ID=\"Default\" ss:Name=\"Normal\">\r\n ");
            OutFileContent.Append( "    <Alignment ss:Vertical=\"Center\" />\r\n ");
            OutFileContent.Append( "    <Borders/>\r\n ");
            OutFileContent.Append( "    <Font ss:FontName=\"ЛОМе\" x:CharSet=\"134\" ss:Size=\"12\"/>\r\n ");
            OutFileContent.Append( "    <Interior/>\r\n ");
            OutFileContent.Append( "    <NumberFormat/>\r\n ");
            OutFileContent.Append( "    <Protection/>\r\n ");
            OutFileContent.Append( "   </Style>\r\n ");
            OutFileContent.Append( "   <Style ss:ID=\"s62\">\r\n ");
            OutFileContent.Append( "    <Alignment ss:Vertical=\"Center\" ss:Horizontal=\"Center\" ss:WrapText=\"1\"/>\r\n ");
            OutFileContent.Append( "    <Font ss:FontName=\"ЛОМе\" x:CharSet=\"134\" ss:Size=\"9\"/>\r\n ");
            OutFileContent.Append( "   </Style>\r\n ");
            OutFileContent.Append( "   <Style ss:ID=\"s74\">\r\n ");
            OutFileContent.Append( "    <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/>\r\n ");
            OutFileContent.Append( "    <Borders>\r\n ");
            OutFileContent.Append( "   <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n ");
            OutFileContent.Append( "   <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n ");
            OutFileContent.Append( "   <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n ");
            OutFileContent.Append( "   <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n ");
            OutFileContent.Append( "   </Borders>\r\n ");
            OutFileContent.Append( "    <Font ss:FontName=\"ЛОМе\" x:CharSet=\"134\" ss:Size=\"12\" ss:Bold=\"1\"/>\r\n ");
            OutFileContent.Append( "    <Interior ss:Color=\"#BFBFBF\" ss:Pattern=\"Solid\"/>\r\n ");
            OutFileContent.Append( "   </Style>\r\n ");
            OutFileContent.Append( "  </Styles>\r\n ");
            OutFileContent.Append( "  <Worksheet ss:Name=\"Sheet1\">\r\n ");
            OutFileContent.Append( "   <Table ss:ExpandedColumnCount=\"255\" x:FullColumns=\"1\" \r\n ");
            OutFileContent.Append( " x:FullRows=\"1\" ss:StyleID=\"s62\" ss:DefaultColumnWidth=\"75\" ss:DefaultRowHeight=\"20.25\">\r\n ");
            OutFileContent.Append( " <Column ss:StyleID=\"s62\" ss:AutoFitWidth=\"0\" ss:Width=\"112.5\"/>\r\n ");
             return OutFileContent;
        }

 

#region 写Excel尾
///   <summary>
         ///  РґexcelОІ
         ///   </summary>
         ///   <param name="OutFileContent"></param>
         ///   <returns></returns>
         private StringBuilder AddEndFile(StringBuilder OutFileContent)
        {
            OutFileContent.Append( " </Table>\r\n ");
            OutFileContent.Append( " <WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">\r\n ");
            OutFileContent.Append( " <Unsynced/>\r\n ");
            OutFileContent.Append( " <Print>\r\n ");
            OutFileContent.Append( "     <ValidPrinterInfo/>\r\n ");
            OutFileContent.Append( "     <PaperSizeIndex>9</PaperSizeIndex>\r\n ");
            OutFileContent.Append( "     <HorizontalResolution>600</HorizontalResolution>\r\n ");
            OutFileContent.Append( "     <VerticalResolution>0</VerticalResolution>\r\n ");
            OutFileContent.Append( " </Print>\r\n ");
            OutFileContent.Append( " <Selected/>\r\n ");
            OutFileContent.Append( " <Panes>\r\n ");
            OutFileContent.Append( "     <Pane>\r\n ");
            OutFileContent.Append( "     <Number>3</Number>\r\n ");
            OutFileContent.Append( "     <RangeSelection>R1:R65536</RangeSelection>\r\n ");
            OutFileContent.Append( "     </Pane>\r\n ");
            OutFileContent.Append( " </Panes>\r\n ");
            OutFileContent.Append( " <ProtectObjects>False</ProtectObjects>\r\n ");
            OutFileContent.Append( " <ProtectScenarios>False</ProtectScenarios>\r\n ");
            OutFileContent.Append( " </WorksheetOptions>\r\n ");
            OutFileContent.Append( " </Worksheet>\r\n ");
            OutFileContent.Append( " <Worksheet ss:Name=\"Sheet2\">\r\n ");
            OutFileContent.Append( " <Table ss:ExpandedColumnCount=\"1\" ss:ExpandedRowCount=\"1\" x:FullColumns=\"1\"\r\n ");
            OutFileContent.Append( " x:FullRows=\"1\" ss:DefaultColumnWidth=\"54\" ss:DefaultRowHeight=\"14.25\">\r\n ");
            OutFileContent.Append( " <Row ss:AutoFitHeight=\"0\"/>\r\n ");
            OutFileContent.Append( " </Table>\r\n ");
            OutFileContent.Append( " <WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">\r\n ");
            OutFileContent.Append( " <Unsynced/>\r\n ");
            OutFileContent.Append( " <ProtectObjects>False</ProtectObjects>\r\n ");
            OutFileContent.Append( " <ProtectScenarios>False</ProtectScenarios>\r\n ");
            OutFileContent.Append( " </WorksheetOptions>\r\n ");
            OutFileContent.Append( " </Worksheet>\r\n ");
            OutFileContent.Append( " <Worksheet ss:Name=\"Sheet3\">\r\n ");
            OutFileContent.Append( " <Table ss:ExpandedColumnCount=\"1\" ss:ExpandedRowCount=\"1\" x:FullColumns=\"1\"\r\n ");
            OutFileContent.Append( " x:FullRows=\"1\" ss:DefaultColumnWidth=\"54\" ss:DefaultRowHeight=\"14.25\">\r\n ");
            OutFileContent.Append( " <Row ss:AutoFitHeight=\"0\"/>\r\n ");
            OutFileContent.Append( " </Table>\r\n ");
            OutFileContent.Append( " <WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">\r\n ");
            OutFileContent.Append( " <Unsynced/>\r\n ");
            OutFileContent.Append( " <ProtectObjects>False</ProtectObjects>\r\n ");
            OutFileContent.Append( " <ProtectScenarios>False</ProtectScenarios>\r\n ");
            OutFileContent.Append( " </WorksheetOptions>\r\n ");
            OutFileContent.Append( " </Worksheet>\r\n ");
            OutFileContent.Append( " </Workbook>\r\n ");
             return OutFileContent;
        }

 

关键的部分来了，可以从DataGrid中遍历，也可以直接从数据源取数据进行遍历。

 

#region 写数据内容
private StringBuilder AddContentFile(StringBuilder OutFileContent,DataSet ds)
{

     // 写列头
            OutFileContent.Append( " <Row ss:AutoFitHeight=\"0\"> ");
            OutFileContent.Append( " <Cell><Data ss:Type=\"String\">姓名</Data></Cell> ");
            OutFileContent.Append( " <Cell><Data ss:Type=\"String\">数学</Data></Cell> ");
            OutFileContent.Append( " <Cell><Data ss:Type=\"String\">语文</Data></Cell> ");
            OutFileContent.Append( " <Cell><Data ss:Type=\"String\">英语</Data></Cell> ");
            OutFileContent.Append( " </Row> ");
             // 写内容
             foreach(DataRow row  in ds.Tables[ 0].Rows)
            {
                OutFileContent.Append( " <Row ss:AutoFitHeight=\"0\"> ");
                OutFileContent.Append( " <Cell><Data ss:Type=\"String\"> "+row[ " GraduateBatchName "].ToString()+ " </Data></Cell> "); 
                OutFileContent.Append( " <Cell><Data  ss:Type=\"String\"> "+row[ " studyRecruitBatchName "].ToString()+ " </Data></Cell> "); 
                OutFileContent.Append( " <Cell><Data ss:Type=\"String\"> "+row[ " EnterRecruitBatchName "].ToString()+ " </Data></Cell> ");  
                OutFileContent.Append( " <Cell><Data ss:Type=\"String\"> "+row[ " StudyLevelName "].ToString()+ " </Data></Cell> "); 
                OutFileContent.Append( " </Row> ");
            }

 

上面的列头可以添加成复合列头，做一下处理即可，如下图所示：

 

最后，导出：

 

#region 使用
     private  void btnExport_Click( object sender, System.EventArgs e)
        {
    StringBuilder OutFileContent =  new StringBuilder(); // 容器
             // 写头文件
            OutFileContent = AddHeadFile(OutFileContent);
// 写内容
StringBuilder sbMsg=  new StringBuilder(); // 容器
    OutFileContent.Append( this. AddContentFile(sbMsg,ds));
     // 写尾文件
            OutFileContent= AddEndFile(OutFileContent);
             // 保存到xls
             string strRandomFileName =  " GraduateApplyQuery ";
             string strPath = Server.MapPath(Context.Request.ApplicationPath) +  " \\ExcelReport\\Report\\ ";
             string strExcelFile = strPath + strRandomFileName +  " .xls ";
            FileStream OutFile =  new FileStream(strExcelFile,FileMode.Create,FileAccess.Write);
             byte[] btArray =  new  byte[OutFileContent.Length];
            btArray = Encoding.UTF8.GetBytes(OutFileContent.ToString());
            OutFile.Write(btArray, 0,btArray.Length);
            OutFile.Flush();
            OutFile.Close();

            WriteThreadLog( " 写文件 => 3 ");
            Response.Redirect(UrlBase+ " /ExcelReport/Report/ "+strRandomFileName+ " .xls ");    
}

 

好了，到此就可以导出。说白了就是把从Excel另存为XML格式的模板加到程序中来，如果避免这么做，可以将头尾XML部分做成单独的文件，程序中将这头尾XML文件读取到串中导出也可。

 

其它的导出可以参考网上的两篇总结，都挺好，贴出网址：

http://www.cnblogs.com/yinhe/archive/2011/11/17/2252990.html

http://blog.csdn.net/zhaoyu_1979/article/details/6294454 zhaoyu_1979

http://www.cnblogs.com/sufei/archive/2009/05/23/1487540.html Perky Su

 

转载于:https://www.cnblogs.com/yangxiaohu1/archive/2012/06/13/aspnet-export-excel-word.html