关闭

告别ASP.NET操作EXCEL的烦恼

标签: excelasp.netobjectpathstring报表
635人阅读 评论(1) 收藏 举报

ASP.NET操作EXCEL代码

公元19XX年前,关于EXCEL的操作就如滔滔江水,连绵不绝,真正操作EXCEL我也是从去年下半年开始的,有些比较复杂的年度报表之类的,做起来也有点费力,不过还是都能画出来了,关于EXCEL的报表导出,考虑到导出耗时的问题我主要采用AJAX来做的,分别捕捉几个起止状态,给客户端提示3个状态:正在检索数据。。。---》准备导出数据。。。(只是从数据库成功取出,还没有读写excel文件)--》正在读写文件--》导出数据成功,当然如果哪一过程出错,都有对应的提示,只所以想到写这篇文章,主要是因为今年有个系统的部分EXCEL的操作也让我做,顺便结合之前操作EXCEL的经验作一下总结,可能也算不上什么,对于绝大多数来说也没什么技术含量,网上一搜一大把,但我想还是有必要总结一下,至少能给园子里的新手些许帮助,OK,Let's Go...

   一. 程序操作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;
下面是调用模板的一段代码

 1  #region 使用模板导出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. 不读模板的话,调用的时候其实会继承一个空白模板,然后写入数据,程序画表头,最终达到一样的效果,程序如下:

 1  #region 不使用模板生成Excel表
 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[11], worksheet.Cells[13]).Merge(Missing.Value); //横向合并
24                         worksheet.get_Range(worksheet.Cells[11], worksheet.Cells[11]).Value2 = "导出EXCEL测试一";
25                         excelOperate.SetBold(worksheet, worksheet.Cells[11], worksheet.Cells[11]); //黑体
26                         excelOperate.SetHAlignCenter(worksheet, worksheet.Cells[11], worksheet.Cells[11]);//居中
27                         excelOperate.SetBgColor(worksheet, worksheet.Cells[11], worksheet.Cells[11], System.Drawing.Color.Red);//背景色
28                         excelOperate.SetFontSize(worksheet, worksheet.Cells[11], worksheet.Cells[11], 16);//字体大小
29                         excelOperate.SetRowHeight(worksheet, worksheet.Cells[11], worksheet.Cells[11], 32.25);//行高
30                         worksheet.get_Range(worksheet.Cells[11], worksheet.Cells[11]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);//黑色连续边框
31 
32                         worksheet.Cells[21= "序号";
33                         worksheet.Cells[22= "公司";
34                         worksheet.Cells[23= "部门";
35                         excelOperate.SetBold(worksheet, worksheet.Cells[21], worksheet.Cells[23]); //黑体
36                         worksheet.get_Range(worksheet.Cells[21], worksheet.Cells[23]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
37                         excelOperate.SetHAlignRight(worksheet, worksheet.Cells[21], worksheet.Cells[23]);
38                         excelOperate.SetBgColor(worksheet, worksheet.Cells[21], worksheet.Cells[23], 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(这是写好的视图,基于视图来检索的),这个排序的字段顺序就不能变了,变了的话就不太好生成想要的形式了,如下图:

这个也是动态画的,用了个简单的模板,模板就一个表头,没多大意义,除非表头很复杂而且在列表中不需要重画,考虑模板就比较好,向上面那个一月份国际的和其它月份的都是需要重画表头的。至于合并,如果不是嵌套的合并,我们可以在向模板循环写数据的时候直接控制,比如下面一个简单的写法:

 1  for (i = 0; i < table.Rows.Count; i++)
 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 - 11]).Merge(Missing.Value); //将第一列按投标单位合并
30                                 worksheet.get_Range(worksheet.Cells[5 + rowid, 1], worksheet.Cells[5 + rowid, 1]).Value2 = bidName + "(" + projNum.ToString() + "个项目)";//合并后的单元格内容

合并单元格的时候也要注意一个问题,就是合并的单元格必须是为空的,不然在执行合并时,会提示“合并后的单元格的值将丢失”,具体不这样提示的,大致是这个意思,一般我们合并都单元格相同的内容,在合并前我们先保存那个值,再清空后合并,上面的代码中把worksheet.Cell[5+rowid,1]这里系列的单元格的值空出来了,没写数据,而且最后合并了再写值,避免了去循环清空。
     2.嵌套的合并向上面那样做可能控制比较麻烦,而且思路可能很混乱,我们可以考虑先循环填充所有的数据,在循环出来要合并的列,比如像下面的这张表

先循环填充数据,如下:

 1  int index = 0, rownum = 0;
 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                             }

下面合并前三列相同内容的单元:

 1  //合并前三列操作
 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 - 11]).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 - 12]).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 - 13]).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 - 11]).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 - 12]).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 - 13]).Merge(Missing.Value);
