ASP.net導出Excel：服務器控件GridView
前臺：
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>人員工作記錄報表</title>
<style type="text/css" media="print">
.noprint{display : none;}
</style>
</head>
<body>
<form id="form1" runat="server">
<div style="margin-top: 10px; border:1px solid #000000;">
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="True"
Width="100%" EmptyDataText="沒有符合條件的資料!" Font-Bold="False" Font-Size="12px"
OnRowDataBound="GridView1_RowDataBound"
OnRowCreated="GridView1_RowCreated">
<AlternatingRowStyle BackColor="WhiteSmoke" />
<EditRowStyle BackColor="#FFFF80" />
<FooterStyle BackColor="Snow" />
<HeaderStyle BackColor="Yellow" />
</asp:GridView>
<asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="匯出Excel" CssClass="noprint" style=" BORDER: #B2B2B2 1px solid; LINE-HEIGHT: 150%;FONT-SIZE: 12px;CURSOR:hand;BACKGROUND-COLOR: #EFEFEF;" />
</div>
</form>
</body>
</html>
后臺：
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Text;
using System.IO;
public partial class test : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
#region
/*
SqlCommand Cmd = new SqlCommand("Query_AFU_FORM_YFGZ", conn);
Cmd.CommandType = CommandType.StoredProcedure;
//if (ActivityID == "")
//ActivityID = "1";
//SqlParameter sp = new SqlParameter();
//Cmd.Parameters.Add(sp);
SqlDataReader odrAll = Cmd.ExecuteReader();
GridView1.DataSource = odrAll;
GridView1.DataBind();
odrAll.Close();
SqlCommand Cmd2 = new SqlCommand("Query_AFU_FORM_YFGZ_UnChecked", conn);
Cmd2.CommandType = CommandType.StoredProcedure;
SqlParameter sp = new SqlParameter("@Year", 2008);
Cmd2.Parameters.Add(sp);
SqlParameter sp2 = new SqlParameter("@Month", 10);
Cmd2.Parameters.Add(sp2);
SqlDataReader odrAll2 = Cmd2.ExecuteReader();
GridView2.DataSource = odrAll2;
GridView2.DataBind();
odrAll2.Close();
*/
#endregion
string str = "server=.;database=Alice;Uid=sa;Pwd=sa"; //定義一個連接字符串；
SqlConnection sqlcon = new SqlConnection(str); //聲明一個SqlConnection對象；
sqlcon.Open(); //打開數據庫連接；
//從數據庫中讀取表中的數據，并存儲到DataSet對象中；
SqlDataAdapter sqlda = new SqlDataAdapter("select * from Supplies", sqlcon);
DataSet ds = new DataSet();
sqlda.Fill(ds);
//把從數據庫中讀取的數據寫到GridView中；
GridView1.DataSource = ds;
GridView1.DataBind();
//關閉數據庫；
sqlcon.Close();
}
protected void Button1_Click(object sender, EventArgs e)
{
Export("application/ms-excel", "FilledStatements.xls");
}
private void Export(string FileType, string FileName)
{
Response.Charset = "big5";
Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8).ToString());
Response.ContentType = FileType;
this.EnableViewState = false;
StringWriter tw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(tw);
GridView1.RenderControl(hw);
Response.Write(tw.ToString());
Response.End();
}
public override void VerifyRenderingInServerForm(Control control)
{
}
ArrayList st = new ArrayList();
ArrayList at = new ArrayList();
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
//if (e.Row.RowType == DataControlRowType.DataRow)
//{
// for (int i = 4; i < 9; i++)
// {
// String date = e.Row.Cells[i].Text;
// String date2 = "";
// String UniqueID = "";
// String applyID = e.Row.Cells[3].Text;
// if (date != " ")
// {
// date2 = date.Substring(date.IndexOf("-") + 1);
// UniqueID = date.Substring(0, date.IndexOf("-"));
// e.Row.Cells[i].Text = date2;
// e.Row.Cells[i].Attributes.Add("onclick", "javascript:location.href='ALCOR_CheckDateInfo_Ajax.aspx?date=" + date2 + "&applyID=" + applyID + "&UniqueID=" + UniqueID + "'");
// }
// }
//}
//if (e.Row.RowType == DataControlRowType.Header)
//{
// string USER_ID = "";
// string a_nam = "";
// if (Request.Cookies["USER_ID"] != null)
// { USER_ID = Request.Cookies["USER_ID"].Value; }
// if (Request.Cookies["_nAutoWebCookie_" + USER_ID + "_"] != null)
// {
// if (Request.Cookies["_nAutoWebCookie_" + USER_ID + "_"]["a_nam"] != null)
// {
// a_nam = Request.Cookies["_nAutoWebCookie_" + USER_ID + "_"]["a_nam"].ToString();
// }
// }
// a_nam = HttpUtility.UrlDecode(a_nam.ToString(), System.Text.Encoding.GetEncoding("big5"));
// TableCellCollection tcHeader = e.Row.Cells;
// tcHeader.Clear();
// tcHeader.Add(new TableHeaderCell());
// tcHeader[0].Attributes.Add("colspan", Convert.ToString(st.Count));
// tcHeader[0].Text = "BU";
// tcHeader.Add(new TableHeaderCell());
// tcHeader[1].Attributes.Add("colspan", Convert.ToString(st.Count));
// tcHeader[1].Text = "DeptCode";
// tcHeader.Add(new TableHeaderCell());
// tcHeader[2].Attributes.Add("colspan", Convert.ToString(at.Count));
// tcHeader[2].Text = "EmployeeName";
// tcHeader.Add(new TableHeaderCell());
// tcHeader[3].Attributes.Add("colspan", Convert.ToString(at.Count));
// tcHeader[3].Text = "EmployeeNo";
// tcHeader.Add(new TableHeaderCell());
// tcHeader[4].Attributes.Add("colspan", Convert.ToString(at.Count));
// tcHeader[4].Text = "1";
// tcHeader.Add(new TableHeaderCell());
// tcHeader[5].Attributes.Add("colspan", Convert.ToString(at.Count));
// tcHeader[5].Text = "2";
// tcHeader.Add(new TableHeaderCell());
// tcHeader[6].Attributes.Add("colspan", Convert.ToString(at.Count));
// tcHeader[6].Text = "3";
// tcHeader.Add(new TableHeaderCell());
// tcHeader[7].Attributes.Add("colspan", Convert.ToString(at.Count));
// tcHeader[7].Text = "4";
// tcHeader.Add(new TableHeaderCell());
// tcHeader[8].Attributes.Add("colspan", Convert.ToString(at.Count));
// tcHeader[8].Text = "5";
// tcHeader.Add(new TableHeaderCell());
// tcHeader[9].Attributes.Add("colspan", Convert.ToString(at.Count));
// tcHeader[9].Text = "TotalList";
//}
}
protected void GridView1_RowCreated(object sender, GridViewRowEventArgs e)
{
e.Row.HorizontalAlign = HorizontalAlign.Center;
/*
if (e.Row.RowType == DataControlRowType.Header)
{
st.Clear();
at.Clear();
for (int i = 0; i < e.Row.Cells.Count; i++)
{
if (e.Row.Cells[i].Text.Substring(e.Row.Cells[i].Text.Length - 1, 1) == "z")
{
e.Row.Cells[i].BackColor = System.Drawing.Color.GreenYellow;
st.Add(e.Row.Cells[i].Text.Remove(e.Row.Cells[i].Text.Length - 1));
}
if (e.Row.Cells[i].Text.Substring(e.Row.Cells[i].Text.Length - 1, 1) == "w")
{
e.Row.Cells[i].BackColor = System.Drawing.Color.GreenYellow;
at.Add(e.Row.Cells[i].Text.Remove(e.Row.Cells[i].Text.Length - 1));
}
}
}
*/
}
}
