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

http://www.soasp.net/FilePage/200804/20080430012831.htm

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}