发表于2014-05-28 23:19:22
将数据导出成Excel文件是相当常用的一个功能，我们经常在开发中用到。不过我们通常会借助.net的Office组件进行开发，在这里我教大家一种依赖任何外部环境的导出方法。
原理很简单﹐excel 2003及以上版本支持一种xml电子表格的格式﹐也只是说我们只要输出一种指定格式的XML，即可被excel视别。文本非常简单，以DataSet为例，全文代码如下：
protected void Page_Load(object sender, EventArgs e) { Response.Clear(); Response.ContentType = "application/vnd.ms-excel"; Response.AddHeader("content-disposition", "attachment; filename=123456.xls"); WriteExcelHead(Response.Output); WriteDataSet(Response.Output, new DAL.SqlHelper().ExecuteTable("select * from zkxy_Category").DataSet); WriteExcelFoot(Response.Output); } private void WriteExcelHead(System.IO.TextWriter writer) { writer.WriteLine("<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>"); writer.WriteLine("<?mso-application progid=\"Excel.Sheet\"?>"); //Excel工作薄开始 writer.WriteLine("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\""); writer.WriteLine(" xmlns:o=\"urn:schemas-microsoft-com:office:office\""); writer.WriteLine(" xmlns:x=\"urn:schemas-microsoft-com:office:excel\""); writer.WriteLine(" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\""); writer.WriteLine(" xmlns:html=\"http://www.w3.org/TR/REC-html40//\">"); writer.WriteLine(" <DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">"); writer.WriteLine(" <Author>www.jiniannet.com</Author>"); writer.WriteLine(string.Format(" <Created>{0}T{1}Z</Created>", DateTime.Now.ToString("yyyy-mm-dd"), DateTime.Now.ToString("HH:MM:SS"))); writer.WriteLine(" <Company>www.jiniannet.com</Company>"); writer.WriteLine(" <Version>11.6408</Version>"); writer.WriteLine(" </DocumentProperties>"); writer.WriteLine(" <ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">"); writer.WriteLine(" <WindowHeight>8955</WindowHeight>"); writer.WriteLine(" <WindowWidth>11355</WindowWidth>"); writer.WriteLine(" <WindowTopX>480</WindowTopX>"); writer.WriteLine(" <WindowTopY>15</WindowTopY>"); writer.WriteLine(" <ProtectStructure>False</ProtectStructure>"); writer.WriteLine(" <ProtectWindows>False</ProtectWindows>"); writer.WriteLine(" </ExcelWorkbook>"); //Excel工作薄结束 //工作薄样式 writer.WriteLine("<Styles>"); writer.WriteLine("<Style ss:ID=\"Default\" ss:Name=\"Normal\">"); writer.WriteLine(" <Alignment/>"); writer.WriteLine(" <Borders/>"); writer.WriteLine(" <Font ss:FontName=\"宋体\" x:CharSet=\"134\" ss:Size=\"12\"/>"); writer.WriteLine(" <Interior/>"); writer.WriteLine(" <Protection/>"); writer.WriteLine("</Style>"); //文本样式 writer.WriteLine("<Style ss:ID=\"StringLiteral\">"); writer.WriteLine(" <NumberFormat ss:Format=\"@\"/>"); writer.WriteLine("</Style>"); //浮点型样式 writer.WriteLine("<Style ss:ID=\"Decimal\">"); writer.WriteLine(" <NumberFormat ss:Format=\"0.00\"/>"); writer.WriteLine("</Style>"); //整型样式 writer.WriteLine("<Style ss:ID=\"Integer\">"); writer.WriteLine(" <NumberFormat ss:Format=\"0\"/>"); writer.WriteLine("</Style>"); //日期样式 writer.WriteLine("<Style ss:ID=\"DateLiteral\">"); writer.WriteLine(" <NumberFormat ss:Format=\"mm/dd/yyyy;@\"/>"); writer.WriteLine("</Style>"); writer.WriteLine(" </Styles>"); } private void WriteExcelFoot(System.IO.TextWriter writer) { writer.WriteLine("</Workbook>"); } private void WriteDataSet(System.IO.TextWriter writer, DataSet ds) { for (int i = 0; i < ds.Tables.Count; i++) { int rows = ds.Tables[i].Rows.Count + 1; int cols = ds.Tables[i].Columns.Count; //第i个工作表 writer.WriteLine(string.Format("<Worksheet ss:Name=\"{0}\">", ds.Tables[i].TableName)); writer.WriteLine(string.Format(" <Table ss:ExpandedColumnCount=\"{0}\" ss:ExpandedRowCount=\"{1}\" x:FullColumns=\"1\"", cols.ToString(), rows.ToString())); writer.WriteLine(" x:FullRows=\"1\" ss:DefaultColumnWidth=\"54\" ss:DefaultRowHeight=\"14.25\">"); //<Table ss:ExpandedColumnCount="3" ss:ExpandedRowCount="4" x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25"> //ExpandedColumnCount：代表Excel文档中的列数 //ExpandedRowCount：代表Excel文档中的行数 //指定每一列的宽度 for (int c = 0; c < ds.Tables[i].Columns.Count; c++) { writer.WriteLine(string.Format("<Column ss:Index=\"{0}\" ss:AutoFitWidth=\"{1}\" ss:Width=\"{2}\"/> ", c + 1, 1, 80)); } //生成标题 //writer.WriteLine(string.Format("<Row ss:AutoFitHeight=\"{0}\" ss:Height=\"{1}\">", 0, 28.5)); writer.WriteLine("<Row>"); foreach (DataColumn eachCloumn in ds.Tables[i].Columns) { writer.Write("<Cell ss:StyleID=\"Default\"><Data ss:Type=\"String\">"); writer.Write(eachCloumn.ColumnName.ToString()); writer.WriteLine("</Data></Cell>"); } writer.WriteLine("</Row>"); //生成数据记录 foreach (DataRow eachRow in ds.Tables[i].Rows) { //writer.WriteLine("<Row ss:AutoFitHeight=\"0\">"); writer.WriteLine("<Row>"); for (int currentRow = 0; currentRow != cols; currentRow++) { object[] getValue = ExcelContent(eachRow[currentRow]); writer.Write(string.Format("<Cell ss:StyleID=\"{0}\"><Data ss:Type=\"{1}\">", getValue[0], getValue[1])); writer.Write(getValue[2]); writer.WriteLine("</Data></Cell>"); } writer.WriteLine("</Row>"); } writer.WriteLine("</Table>"); writer.WriteLine("<WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">"); writer.WriteLine("<Selected/>"); writer.WriteLine("<Panes>"); writer.WriteLine("<Pane>"); writer.WriteLine(" <Number>3</Number>"); writer.WriteLine(" <ActiveRow>1</ActiveRow>"); writer.WriteLine("</Pane>"); writer.WriteLine("</Panes>"); writer.WriteLine("<ProtectObjects>False</ProtectObjects>"); writer.WriteLine("<ProtectScenarios>False</ProtectScenarios>"); writer.WriteLine("</WorksheetOptions>"); writer.WriteLine("</Worksheet>"); } } private static object[] ExcelContent(object Value) { object[] strValue = new object[3]; System.Type rowType = Value.GetType(); switch (rowType.ToString()) { case "System.String": case "System.Guid": string XMLstring = Value.ToString(); XMLstring = XMLstring.Trim(); XMLstring = XMLstring.Replace("&", "&"); XMLstring = XMLstring.Replace(">", ">"); XMLstring = XMLstring.Replace("<", "<"); strValue[0] = "StringLiteral"; strValue[1] = "String"; strValue[2] = XMLstring; break; case "System.DateTime": DateTime XMLDate = (DateTime)Value; string XMLDatetoString = ""; //Excel Converted Date //把日期时间转化为：“yyyy-MM-ddTHH:mm:ss”这种Excel中的格式 XMLDatetoString = XMLDate.ToString(System.Globalization.DateTimeFormatInfo.CurrentInfo.SortableDateTimePattern); strValue[0] = "DateLiteral"; strValue[1] = "DateTime"; if (XMLDate < Convert.ToDateTime("1900-1-1")) { strValue[0] = "StringLiteral"; strValue[1] = "String"; XMLDatetoString = string.Empty; } strValue[2] = XMLDatetoString; break; case "System.Boolean": strValue[0] = "StringLiteral"; strValue[1] = "String"; strValue[2] = Value.ToString(); break; case "System.Int16": case "System.Int32": case "System.Int64": case "System.Byte": strValue[0] = "Integer"; strValue[1] = "Number"; strValue[2] = Value.ToString(); break; case "System.Byte[]": strValue[0] = "StringLiteral"; strValue[1] = "String"; strValue[2] = (byte[])Value; break; case "System.Decimal": case "System.Double": strValue[0] = "Decimal"; strValue[1] = "Number"; strValue[2] = Value.ToString(); break; case "System.DBNull": strValue[0] = "StringLiteral"; strValue[1] = "String"; strValue[2] = ""; break; default: throw (new Exception(rowType.ToString() + " not handled.")); } return strValue; }
过段时间我把代理整理一下，做成通用的类库。希望大家喜欢！