DataSet/DataTable中数据导入到Excel
项目中经常遇到把数据导入到Excel,网上很多案例都是把gridview数据导入到Excel,很少有把DataTable,dataSet中数据导入到Excel
不同项目有不同数据和不同需求,现在以订单为例:
从ViewState中取出存储的DataTable数据
让后做相应的格式化;
代码:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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();