一. 程序操作EXCEL的应用主要还是在统计报表方面,您可能会考虑读EXCEL模板,也可能会考虑没必要读模板,其实读不读模板都能达到一样的效果,看实际情况而用了。
1. 读模板的话,首先模板存放在某个路径下,根据模板把从数据库里取出的数据写回EXCEL然后生成一个新的EXCEL存放都另一个路径以供下载,模板不变。
我这里的EXCEL操作主要是在VS2005里的,VS2003也可以的,不过没怎么研究03里的操作(文章最后我会把05,03的示例下载地址贴上)vs05中操作EXCEL直接引用.NET自带的COM组件
![](https://i-blog.csdnimg.cn/blog_migrate/741898ec50c1f9a8a434024d32dfae4b.jpeg)
![](https://i-blog.csdnimg.cn/blog_migrate/bad47869366a404ce12eedb6a4a6bd85.jpeg)
页面的命名空间引用 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
![](https://i-blog.csdnimg.cn/blog_migrate/5f6b7de125e3061e44b1fc9ea1ae1073.jpeg)
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
![](https://i-blog.csdnimg.cn/blog_migrate/fe601fc5d5f2c57fa8c6f91f6e702889.jpeg)
以上我给了两个最简单的操作说明,下面详细说一下对于一些稍微复杂的报表的生成处理
二. 对于复杂的EXCEL报表的生成处理,无非是纵向合并相同的数据行及嵌套纵向合并等一些操作,下面就几个具有针对性的报表作下说明.
1.要生成相对复杂的EXCEL表,在从数据库取数据时,要注意先按照合理的要求排好序,有时候可能order by后面要跟好几个字段,而且这几个字段谁先谁后也要注意,因为这些会直接影响报表呈现的效果,比如你的EXCEL表要按月份统计国内外的项目,显示出来的时候要多个项目相同的人连续,那么排序就可能要这样order by 月份,项目类别,用户ID,项目ID(这是写好的视图,基于视图来检索的),这个排序的字段顺序就不能变了,变了的话就不太好生成想要的形式了,如下图:
![](https://i-blog.csdnimg.cn/blog_migrate/3feffa8c61305183923f59dc66964453.jpeg)
这个也是动态画的,用了个简单的模板,模板就一个表头,没多大意义,除非表头很复杂而且在列表中不需要重画,考虑模板就比较好,向上面那个一月份国际的和其它月份的都是需要重画表头的。至于合并,如果不是嵌套的合并,我们可以在向模板循环写数据的时候直接控制,比如下面一个简单的写法:
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.嵌套的合并向上面那样做可能控制比较麻烦,而且思路可能很混乱,我们可以考虑先循环填充所有的数据,在循环出来要合并的列,比如像下面的这张表
![](https://i-blog.csdnimg.cn/blog_migrate/5f260f1d39e526df4074ad41ea90d347.jpeg)
先循环填充数据,如下:
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();
下面我们看下几个效果图:
![](https://i-blog.csdnimg.cn/blog_migrate/a9b2c0574f014d73587d949a5679f47a.jpeg)
![](https://i-blog.csdnimg.cn/blog_migrate/b2eb086eecbed3deb78f76f48d388259.jpeg)
![](https://i-blog.csdnimg.cn/blog_migrate/279a59d60390385f7d8fc51156320d07.jpeg)
(注意:这里提示的导出数据是指从数据库成功取出数据,还没有操作EXCEL对象,刚开始已经说过了,当然这个提示文字换成其它的也可以)
![](https://i-blog.csdnimg.cn/blog_migrate/a9c2fba777f84a70c7adb57089cf1225.jpeg)
![](https://i-blog.csdnimg.cn/blog_migrate/b4b3c623b373499fb08470333e0cfa89.jpeg)
整个过程采用AJAX提示的,一来不刷新,二来导出时间比较长的话,可以给客户一个良好的体验效果,否可,用户一点导出按钮,半天没反应也没提示,客户就觉得怎么这么慢的,是不是你们程序有问题,指责一大堆,有了这么些交互提示信息,让客户多等几分钟也能承受。
3.生成的表格包含多个sheet的操作,比如下面一种情况
![](https://i-blog.csdnimg.cn/blog_migrate/8efe54bd9d325d2ee15a0b78766b8520.jpeg)
绘制这张表的要求是根据选择某年的几月到几月,生成这个几个月的一个综合情况的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的链接帖出来给大家,还有一些空模板和对应生成的数据表给大家对照看下,尤其相对复杂一些的表画应该是能画出来的,主要看大家采用什么样的方法,能少循环一次就尽量少循环,呵呵~~~
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>准备导出数据,请稍等
![](https://www.cnblogs.com/Images/dot.gif)
![](https://www.cnblogs.com/Images/dot.gif)
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>正在读写报表文件,请稍后
![](https://www.cnblogs.com/Images/dot.gif)
![](https://www.cnblogs.com/Images/dot.gif)
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()方法里的数据值调得让页面不显示,但是状态栏还是有显示的。