目的
网页上的数据导出有很多种导出方法，其中有的是真正格式的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文件即可。
代码：
写Excel头 #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; }
写Excel尾 #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> "); }
上面的列头可以添加成复合列头，做一下处理即可，如下图所示：
最后，导出：
导出XML格式的Excel #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文件读取到串中导出也可。
其它的导出可以参考网上的两篇总结，都挺好，贴出网址：
zhaoyu_1979
Perky Su