以HTML格式导出Excel报表

Hi man, welcome to to this example of creating a sample report using C#.

In this artical, it will cover how to export a report to a excel file and how to farmat the data came from a sql database.

Below pic is the screen shot of this example.

As what u are seeing. I wil use a GridView to produce this report. Here is the HTML Code I used in this sample.

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
        CellPadding="4" ForeColor="#333333" GridLines="None" Width="317px">
        <AlternatingRowStyle BackColor="White"/>
        <Columns>
            <asp:BoundField DataField="ID" HeaderText="ID"/>
            <asp:BoundField DataField="EMPNO" HeaderText="EMPNO"/>
            <asp:BoundField DataField="NAME" HeaderText="NAME"/>
            <asp:BoundField DataField="AGE" HeaderText="AGE"/>
        </Columns>
        <EditRowStyle BackColor="#7C6F57"/>
        <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White"/>
        <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White"/>
        <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center"/>
        <RowStyle BackColor="#E3EAEB" HorizontalAlign ="Center"/>
        <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333"/>
        <SortedAscendingCellStyle BackColor="#F8FAFA"/>
        <SortedAscendingHeaderStyle BackColor="#246B61"/>
        <SortedDescendingCellStyle BackColor="#D4DFE1"/>
        <SortedDescendingHeaderStyle BackColor="#15524A"/>
    </asp:GridView>
    <asp:Button ID="btnExport" runat="server" onclick="btnExport_Click" 
        Text="Export to Excel"/>

How to bind the data to GirdView? Here is the source code if you are interested.

protectedvoid Page_Load(object sender, EventArgs e)
        {
            LoadReport();
        }
        protectedvoid LoadReport() {
            string cmdText ="";
            string connString = ConfigurationManager.ConnectionStrings["testConnectionString"].ToString();

            StringBuilder sbuilder =new StringBuilder("Select ");
            sbuilder.AppendLine("ID");
            sbuilder.AppendLine(",EMPNO");
            sbuilder.AppendLine(",NAME");
            sbuilder.AppendLine(",AGE");
            sbuilder.AppendLine("From TEST");

            cmdText = sbuilder.ToString();
            DataSet ds =new DataSet();
            using (SqlConnection conn =new SqlConnection(connString))
            {
                SqlCommand cmd =new SqlCommand(cmdText,conn);
                SqlDataAdapter da =new SqlDataAdapter(cmd);
                da.Fill(ds);
            }
            GridView1.DataSource = ds.Tables[0];
            GridView1.DataBind();
        }

What's the next? Right, we need to export it to an excel. I directly write the data as Html to excel file, and also you can include the data format. You can using CSS to format your report. In the following source code, I provided the simplest and most brife code snap.

protectedvoid btnExport_Click(object sender, EventArgs e)
        {
            string attachment ="attachment; filename= Test Report"+ DateTime.Now +".xls";
            if (GridView1.Rows.Count +1<65536){
                GridView1.AllowPaging =false;
                StringWriter sw =new StringWriter();
                HtmlTextWriter htw =new HtmlTextWriter(sw);
                Response.ContentType ="";
                HtmlForm frm =new HtmlForm();

                //start the excel output
                Response.ContentType ="application/vnd.ms-excel";
                Response.AddHeader("content-disposition", attachment);
                Response.Charset ="";

                Controls.Add(frm);
                frm.Controls.Add(GridView1);
                frm.RenderControl(htw);

                Response.Write(sw.ToString());
                Response.End();
            }
            else {
                Response.Write("<script>alert('Too many rows!')</script>");
            }
        }

Now, have you got the correct result? Help this article will help you. :)

If you want to know how to format your data, pls go to this link: http://www.cnblogs.com/Dannier/archive/2011/03/09/1978632.html