告别ASP.NET操作EXCEL的烦恼
一. 程序操作EXCEL的应用主要还是在统计报表方面，您可能会考虑读EXCEL模板，也可能会考虑没必要读模板，其实读不读模板都能达到一样的效果，看实际情况而用了。
1. 读模板的话，首先模板存放在某个路径下，根据模板把从数据库里取出的数据写回EXCEL然后生成一个新的EXCEL存放都另一个路径以供下载，模板不变。
我这里的EXCEL操作主要是在VS2005里的，VS2003也可以的，不过没怎么研究03里的操作(文章最后我会把05的示例下载地址贴上 那个demo里之前打包忘了放了一个生成数据的文件，刚放进去了，不加也是可以运行的，还有模板文件的数据稍微过滤了下重新放了部分对照看下）vs05中操作EXCEL直接引用.NET自带的COM组件
，添加后项目的bin目录下会自动出现
Interop.Excel.dll这个DLL（需安装office2003 excel，下面的说明及示例都是基于office2003的，版本不同调用可能会不一样）页面的命名空间引用 using Excel;
下面是调用模板的一段代码
2 case "ReportByTemp":
3 {
4
5 DataView dv = Cache["ReportByTemp"] as DataView;
6 //建立一个Excel.Application的新进程
7 Excel.Application app = new Excel.Application();
8 if (app == null)
9 {
10 return;
11 }
12 app.Visible = false;
13 app.UserControl = true;
14 Workbooks workbooks = app.Workbooks;
15 _Workbook workbook = workbooks.Add(template_path + "\\EXCEL测试模板.xls");//这里的Add方法里的参数就是模板的路径
16 Sheets sheets = workbook.Worksheets;
17 _Worksheet worksheet = (_Worksheet)sheets.get_Item(1);//模板只有一个sheet表
18 if (worksheet == null)
19 {
20 return;
21 }
22
23 int rowNum = 0;
24 for (int i = 0; i < dv.Count; i++)
25 {
26 rowNum = i + 1;
27 worksheet.Cells[3 + i, 1] = rowNum;
28 worksheet.Cells[3 + i, 2] = dv[i].Row[0].ToString();
29 worksheet.Cells[3 + i, 3] = dv[i].Row[1].ToString();
30
31 excelOperate.SetBold(worksheet, worksheet.Cells[3 + i, 1], worksheet.Cells[3 + i, 1]); //黑体
32 excelOperate.SetHAlignCenter(worksheet, worksheet.Cells[3 + i, 1], worksheet.Cells[3 + i, 3]);//居中
33 worksheet.get_Range(worksheet.Cells[3 + i, 1], worksheet.Cells[3 + i, 3]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
34
35 }
36
37 tick = DateTime.Now.Ticks.ToString();
38 save_path = temp_path + "\\" + tick + ".xls";
39 workbook.SaveAs(save_path, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
40 excelOperate.Dispose(worksheet, workbook, app);//关闭Excel进程
41
42 }
43 break;
44 #endregion
2. 不读模板的话，调用的时候其实会继承一个空白模板，然后写入数据，程序画表头，最终达到一样的效果，程序如下：
2 case "ReportByNone":
3 {
4
5 DataView dv = Cache["ReportByNone"] as DataView;
6 //建立一个Excel.Application的新进程
7 Excel.Application app = new Excel.Application();
8 if (app == null)
9 {
10 return;
11 }
12 app.Visible = false;
13 app.UserControl = true;
14 Workbooks workbooks = app.Workbooks;
15 _Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);//这里的Add方法里的参数就相当于继承了一个空模板(暂这样理解吧)
16 Sheets sheets = workbook.Worksheets;
17 _Worksheet worksheet = (_Worksheet)sheets.get_Item(1);
18 if (worksheet == null)
19 {
20 return;
21 }
22
23 worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 3]).Merge(Missing.Value); //横向合并
24 worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 1]).Value2 = "导出EXCEL测试一";
25 excelOperate.SetBold(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1]); //黑体
26 excelOperate.SetHAlignCenter(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1]);//居中
27 excelOperate.SetBgColor(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1], System.Drawing.Color.Red);//背景色
28 excelOperate.SetFontSize(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1], 16);//字体大小
29 excelOperate.SetRowHeight(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1], 32.25);//行高
30 worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 1]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);//黑色连续边框
31
32 worksheet.Cells[2, 1] = "序号";
33 worksheet.Cells[2, 2] = "公司";
34 worksheet.Cells[2, 3] = "部门";
35 excelOperate.SetBold(worksheet, worksheet.Cells[2, 1], worksheet.Cells[2, 3]); //黑体
36 worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[2, 3]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
37 excelOperate.SetHAlignRight(worksheet, worksheet.Cells[2, 1], worksheet.Cells[2, 3]);
38 excelOperate.SetBgColor(worksheet, worksheet.Cells[2, 1], worksheet.Cells[2, 3], System.Drawing.Color.Silver);//背景色
39 int rowNum = 0;
40 for (int i = 0; i < dv.Count; i++)
41 {
42 rowNum = i + 1;
43 worksheet.Cells[3 + i, 1] = rowNum;
44 worksheet.Cells[3 + i, 2] = dv[i].Row[0].ToString();
45 worksheet.Cells[3 + i, 3] = dv[i].Row[1].ToString();
46
47 excelOperate.SetBold(worksheet, worksheet.Cells[3 + i, 1], worksheet.Cells[3 + i, 1]); //黑体
48 excelOperate.SetHAlignCenter(worksheet, worksheet.Cells[3 + i, 1], worksheet.Cells[3 + i, 3]);//居中
49 worksheet.get_Range(worksheet.Cells[3 + i, 1], worksheet.Cells[3 + i, 3]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);//设置边框颜色，不然打印预览，会非常不雅观
50
51 }
52 excelOperate.SetColumnWidth(worksheet, "A", 10);
53 excelOperate.SetColumnWidth(worksheet, "B", 20);
54 excelOperate.SetColumnWidth(worksheet, "C", 20);
55 worksheet.Name = "导出EXCEL测试一";
56
57 tick = DateTime.Now.Ticks.ToString();
58 save_path = temp_path + "\\"+ tick + ".xls";
59 workbook.SaveAs(save_path, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
60 excelOperate.Dispose(worksheet, workbook, app);//关闭Excel进程
61
62 }
63 break;
64
65 #endregion
以上我给了两个最简单的操作说明，下面详细说一下对于一些稍微复杂的报表的生成处理
二. 对于复杂的EXCEL报表的生成处理，无非是纵向合并相同的数据行及嵌套纵向合并等一些操作，下面就几个具有针对性的报表作下说明.
1.要生成相对复杂的EXCEL表，在从数据库取数据时，要注意先按照合理的要求排好序，有时候可能order by后面要跟好几个字段，而且这几个字段谁先谁后也要注意，因为这些会直接影响报表呈现的效果，比如你的EXCEL表要按月份统计国内外的项目，显示出来的时候要多个项目相同的人连续，那么排序就可能要这样order by 月份，项目类别，用户ID，项目ID（这是写好的视图，基于视图来检索的），这个排序的字段顺序就不能变了，变了的话就不太好生成想要的形式了，如下图：
这个也是动态画的，用了个简单的模板，模板就一个表头，没多大意义，除非表头很复杂而且在列表中不需要重画，考虑模板就比较好，向上面那个一月份国际的和其它月份的都是需要重画表头的。至于合并，如果不是嵌套的合并，我们可以在向模板循环写数据的时候直接控制，比如下面一个简单的写法：
2 {
3 bidName = table.Rows[index]["BIDNAME"].ToString();
4 if (table.Rows[i]["BIDNAME"].ToString() == bidName)
5 {
6 projNum++;
7 worksheet.Cells[5 + i, 2] = table.Rows[i]["PROJNO"];
8 worksheet.Cells[5 + i, 3] = table.Rows[i]["PROJNAME"];
9 worksheet.Cells[5 + i, 4] = table.Rows[i]["STAT_DATE"];
10 worksheet.Cells[5 + i, 5] = table.Rows[i]["PROJTYPE"];
11 worksheet.Cells[5 + i, 6] = table.Rows[i]["CONTENT"];
12 worksheet.Cells[5 + i, 7] = table.Rows[i]["OPENDT"];
13 worksheet.Cells[5 + i, 8] = table.Rows[i]["OPENADDRESS"];
14 worksheet.Cells[5 + i, 9] = table.Rows[i]["REV_DATE"];
15 worksheet.Cells[5 + i, 10] = table.Rows[i]["BID_UNIT"];
16 worksheet.Cells[5 + i, 11] = table.Rows[i]["AGT_AMOUNT"];
17 worksheet.Cells[5 + i, 12] = table.Rows[i]["CURRENCY"] + ":" + table.Rows[i]["BIDSER_AMOUNT"];
18 worksheet.Cells[5 + i, 13] = table.Rows[i]["SENDDATE"];
19 worksheet.Cells[5 + i, 14] = table.Rows[i]["CURRENCY"] + ":" + table.Rows[i]["BIDPRICE"];
20 worksheet.Cells[5 + i, 15] = table.Rows[i]["BOOKAMOUNT"];
21 worksheet.Cells[5 + i, 16] = table.Rows[i]["CURRENCY"] + ":" + table.Rows[i]["BAIL_AMOUNT"];
22 worksheet.Cells[5 + i, 17] = table.Rows[i]["USERNAME"];
23 worksheet.Cells[5 + i, 18] = table.Rows[i]["SECOND_USER"];
24 worksheet.Cells[5 + i, 19] = "";
25 worksheet.get_Range(worksheet.Cells[5 + i, 1], worksheet.Cells[5 + i, 19]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
26 continue;
27 }
28
29 worksheet.get_Range(worksheet.Cells[5 + rowid, 1], worksheet.Cells[5 + i - 1, 1]).Merge(Missing.Value); //将第一列按投标单位合并
30 worksheet.get_Range(worksheet.Cells[5 + rowid, 1], worksheet.Cells[5 + rowid, 1]).Value2 = bidName + "(" + projNum.ToString() + "个项目)";//合并后的单元格内容
2.嵌套的合并向上面那样做可能控制比较麻烦，而且思路可能很混乱，我们可以考虑先循环填充所有的数据，在循环出来要合并的列，比如像下面的这张表
先循环填充数据，如下：
2 string ProjNo = "";
3 for (i = 0; i < table.Rows.Count; i++)
4 {
5 ProjNo = table.Rows[index]["PROJNO"].ToString();
6 if (table.Rows[i]["PROJNO"].ToString() == ProjNo)
7 {
8 wksheet.Cells[3 + i, 1] = rownum + 1;
9 wksheet.Cells[3 + i, 2] = "'" + table.Rows[i]["PROJNO"]; //加上单引号保证以0开头的字符原样输出
10 wksheet.Cells[3 + i, 3] = "'" + table.Rows[i]["PROJNAME"];
11 wksheet.Cells[3 + i, 4] = "'" + table.Rows[i]["PA_NAME"];
12 wksheet.Cells[3 + i, 5] = "'" + table.Rows[i]["BIDER_NAME"];
13 wksheet.Cells[3 + i, 6] = table.Rows[i]["BAIL_AMOUNT"];
14 wksheet.Cells[3 + i, 7] = table.Rows[i]["NOT_BACK"];
15 wksheet.get_Range(wksheet.Cells[3 + i, 1], wksheet.Cells[3 + i, 7]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
16 continue;
17 }
18
19 index = i;
20 rownum++;
21 i--;
22
23 }
2 int m = 1, rowid = 3, k;
3 string projName = "";
4 for (k = 3; k <= i + 2; k++)
5 {
6 if (Convert.ToInt32(wksheet.get_Range(wksheet.Cells[k, 1], wksheet.Cells[k, 1]).Value2) == m)
7 {
8 ProjNo = wksheet.get_Range(wksheet.Cells[k, 2], wksheet.Cells[k, 2]).Value2.ToString();
9 projName = wksheet.get_Range(wksheet.Cells[k, 3], wksheet.Cells[k, 3]).Value2.ToString();
10 wksheet.get_Range(wksheet.Cells[k, 1], wksheet.Cells[k, 1]).Value2 = "";
11 wksheet.get_Range(wksheet.Cells[k, 2], wksheet.Cells[k, 2]).Value2 = "";
12 wksheet.get_Range(wksheet.Cells[k, 3], wksheet.Cells[k, 3]).Value2 = "";
13 continue;
14 }
15 wksheet.get_Range(wksheet.Cells[rowid, 1], wksheet.Cells[k - 1, 1]).Merge(Missing.Value);
16 wksheet.get_Range(wksheet.Cells[rowid, 1], wksheet.Cells[rowid, 1]).Value2 = m;
17
18 wksheet.get_Range(wksheet.Cells[rowid, 2], wksheet.Cells[k - 1, 2]).Merge(Missing.Value);
19 wksheet.get_Range(wksheet.Cells[rowid, 2], wksheet.Cells[rowid, 2]).Value2 = "'" + ProjNo;
20
21 wksheet.get_Range(wksheet.Cells[rowid, 3], wksheet.Cells[k - 1, 3]).Merge(Missing.Value);
22 wksheet.get_Range(wksheet.Cells[rowid, 3], wksheet.Cells[rowid, 3]).Value2 = "'" + projName;
23
24 m++;
25 rowid = k;
26 k--;
27 }
28 //跳出循环后合并最后一个招标项目
29
30 wksheet.get_Range(wksheet.Cells[rowid, 1], wksheet.Cells[k - 1, 1]).Merge(Missing.Value);
31 wksheet.get_Range(wksheet.Cells[rowid, 1], wksheet.Cells[rowid, 1]).Value2 = m;
32
33 wksheet.get_Range(wksheet.Cells[rowid, 2], wksheet.Cells[k - 1, 2]).Merge(Missing.Value);
34 wksheet.get_Range(wksheet.Cells[rowid, 2], wksheet.Cells[rowid, 2]).Value2 = "'" + ProjNo;
35
36 wksheet.get_Range(wksheet.Cells[rowid, 3], wksheet.Cells[k - 1, 3]).Merge(Missing.Value);
37 wksheet.get_Range(wksheet.Cells[rowid, 3], wksheet.Cells[rowid, 3]).Value2 = "'" + projName;
2
3 index = 0; rowid = 3; //重置变量
4 string pa_name = string.Empty; //标段名称
5 for (k = 3; k <= i + 2; k++)
6 {
7 pa_name = table.Rows[index]["PA_NAME"].ToString();
8 if (wksheet.get_Range(wksheet.Cells[k, 4], wksheet.Cells[k, 4]).Value2.ToString() == pa_name)
9 {
10 wksheet.get_Range(wksheet.Cells[k, 4], wksheet.Cells[k, 4]).Value2 = "";
11 continue;
12 }
13 wksheet.get_Range(wksheet.Cells[rowid, 4], wksheet.Cells[k - 1, 4]).Merge(Missing.Value);
14 wksheet.get_Range(wksheet.Cells[rowid, 4], wksheet.Cells[rowid, 4]).Value2 = "'" + pa_name;
15 index = k - 3;
16 rowid = k;
17 k--;
18
19 }
20 //退出循环时合并最后一个项目的标段
21 wksheet.get_Range(wksheet.Cells[rowid, 4], wksheet.Cells[k - 1, 4]).Merge(Missing.Value);
22 wksheet.get_Range(wksheet.Cells[rowid, 4], wksheet.Cells[rowid, 4]).Value2 = "'" + pa_name;
23 tick = DateTime.Now.ToString("yyyyMMddhhmmss");
24 save_path = temp_path + "\\" + tick + "保证金收退情况表.xls";
25 Session["BailBackID"] = tick + "保证金收退情况表.xls";
26 Session["_BailBack"] = "true";
27 workbook.SaveAs(save_path, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
28 excelOperate.Dispose(worksheet, workbook, app);//关闭Excel进程
29 //DownLoad(save_path);
30 //Page_Close();
下面我们看下几个效果图：
（注意：这里提示的导出数据是指从数据库成功取出数据，还没有操作EXCEL对象，刚开始已经说过了，当然这个提示文字换成其它的也可以）
整个过程采用AJAX提示的，一来不刷新，二来导出时间比较长的话，可以给客户一个良好的体验效果，否可，用户一点导出按钮，半天没反应也没提示，客户就觉得怎么这么慢的，是不是你们程序有问题，指责一大堆，有了这么些交互提示信息，让客户多等几分钟也能承受。
3.生成的表格包含多个sheet的操作，比如下面一种情况
绘制这张表的要求是根据选择某年的几月到几月，生成这个几个月的一个综合情况的sheet，然后分别生成这几个月的单独的sheet表，生成上面表的模板，包含两个sheet ，一个综合月份的sheet和一个单独月份的sheet，因为单独月份的sheet表现形式都是一样的，我们可以根据选择的月份个数Copy几个sheet就可以了
2
3 _Workbook workbook = workbooks.Add(template_path + "\\招标单位年度招标情况逐月统计表.xls");
4 Sheets sheets = workbook.Worksheets;
5 _Worksheet Yearsheet = (_Worksheet)sheets.get_Item(1);
6 _Worksheet worksheet = (_Worksheet)sheets.get_Item(2);
7 if (worksheet == null)
8 {
9 return;
10 }
11 for (int i = 1; i < monthCount; i++)
12 worksheet.Copy(Missing.Value, workbook.Worksheets[2]);//月统计工作薄
Yearsheet的操作就不说了，和前面几个一样操作，关键是月份的sheet的生成，其实就是循环操作get_Item(i)，代码如下
2
3 int item_id = 2;
4 rowNum = 0; book_Amount = 0; index = 0;
5 bid_Amount = ""; bidser_Amount = ""; agent_Amount = 0;//清空变量
6 _Worksheet ws = null;
7 for (int i = 0; i < tableMM.Rows.Count; i++)
8 {
9 rowNum++;
10 Month = tableMM.Rows[index]["DATE_MONTH"].ToString();
11 if (tableMM.Rows[i]["DATE_MONTH"].ToString() == Month)
12 {
13 ws = (_Worksheet)sheets.get_Item(item_id);
14 ws.Cells[3 + rowNum - 1, 1] = rowNum;
15 ws.Cells[3 + rowNum - 1, 2] = tableMM.Rows[i]["PROJNO"];
16 ws.Cells[3 + rowNum - 1, 3] = tableMM.Rows[i]["PROJNAME"];
17 ws.Cells[3 + rowNum - 1, 4] = tableMM.Rows[i]["BID_TYPE"];
18 ws.Cells[3 + rowNum - 1, 5] = tableMM.Rows[i]["BID_MODE"];
19 ws.Cells[3 + rowNum - 1, 6] = tableMM.Rows[i]["OPENDT"];
20 ws.Cells[3 + rowNum - 1, 7] = tableMM.Rows[i]["OPENADDRESS"];
21 ws.Cells[3 + rowNum - 1, 8] = tableMM.Rows[i]["BID_UNIT"];
22 ws.Cells[3 + rowNum - 1, 9] = tableMM.Rows[i]["NOTICE_NO"].ToString().Replace("神华国贸", "");
23 ws.Cells[3 + rowNum - 1, 10] = tableMM.Rows[i]["BOOKAMOUNT"];
24 ws.Cells[3 + rowNum - 1, 11] = tableMM.Rows[i]["BIDPRICE"] + "(万" + tableMM.Rows[i]["CURRENCY"] + ")";
25 ws.Cells[3 + rowNum - 1, 12] = tableMM.Rows[i]["BIDSER_AMOUNT"] + "(万" + tableMM.Rows[i]["CURRENCY"]+")";
26 ws.Cells[3 + rowNum - 1, 13] = tableMM.Rows[i]["AGT_AMOUNT"];
27 ws.Cells[3 + rowNum - 1, 14] = "";
28 ws.get_Range(ws.Cells[3 + rowNum - 1, 1], ws.Cells[3 + rowNum - 1, 14]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
29 continue;
30 }
31
32 ws.Cells[1, 1] = year + "年" + bidName + GetMonth(Month) + "月份招标项目情况一览表";
33
34 //每月合计
35 sql = " SELECT CURRENCY, NVL(SUM(BIDPRICE),0) AS BIDPRICE,NVL(SUM(BOOKAMOUNT),0) AS BOOKAMOUNT,NVL(SUM(BIDSER_AMOUNT),0) AS BIDSER_AMOUNT,NVL(SUM(AGT_AMOUNT),0) AS AGT_AMOUNT FROM IBS_V_BID_MONTHLY_STAT" + SqlFilter +
36 " AND DATE_YEAR ='" + year + "' AND COMPANY_ID=" + biderID + " AND DATE_MONTH ='" + Month + "'" +
37 " GROUP BY CURRENCY";
38 System.Data.DataTable dt1 = OracleHelper.RetDataTable(sql);
39 for (int m = 0; m < dt1.Rows.Count; m++)
40 {
41 bid_Amount += dt1.Rows[m]["BIDPRICE"] + "(万"+dt1.Rows[m]["CURRENCY"] + ")\r\t";
42 book_Amount += float.Parse(dt1.Rows[m]["BOOKAMOUNT"].ToString());
43 bidser_Amount += dt1.Rows[m]["BIDSER_AMOUNT"] + "(万" + dt1.Rows[m]["CURRENCY"] + ")\r\t";
44 agent_Amount += float.Parse(dt1.Rows[m]["AGT_AMOUNT"].ToString());
45 }
46
47 ws.Cells[3 + rowNum - 1, 3] = "合 计";
48 ws.Cells[3 + rowNum - 1, 10] = book_Amount;
49 ws.Cells[3 + rowNum - 1, 11] = bid_Amount;
50 ws.Cells[3 + rowNum - 1, 12] = bidser_Amount;
51 ws.Cells[3 + rowNum - 1, 13] = agent_Amount;
52 ws.get_Range(ws.Cells[3 + rowNum - 1, 1], ws.Cells[3 + rowNum - 1, 14]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
53 ws.Name = GetMM(Month);
54
55 item_id++;
56 index = i; //汇总下一个月份的招标项目
57 i--;
58 rowNum = 0; book_Amount = 0;
59 bid_Amount = ""; bidser_Amount = ""; agent_Amount = 0;//清空变量
60 }
61
62 //跳出循环时进行最后一个月份的项目汇总
用的是oracle数据库，所以上面那个sql语句。。。 呵呵
============================================================================================
上面大致说得就差不多了，因为是不断循环的什么的，可能对于大的数据量读写来说，比较好性能，如果大家有什么更好的方法，可以指点下，为了弥补等待时间过长，所以才结合了AJAX来处理。
最后我把做的一个小demo的链接帖出来给大家，还有一些空模板和对应生成的数据表给大家对照看下，尤其相对复杂一些的表画应该是能画出来的，主要看大家采用什么样的方法，能少循环一次就尽量少循环，呵呵~~~
EXCEL模板读写说明
http://www.justlike.com.cn/upfiles/template_xls.rar
http://www.justlike.com.cn/upfiles/ExcelFiles.rar
http://www.justlike.com.cn/upfiles/ExcelReportDemo.rar
（说明：最后弹出下载文件的一个页面一直想让其自动关掉，但是不行，如果不关掉，再点导出，不会弹出下载框，实际的处理中我们可以在导出旁边放个下载按钮，就像上面的效果图里那样，当然可以点导出的时候让其在网页中直接打开，点下载的时候再弹出下载框，但是直接打开的话，文件需要生成在虚拟目录下，不太安全，呵呵~~，看实际情况处理了）
==========================================================================================
今天补充说明下，关于那个调用ajax回调的效果，有个地方用到了所谓的“ajax嵌套调用”，如下
2 {
3 if(resp.value == "OK")
4 {
5
6 $('tipMsg').innerHTML = "<img border=\"0\" src=\"images/s_progressbar.gif\"><font color=#FF0000 style=font-weight:bold>准备导出数据,请稍等
</font>";7 setTimeout("RedirectUrl()",1000);//延时体验
8 }
9 else
10 if(resp.value == "NO")
11 {
12 $('tipMsg').innerHTML = "<font color=#FF0000 style=font-weight:bold>没有找到符合该查询条件的数据</font>";
13 $('btnExcel').disabled = false;
14 }
15 else
16 {
17 $('tipMsg').innerHTML = "<font color=#FF0000 style=font-weight:bold>警告:导出数据出错</font>";
18 $('btnExcel').disabled = false;
19 }
20
21
22 }
2 {
3 $('tipMsg').innerHTML = "<img border=\"0\" src=\"images/ajaxloading.gif\"><font color=#7fffd4 style=font-weight:bold>正在读写报表文件,请稍后
</font>";4 var ajax = new ajax_request("ExcelReport.aspx?flag=ReportByTemp&"+Math.random(), "", "", ReportCallback);
5 function ReportCallback(resp)
6 {
7 if(resp.value != "Error" && resp.value !="")
8 {
9 $('btnExcel').disabled = false;
10 $('tipMsg').innerHTML = "<font color=#FF0000 style=font-weight:bold>数据导出成功!</font>";
11 Open("XLS_DownLoad.aspx?path="+resp.value);//window.location.href = resp.value;//
12 }
13 else
14 {
15 $('btnExcel').disabled = false;
16 $('tipMsg').innerHTML = "<font color=#FF0000 style=font-weight:bold>文件读写出错,请检查文件模板是否存在或对文件是否有读写权限!</font>";
17 }
18 }
19
20 }
21
22 function Open(url)
23 {
24 window.open(url,'newwindow','height=1,width=1,top=1500,left=1500,toolbar=no,menubar=no,scrollbars=yes,location=no,status=no')
25 }
ExcelReportCallback(resp)原本是一个回调函数，但是里面调用了一个RedirectUrl()方法，这个方法又包含了一个回调函数，这样就形成了回调的嵌套，之所以这么做，是因为，第一个回调是处理从数据库取出数据成功与否，如果成功了跳转到画EXCEL的页面，这样的话会出现一个空白页等生成好后出现下载框，后来觉得是否可以嵌套一个回调来继续一次异步操作，这样就不会出现长时间等待的空白页面了，而是生成好EXCEL后返回地址，或者可以返回一个文件名到XLS_DownLoad.aspx页面直接下载，但是XLS_DownLoad.aspx也是要出现的，我尝试过让下载后这个页面自动关闭，无赖做不到，所以把Open()方法里的数据值调得让页面不显示，但是状态栏还是有显示的。
到这里算是写完了，决定奢侈下，放到首页下：），总觉得首页的文章只有高手才能放，而且放到首页也是一种奢侈，希望对园子里的某些人有一定的帮助吧~~
上面给的地址由于服务器原因下不了了，我重新贴下博客园的下载地址(其实评论里早就贴了)
https://files.cnblogs.com/peaceli/ExcelReportDemo.rar
https://files.cnblogs.com/peaceli/shuoming.rar 文档说明
注意两个地方：
1、获取范围用： Excel.Range tmpRange = tmpSheet.Range[tmpSheet.Cells[1, 1], tmpSheet.Cells[1,4]];
2、在页面下载用：
private void DownLoad(string path)
{
if (path != null && File.Exists(path))
{
System.IO.FileInfo file = new System.IO.FileInfo(path);
//清除缓冲区流中的所有内容输出
Response.Clear();
//将下载保存对话框指定默认的文件名添加到HTTP头中
//Response.AddHeader("Content-Disposition", "attachment; filename=" + file.Name);
Response.AddHeader("Content-Disposition", "attachment; filename=" + System.Web.HttpUtility.UrlEncode(file.Name, System.Text.Encoding.UTF8));//避免中文出现乱码现象
//在header中指定文件的大小，使浏览器能显示下载过程
Response.AddHeader("Content-Length", file.Length.ToString());
//设置输出流的 HTTP MIME 类型
Response.ContentType = "application/octet-stream";
// 发送文件流到客户端
Response.WriteFile(file.FullName);
// 停止该页的执行
Response.End();
}
else
{
Response.Write("文件自动下载中断,请手动下载");
}
}
3、类文件
using System;
using System.Web;
using Excel=Microsoft.Office.Interop.Excel;
/// <summary>
/// ExcelOperate 的摘要说明。Excel操作函数
/// </summary>
public class ExcelOperate
{
private object mValue = System.Reflection.Missing.Value;
public ExcelOperate()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
/// <summary>
/// 合并单元格
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objStartCell">开始单元格</param>
/// <param name="objEndCell">结束单元格</param>
public void Merge(Excel._Worksheet CurSheet,object objStartCell,object objEndCell)
{
CurSheet.get_Range(objStartCell, objEndCell).Merge(mValue);
}
/// <summary>
/// 设置连续区域的字体大小
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="strStartCell">开始单元格</param>
/// <param name="strEndCell">结束单元格</param>
/// <param name="intFontSize">字体大小</param>
public void SetFontSize(Excel._Worksheet CurSheet, object objStartCell, object objEndCell, int intFontSize)
{
CurSheet.get_Range(objStartCell, objEndCell).Font.Size = intFontSize.ToString();
}
/// <summary>
/// 横向打印
/// </summary>
/// <param name="CurSheet"></param>
public void xlLandscape(Excel._Worksheet CurSheet)
{
CurSheet.PageSetup.Orientation=Excel.XlPageOrientation.xlLandscape;
}
/// <summary>
/// 纵向打印
/// </summary>
/// <param name="CurSheet"></param>
public void xlPortrait(Excel._Worksheet CurSheet)
{
CurSheet.PageSetup.Orientation=Excel.XlPageOrientation.xlPortrait;
}
/// <summary>
/// 在指定单元格插入指定的值
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="Cell">单元格 如Cells[1,1]</param>
/// <param name="objValue">文本、数字等值</param>
public void WriteCell(Excel._Worksheet CurSheet,object objCell, object objValue)
{
CurSheet.get_Range(objCell, mValue).Value2 = objValue;
}
/// <summary>
/// 在指定Range中插入指定的值
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="StartCell">开始单元格</param>
/// <param name="EndCell">结束单元格</param>
/// <param name="objValue">文本、数字等值</param>
public void WriteRange(Excel._Worksheet CurSheet,object objStartCell, object objEndCell, object objValue)
{
CurSheet.get_Range(objStartCell, objEndCell).Value2 = objValue;
}
/// <summary>
/// 合并单元格，并在合并后的单元格中插入指定的值
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objStartCell">开始单元格</param>
/// <param name="objEndCell">结束单元格</param>
/// <param name="objValue">文本、数字等值</param>
public void WriteAfterMerge(Excel._Worksheet CurSheet,object objStartCell, object objEndCell, object objValue)
{
CurSheet.get_Range(objStartCell, objEndCell).Merge(mValue);
CurSheet.get_Range(objStartCell, mValue).Value2 = objValue;
}
/// <summary>
/// 为单元格设置公式
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objCell">单元格</param>
/// <param name="strFormula">公式</param>
public void SetFormula(Excel._Worksheet CurSheet,object objCell, string strFormula)
{
CurSheet.get_Range(objCell, mValue).Formula = strFormula;
}
/// <summary>
/// 单元格自动换行
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objStartCell">开始单元格</param>
/// <param name="objEndCell">结束单元格</param>
public void AutoWrapText(Excel._Worksheet CurSheet,object objStartCell, object objEndCell)
{
CurSheet.get_Range(objStartCell,objEndCell).WrapText=true;
}
/// <summary>
/// 设置整个连续区域的字体颜色
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objStartCell">开始单元格</param>
/// <param name="objEndCell">结束单元格</param>
/// <param name="clrColor">颜色</param>
public void SetColor(Excel._Worksheet CurSheet,object objStartCell, object objEndCell, System.Drawing.Color clrColor)
{
CurSheet.get_Range(objStartCell, objEndCell).Font.Color = System.Drawing.ColorTranslator.ToOle(clrColor);
}
/// <summary>
/// 设置整个连续区域的单元格背景色
/// </summary>
/// <param name="CurSheet"></param>
/// <param name="objStartCell"></param>
/// <param name="objEndCell"></param>
/// <param name="clrColor"></param>
public void SetBgColor(Excel._Worksheet CurSheet, object objStartCell, object objEndCell, System.Drawing.Color clrColor)
{
CurSheet.get_Range(objStartCell, objEndCell).Interior.Color = System.Drawing.ColorTranslator.ToOle(clrColor);
}
/// <summary>
/// 设置连续区域的字体名称
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objStartCell">开始单元格</param>
/// <param name="objEndCell">结束单元格</param>
/// <param name="fontname">字体名称 隶书、仿宋_GB2312等</param>
public void SetFontName(Excel._Worksheet CurSheet,object objStartCell, object objEndCell,string fontname)
{
CurSheet.get_Range(objStartCell, objEndCell).Font.Name=fontname;
}
/// <summary>
/// 设置连续区域的字体为黑体
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objStartCell">开始单元格</param>
/// <param name="objEndCell">结束单元格</param>
public void SetBold(Excel._Worksheet CurSheet,object objStartCell, object objEndCell)
{
CurSheet.get_Range(objStartCell, objEndCell).Font.Bold = true;
}
/// <summary>
/// 设置连续区域的边框：上下左右都为黑色连续边框
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objStartCell">开始单元格</param>
/// <param name="objEndCell">结束单元格</param>
public void SetBorderAll(Excel._Worksheet CurSheet,object objStartCell, object objEndCell)
{
CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlEdgeTop].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlEdgeBottom].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlEdgeLeft].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlEdgeRight].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlInsideHorizontal].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlInsideVertical].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
}
/// <summary>
/// 设置连续区域水平居中
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objStartCell">开始单元格</param>
/// <param name="objEndCell">结束单元格</param>
public void SetHAlignCenter(Excel._Worksheet CurSheet,object objStartCell, object objEndCell)
{
CurSheet.get_Range(objStartCell, objEndCell).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
}
/// <summary>
/// 设置连续区域水平居左
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objStartCell">开始单元格</param>
/// <param name="objEndCell">结束单元格</param>
public void SetHAlignLeft(Excel._Worksheet CurSheet,object objStartCell, object objEndCell)
{
CurSheet.get_Range(objStartCell, objEndCell).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
}
/// <summary>
/// 设置连续区域水平居右
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objStartCell">开始单元格</param>
/// <param name="objEndCell">结束单元格</param>
public void SetHAlignRight(Excel._Worksheet CurSheet,object objStartCell, object objEndCell)
{
CurSheet.get_Range(objStartCell, objEndCell).HorizontalAlignment=Excel.XlHAlign.xlHAlignRight;
}
/// <summary>
/// 设置连续区域的显示格式
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objStartCell">开始单元格</param>
/// <param name="objEndCell">结束单元格</param>
/// <param name="strNF">如"#,##0.00"的显示格式</param>
public void SetNumberFormat(Excel._Worksheet CurSheet,object objStartCell, object objEndCell, string strNF)
{
CurSheet.get_Range(objStartCell, objEndCell).NumberFormat = strNF;
}
/// <summary>
/// 设置列宽
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="strColID">列标识，如A代表第一列</param>
/// <param name="dblWidth">宽度</param>
public void SetColumnWidth(Excel._Worksheet CurSheet,string strColID, double dblWidth)
{
((Excel.Range)CurSheet.Columns.GetType().InvokeMember("Item",System.Reflection.BindingFlags.GetProperty, null, CurSheet.Columns,new object[]{(strColID + ":" + strColID).ToString()})).ColumnWidth = dblWidth;
}
/// <summary>
/// 设置列宽
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objStartCell">开始单元格</param>
/// <param name="objEndCell">结束单元格</param>
/// <param name="dblWidth">宽度</param>
public void SetColumnWidth(Excel._Worksheet CurSheet,object objStartCell, object objEndCell, double dblWidth)
{
CurSheet.get_Range(objStartCell,objEndCell).ColumnWidth=dblWidth;
}
/// <summary>
/// 设置行高
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objStartCell">开始单元格</param>
/// <param name="objEndCell">结束单元格</param>
/// <param name="dblHeight">行高</param>
public void SetRowHeight(Excel._Worksheet CurSheet,object objStartCell, object objEndCell, double dblHeight)
{
CurSheet.get_Range(objStartCell,objEndCell).RowHeight=dblHeight;
}
/// <summary>
/// 为单元格添加超级链接
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objCell">单元格</param>
/// <param name="strAddress">链接地址</param>
/// <param name="strTip">屏幕提示</param>
/// <param name="strText">链接文本</param>
public void AddHyperLink(Excel._Worksheet CurSheet,object objCell, string strAddress, string strTip, string strText)
{
CurSheet.Hyperlinks.Add(CurSheet.get_Range(objCell, objCell),strAddress, mValue, strTip, strText);
}
/// <summary>
/// 另存为xls文件
/// </summary>
/// <param name="CurBook">Workbook</param>
/// <param name="strFilePath">文件路径</param>
public void Save(Excel._Workbook CurBook,string strFilePath)
{
CurBook.SaveCopyAs(strFilePath);
}
/// <summary>
/// 保存文件
/// </summary>
/// <param name="CurBook">Workbook</param>
/// <param name="strFilePath">文件路径</param>
public void SaveAs(Excel._Workbook CurBook,string strFilePath)
{
CurBook.SaveAs(strFilePath, mValue, mValue, mValue, mValue, mValue, Excel.XlSaveAsAccessMode.xlShared, mValue, mValue, mValue, mValue, mValue);
}
/// <summary>
/// 另存为html文件
/// </summary>
/// <param name="CurBook">Workbook</param>
/// <param name="strFilePath">文件路径</param>
public void SaveHtml(Excel._Workbook CurBook,string strFilePath)
{
CurBook.SaveAs(strFilePath, Excel.XlFileFormat.xlHtml, mValue, mValue, mValue, mValue, Excel.XlSaveAsAccessMode.xlNoChange, mValue, mValue, mValue, mValue, mValue);
}
/// <summary>
/// 释放内存
/// </summary>
public void Dispose(Excel._Worksheet CurSheet,Excel._Workbook CurBook,Excel._Application CurExcel)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(CurSheet);
CurSheet = null;
CurBook.Close(false, mValue, mValue);
System.Runtime.InteropServices.Marshal.ReleaseComObject(CurBook);
CurBook = null;
CurExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(CurExcel);
CurExcel = null;
GC.Collect();
GC.WaitForPendingFinalizers();
}
catch(System.Exception ex)
{
HttpContext.Current.Response.Write( "在释放Excel内存空间时发生了一个错误:"+ex);
}
finally
{
foreach(System.Diagnostics.Process pro in System.Diagnostics.Process.GetProcessesByName("Excel"))
//if (pro.StartTime < DateTime.Now)
pro.Kill();
}
System.GC.SuppressFinalize(this);
}
}