37                             wksheet.get_Range(wksheet.Cells[rowid, 3], wksheet.Cells[rowid, 3]).Value2 = "'" + projName;

下面合并标段列

 1  //合并标段列
 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 - 14]).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 - 14]).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就可以了

 1  Workbooks workbooks = app.Workbooks;
 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),代码如下

 1  //////////////////////////////////////每月详细统计////////////////////////////////////
 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 - 11= rowNum;
15                                     ws.Cells[3 + rowNum - 12= tableMM.Rows[i]["PROJNO"];
16                                     ws.Cells[3 + rowNum - 13= tableMM.Rows[i]["PROJNAME"];
17                                     ws.Cells[3 + rowNum - 14= tableMM.Rows[i]["BID_TYPE"];
18                                     ws.Cells[3 + rowNum - 15= tableMM.Rows[i]["BID_MODE"];
19                                     ws.Cells[3 + rowNum - 16= tableMM.Rows[i]["OPENDT"];
20                                     ws.Cells[3 + rowNum - 17= tableMM.Rows[i]["OPENADDRESS"];
21                                     ws.Cells[3 + rowNum - 18= tableMM.Rows[i]["BID_UNIT"];
22                                     ws.Cells[3 + rowNum - 19= tableMM.Rows[i]["NOTICE_NO"].ToString().Replace("神华国贸""");
23                                     ws.Cells[3 + rowNum - 110= tableMM.Rows[i]["BOOKAMOUNT"];
24                                     ws.Cells[3 + rowNum - 111= tableMM.Rows[i]["BIDPRICE"+ "(万" + tableMM.Rows[i]["CURRENCY"+ ")";
25                                     ws.Cells[3 + rowNum - 112= tableMM.Rows[i]["BIDSER_AMOUNT"+ "(万" + tableMM.Rows[i]["CURRENCY"]+")";
26                                     ws.Cells[3 + rowNum - 113= tableMM.Rows[i]["AGT_AMOUNT"];
27                                     ws.Cells[3 + rowNum - 114= "";
28                                     ws.get_Range(ws.Cells[3 + rowNum - 11], ws.Cells[3 + rowNum - 114]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
29                                     continue;
30                                 }
31 
32                                 ws.Cells[11= 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 - 13= "合  计";
48                                 ws.Cells[3 + rowNum - 110= book_Amount;
49                                 ws.Cells[3 + rowNum - 111= bid_Amount;
50                                 ws.Cells[3 + rowNum - 112= bidser_Amount;
51                                 ws.Cells[3 + rowNum - 113= agent_Amount;
52                                 ws.get_Range(ws.Cells[3 + rowNum - 11], ws.Cells[3 + rowNum - 114]).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嵌套调用”,如下

 1  function ExcelReportCallback(resp)
 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  }

 

 1 function RedirectUrl()
 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()方法里的数据值调得让页面不显示,但是状态栏还是有显示的。

到这里算是写完了,决定奢侈下,放到首页下:),总觉得首页的文章只有高手才能放,而且放到首页也是一种奢侈,希望对园子里的某些人有一定的帮助吧~~
 上面给的地址由于服务器原因下不了了,我重新贴下博客园的下载地址(其实评论里早就贴了)
http://files.cnblogs.com/peaceli/ExcelReportDemo.rar
http://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);

   } 


 }

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:11189次
    • 积分:182
    • 等级:
    • 排名:千里之外
    • 原创:8篇
    • 转载:2篇
    • 译文:0篇
    • 评论:3条
    文章存档
    最新评论
    网站制作