Execl表格中的数据导入到GridView控件中并导出打印

View Code 
  1 using System;
  2 using System.Collections;
  3 using System.Configuration;
  4 using System.Data;
  5 using System.Linq;
  6 using System.Web;
  7 using System.Web.Security;
  8 using System.Web.UI;
  9 using System.Web.UI.HtmlControls;
 10 using System.Web.UI.WebControls;
 11 using System.Web.UI.WebControls.WebParts;
 12 using System.Xml.Linq;
 13 using System.Data.OleDb;
 14 using System.Data.SqlClient;
 15 //using System.Reflection;
 16 using System.Collections.Generic;
 17 public partial class xls_read : System.Web.UI.Page
 18 {
 19 string filePath = "C:/Documents and Settings/Administrator/桌面/user.xls"; //"E:/Test/Web/user.xls"xls文件路径（绝对路径）
 20 protected void Page_Load(object sender, EventArgs e)
 21 {
 22 if (!IsPostBack)
 23 getDs(); //加载数据
 24 }
 25 /// <summary>
 26 /// 加载xls数据到dataset中
 27 /// </summary>
 28 public DataSet loadData()
 29 {
 30 string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0;";
 31 OleDbConnection conn = new OleDbConnection(strConn);
 32 conn.Open();
 33 //DataTable dt1 = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,new object[]{null,null,null,"Table"});
 34 //fileName = "select * from [" + dt1.Rows[0]["Sheet1"] + "$]";
 35 OleDbDataAdapter adap = new OleDbDataAdapter("select * from [Sheet1$]", conn); //sheet1好像不能变
 36 DataSet ds = new DataSet();
 37 adap.Fill(ds);
 38 return ds;
 39 }
 40 /// <summary>
 41 /// 获取ds表中的信息
 42 /// </summary>
 43 public void getDs()
 44 {
 45 List<Test> list = new List<Test>();
 46 Test info=null; //实例化实体类
 47 DataSet ds1 = loadData();
 48 DataTable dt = ds1.Tables[0];
 49 if (dt.Rows.Count > 0)
 50 {
 51 foreach (DataRow row in dt.Rows)
 52 {
 53 //ds1中的数据赋值给实体中的变量
 54 info = new Test();
 55 info.UName = row["uname"].ToString();
 56 info.UPwd = row["upwd"].ToString();
 57 info.Name = row["name"].ToString();
 58 info.BrithDay =Convert.ToDateTime( row["brithday"].ToString());
 59 info.Address = row["address"].ToString();
 60 info.Sex = row["sex"].ToString();
 61 list.Add(info);
 62 }
 63 //把数据绑定到数据控件中
 64 gridView1.DataSource = list;
 65 gridView1.DataBind();
 66 }
 67 }
 68 
 69 /// <summary>
 70 /// 导出execl
 71 /// filename为Excel的名字，gridView1（也就是DataGrid控件的id）就是数据源，在此为DataGrid数据源；
 72 /// </summary>
 73 /// <param name="sender"></param>
 74 /// <param name="e"></param>
 75 protected void Button1_Click(object sender, EventArgs e)
 76 {
 77 GridView gridview = gridView1;
 78 HttpContext.Current.Response.Charset = "GB2312";
 79 //Response.ContentEncoding = System.Text.Encoding.UTF8;
 80 HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + "userInfo" + ".xls");
 81 HttpContext.Current.Response.ContentType = "application/ms-excel";
 82 HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
 83 HttpContext.Current.Response.Write("<meta http-equiv=Content-Type content=text/html;charset=GB2312>");
 84 StringWriter sw = new StringWriter();
 85 HtmlTextWriter htw = new HtmlTextWriter(sw);
 86 gridview.RenderControl(htw);
 87 HttpContext.Current.Response.Write(sw.ToString());
 88 HttpContext.Current.Response.End();
 89 }
 90 /// <summary>
 91 /// 导出Execl一定要重写这个方法
 92 /// </summary>
 93 /// <param name="control"></param>
 94 public override void VerifyRenderingInServerForm(Control control)
 95 {
 96 
 97 }
 98 
 99 
100 }

 

posted @ 2012-11-19 17:18  weych  阅读(1019)  评论(3)    收藏  举报