DataSet/DataTable中数据导入到Excel

项目中经常遇到把数据导入到Excel，网上很多案例都是把gridview数据导入到Excel，很少有把DataTable，dataSet中数据导入到Excel

不同项目有不同数据和不同需求，现在以订单为例：

从ViewState中取出存储的DataTable数据

让后做相应的格式化；

代码：

导出Excel

 1 if (ViewState["dt"] == null)
 2         {
 3             return;
 4         }
 5         DataTable dt = (DataTable)ViewState["dt"];
 6         if (dt == null || dt.Rows.Count <= 0)
 7         {
 8             return;
 9         }
10         DataTable newdt = new DataTable();
11         newdt.Columns.Add("产品名称", typeof(string));
12         newdt.Columns.Add("产品编号", typeof(string));
13         newdt.Columns.Add("规格", typeof(string));
14         newdt.Columns.Add("单价", typeof(string));
15         newdt.Columns.Add("数量", typeof(string));
16         newdt.Columns.Add("金额", typeof(string));
17         newdt.Columns.Add("供应商", typeof(string));
18         newdt.Columns.Add("采购申请人", typeof(string));
19         newdt.Columns.Add("备注", typeof(string));
20         newdt.Columns.Add("提交时间", typeof(string));
21         for (int i = 0; i < dt.Rows.Count; i++)
22         {
23             DataRow dr = newdt.NewRow();
24             dr["产品名称"] = dt.Rows[i]["PRODUCTNAME"].ToString();
25             dr["产品编号"] = dt.Rows[i]["PRODUCTID"].ToString();
26             dr["规格"] = dt.Rows[i]["PURITY"].ToString();
27             dr["单价"] = dt.Rows[i]["UNITPRICE"].ToString();
28             dr["数量"] = dt.Rows[i]["QUANTITY"].ToString();
29             dr["金额"] = dt.Rows[i]["SUMPRICE"].ToString();
30             dr["供应商"] = dt.Rows[i]["SUPPLYNAME"].ToString();
31             dr["采购申请人"] = dt.Rows[i]["FORPERSON"].ToString();
32             dr["备注"] = dt.Rows[i]["BEIZHU"].ToString();
33             dr["提交时间"] = dt.Rows[i]["CREATETIME"].ToString();
34             newdt.Rows.Add(dr);
35         }
36         StringWriter sw = new StringWriter();
37         string str = "";
38         //构建Excel 表头
39         for (int i = 0; i < newdt.Columns.Count; i++)
40         {
41             str += newdt.Columns[i].ColumnName + "\t";
42         }
43         str = str.Substring(0, str.Length - 1);
44         sw.WriteLine(str);
45         //构建Excel数据部分
46         for (int i = 0; i < newdt.Rows.Count; i++)
47         {
48             str = "";
49             for (int j = 0; j < newdt.Columns.Count; j++)
50             {
51                 str += newdt.Rows[i][newdt.Columns[j].ColumnName].ToString() + "\t";
52             }
53             str = str.Substring(0, str.Length - 1);
54             sw.WriteLine(str);
55         }
56         //输出到Excel
57         Response.Clear();
58         Response.AddHeader("Content-disposition", "attachment;filename=outOrginOrderInfos.xls");
59         Response.ContentEncoding = Encoding.GetEncoding("gb2312");
60         Response.ContentType = "application/ms-excel";
61         Response.Charset = "gb2312";
62         Response.Write(sw.ToString());
63         sw.Close();
64         Response.Flush();
65         Response.End();

posted on 2012-06-09 17:05 梦里故乡阅读(702) 评论(0) 编辑收藏举报

刷新页面返回顶部

工作中点滴记录

公告