The Microsoft Office Access database engine could

问题:

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

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.

My code is:

 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..

回答1:

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.

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

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

回答2:

I had similar issue, I sorted it out by

1. Saving the excel file from fileuploader to a temporary folder inside website folder.
2. Using path to that file in my connection string

Rest all was same and now the error: The Microsoft Office Access database engine could not find the object 'sheet1$' was gone.