C#操作Excel

首先说连接字符串:

string conStr = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = '" + address + "'; Extended Properties = 'Excel 8.0; HDR = Yes; IMEX = 1';";

1.HDR 表示第一行是否为字段名。Yes为首行字段，No为无首行;

2.IMEX 表示对同一列中有混合数据类型的列，是统一按字符型处理，还是将个别不同类型的值读为DBNULL。1为混合，2为不混合;

this.Label1.Text为取得的文件的扩展名

/*

 *以下是操作Office2003

 */

 

if(this.Label1.Text==".xlsx")

{

　　string conStr = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = '" + address + "'; Extended Properties = 'Excel 8.0; HDR = Yes; IMEX = 1';";
　　OleDbConnection odCon = new OleDbConnection(conStr);
　　odCon.Open();

　　DataTable dt = new DataTable();
　　string strSql = "select * from [Sheet1$]";
　　using (OleDbCommand odcmd = new OleDbCommand(strSql, odCon))
　　{
      　　OleDbDataAdapter odda = new OleDbDataAdapter(odcmd);
      　　try
      　　{
           　　odda.Fill(dt);
      　　}
      　　catch (Exception ex)
      　　{
            　　throw new Exception(ex.ToString());
      　　}
      　　GridView1.DataSource = dt;
      　　GridView1.DataBind();
　　}

}

 

/*

 *以下是操作Office2007和Office2010

 */

 

 if(this.Label1.Text==".xlsx")        
 {
      string conStr = "Provider=Microsoft.ACE.OLEDB.12.0;" +"Data Source = " + address + "; Extended Properties='Excel 12.0 Xml; HDR = Yes; IMEX = 1';" ;
      OleDbConnection odCon = new OleDbConnection(conStr);
      odCon.Open();
      DataTable dt = new DataTable();
      string strSql = "select * from [Sheet1$]";
      using (OleDbCommand odcmd = new OleDbCommand(strSql, odCon))
      {
           OleDbDataAdapter odda = new OleDbDataAdapter(odcmd);
           try
           {
                 odda.Fill(dt);
           }
           catch (Exception ex)
           {
                throw new Exception(ex.ToString());
           }
           GridView1.DataSource = dt;
           GridView1.DataBind();
    }

/*

 *以下方法读取Excel文件某单元格的值

 */

　 using (OleDbCommand cmd=new OleDbCommand(strSql,odCon))
    {
          OleDbDataReader dr = cmd.ExecuteReader();
          while (dr.Read())
          {
               Response.Write(dr["userName"].ToString());
          }
    }
}