** 最简单的XML格式Excel表格文件当然,还有几个地方是可以删除掉的内容,但是这样就有些破坏完整性了。这个文档的作用就是从XML数据源中导出数据之后,使用XSLT转换也可以把数据导出。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
| <?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"> <Title>Excel表格</Title> <LastAuthor>bigtall</LastAuthor> </DocumentProperties> <Styles> <Style ss:ID="Default" ss:Name="Normal"> <Alignment ss:Vertical="Center"/> <Font ss:FontName="宋体" x:CharSet="134" ss:Size="12"/> </Style> </Styles> <Worksheet ss:Name="tt"> <Table> <Row> <Cell ss:MergeAcross="6" > <Data ss:Type="String">Hello!World!</Data> </Cell> </Row> </Table> </Worksheet> </Workbook>
|
** 其实还可以精简到这样:
1 2 3 4 5 6 7 8 9 10 11
| <?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"> <Worksheet ss:Name="tt"> <Table> <Row> <Cell><Data ss:Type="String">Hello!World!</Data></Cell> </Row> </Table> </Worksheet> </Workbook>
|
** 导出excel方法:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93
|
public static bool StreamExport(DataTable dt, ArrayList columns, string fileName, System.Web.UI.Page pages) { if (dt.Rows.Count > 65535) { throw new Exception("预导出的数据总行数大于excel的行数"); } if (string.IsNullOrEmpty(fileName)) return false; StringBuilder content = new StringBuilder(); StringBuilder strtitle = new StringBuilder(); content.Append("<html 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='http://www.w3.org/TR/REC-html40'>"); content.Append("<head><title></title><meta http-equiv='Content-Type' content=\"text/html; charset=gb2312\">"); content.Append("<!--[if gte mso 9]>"); content.Append("<xml>"); content.Append(" <x:ExcelWorkbook>"); content.Append(" <x:ExcelWorksheets>"); content.Append(" <x:ExcelWorksheet>"); content.Append(" <x:Name>Sheet1</x:Name>"); content.Append(" <x:WorksheetOptions>"); content.Append(" <x:Print>"); content.Append(" <x:ValidPrinterInfo />"); content.Append(" </x:Print>"); content.Append(" </x:WorksheetOptions>"); content.Append(" </x:ExcelWorksheet>"); content.Append(" </x:ExcelWorksheets>"); content.Append("</x:ExcelWorkbook>"); content.Append("</xml>"); content.Append("<![endif]-->"); content.Append("</head><body><table style='border-collapse:collapse;table-layout:fixed;'><tr>"); if (columns != null) { for (int i = 0; i < columns.Count; i++) { if (columns[i] != null && columns[i] != "") { content.Append("<td><b>" + columns[i] + "</b></td>"); } else { content.Append("<td><b>" + dt.Columns[i].ColumnName + "</b></td>"); } } } else { for (int j = 0; j < dt.Columns.Count; j++) { content.Append("<td><b>" + dt.Columns[j].ColumnName + "</b></td>"); } } content.Append("</tr>\n"); for (int j = 0; j < dt.Rows.Count; j++) { content.Append("<tr>"); for (int k = 0; k < dt.Columns.Count; k++) { object obj = dt.Rows[j][k]; Type type = obj.GetType(); if (type.Name == "Int32" || type.Name == "Single" || type.Name == "Double" || type.Name == "Decimal") { double d = obj == DBNull.Value ? 0.0d : Convert.ToDouble(obj); if (type.Name == "Int32" || (d - Math.Truncate(d) == 0)) content.AppendFormat("<td style='vnd.ms-excel.numberformat:#,##0'>{0}</td>", obj); else content.AppendFormat("<td style='vnd.ms-excel.numberformat:#,##0.00'>{0}</td>", obj); } else content.AppendFormat("<td style='vnd.ms-excel.numberformat:@'>{0}</td>", obj); } content.Append("</tr>\n"); } content.Append("</table></body></html>"); content.Replace(" ", ""); pages.Response.Clear(); pages.Response.Buffer = true; pages.Response.ContentType = "application/ms-excel"; pages.Response.Charset = "UTF-8"; pages.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); fileName = System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8); pages.Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName); pages.Response.Write(content.ToString()); HttpContext.Current.ApplicationInstance.CompleteRequest(); return true; }
|