DataTable导出excel 设置单元格格式

 1 public static void DataTableExcel(DataTable dtData, String FileName)
 2 {
 3     System.Web.UI.WebControls.GridView dgExport = null;
 4     System.Web.HttpContext curContext = System.Web.HttpContext.Current;
 5     System.IO.StringWriter strWriter = null;
 6     System.Web.UI.HtmlTextWriter htmlWriter = null;
 7 
 8     if (dtData != null)
 9     {
10         //设置编码和附件格式 作用是中文文件名乱码
11         //System.Web.HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8)
12         curContext.Response.ContentType = "application/ms-excel"; // "application nd.ms-excel";
13         curContext.Response.ContentEncoding = System.Text.Encoding.Default;
14         curContext.Response.Charset = "gb2312";
15         curContext.Response.AddHeader("content-disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8) + ".xls");
16         //导出Excel文件 
17         strWriter = new System.IO.StringWriter();
18         htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter);
19 
20         //为了解决dgData中可能进行了分页的情况,需要重新定义一个无分页的GridView 
21         dgExport = new System.Web.UI.WebControls.GridView();
22         dgExport.DataSource = dtData;
23         dgExport.AllowPaging = false;
24         dgExport.DataBind();
25 
26         //设置导出格式为文本格式防止丢失数据
27         dgExport.Attributes.Add("style", "vnd.ms-excel.numberformat:@");
28 
29         //设置excel单元格格式防止转换数据丢失数字
30         //for (int i = 0; i < dtData.Rows.Count; i++)
31         //{
32         //    for (int j = 0; j < dtData.Columns.Count; j++)
33         //    {
34         //        dgExport.Rows[i].Cells[j].Attributes.Add("style", "vnd.ms-excel.numberformat:@");
35         //    }
36         //}
37 
38         //下载到客户端 
39         dgExport.RenderControl(htmlWriter);
40         curContext.Response.Write("<meta http-equiv=\"Content-Type\" content=\"text/html; charset=gb2312\" />" + strWriter.ToString());
41         curContext.Response.End();
42     }

1 DataTable datSource;//数据源表
2 //过滤表
3 DataView davTemp = new DataView(datSource, "过滤条件", "排序字段", DataViewRowState.各种 状态);
4 //把过滤后的表赋给新表
5 DataTable datNew = davTemp.ToTable();

1 DataTable datSource;//数据源表
2 DataTable datNew= datSource.DefaultView.ToTable(false, new string[] { "列名", "列名" .....});

1 DataTable datSource;
2 DataTable datNew;
3 datSource.Rows[i].ItemArray= datNew. Rows[i].ItemArray;

1 DataTable dat = new DataTable();
2 //添加三列
3 dat.Columns.Add("col1");
4 dat.Columns.Add("col2");
5 dat.Columns.Add("col3");
6 //添加一行数据
7 dat.Rows.Add(1,2,3);
8 //把第三列放到第一的位置
9 dat.Columns["col3"].SetOrdinal(0);