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 }
View Code
