[ASP.NET][WebControl] GridView簡易匯出Excel
簡易方式匯出就是透過 GridView 控制項所輸出的 HTML,輸出的檔案存成*.xls然後就Excel可以去開啟檔案了,這個匯出方式比較簡易
我將匯出再調整為直接透過GridView 控制項所輸出的 HTML與按照GridView裡面資料自行調整HTML
直接透過GridView 控制項所輸出的 HTML,記得要將EnableEventValidation="false"和覆寫
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Button ID="btn" runat="server" Text="ExportToExcel" onclick="btn_Click" />
</div>
<div>
<asp:GridView ID="GridView1" runat="server" PageSize="2" AllowPaging="True"
AutoGenerateColumns="False" onpageindexchanging="GridView1_PageIndexChanging">
<Columns>
<asp:BoundField DataField="EmployeeID" HeaderText="編號" SortExpression="EmployeeID" />
<asp:BoundField DataField="LastName" HeaderText="姓氏" SortExpression="LastName" />
<asp:BoundField DataField="FirstName" HeaderText="名" SortExpression="FirstName" />
<asp:BoundField DataField="Title" HeaderText="標題" SortExpression="Title" />
</Columns>
</asp:GridView>
</div>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:DBConnection %>" ></asp:SqlDataSource>
</form>
</body>
</html>
{
string filename = "SampleExcelFile";
string strfileext = ".xls";
StringWriter tw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(tw);
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment; filename=" + filename + strfileext);
HttpContext.Current.Response.Write("<meta http-equiv=Content-Type content=text/html;charset=utf-8>");
//先把分頁關掉
GridView1.AllowPaging = false;
bindgv();
//Get the HTML for the control.
GridView1.RenderControl(hw);
HttpContext.Current.Response.Write(tw.ToString());
HttpContext.Current.Response.End();
GridView1.AllowPaging = true;
bindgv();
}
//必須覆寫 Page.VerifyRenderingInServerForm
public override void VerifyRenderingInServerForm(Control control)
{
}
匯出結果
另一個方式還是透過GridView,不過自己編寫HTML
{
string filename = "SampleExcelFile";
string title = " Excel Title ";
string html = "<html><head><meta http-equiv=\"Content-Type\" content=\"text/html; charset=big5\">"
+ "<title>新增網頁1</title></head>" + title + "<br>";
html += "<table border=\"1\">";
//表頭
html += "<tr>";
for (int i = 0; i < GridView1.Columns.Count; i++)
{
if (!GridView1.Columns[i].Visible)
{
continue;
}
html += "<td bgcolor=\"#E4E4E4\">" + GridView1.HeaderRow.Cells[i].Text;
}
html += "</tr>";
//先把分頁關掉
GridView1.AllowPaging = false;
bindgv();
//內容
for (int i = 0; i < GridView1.Rows.Count; i++)
{
html += "<tr>";
for (int c = 0; c < GridView1.Columns.Count; c++)
{
if (!GridView1.Columns[c].Visible)
{
continue;
}
if (GridView1.Rows[i].Cells[c].Controls.Count > 0)
{
//html += "<td>" + ((Label)gv.Rows[i].Cells[c].Controls[1]).Text + "</td>";
html += "<td>" + GridView1.Rows[i].Cells[c].Text + "</td>";
}
else
{
html += "<td>" + GridView1.Rows[i].Cells[c].Text + "</td>";
}
}
html += "</tr>";
}
html += "</table>";
GridView1.AllowPaging = true;
bindgv();
string sContentDisposition = "";
sContentDisposition += "attachment; "; // 強制存檔,未設定則依瀏覽器預設開啟或存檔
sContentDisposition += "filename=" + HttpUtility.UrlEncode(filename, System.Text.Encoding.UTF8) + ".xls"; //設定檔名可為中文_#1
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("Content-disposition", sContentDisposition);
HttpContext.Current.Response.ContentType = "application/vnd.ms-excle";
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
//utt8 to ascii
Byte[] bytes = System.Text.Encoding.Unicode.GetBytes(html);
Byte[] outByte = System.Text.Encoding.Convert(System.Text.Encoding.Unicode, System.Text.Encoding.Default, bytes);
HttpContext.Current.Response.BinaryWrite(outByte);
HttpContext.Current.Response.End();
}
匯出結果
這個簡易匯出方式,都是透過GridView,也可直接透過DataTable取得資料去進行匯出,反正都是簡易的匯出方式