[ASP.NET] GridView -> DataTable -> Excel 匯出

2010-10-07

15907
0
ASP.NET

摘要:[ASP.NET] GridView -> DataTable -> Excel 匯出

情況：要產生Excel餵製卡機，先由user輸入卻製卡的學號並在GridView上預覽，確認後輸出Excel
Step1.建立DataTable格式

//匯出Excel
//先建立表格格式
DataTable dt = new DataTable();
//定義DataTable欄位格式
dt.Columns.Add(new DataColumn("Col1", typeof(string)));
dt.Columns.Add(new DataColumn("Col2", typeof(string)));
dt.Columns.Add(new DataColumn("Col3", typeof(string)));
//標題列
DataRow drtitle = dt.NewRow();
drtitle[0] = "DEPT";
drtitle[1] = "STATUS";
drtitle[2] = "NAME";
dt.Rows.Add(drtitle);

Step2.抓GridView的資料

//抓GridView內的資料
for (int i = 0; i < this.gvList.Rows.Count; i++)
{
   GridViewRow grv = gvList.Rows[i];
   Label lbDept;
   Label lbChar;
   Label lbStuName;

   lbDept = (Label)grv.FindControl("gv_lbDeptName");
   lbChar = (Label)grv.FindControl("gv_lbChargeType");
   lbStuName = (Label)grv.FindControl("gv_lbStuCName");

   DataRow dr = dt.NewRow();
   dr[0] = lbDept.Text.Trim();
   dr[1] = lbChar.Text.Trim();
   dr[2] = lbStuName.Text.Trim();
   dt.Rows.Add(dr);
}

Step3.匯出Excel

GridView gv = new GridView();
gv.DataSource = dt;
gv.DataBind();
gv.HeaderRow.Visible = false;

Response.Clear();
Response.Buffer = true;
Response.Charset = "BIG5";
//所有欄位格式改為"文字"
Response.Write("<style>");
Response.Write("td{mso-number-format:\"\\@\";}"); 
Response.Write("</style>");

Response.AppendHeader("Content-Disposition", "attachment;filename=StuCard.xls");
//預設字集為BIG5
Response.ContentEncoding = System.Text.Encoding.GetEncoding("BIG5");
Response.ContentType = "application/ms-excel";
System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-TW", true);
System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad);
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
gv.RenderControl(oHtmlTextWriter);
Response.Write(oStringWriter.ToString());
Response.End();

雖然成功匯出Excel，但背後格式是由HTML產生，製卡機是用ODBC抓所以不吃......殘念 Orz

回首頁