注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

Bioinformatics home

 
 
 

日志

 
 

asp.net excel six  

2008-06-02 00:18:57|  分类: 编程 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

 3.生成的表格包含多个sheet的操作,比如下面一种情况

asp.net excel six - xiaofeng1982 - Tiger-Leon

 

绘制这张表的要求是根据选择某年的几月到几月,生成这个几个月的一个综合情况的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 - 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语句。。。 呵呵

  评论这张
 
阅读(525)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017