在Microsoft Office Access数据库引擎找不到对象 [英] The Microsoft Office Access database engine could not find an object

问题描述

我试图将数据从Excel中复制到SQL Server，但面对下面的错误。

I'm trying to copy data from excel to sql server but facing the following error.

Microsoft Office Access数据库引擎可能找不到对象'工作表Sheet1 $。 。确保对象存在并拼写其名称和正确的路径名

The Microsoft Office Access database engine could not find the object 'sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.

我的代码是：

 protected void importdatafromexcel(string filepath)
    {
        string sqltable = "PFDummyExcel";
        string exceldataquery = "select EmployeeId,EmployeeName,Amount from [Sheet1$]";
        string excelconnectionstring = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties=Excel 12.0;Persist Security Info=False";
        string sqlconnectionstring = System.Configuration.ConfigurationManager.ConnectionStrings["HRGold"].ConnectionString;
        SqlConnection con = new SqlConnection(sqlconnectionstring);
        OleDbConnection oledb = new OleDbConnection(excelconnectionstring);
        OleDbCommand oledbcmd = new OleDbCommand(exceldataquery, oledb);
        oledb.Open();
        OleDbDataReader dr = oledbcmd.ExecuteReader();
        SqlBulkCopy bulkcopy = new SqlBulkCopy(sqlconnectionstring);
        bulkcopy.DestinationTableName = sqltable;
        while (dr.Read())
        {
            bulkcopy.WriteToServer(dr);
        }
        oledb.Close();
    }

请告诉我如何解决这个问题。

Please tell me how i solve this..

推荐答案

这是错误的，因为你正在试图访问表（这名字是Sheet1中）在Excel文件中提出的。默认情况下第一张名为工作表Sheet1，但用户要么重新命名这个名字或删除此片。

This error is raised because of you are trying to access sheet (which name is sheet1) in excel file. By default first sheet name is "sheet1" but user have either rename this name or delete this sheet.

要解决这个问题首先你必须得到从Excel文件中的所有工作表名称，那么你必须在你上面的代码中导入数据通过这个表名称。

To resolved this issue first of all you have to get all sheet name from excel file, then you have to pass this sheet name in your above code to import data.

string  filePath = "your file path";

string excelconnectionstring = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties=Excel 12.0;Persist Security Info=False";

OleDbConnection Connection  = new OleDbConnection(excelconnectionstring); 


DataTable activityDataTable = Connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

if(activityDataTable != null)
{
    //validate worksheet name.
    var itemsOfWorksheet = new List<SelectListItem>();
    string worksheetName;
    for (int cnt = 0; cnt < activityDataTable.Rows.Count; cnt++)
    {
        worksheetName = activityDataTable.Rows[cnt]["TABLE_NAME"].ToString();

        if (worksheetName.Contains('\''))
        {
            worksheetName = worksheetName.Replace('\'', ' ').Trim();
        }
        if (worksheetName.Trim().EndsWith("$"))
            itemsOfWorksheet.Add(new SelectListItem { Text = worksheetName.TrimEnd('$'), Value = worksheetName });
    }
}

// itemsOfWorksheet : all worksheet name is added in this

所以你可以使用itemsOfWorksheet [0]如表名称就地工作表Sheet1的

so you can use itemsOfWorksheet[0] as sheet name in-place of "sheet1"

这篇关于在Microsoft Office Access数据库引擎找不到对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！