正文

也谈Excel导出

IP云
IP云V管理员/11 阅读
06/19
提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

吐槽

Excel导出在天朝的软件大环境下，差点成为软件开发必备。俺就遇到过，所有报表不提供导出功能，就能不验收的囧事。报表能查看能打印能形成图表已经完美，实在搞不懂导出excel有个毛用，但是公司依靠客户盈利俺们码农依靠公司发工资过活，哎！不得不吐槽下。
博客园有篇文章提到Excel导出10W条数据几秒钟内搞定，看到后很诧异，Excel最大支持6W多条数据，强行插入这么长，人家Excel小姑娘如何受得了？你整这么多数据你爽了，但是客户导出后打不开，碰到小白客户可能还得重启电脑，然后你就爽不下去了。俺后来试了下导出5W多条数据（20列）后打开Excel慢的要死，而且我的wps2012个人版的神器竟然直接Game Over 了。
好了不扯淡了，下面整理下excel导出的三种方法，来源自网络，后又加上俺的整理,后两种已经在项目中实际运用。

方法一:

用Microsoft.Office.Interop.Excel 组建方式导出，直接遍历excel每个单元格插入。
吐槽下此方法慢如蜗牛，不喜欢的跳过。

程序准备

1、在项目中添加Microsoft.Office.Interop.Excel的引用,制作一个需要导出的excel样式模版放到项目某个文件夹下，
2、用此方法导出需要添加Microsoft.Office.Interop.Excel的引用，且服务器上需要安装excel，建议服务器上安装Excel版本低一些，2003就不错。
3、服务器权限设定，出现类似检索 COM 类工厂中 CLSID 为 {00024500-0000-0000-C000-000000000046}的组件时失败 的错误多半是服务器上的Excel权限没设好。打开控制面板=>管理工具=>组建服务=>组建服务=>计算机=>我的电脑=>DCOM配置=>Microsoft Excel。右击属性标志选择交互式用户 ，安全标签中的启动与激活权限选择自定义然后点击编辑按钮，点添加ASP.NET。

具体程序如下

生成的方法

