将数据从Excel工作表传输到SQL Server数据库

I have an Excel sheet that contains some data. I want to store that data into a SQL Server database from that aspx page.

我有一张包含一些数据的Excel表格。我想将该数据存储在该aspx页面的SQL Server数据库中。

I am trying to store data but get error as below:

我试图存储数据,但得到如下错误:

The Microsoft Office Access database engine cannot open or write to the file. It is already opened exclusively by another user, or you need permission to view and write its data.

Microsoft Office Access数据库引擎无法打开或写入该文件。它已由其他用户专门打开,或者您需要获得查看和写入其数据的权限。

This is my code:

这是我的代码:

string excelConnectionString = @"provider=Microsoft.ACE.OLEDB.12.0;data source=" + path + ";extended properties='Excel 12.0;HDR=YES;'";

//Create Connection to Excel work book
OleDbConnection excelConnection =new OleDbConnection(excelConnectionString);
//Create OleDbCommand to fetch data from Excel
OleDbCommand cmd = new OleDbCommand("Select [ID],[Name],[Designation] from [Sheet1$]",excelConnection);
excelConnection.Open();
OleDbDataReader dReader;
dReader = cmd.ExecuteReader();
SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
//Give your Destination table name
sqlBulk.DestinationTableName = "Excel_table";
sqlBulk.WriteToServer(dReader);
excelConnection.Close();

and thanks in advance ...

并提前感谢...

3 个解决方案

#1

YOU NEED TO close the Access database database file than your code will access the data/.

您需要关闭Access数据库数据库文件,而不是您的代码将访问数据/。

#2

The appropriate user account (NETWORK SERVICE by default) needs MODIFY permissions on the folder that the Excel file sits in. Adds the permission to that folder in which Excel file present.

相应的用户帐户(默认情况下为NETWORK SERVICE)需要对Excel文件所在的文件夹具有MODIFY权限。将权限添加到Excel文件所在的文件夹中。

#3

Hi check the following things:

嗨检查以下事项:

1.Access file which you are using should not be opened.

1.不应打开您正在使用的访问文件。

2.It should not be read only.

2.它不应该只读。

3.You should have read/write permission for folder where file that you want to access is placed.

3.您应该具有要放置文件的文件夹的读/写权限。

#1