读取Excel到SQL数据库（方法二）

 public void putExcelIntoSQL(string strPath,string strSheetName)
  {
   try
   {
    string strConn=System.Configuration.ConfigurationSettings.AppSettings["strCon"];
    strConn="Provider=SQLOLEDB;User ID=sa;Initial Catalog=myDatabase;password=;datasource=localhost";
    OleDbConnection Olecon=new OleDbConnection(strConn);
    string sql="";
    sql="insert INTO tableTest ";
    sql = sql + " select JAN";
    sql = sql + ",商品名";
    sql = sql + ",部門,状態";
    sql = sql + ",棚卸単位";
    sql = sql + ",棚卸単位入数";
    sql = sql + ",棚卸原単";
    sql = sql + ",棚卸売単 ";
    sql = sql + "FROM  OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source=/"";
    sql = sql + strPath + "/"" ;
    sql = sql + ";User ID=Admin;Password=;Extended properties=Excel 5.0')...["+ strSheetName +"$]";
    OleDbCommand oleCmd=new OleDbCommand(sql,Olecon);
    Olecon.Open();
    oleCmd.ExecuteNonQuery();
    Olecon.Close();
   }
   catch(Exception ex)
   {
    throw ex;
   }
  }

 

 

注意：Excel中若有混合类型的数据，则有可能出现读取数据丢失现象，

可以通过修改注册表来解决：

在HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Jet/4.0/Engines/Excel中修改TypeGuessRows的值，最好修改为0。