将excel表的数据加载到表中的问题 - plz帮助 [英] Problem in loading the data of excel sheet into table - plz help

问题描述

我尝试使用以下代码将excel文件数据加载到sql server表中

但它显示以下内容错误....

Microsoft Jet数据库引擎无法找到对象'order_form'。请确保该对象存在，并且您正确拼写其名称和路径名。

这里我使用order_form作为excel表名，order作为excel书名

locationd：\

 con =  new  SqlConnection（str）; 
 
  string  ssqltable =   order_item_detl_temp; 
  string  myexceldataquery =   select sale_code，sizecd ，阴影，单位来自[order_form]; 
  string  sexcelconnectionstring =  @  provider = microsoft .jet.oledb.4.0;数据
 source = d：\order.xls; extended properties = +   \excel 8.0; hdr = yes; \; 
 
  string  sclearsql =  从 + ssqltable;删除; 
 SqlCommand cmd =  new  SqlCommand（sclearsql，con）; 
 con.Open（）; 
 cmd.ExecuteNonQuery（）; 
 con.Close（）; 
 OleDbConnection oledbconn =  new  OleDbConnection（sexcelconnectionstring）; 
 OleDbCommand oledbcmd =  new  OleDbCommand（myexceldataquery，oledbconn）; 
 oledbconn.Open（）; 
 OleDbDataReader dr = oledbcmd.ExecuteReader（）; 
 SqlBulkCopy bulkcopy =  new  SqlBulkCopy（str）; 
 bulkcopy.DestinationTableName = ssqltable; 
  while （dr.Read（））
 {
 bulkcopy.WriteToServer（dr）; 
} 
 dr.Close（）; 
 oledbconn.Close（）; 
 MessageBox.Show（ 成功传输Excel数据）; 

谢谢和问候



Madhu



我的尝试：



我在sexcelconnectionstring的数据源中更改了excel文件路径位置和路径格式，如（'/'，'\'，'//'）等。但它无法正常工作。

解决方案

更改文件名不太可能有任何区别。您需要检查OleDb找到的工作表名称。



您可以使用以下代码列出名称：

 DataTable dtSchema = oleDbConnection.GetOleDbSchemaTable（OleDbSchemaGuid.Tables， new   object  [] { null ， null ， null ，  TABLE}）; 
  //  使用LINQ从电子表格架构中获取表格（即表格） 
 List< object> worksheets =（ from  dr  in  dtSchema.AsEnumerable（） / /  返回IEnumerable< DataRow>集合 
 选择 dr [  TABLE_NAME]）。ToList（）;  //  从每个架构条目中获取工作表名称 
  foreach （字符串名称在工作表中）
 {
  //  列出或显示名称 
} 

Hi,

I trying to load the excel file data into sql server table using the following code
but it showing the following error....

"The Microsoft Jet database engine could not find the object 'order_form'.  Make sure the object exists and that you spell its name and the path name correctly."

Here i am using "order_form" as excel sheet name,"order" as excel book name
location "d:\"

con = new SqlConnection(str);
         
            string ssqltable = "order_item_detl_temp";   
    string myexceldataquery = "select sale_code,sizecd,shade,units from [order_form]";   
   string sexcelconnectionstring = @"provider=microsoft.jet.oledb.4.0;data 
 source=d:\order.xls;extended properties=" + "\"excel 8.0;hdr=yes;\"";   

    string sclearsql = "delete from " + ssqltable;   
        SqlCommand cmd = new SqlCommand(sclearsql, con);   
        con.Open();   
        cmd.ExecuteNonQuery();   
        con.Close();   
        OleDbConnection oledbconn = new OleDbConnection(sexcelconnectionstring);   
        OleDbCommand oledbcmd = new OleDbCommand(myexceldataquery, oledbconn);   
        oledbconn.Open();   
        OleDbDataReader dr = oledbcmd.ExecuteReader();   
        SqlBulkCopy bulkcopy = new SqlBulkCopy(str);   
        bulkcopy.DestinationTableName = ssqltable;   
        while (dr.Read())   
        {   
            bulkcopy.WriteToServer(dr);   
        }   
        dr.Close();   
        oledbconn.Close();
        MessageBox.Show("Excel data transferred successfully");

Thanks and regards

Madhu

What I have tried:

I have changed the excel file path location and path format like ('/','\','//' ) etc.. in datasource of sexcelconnectionstring. but it is not working.

解决方案

Changing the name of the file is not likely to make any difference. You need to check the worksheet names as found by OleDb.

You can list the names with the following code:

DataTable dtSchema = oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
// Use LINQ to get the table (i.e. sheet) names from the spreadsheet schema
List<object> worksheets = (from dr in dtSchema.AsEnumerable()	// returns an IEnumerable<DataRow> collection
select dr["TABLE_NAME"]).ToList();    // gets the sheet name from each schema entry
foreach (string name in worksheets)
{
    // list or show the names
}

这篇关于将excel表的数据加载到表中的问题 - plz帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！