ASP.NET 如何导出excel(二)
在上一篇 ASP.NET 如何导出excel(一)中介绍了导出html格式的excel文件的方法。本篇介绍另一种方法:导出xml格式的excel文件。该方法比上一种方法更具有通用性,功能也更强大,他可以导出多个工作表,并且支持各种样式以及常用的公式,不过也有缺点,就是不能包含图片以及图表。
一)确定要导出的excel文件的格式
我们可以先制作一个excel文件做为模板,他包含了要导出的格式,如:
然后选择 “文件”/ “另存为” 保存为xml表格,用记事本打开保存的xml文件,就可以看到他的内容了:
<?xml version="1.0"?>
<?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">
<Created>1996-12-17T01:32:42Z</Created>
<LastSaved>2009-01-19T15:35:16Z</LastSaved>
<Version>11.5606</Version>
</DocumentProperties>
<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
<RemovePersonalInformation/>
</OfficeDocumentSettings>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>4530</WindowHeight>
<WindowWidth>8505</WindowWidth>
<WindowTopX>480</WindowTopX>
<WindowTopY>120</WindowTopY>
<AcceptLabelsInFormulas/>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font ss:FontName="宋体" x:CharSet="134" ss:Size="12"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s23">
<NumberFormat ss:Format="yyyy/mm/dd"/>
</Style>
<Style ss:ID="s26">
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="宋体" x:CharSet="134" ss:Size="12" ss:Bold="1"/>
<Interior ss:Color="#99CCFF" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s27">
<NumberFormat/>
</Style>
</Styles>
<Worksheet ss:Name="Sheet1">
<Table ss:ExpandedColumnCount="4" ss:ExpandedRowCount="3" x:FullColumns="1"
x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25">
<Column ss:AutoFitWidth="0" ss:Width="55.5"/>
<Column ss:AutoFitWidth="0" ss:Width="87"/>
<Column ss:AutoFitWidth="0" ss:Width="61.5"/>
<Column ss:AutoFitWidth="0" ss:Width="87.75"/>
<Row ss:AutoFitHeight="0" ss:Height="19.5">
<Cell ss:StyleID="s26"><Data ss:Type="String">姓名</Data></Cell>
<Cell ss:StyleID="s26"><Data ss:Type="String">出生日期</Data></Cell>
<Cell ss:StyleID="s26"><Data ss:Type="String">年龄</Data></Cell>
<Cell ss:StyleID="s26"><Data ss:Type="String">薪水</Data></Cell>
</Row>
<Row ss:AutoFitHeight="0" ss:Height="19.5">
<Cell><Data ss:Type="String">刘武</Data></Cell>
<Cell ss:StyleID="s23"><Data ss:Type="DateTime">1983-01-01T00:00:00.000</Data></Cell>
<Cell ss:StyleID="s27"><Data ss:Type="Number">26</Data></Cell>
<Cell><Data ss:Type="Number">5000</Data></Cell>
</Row>
<Row ss:AutoFitHeight="0" ss:Height="19.5">
<Cell><Data ss:Type="String">刘文</Data></Cell>
<Cell ss:StyleID="s23"><Data ss:Type="DateTime">1978-04-08T00:00:00.000</Data></Cell>
<Cell ss:StyleID="s27"><Data ss:Type="Number">31</Data></Cell>
<Cell><Data ss:Type="Number">8000</Data></Cell>
</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<Selected/>
<Panes>
<Pane>
<Number>3</Number>
<ActiveRow>7</ActiveRow>
<ActiveCol>2</ActiveCol>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet2">
<Table ss:ExpandedColumnCount="0" ss:ExpandedRowCount="0" x:FullColumns="1"
x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25"/>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet3">
<Table ss:ExpandedColumnCount="0" ss:ExpandedRowCount="0" x:FullColumns="1"
x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25"/>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>
这是一个标准的xml文件,可以用excel打开,同时也支持open office。
二 根据模板的内容编写输出流
Response.AddHeader("Content-Disposition", "attachment;filename=" +
HttpUtility.UrlEncode(DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls",
System.Text.Encoding.UTF8));
Response.ContentEncoding = System.Text.Encoding.Default;
Response.ContentType = "application/ms-excel";
System.IO.StringWriter sw = new System.IO.StringWriter();
sw.WriteLine("<?xml version=\"1.0\"?>");
sw.WriteLine("<?mso-application progid=\"Excel.Sheet\"?>");
sw.WriteLine("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"");
sw.WriteLine(" xmlns:o=\"urn:schemas-microsoft-com:office:office\"");
sw.WriteLine(" xmlns:x=\"urn:schemas-microsoft-com:office:excel\"");
sw.WriteLine(" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"");
sw.WriteLine(" xmlns:html=\"http://www.w3.org/TR/REC-html40\">");
sw.WriteLine("<Worksheet ss:Name=\"Sheet1\">");
// ........
sw.WriteLine("</Worksheet>");
sw.WriteLine("</Workbook>");
Response.Write(sw);
Response.End();