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

来源:互联网 发布:演员高露的淘宝店 编辑:程序博客网 时间:2024/03/29 12:48
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)
  • {
  • }
  • 后台代码:
  • 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: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();
  • }
  • 由于时间关系,这个文章先到此。有时间再写,其实还有很多技巧,不过我觉得如果能融汇贯通应该可以举一反三。