Asp.net无须任何插件导出Excel
作者：翅膀的初衷 来源：本站原创 发布时间：2014-05-28 查看数：62668
将数据导出成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;
}
过段时间我把代理整理一下，做成通用的类库。希望大家喜欢！\
湘公网安备 43010302000398号