[ASP.NET][WebControl] GridView簡易匯出Excel

[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)
    {
    }

 

 

匯出結果

image

 

 

另一個方式還是透過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();
    }

匯出結果

image

 

這個簡易匯出方式,都是透過GridView,也可直接透過DataTable取得資料去進行匯出,反正都是簡易的匯出方式

 

範例檔案ExportToExcel.rar

 

Dotblogs 的標籤: ,,