GridView数据导入Excel/Excel数据读入GridView

18.GridView数据导入Excel/Excel数据读入GridView

效果图：

解决方案：
页面增加一个按钮，单击事件添加如下方法：

 1protected void Button1_Click(object sender, EventArgs e)
 2{
 3    Export("application/ms-excel", "学生成绩报表.xls");
 4}
 5private void Export(string FileType, string FileName)
 6{
 7    Response.Charset = "GB2312";
 8    Response.ContentEncoding = System.Text.Encoding.UTF7;
 9    Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8).ToString());
10    Response.ContentType = FileType;
11    this.EnableViewState = false;
12    StringWriter tw = new StringWriter();
13    HtmlTextWriter hw = new HtmlTextWriter(tw);
14    GridView1.RenderControl(hw);
15    Response.Write(tw.ToString());
16    Response.End();
17}
18//如果没有下面方法会报错类型“GridView”的控件“GridView1”必须放在具有 runat=server 的窗体标记内
19public override void VerifyRenderingInServerForm(Control control)
20{
21}

还有由于是文件操作所以要引入名称空间IO和Text

 1using System;
 2using System.Data;
 3using System.Configuration;
 4using System.Web;
 5using System.Web.Security;
 6using System.Web.UI;
 7using System.Web.UI.WebControls;
 8using System.Web.UI.WebControls.WebParts;
 9using System.Web.UI.HtmlControls;
10using System.Data.SqlClient;
11using System.Drawing;
12using System.IO;
13using System.Text;
14public partial class Default7 : System.Web.UI.Page
15{
16    SqlConnection sqlcon;
17    SqlCommand sqlcom;
18    string strCon = "Data Source=(local);Database=北风贸易;Uid=sa;Pwd=sa";
19    protected void Page_Load(object sender, EventArgs e)
20    {
21        if (!IsPostBack)
22        {
23            bind();
24        }
25    }
26    
27    public void bind()
28    {
29        string sqlstr = "select top 5 * from 飞狐工作室";
30        sqlcon = new SqlConnection(strCon);
31        SqlDataAdapter myda = new SqlDataAdapter(sqlstr, sqlcon);
32        DataSet myds = new DataSet();
33        sqlcon.Open();
34        myda.Fill(myds, "飞狐工作室");
35        GridView1.DataSource = myds;
36        GridView1.DataKeyNames = new string[] { "身份证号码" };
37        GridView1.DataBind();
38        sqlcon.Close();
39    }
40    protected void Button1_Click(object sender, EventArgs e)
41    {
42        Export("application/ms-excel", "学生成绩报表.xls");
43    }
44    private void Export(string FileType, string FileName)
45    {
46        Response.Charset = "GB2312";
47        Response.ContentEncoding = System.Text.Encoding.UTF7;
48        Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8).ToString());
49        Response.ContentType = FileType;
50        this.EnableViewState = false;
51        StringWriter tw = new StringWriter();
52        HtmlTextWriter hw = new HtmlTextWriter(tw);
53        GridView1.RenderControl(hw);
54        Response.Write(tw.ToString());
55        Response.End();
56    }
57    public override void VerifyRenderingInServerForm(Control control)
58    {
59    }
60}

 1<asp:GridView ID="GridView1" runat="server"    AutoGenerateColumns="False" CellPadding="3"  
 2     BackColor="White" BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px" Font-Size="12px"   >
 3    <FooterStyle BackColor="White" ForeColor="#000066" />
 4    <Columns>
 5        <asp:BoundField DataField="身份证号码" HeaderText="编号" ReadOnly="True" />
 6        <asp:BoundField DataField="姓名" HeaderText="姓名"  />
 7        <asp:BoundField DataField="出生日期" HeaderText="邮政编码"  />
 8        <asp:BoundField DataField="家庭住址" HeaderText="家庭住址"  />
 9        <asp:BoundField DataField="邮政编码" HeaderText="邮政编码" />
10        <asp:BoundField DataField="起薪" HeaderText="起薪"  />
11       
12    </Columns>
13    <RowStyle ForeColor="#000066" />
14    <SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White" />
15    <PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left"  CssClass="ms-formlabel DataGridFixedHeader"/>
16    <HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" />
17</asp:GridView>
18<asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="导出" />

读取Excel数据的代码：这个很简单的

 1private DataSet CreateDataSource()
 2{
 3    string strCon;
 4    strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("excel.xls") + "; Extended Properties=Excel 8.0;";
 5    OleDbConnection olecon = new OleDbConnection(strCon);
 6    OleDbDataAdapter myda = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strCon);
 7    DataSet myds = new DataSet();
 8    myda.Fill(myds);
 9    return myds;
10}
11protected void Button1_Click(object sender, EventArgs e)
12{
13    GridView1.DataSource = CreateDataSource();
14    GridView1.DataBind();
15}