我正在尝试将数据从 Excel 复制到 SQL Server,但遇到以下错误。
Microsoft Office Access 数据库引擎找不到对象“sheet1$”。确保该对象存在并且其名称和路径名称拼写正确。
我的代码是:
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();
}
请告诉我如何解决这个问题..
出现此错误是因为您尝试访问 Excel 文件中的工作表(名称为sheet1)。默认情况下,第一个工作表名称为“sheet1”,但用户可以重命名此名称或删除此工作表。
要解决此问题,首先您必须从 Excel 文件中获取所有工作表名称,然后必须在上面的代码中传递此工作表名称以导入数据。
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”
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)