以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.

View Code

< 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.

View Code

protected void Page_Load( object sender, EventArgs e)
{
LoadReport();
}
protected void 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.

View Code

protected void 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

转载于:https://www.cnblogs.com/Dannier/archive/2011/03/17/excel.html