ASP.NET下导出Excel 以及在Ajax下面的实现

正常导出Excel代码

1 using System;
2 using System.Data;
3 using System.Configuration;
4 using System.Web;
5 using System.Web.Security;
6 using System.Web.UI;
7 using System.Web.UI.WebControls;
8 using System.Web.UI.WebControls.WebParts;
9 using System.Web.UI.HtmlControls;
10 using System.Data.SqlClient;
11 using System.IO;
12 using System.Threading;
13
14 public partial class _Default : System.Web.UI.Page
15 {
16    protected void Page_Load(object sender, EventArgs e)
17    {
18
19    }
20    protected void btnOK_Click(object sender, EventArgs e)
21    {
22        Thread.Sleep(5000);
23        bind();
24    }
25
26   
27
28    protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
29    {
30        GridView1.PageIndex = e.NewPageIndex;
31        bind();
32    }
33
34    protected void bind()
35    {
36        SqlConnection con = new SqlConnection("server=.;database=Northwind;uid=sa;pwd=;");
37        con.Open();
38        SqlDataAdapter sda = new SqlDataAdapter("select CustomerID,ContactName,ContactTitle,City from Customers", con);
39        DataSet ds = new DataSet();
40        sda.Fill(ds);
41        this.GridView1.DataSource = ds.Tables[0];
42        this.GridView1.DataBind();
43    }
44    protected void btnExcel_Click(object sender, EventArgs e)
45    {
46        Thread.Sleep(2000);
47        SqlConnection con = new SqlConnection("server=.;database=Northwind;uid=sa;pwd=;");
48        con.Open();
49        SqlDataAdapter sda = new SqlDataAdapter("select CustomerID,ContactName,ContactTitle,City from Customers", con);
50        DataSet ds = new DataSet();
51        sda.Fill(ds);
52        DataTable dt = ds.Tables[0];
53
54        StringWriter sw = new StringWriter();
55        string sWriteLine = "";
56        string sExcelFileName = "Excel.xls";
57        int i = 0;
58        //写标题
59        for (i = 0; i < dt.Columns.Count - 1; i++)
60        {
61            sWriteLine += dt.Columns[i].ColumnName.ToString().Trim() + "\t";
62        }
63        sWriteLine += dt.Columns[dt.Columns.Count - 1].ColumnName.ToString().Trim();
64        sw.WriteLine(sWriteLine);
65        //写内容
66        foreach (DataRow dr in dt.Rows)
67        {
68            sWriteLine = "";
69            for (i = 0; i < dt.Columns.Count - 1; i++)
70            {
71                sWriteLine += dr[i].ToString().Trim() + "\t";
72            }
73            sWriteLine += dr[dt.Columns.Count - 1].ToString().Trim();
74            sw.WriteLine(sWriteLine);
75        }
76        sw.Close();
77        ds.Dispose();
78        con.Close();
79
80
81        Page.Response.Clear();
82        // 防止中文内容为乱码
83        Page.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
84        //可令中文文件名不为乱码
85        Page.Response.AppendHeader("content-disposition", "attachment;filename=" + HttpUtility.UrlPathEncode(sExcelFileName));
86        System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN", true);
87        Page.Response.ContentType = "application/ms-excel";
88        HttpContext.Current.Response.Charset = "gb2312";//编码，根据需要修改
89
90        Page.Response.Write(sw);
91        Page.Response.End();
92    }
93}
94

前台页面

<% @ Page Language = " C# " AutoEventWireup = " true "   CodeFile = " Default.aspx.cs " Inherits = " _Default " %>

<% @ Register Assembly = " System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35 "
    Namespace = " System.Web.UI " TagPrefix = " asp " %>

<! DOCTYPE html PUBLIC " -//W3C//DTD XHTML 1.0 Transitional//EN " " http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd " >

< html xmlns = " http://www.w3.org/1999/xhtml " >
< head runat = " server " >
    < title > 无标题页 </ title >
</ head >
< body >
    < form id = " form1 " runat = " server " >
        < asp:ScriptManager ID = " ScriptManager1 " runat = " server " >
        </ asp:ScriptManager >
        < asp:UpdatePanel ID = " UpdatePanel1 " runat = " server " >
            < ContentTemplate >
        < div >
        < asp:Button ID = " btnOK " runat = " server " OnClick = " btnOK_Click " Text = " 获取 " />
        < asp:Button ID = " btnExcel " runat = " server " OnClick = " btnExcel_Click " Text = " Excel " />< br />
        & nbsp; < asp:GridView ID = " GridView1 " runat = " server " AllowPaging = " True " AllowSorting = " True "
            CellPadding = " 4 " ForeColor = " #333333 " GridLines = " None " Height = " 282px " OnPageIndexChanging = " GridView1_PageIndexChanging "
            Width = " 250px " >
            < FooterStyle BackColor = " #507CD1 " Font - Bold = " True " ForeColor = " White " />
            < RowStyle BackColor = " #EFF3FB " />
            < PagerStyle BackColor = " #2461BF " ForeColor = " White " HorizontalAlign = " Center " />
            < SelectedRowStyle BackColor = " #D1DDF1 " Font - Bold = " True " ForeColor = " #333333 " />
            < HeaderStyle BackColor = " #507CD1 " Font - Bold = " True " ForeColor = " White " />
            < EditRowStyle BackColor = " #2461BF " />
            < AlternatingRowStyle BackColor = " White " />
        </ asp:GridView >
        </ div >
           </ ContentTemplate >
           < Triggers >
            < asp:PostBackTrigger ControlID = " btnExcel " />
           </ Triggers >
        </ asp:UpdatePanel >
        < asp:UpdateProgress ID = " UpdateProgress1 " runat = " server " >
            < ProgressTemplate >
                程序加载中Loading
            </ ProgressTemplate >
        </ asp:UpdateProgress >
        < br />
    </ form >
</ body >
</ html >

实际上Ajax下面实现导出Excel 就是添加Triggers

< Triggers >
            < asp:PostBackTrigger ControlID = " btnExcel " />
           </ Triggers >

OK