GridView数据导入Excel/Excel数据读入GridView _gridview导入excel sqlcommand-CSDN博客

本文链接：https://blog.csdn.net/hexzwj/article/details/2744848

本文介绍如何使用 ASP.NET 将 GridView 中的数据导出为 Excel 文件，并提供了一个示例按钮点击事件的方法实现。此外，还展示了如何从 Excel 文件中读取数据并填充到 GridView 控件。

摘要由CSDN通过智能技术生成

解决方案：
页面增加一个按钮，单击事件添加如下方法：
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}