Asp.net导出Execl
1 public void importReportShops_toExecl()
2 {
3 //动态创建一个DataTable
4 DataTable dt_New = new DataTable();
5 DataColumn dc1 = new DataColumn("ShopName", Type.GetType("System.String"));
6 DataColumn dc2 = new DataColumn("TotalMoney", Type.GetType("System.String"));
7 DataColumn dc3 = new DataColumn("CashMoney", Type.GetType("System.String"));
8 DataColumn dc4 = new DataColumn("CardMoney", Type.GetType("System.String"));
9 DataColumn dc5 = new DataColumn("RechageMoney", Type.GetType("System.String"));
10 DataColumn dc6 = new DataColumn("TotalPoint", Type.GetType("System.String"));
11 DataColumn dc7 = new DataColumn("GiftNumber", Type.GetType("System.String"));
12 DataColumn dc8 = new DataColumn("MemNumber", Type.GetType("System.String"));
13 dt_New.Columns.Add(dc1);
14 dt_New.Columns.Add(dc2);
15 dt_New.Columns.Add(dc3);
16 dt_New.Columns.Add(dc4);
17 dt_New.Columns.Add(dc5);
18 dt_New.Columns.Add(dc6);
19 dt_New.Columns.Add(dc7);
20 dt_New.Columns.Add(dc8);
21
22 string timeStart = page.Request["reportExHistory_Start"] != null ? page.Request["reportExHistory_Start"].ToString() : "";
23 string timeEnd = page.Request["reportExHistory_End"] != null ? page.Request["reportExHistory_End"].ToString() : "";
24 string shopID = page.Request["GoodLogtShop"] != null ? page.Request["GoodLogtShop"].ToString() : "";
25
26 StringBuilder sbWhere = new StringBuilder();
27 sbWhere.Append("1=1");
28 if (currentShop != "" & shopID != "null") //如果是分店
29 {
30 sbWhere.AppendFormat(" and ID='{0}'", currentShop);
31 }
32 Shop shop = new Shop();
33 int resCount;
34 DataTable dt = shop.GetList(sbWhere.ToString()).Tables[0];
35 foreach (DataRow dr in dt.Rows)
36 {
37 DataRow dr_New = dt_New.NewRow();
38 dr_New["ShopName"] = dr["ShopName"].ToString();
39 dr_New["TotalMoney"] = GetShopSumMoney(timeStart, timeEnd, dr["ID"].ToString());
40 dr_New["CashMoney"] = GetShopCrashMoney(timeStart, timeEnd, dr["ID"].ToString());
41 dr_New["CardMoney"] = GetShopCardMoney(timeStart, timeEnd, dr["ID"].ToString());
42 dr_New["RechageMoney"] = GetShopRechageMoney(timeStart, timeEnd, dr["ID"].ToString());
43 dr_New["TotalPoint"] = GetShopPointM(timeStart, timeEnd, dr["ID"].ToString());
44 dr_New["GiftNumber"] = GetShopGift(timeStart, timeEnd, dr["ID"].ToString());
45 dr_New["MemNumber"] = GetMemcardNum(timeStart, timeEnd, dr["ID"].ToString());
46 dt_New.Rows.Add(dr_New);
47 }
48 string str = "";
49 str = string.Format("<meta http-equiv=\"content-type\" content=\"application/ms-excel; charset={0}\"/>", System.Text.Encoding.UTF8) + Environment.NewLine;
50 str += "<table cellspacing='0' rules='all' border='1' style='border-collapse:collapse;' bordercolor='#cccccc'>";
51 str += "<tr><td colspan='8' style='text-align:center; '><h3><strong>分店汇总报表</h3></td></tr>";
52 str += "<tr><td colspan='8' style='text-align:center; '><strong>制表人:" + page.appMaster.Name + " 制表时间:" + DateTime.Now + "</strong></td></tr>";
53 str += "<tr style='color:red'><td>店名</td><td>总营业额</td><td>现金收款</td><td>卡内扣款</td><td>会员存款</td><td>消费得积分</td><td>礼品兑换</td><td>发卡量</td></tr>";
54 foreach (DataRow dr in dt_New.Rows)
55 {
56 str += "<tr style='text-align:left;'><td style='width:150px'>" + dr["ShopName"] + " </td><td style='width:150px'>" + dr["TotalMoney"] + "</td><td style='width:100px'>" + dr["CashMoney"] + "</td><td style='width:100px'>" + dr["CardMoney"] + "</td><td style='width:150px'>" + dr["RechageMoney"] + "</td><td style='width:150px'>" + dr["TotalPoint"] + "</td><td style='width:150px'>" + dr["GiftNumber"] + "</td><td style='width:150px'>" + dr["MemNumber"] + "</td></tr>";
57
58 }
59
60 str += "</table>";
61 ExportExcel(str, "分店总汇报表.xls", 100, System.Text.Encoding.UTF8);
62 }
编辑器加载中...