将数据导出成EXCEL格式
(1)gridview导出
protected void Page_Load(object sender, EventArgs e) { GridViewBind(""); } private void ToExcel(string FileType, string FileName) { Response.Charset = "utf-8"; 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.Flush(); Response.End(); } //页面加载 public void GridViewBind(string str) { conn = new SqlConnection(@"server=.\;database=ktjx;uid=sa;pwd=118114"); SqlCommand sd = new SqlCommand("select * from User_Info", conn); SqlDataAdapter sda = new SqlDataAdapter(); sda.SelectCommand = sd; ds = new DataSet(); sda.Fill(ds); GridView1.DataSource = ds; GridView1.DataBind(); //List<Student> Items = new List<Student>(); //for (int i = 0; i < 100; i++) //{ // Items.Add(new Student() { Id = 100 + i, Name = "Peter杨" + i }); //} //GridView1.DataSource = Items; //GridView1.DataBind(); } //实现GridView分页 protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e) { GridView1.PageIndex = e.NewPageIndex; GridViewBind(""); } public override void VerifyRenderingInServerForm(Control control) { } protected void bt_ExportExcel_Click(object sender, EventArgs e) { GridView1.AllowPaging = false; GridViewBind(""); ToExcel("application/ms-excel", "用户表.xls"); GridView1.AllowPaging = true; GridViewBind(""); CreateExcelds(ds, "1", "用户名.xls"); }
这种方法不适用于List集合的绑定,导出来的只要是中文全是乱码!希望有大牛指点一二!
(2)List<>导出
static List<Student> Items; protected void Page_Load(object sender, EventArgs e) { Bind(); } private void Bind() { Items = new List<Student>(); for (int i = 0; i < 100; i++) { Student Model = new Student() { Id = 100 + i, Name = "Peter杨" + i }; Items.Add(Model); } GridView1.DataSource = Items.ToList(); GridView1.DataBind(); } protected void bt_ExportExcel_Click(object sender, EventArgs e) { CreateExcelList<Student>(Items, "用户表.xls"); } /// <summary> /// 由List导出excel /// </summary> /// <param name="ds"></param> /// <param name="typeid"></param> /// <param name="FileName"></param> public void CreateExcelList<T>(List<T> Items, string FileName) { Type t = typeof(T); HttpResponse resp; resp = Page.Response; resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); resp.AppendHeader("Content-Disposition", "attachment;filename=" + FileName); string colHeaders = "", ls_item = ""; int i = 0; //取得数据表各列标题,各标题之间以 分割,最后一个列标题后加回车符 for (i = 0; i < t.GetProperties().Count(); i++) { if (i != t.GetProperties().Count() - 1) { colHeaders += t.GetProperties()[i].Name + "\t"; } else { colHeaders += t.GetProperties()[i].Name + "\n"; } } //向HTTP输出流中写入取得的数据信息 resp.Write(colHeaders); //逐行处理数据 foreach (var item in Items) { var ps = t.GetProperties(); for (int x = 0; x < ps.Count(); x++) { if (x != t.GetProperties().Count() - 1) { ls_item += (ps[x].GetValue(item, null) ?? "").ToString() + "\t"; } else { ls_item += (ps[x].GetValue(item, null) ?? "").ToString() + "\n"; } } //当前行数据写入HTTP输出流,并且置空ls_item以便下行数据 resp.Write(ls_item); ls_item = ""; } //写缓冲区中的数据到HTTP头文件中 resp.End(); }