使用GridView将数据导入或导出Excel中
来源:互联网 发布:淘客联盟和淘宝联盟 编辑:程序博客网 时间:2024/04/30 11:53
using System;using System.Collections;using System.Configuration;using System.Data;using System.Linq;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.HtmlControls;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Xml.Linq;using System.IO;using System.Data.OleDb;public partial class Default3 : System.Web.UI.Page{ protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { Bind(); //窗体加载时向GridView中填充数据 } } PosDataContext pos = new PosDataContext(); //使用Linq to sql进行操作数据库的数据 //自定义向GridView控件填充数据方法 private void Bind() { GridView1.DataSource = pos.Product.ToList(); //填充数据 GridView1.DataKeyNames = new string[] {"Id" }; //设置主键字段 GridView1.DataBind(); //将数据绑定到控件中 } //导出数据事件 protected void btnExcel_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; //将Http头添加到输出流 Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8).ToString()); Response.ContentType = FileType; this.EnableViewState = false; StringWriter tw = new StringWriter(); HtmlTextWriter hw = new HtmlTextWriter(tw); GridView1.RenderControl(hw); //将GridView中的内容输出到指定字符串中 Response.Write(tw.ToString()); Response.End(); //将当前面的所有缓冲发送到客户端 } //如果没有下面方法会报错类型“GridView”的控件“GridView1”必须放在具有 runat=server 的窗体标记内 public override void VerifyRenderingInServerForm(Control control) { //base.VerifyRenderingInServerForm(control); } //将Excel中的数据导入到指定控件中 public DataSet CreateDataSource() { string strCon = "provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("Excel.xls")+"; Extended Properties= Excle 8.0"; OleDbConnection olecon=new OleDbConnection(strCon); OleDbDataAdapter myda=new OleDbDataAdapter("select * from [Sheet1$]",strCon); DataSet myds=new DataSet(); myda.Fill(myds); return myds; } //将数据从Excel导入到指定控件中protected void btnExcelInfo_Click(object sender, EventArgs e){ GridView1.DataSource= CreateDataSource(); GridView1.DataBind();}}