将Excel工作表数据插入数据库时​​出错 [英] Error while inserting excel sheet data into database

问题描述

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

在以下代码中它给出错误

in the following code it gives error at

excelConnection.Open();

我尝试过：



数据库表包含以下列

1）Id

2）名字

指定

What I have tried:

Database table contain following columns
1)Id
2)name
Designation

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace WebApplication5
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void btnSend_Click(object sender, EventArgs e)
        {
            String strConnection = "Data Source=DESKTOP-HL3PK63;Initial Catalog=Demo;Integrated Security=True";
            //file upload path
            string path = fileuploadExcel.PostedFile.FileName;
            //Create connection string to Excel work book
            string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=False";
            //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();
        }
    }
}

推荐答案

，excelConnection）;
excelConnection。 Open（）;
OleDbDataReader dReader;
dReader = cmd.ExecuteReader（）;
SqlBulkCopy sqlBulk = new SqlBulkCopy（strConnection）;
//给你的目的地表名
sqlBulk.DestinationTableName =Excel_table;
sqlBulk.WriteToServer（dReader）;
excelConnection.Close（）;
}
}
}

", 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(); } } }

尝试添加Mode = Read仅以只读方式打开;

Try adding Mode=Read to only open as read only;

string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Mode=Read;Extended Properties=Excel 12.0;Persist Security Info=False";

Quote：

string path = fileuploadExcel.PostedFile.FileName;
string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=False";

您的代码在服务器上运行 。



FileName property返回客户端上文件的路径 - 有时只是名称 - 。



服务器上运行的代码无法访问到客户端的文件系统。



在Visual Studio中调试代码时，它可能出现。但这只是因为服务器和客户端在该特定实例中是相同的。



您需要将发布的文件保存在服务器上的某个位置，然后使用服务器读取文件的路径：

Your code is running on the server.

The FileName property returns the path - or sometimes just the name - of the file on the client.

Code running on the server has no access to the client's file system.

It might appear to work when you're debugging your code in Visual Studio. But that's just because the server and the client are the same in that specific instance.

You need to save the posted file somewhere on the server, and then use the server path to read the file:

string excelPath = System.IO.Path.Combine(System.IO.Path.GetTempPath(), Guid.NewGuid().ToString("N") + ".xls");

fileuploadExcel.SaveAs(excelPath);

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

try
{
    ...
}
finally
{
    System.IO.File.Delete(excelPath);
}

这篇关于将Excel工作表数据插入数据库时​​出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！