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 - 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语句。。。 呵呵
上一页 [1] [2] [3] [4] [5] [6] [7] 下一页
【责编:Ken】
相关文章
相关产品和培训
文章评论
您现在的位置： 
2008-4-15
