ADO.NET 读取EXCEL的实现代码((c#))

// 连接字符串            
         string xlsPath = Server.MapPath("~/app_data/somefile.xls"); // 绝对物理路径
         string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                         "Extended Properties=Excel 8.0;" + 
                         "data source=" + xlsPath;
         // 查询语句
         string sql = "SELECT * FROM [Sheet1$]";
         DataSet ds = new DataSet();
         OleDbDataAdapter da = new OleDbDataAdapter(sql, connStr);
         da.Fill(ds);    // 填充DataSet        

         // 在这里对DataSet中的数据进行操作       

         // 输出，绑定数据
         GridView1.DataSource = ds.Tables[0]; 
         GridView1.DataBind();

// 读取Excel数据，填充DataSet
         // 连接字符串            
         string xlsPath = Server.MapPath("~/app_data/somefile.xls");
         string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +

 "Extended Properties=\"Excel 8.0;HDR=No;IMEX=1\";" + // 指定扩展属性为
 Microsoft Excel 8.0 (97) 9.0 (2000) 10.0 (2002)，并且第一行作为数据返回，且以文本方式读取
                         "data source=" + xlsPath;
         string sql_F = "SELECT * FROM [{0}]";

         OleDbConnection conn = null;
         OleDbDataAdapter da = null;
         DataTable tblSchema = null;
         IList<string> tblNames = null;

         // 初始化连接，并打开
         conn = new OleDbConnection(connStr);
         conn.Open();

         // 获取数据源的表定义元数据                        
         //tblSchema = conn.GetSchema("Tables");
         tblSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

         GridView1.DataSource = tblSchema;
         GridView1.DataBind();

         // 关闭连接
         conn.Close();
 GetOleDbSchemaTable 方法的详细说明可以参考：
 http://msdn2.microsoft.com/zh-CN/library/system.data.oledb.oledbconnection.getoledbschematable.aspx

 接着是一段利用“架构信息”动态读取Excel内部定义的表单或者命名区域的程序片断：

         // 读取Excel数据，填充DataSet
         // 连接字符串            
         string xlsPath = Server.MapPath("~/app_data/somefile.xls");
         string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +

 "Extended Properties=\"Excel 8.0;HDR=No;IMEX=1\";" + // 指定扩展属性为
 Microsoft Excel 8.0 (97) 9.0 (2000) 10.0 (2002)，并且第一行作为数据返回，且以文本方式读取
                         "data source=" + xlsPath;
         string sql_F = "SELECT * FROM [{0}]";

         OleDbConnection conn = null;
         OleDbDataAdapter da = null;
         DataTable tblSchema = null;
         IList<string> tblNames = null;

         // 初始化连接，并打开
         conn = new OleDbConnection(connStr);
         conn.Open();

         // 获取数据源的表定义元数据                        
         //tblSchema = conn.GetSchema("Tables");
         tblSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

         //GridView1.DataSource = tblSchema;
         //GridView1.DataBind();

         // 关闭连接
         //conn.Close();

         tblNames = new List<string>();
         foreach (DataRow row in tblSchema.Rows) {
             tblNames.Add((string)row["TABLE_NAME"]); // 读取表名
         }

         // 初始化适配器
         da = new OleDbDataAdapter();
         // 准备数据，导入DataSet
         DataSet ds = new DataSet();

         foreach (string tblName in tblNames) {
             da.SelectCommand = new OleDbCommand(String.Format(sql_F, tblName), conn);
             try {
                 da.Fill(ds, tblName);
             }
             catch {
                 // 关闭连接
                 if (conn.State == ConnectionState.Open) {
                     conn.Close();
                 }
                 throw;
             }
         }

         // 关闭连接
         if (conn.State == ConnectionState.Open) {
             conn.Close();
         }

         // 对导入DataSet的每张sheet进行处理        
         // 这里仅做显示
         GridView1.DataSource = ds.Tables[0];
         GridView1.DataBind();

         GridView2.DataSource = ds.Tables[1];
         GridView2.DataBind();

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.Data.OleDb;

 public partial class _Default : System.Web.UI.Page 
 {
     protected void Page_Load(object sender, EventArgs e)
     {

     }
     protected void Button1_Click(object sender, EventArgs e)
     {

         string strconn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @"D:/last.xls" + ";Extended Properties=Excel 8.0;"; //HDR=no";//IMEX=1
         OleDbConnection conn = new OleDbConnection(strconn);
         DataSet myset = new DataSet();
         try
         {
             conn.Open();

 
             string mysql = "select * from [Sheet1$] ";//where chs <> &#39;&#39;

             OleDbDataAdapter aper = new OleDbDataAdapter(mysql, conn);

             myset.Tables.Clear();
             aper.Fill(myset, "book");

             conn.Close();

             GridView1.DataSource = myset.Tables["book"];
             GridView1.DataBind();
         }
         catch (Exception ex)
         {
             conn.Close();
             this.lb_msg.Text = ex.Message;
             return;
             // return ex.Message;
         }
     }
 }