ASP.NET導出Excel報表(不用考慮Excel版本)

 

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

//前臺代碼

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

//解決導出報表時異常報錯
public override void VerifyRenderingInServerForm(Control control)
{
//EnableEventValidation="false"，頁面的屬性可以不設置
//注意：解決異常(Control '' of type 'GridView' must be placed inside a form tag with runat=server.)
//base.VerifyRenderingInServerForm(control);
}

 

protected void btnExportExcel_Click(object sender, EventArgs e)
{
string fileName = "Report" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls";

#region 獲取數據集
DataTable dt = new DataTable();

mTest = new Ttest();
dt = mTest.GetAllNGRecord_SA(); //獲取數據集
#endregion

//動態創建gridview，作為數據容器
GridView gv = new GridView();
gv.Page = this.Page; //注意：因為gridview是動態創建的，所以必須傳遞當前Page，否則導出時會報異常

//Ttest.GridViewBind(gv, dt, ""); //先綁定，后綁定都可以

mTest.ExportToExcel(gv, dt, fileName); //導出excel
}

 

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

//後臺代碼

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

/// <summary>
/// 導出Excel報表，無需考慮Excel版本問題
/// </summary>
/// <param name="gv">gridview作為數據容器，可動態創建</param>
/// <param name="dt">datatable存放數據集</param>
/// <param name="fileName">excel文件名</param>
public void ExportToExcel(GridView gv, System.Data.DataTable dt, string fileName)
{
try
{
//gridview填充數據
GridViewBind(gv, dt, "");

gv.Attributes.Add("style", "vnd.ms-excel numberformat:@");
gv.AllowSorting = false;
gv.AllowPaging = false;
//string style = @"<style> .text { mso-number-format:\@; } </style> ";//将gridview中的数据在excel中以文本格式显示否则001excel默认显示1
gv.HeaderStyle.BackColor = Color.LightGray;//using System.Drawing;
gv.HeaderStyle.Font.Bold = true;
gv.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
//gv.Columns[0].HeaderStyle.Width = 120;
//gv.Columns[1].HeaderStyle.Width = 150;
//gv.Columns[2].HeaderStyle.Width = 50;
//gv.Columns[3].HeaderStyle.Width = 150;
//gv.Columns[4].HeaderStyle.Width = 150;
//gv.DataSource = dt;
//gv.DataBind();

HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Buffer = true;//解析完再输出
HttpContext.Current.Response.Charset = "Big5";
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
HttpContext.Current.Response.AddHeader("content-disposition", "online;filename=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8).ToString());//防止中文文件名乱码
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";

gv.AllowPaging = true;
gv.PageIndex = 0;
//gv.PageCount = 10;

gv.Page.EnableViewState = false; //注意：gridview的Page屬性必須傳遞進來，否則報異常
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);
//HttpContext.Current.Response.Write(style);
//HttpContext.Current.Response.Write(oStringWriter.ToString());
HttpContext.Current.Response.Write("<meta http-equiv=\"content-type\" content=\"application/ms-excel; charset=UTF-8\"/>" + oStringWriter.ToString());//注意：解決中文亂碼
HttpContext.Current.Response.End(); //注意：雖然單步執行有異常，但必須要調用這個，否則會導出整個頁面的信息
//HttpContext.Current.ApplicationInstance.CompleteRequest();

}
catch (Exception ex)
{
throw (ex);
}
}

 

/// <summary>
/// 數據集綁定gridview
/// </summary>
/// <param name="gdv">gridview</param>
/// <param name="dtDataSource">數據集</param>
/// <param name="strDataKey">似乎是數據集的主鍵，此方法沒有用到</param>
private void GridViewBind(GridView gdv, DataTable dtDataSource, string strDataKey)
{
gdv.Columns.Clear();

if (dtDataSource.Rows.Count == 0)
{
DataTable dtTmp = dtDataSource.Clone();
dtTmp.Rows.Add(dtTmp.NewRow());
gdv.DataSource = dtTmp;
gdv.DataBind();
int columnCount = dtTmp.Columns.Count;
gdv.Rows[0].Cells.Clear();
gdv.Rows[0].Cells.Add(new TableCell());
gdv.Rows[0].Cells[0].ColumnSpan = columnCount;
gdv.Rows[0].Cells[0].Style.Add("text-align", "center");
gdv.Rows[0].Cells[0].Text = "沒有任何數據";
}
else
{
gdv.AutoGenerateColumns = false;
gdv.DataSource = dtDataSource;
//gdv.DataKeyNames = new string[] { strDataKey };

for (int i = 0; i < dtDataSource.Columns.Count; i++) //绑定普通数据列
{
BoundField bfColumn = new BoundField();
bfColumn.DataField = dtDataSource.Columns[i].ColumnName;
bfColumn.HeaderText = dtDataSource.Columns[i].Caption;
gdv.Columns.Add(bfColumn);
}

//gdv.Columns[1].Visible = false;

//CommandField cfModify = new CommandField(); //绑定命令列
//cfModify.ButtonType = ButtonType.Button;
//cfModify.SelectText = "修改";
//cfModify.ShowSelectButton = true;
//gdv.Columns.Add(cfModify);

gdv.DataBind();
}
}