Excel报表开发（本节主要讲述导出到Excel操作） 三、C#操作Excel常见问题
一、Excel导入到GridView以及数据库操作比较简单，这儿不做过多讲解，需要注意的有二点：
1、设置IMEX=1将强制混合数据转换为文本。
2、解决Excel驱动程序默认读取8行：将”HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel”目录下的TypeGuessRows属性值修改为0。
二、导出到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[1]; 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 = 0; i < rowCount; i++) { for (int j = 0; j < colCount; j++) { mySheet.Cells[i + 1, j] = dt.Rows[i - 1][j - 1].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[1]; 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 + 1, colCount]; for (int i = 0; i < rowCount; i++) { for (int j = 0; j < colCount; j++) { dataArray[i, j] = dt.Rows[i][j]; } } //从A2开始导入写入数据即可以避开表头被重写 excelSheet.get_Range("A2", excelSheet.Cells[rowCount + 1, 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 = 0; columi < columncount; columi++) { strb.Append(" <td> <b>" + columnNames[columi] + " </b> </td>"); } strb.Append(" </tr>"); //写数据 for (int i = 0; i < dt.Rows.Count; i++) { strb.Append(" <tr>"); for (int j = 0; 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(); }
经常会有项目需要把表格导出为 Excel 文件，或者是导入一份 Excel 来操作，那么如何在 C# 中操作 Excel 文件成了一个最基本的问题。
做开发这几年来，陆陆续续也接触过这样的需求，但因为不频繁，所以经常是遇到问题再去网上搜。最近的一个项目，要导出的这个 Excel 涉及了很多比较偏僻的操作，所以决定在这里开一篇文章，专门用来收集和整理使用到的代码，以及一些技巧。如果各位看官有一些自己的心得，或者有更好的方案，也欢迎交流。我会时不时更新一下。
0. 使用之前
在写代码之前，我们需要先添加引用，在 程序集 – 扩展 里面：Microsoft.Office.Interop.Excel。
还有要注意的是，引用之后，需要将属性中的「嵌入互操作类型」设置为 Flase，不然编译时可能会出错。
然后就是记得 using 啦：
using Microsoft.Office.Interop.Excel;
using System.Reflection;
▲ 这里的第二个 using 是因为在 Excel 操作中会经常用到一个 Missing.Value 的默认值，所以需要先引用 System.Reflection。
1. 开始使用
一般在使用中，我们只是操作一份 Excel 中的第一个工作表（sheet），下面来最简单的创建和读取一份 Excel 中的第一个 sheet。
// 定义一个 Missing 的值，方便后面使用
Missing miss = Missing.Value;
// 创建 Excel，并制定是不可见的
ApplicationClass excel = new ApplicationClass();
excel.Visible = false;
// 新建一份电子表格，或者打开现有的文件
Workbook wb = excel.Workbooks.Add();
Workbook wb = excel.Workbooks.Open("demo.xls");
// 取得到第一个工作表，或者取得当前默认的工作表
Worksheet ws = wb.Sheets[1] as Worksheet;
Worksheet ws = wb.ActiveSheet as Worksheet;
▲ 注意，在 Excel 的操作中，许多时候，索引是从 1 开始的，而不是 0，这和大多数程序语法有区别。
2. 使用之后
既然已经把 Excel 都创建出来了，我们就先来说说使用之后的结束，以及保存文件，需要注意的问题。这就好比在写代码，两个花括号都是同时敲的，过后再来写里面的代码。
许多资料中，Excel 使用之后都直接就 excel = null; 来结束代码，这些朋友难道没发现，这样会在系统中留下许多 EXCEL.EXE 的进程吗？如下图：
如果这是在用户的客户端，可能会因为关机，而把这个问题忽略。但如果是在服务器上生成 Excel 文件，一个用户生成一次，就产生一个进程，那么后果可想而知。所以我们要来先说说使用之后怎么结束 Excel 的进程。
结束 Excel 不能单单把 EXCEL.EXE 结束就好，这样的话，如果用户正好打开了一个 Excel 也会被结束掉。
下面是正确结束 Excel 的代码：
[DllImport("User32.dll", CharSet = CharSet.Auto)]
public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
// 结束 Excel 进程
public static void KillExcel(Application excel)
{
IntPtr t = new IntPtr(excel.Hwnd);
int k = 0;
GetWindowThreadProcessId(t, out k);
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
p.Kill();
}
▲ 注意 DllImport 需要 using System.Runtime.InteropServices 。
接下来就是比较保险的关闭，以及调用上面的代码：
// 关闭电子表格，释放资源
wb.Close();
wb = null;
// 退出 Excel，释放资源
excel.Quit();
KillExcel(excel);
excel = null;
彻底关闭excel.exe,还有一种方法关闭之后GC.Collect();强制资源回收，这样excel.exe就没有了
3. 保存时的格式问题
「文件格式和扩展名不匹配。文件可能已损坏或不安全。」看起来似乎很严重，尤其对一些电脑小白来说，不安全这个词很耀眼。这个问题，可能很多朋友在做 Excel 导出的时候，都会遇到，包括我自己使用一些软件也遇过，绝大多数都没有对这个问题进行处理，觉得让用户点一下「是」就好了。但对于我这种相对注重用户体验的开发者来说，这样当然是不行的。那么到底是什么造成的？
其实解决这个问题非常简单，之所以会出现这个问题，是和 Office Excel 的版本有关系。我们都知道 Excel 有一个 97-2003 的格式，就是最常见的 .xls 文件，除了这个还有一种 .xlsx 文件，这种是自Office Excel 2007 之后有的新格式，除了这些，Excel 还支持把表格保存为 .xml 甚至是纯文本的格式。而我们用程序在生成 Excel 的时候，考虑到国内还有一大批 Office 2003 的使用者，所以我们都会保存为 .xls 以便更好的兼容他们。大部分都是像下面的代码这个保存的：
// 保存
wb.SaveAs("demo.xls");
虽然你的保存路径中有包含 .xls 后缀名，但其实这个时候，Excel 并不知道你是要以什么格式去保存，所以它可能是无格式的，或者是当前系统中 Office 版本的默认格式。
现在我们来看看 SaveAs 的参数中，会发现还有第二个参数 FileFormat，顾名思义，就是文件格式，正好是我们要的参数，所以我们只要告诉 Excel 要保存的格式，问题迎面而解：
// 保存，格式编码为56（xls）（.xlsx 的编码为51）
wb.SaveAs("demo.xls", 56);
现在再打开生成的 demo.xls 文件，会发现直接就打开了，不会再出现上面的问题了。
4. 常用的格式设置
在操作 Excel 的时候，除了上面这些基础问题，还有就是一些常见的格式设置。包括字体字号、粗体、合并单元格、垂直居中、横向居中、行高、列宽、单元格格式，边框样式，等等。下面的代码就包含了这些常用的设置：
// 选择一块区域（一个或多个单元格之间）
Range range = ws.get_Range(ws.Cells[1, 1], ws.Cells[2, 10]);
// 设定单元格格式，@是指文本格式（导出一串长数字时，例如手机号码，会被处理为数字，所以我们要强制为文本）
range.NumberFormat = "@";
// 合并单元格
range.MergeCells = true;
// 设置行高、列宽
range.RowHeight = 35;
range.ColumnWidth = 100;
// 设置字体字号、粗体、字体（还有大多数字体相关的都在 Font 属性中）
range.Font.Size = 12;
range.Font.Bold = true;
range.Font.Name = "楷体";
// 横向居中、垂直居中
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
range.VerticalAlignment = XlHAlign.xlHAlignCenter;
// 设置边框
range.Borders.LineStyle = 1;
range.Borders.LineStyle = XlLineStyle.xlContinuous;
// 给单元格设置值（内容）
range.set_Value(miss, "abel.cnblogs.com");
比较麻烦的是，每操作一个区域，我们就要重新设定一次 Range，这相当于在 Excel 实际做了一次选择某些单元格的操作，所以数据量大的话，生成 Excel 的速度会有点慢。
5. 常用的打印设置
这部分可能在网上比较少见，但一些项目中也会有相关的需求，比如要默认横向纸张啦，打印标题行啦（就是不管打印到第几页，都会出现这一行，一般是表格第一行）等等，我们来看看代码吧：
// 设置横向纸张
ws.PageSetup.Orientation = XlPageOrientation.xlLandscape;
// 设置打印标题的范围
ws.PageSetup.PrintTitleRows = "$3:$3";
6. 其它设置
还有一些其它的设置和操作，暂时先都整理在这吧。
// 设置电子表格的名称
ws.Name = "Hello C#";
7. 关于生成速度慢的解决方案…
上面我们有提到，使用程序来生成 Excel，遇到数据量大的话（十万级的数据就足够了），会比较慢的问题，这个怎么破？
其实我们可以不使用 Excel 操作类来生成，而是直接用 IO 来生成 xml 格式的 Excel 表格，最后保存为 .xls 文件即可，速度可以提高N倍。当然，会出现上面第三点说的问题。
那有没有速度又快，又不会出现那个安全提示的方法呢？也是有的，来看看代码：
// 打开 XML 格式的 Excel 文件
Workbook wb = excel.Workbooks.OpenXML("temp.xml");
// 再保存为真正 xls 格式的 Excel 文件
wb.SaveAs("demo.xls", 56);
是的，方法就是先打开一份用 IO 生成好的 xml 格式的 Excel，再另存为 Office 97-2003 格式的 xls 文件。
注意，这个方法只适合格式简单的 Excel，不然在保存的时候，会提示兼容问题！