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

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

效果图：

解决方案：
页面增加一个按钮，单击事件添加如下方法：
protected void
Button1_Click(object sender, EventArgs e)
    {
       
Export("application/ms-excel", "学生成绩报表.xls");
    }

    private void Export(string FileType, string FileName)
    {
       
Response.Charset = "GB2312";
        Response.ContentEncoding =
System.Text.Encoding.UTF7;
       
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();
   
}
//如果没有下面方法会报错类型“GridView”的控件“GridView1”必须放在具有
runat=server 的窗体标记内
    public override void
VerifyRenderingInServerForm(Control control)
    {
   
}
还有由于是文件操作所以要引入名称空间IO和Text

后台代码：
using System;
using System.Data;
using
System.Configuration;
using System.Web;
using
System.Web.Security;
using System.Web.UI;
using
System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using
System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using
System.Drawing;
using System.IO;
using System.Text;
public partial
class Default7 : System.Web.UI.Page
{
    SqlConnection sqlcon;
   
SqlCommand sqlcom;
    string strCon = "Data
Source=(local);Database=北风贸易;Uid=sa;Pwd=sa";
    protected void
Page_Load(object sender, EventArgs e)
    {
        if
(!IsPostBack)
        {
            bind();

        }
    }
   
    public void bind()
    {
       
string sqlstr = "select top 5 * from 飞狐工作室";
        sqlcon = new
SqlConnection(strCon);
        SqlDataAdapter myda = new
SqlDataAdapter(sqlstr, sqlcon);
        DataSet myds = new
DataSet();
        sqlcon.Open();
        myda.Fill(myds,
"飞狐工作室");
        GridView1.DataSource = myds;
       
GridView1.DataKeyNames = new string[] { "身份证号码" };
       
GridView1.DataBind();
        sqlcon.Close();
    }

    protected void Button1_Click(object sender, EventArgs e)
   
{
        Export("application/ms-excel", "学生成绩报表.xls");
    }

    private void Export(string FileType, string FileName)
    {
       
Response.Charset = "GB2312";
        Response.ContentEncoding =
System.Text.Encoding.UTF7;
       
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)
    {
   
}
   
}

前台：
<asp:GridView ID="GridView1" runat="server"   
AutoGenerateColumns="False" CellPadding="3"
                        
BackColor="White" BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px"
Font-Size="12px"   >
                        <FooterStyle
BackColor="White" ForeColor="#000066" />
                       
<Columns>
                            <asp:BoundField
DataField="身份证号码" HeaderText="编号" ReadOnly="True"
/>
                            <asp:BoundField DataField="姓名"
HeaderText="姓名" />
                            <asp:BoundField
DataField="出生日期" HeaderText="邮政编码" />
                           
<asp:BoundField DataField="家庭住址" HeaderText="家庭住址"
/>
                            <asp:BoundField DataField="邮政编码"
HeaderText="邮政编码" />
                            <asp:BoundField
DataField="起薪" HeaderText="起薪" />
                          

                        </Columns>
                       
<RowStyle ForeColor="#000066" />
                       
<SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White"
/>
                        <PagerStyle BackColor="White"
ForeColor="#000066" HorizontalAlign="Left" CssClass="ms-formlabel
DataGridFixedHeader"/>
                        <HeaderStyle
BackColor="#006699" Font-Bold="True" ForeColor="White"
/>
                    </asp:GridView>
        <asp:Button
ID="Button1" runat="server" OnClick="Button1_Click" Text="导出" />

读取Excel数据的代码：这个很简单的
private DataSet
CreateDataSource()
    {
        string strCon;
        strCon =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("excel.xls") +
"; Extended Properties=Excel 8.0;";
        OleDbConnection olecon = new
OleDbConnection(strCon);
        OleDbDataAdapter myda = new
OleDbDataAdapter("SELECT * FROM [Sheet1$]", strCon);
        DataSet myds =
new DataSet();
        myda.Fill(myds);
        return myds;
   
}
    protected void Button1_Click(object sender, EventArgs e)
   
{
        GridView1.DataSource = CreateDataSource();
       
GridView1.DataBind();
    }