public void CreateExcel(System.Data.DataTable dt, string creatName, string MoName)
{
// 输入文件名
string inputFilePath = System.Web.HttpContext.Current.Server.MapPath(MoName);
string outFilePath = System.Web.HttpContext.Current.Server.MapPath(creatName);
//如果文件不存在，则将模板文件拷贝一份作为输出文件
if (!File.Exists(outFilePath))
{
File.Copy(inputFilePath, outFilePath, true);
}
GC.Collect();
ApplicationClass myApp = new ApplicationClass();
Workbook myBook = null;
Worksheet mySheet = null;
myApp.Visible = false;
object oMissiong = System.Reflection.Missing.Value;
myApp.Workbooks.Open(outFilePath, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong
, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong);
myBook = myApp.Workbooks[];
mySheet = (Worksheet)myBook.ActiveSheet;
DataTableToExcel(dt, mySheet);
myBook.Save();
myBook.Close(true, outFilePath, true);
System.Runtime.InteropServices.Marshal.ReleaseComObject(mySheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(myBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(myApp);
GC.Collect();
}

public void DataTableToExcel(System.Data.DataTable dt, Worksheet excelSheet)
{
int rowCount = dt.Rows.Count;
int colCount = dt.Columns.Count;
for (int i = ; i < rowCount; i++)
{
for (int j = ; j < colCount; j++)
{
mySheet.Cells[i + , j] = dt.Rows[i - ][j - ].ToString();
}
}
}

调用的方法

CreateExcel ce = new CreateExcel();
string MoName = "UserFiles/DataIn.xls";//模板的路径
string creatName = "UserFiles\\" + "数据导出.xls" ;//生成excel的路径
ce.Create(tbl, creatName, MoName);
Response.Redirect(creatName);

方法二:

用Microsoft.Office.Interop.Excel 组建方式导出，数据依照二维数组方式存放，和第一种方法虽然只有几行代码区别，但是速度快了几十倍，10W条数据15秒钟左右（小白pc机）具体的服务器配置如方法一
程序如下
生成方法

public void Create(System.Data.DataTable dt, string creatName, string MoName)
{
// 输入文件名
string inputFilePath = System.Web.HttpContext.Current.Server.MapPath(MoName);
string outFilePath = System.Web.HttpContext.Current.Server.MapPath(creatName);
//如果文件不存在，则将模板文件拷贝一份作为输出文件
if (!File.Exists(outFilePath))
{
File.Copy(inputFilePath, outFilePath, true);
}
GC.Collect();
ApplicationClass myApp = new ApplicationClass();
Workbook myBook = null;
Worksheet mySheet = null;
myApp.Visible = false;
object oMissiong = System.Reflection.Missing.Value;
myApp.Workbooks.Open(outFilePath, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong
, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong);
myBook = myApp.Workbooks[];
mySheet = (Worksheet)myBook.ActiveSheet;
DataTableToExcel(dt, mySheet);
myBook.Save();
myBook.Close(true, outFilePath, true);
System.Runtime.InteropServices.Marshal.ReleaseComObject(mySheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(myBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(myApp);
GC.Collect();
} public void DataTableToExcel(System.Data.DataTable dt, Worksheet excelSheet)
{
int rowCount = dt.Rows.Count;
int colCount = dt.Columns.Count;
object[,] dataArray = new object[rowCount + , colCount];
for (int i = ; i < rowCount; i++)
{
for (int j = ; j < colCount; j++)
{
dataArray[i, j] = dt.Rows[i][j];
}
}
//从A2开始导入写入数据即可以避开表头被重写
excelSheet.get_Range("A2", excelSheet.Cells[rowCount + , colCount]).Value2 = dataArray;
}

调用方法

CreateExcel ce = new CreateExcel();
string MoName = "UserFiles/DataIn.xls";//模板的路径
string creatName = "UserFiles\\" + "数据导出.xls" ;//生成excel的路径
ce.Create(tbl, creatName, MoName);
Response.Redirect(creatName);

方法三:

此方法不需要在服务器上安装Excel，采用生成xml以excel方式输出到客户端，可能需要客户机安装excel（心虚没试过）所以也不会有乱七八糟的权限设定，和莫名其妙的版本问题。而且此种方法比第二种更快试了下10W条数据（20列）不到10秒（小白pc）。

方法如下

生成方法：

public static string CreateExcel(DataTable dt, List<string> columnNames)
{
StringBuilder strb = new StringBuilder();
strb.Append(" <html xmlns:o=\"urn:schemas-microsoft-com:office:office\"");
strb.Append("xmlns:x=\"urn:schemas-microsoft-com:office:excel\"");
strb.Append("xmlns=\"http://www.w3.org/TR/REC-html40\"");
strb.Append(" <head> <meta http-equiv='Content-Type' content='text/html; charset=gb2312'>");
strb.Append(" <style>");
strb.Append(".xl26");
strb.Append(" {mso-style-parent:style0;");
strb.Append(" font-family:\"Times New Roman\", serif;");
strb.Append(" mso-font-charset:0;");
strb.Append(" mso-number-format:\"@\";}");
strb.Append(" </style>");
strb.Append(" <xml>");
strb.Append(" <x:ExcelWorkbook>");
strb.Append(" <x:ExcelWorksheets>");
strb.Append(" <x:ExcelWorksheet>");
strb.Append(" <x:Name>Sheet1 </x:Name>");
strb.Append(" <x:WorksheetOptions>");
strb.Append(" <x:DefaultRowHeight>285 </x:DefaultRowHeight>");
strb.Append(" <x:Selected/>");
strb.Append(" <x:Panes>");
strb.Append(" <x:Pane>");
strb.Append(" <x:Number>3 </x:Number>");
strb.Append(" <x:ActiveCol>1 </x:ActiveCol>");
strb.Append(" </x:Pane>");
strb.Append(" </x:Panes>");
////设置工作表只读属性
//strb.Append(" <x:ProtectContents>False </x:ProtectContents>");
//strb.Append(" <x:ProtectObjects>False </x:ProtectObjects>");
//strb.Append(" <x:ProtectScenarios>False </x:ProtectScenarios>");
strb.Append(" </x:WorksheetOptions>");
strb.Append(" </x:ExcelWorksheet>");
strb.Append(" <x:WindowHeight>6750 </x:WindowHeight>");
strb.Append(" <x:WindowWidth>10620 </x:WindowWidth>");
strb.Append(" <x:WindowTopX>480 </x:WindowTopX>");
strb.Append(" <x:WindowTopY>75 </x:WindowTopY>");
strb.Append(" <x:ProtectStructure>False </x:ProtectStructure>");
strb.Append(" <x:ProtectWindows>False </x:ProtectWindows>");
strb.Append(" </x:ExcelWorkbook>");
strb.Append(" </xml>");
strb.Append("");
strb.Append(" </head> <body> <table align=\"center\" style='border-collapse:collapse;table-layout:fixed'> <tr>");
//if (ds.Tables.Count > 0)
//{
//写列标题
int columncount = columnNames.Count;
for (int columi = ; columi < columncount; columi++)
{
strb.Append(" <td> <b>" + columnNames[columi] + " </b> </td>");
}
strb.Append(" </tr>");
//写数据
for (int i = ; i < dt.Rows.Count; i++)
{
strb.Append(" <tr>");
for (int j = ; j < dt.Columns.Count; j++)
{
strb.Append(" <td class='xl26'>" + dt.Rows[i][j].ToString() + " </td>");
}
strb.Append(" </tr>");
}
//}
strb.Append(" </body> </html>");
return strb.ToString(); }

调用方法：

protected void Button1_Click(object sender, EventArgs e)
{
string strSql = “select * from table1";
System.Data.DataTable tbl = SqlHelper.GetDateTable(strSql);
List<string> names = new List<string>() {
"序号","固定资产管理码","品名","资产大类","资产小类","品牌","规格型号","编码1","编码2","使用部门","责任人","使用人","放置地点","价格","购置日期","供应商","保修截至日期","预计使用月份","备注","其他"
};
string str = ExcelHelper.CreateExcel(tbl, names);
Response.Clear();
Response.Buffer = true;
Response.Charset = "GB2312";
Response.AppendHeader("Content-Disposition", "attachment;filename=1213.xls");
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文
Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
//this.EnableViewState = false;
Response.Write(str);
Response.End();
}