如何将数据从Excel工作表导入MySQL数据库？ [英] How to Import Data from Excel sheet to MySQL database ?

问题描述

我有一张包含数据的Excel表格，我想把它上传到MYSQL数据库。

我用过FileUpload控件用户选择Excel文件。

从Excel工作表中读取数据时oledb.Open（）发生以下错误：

 Microsoft Office Access数据库引擎无法打开或写入文件''。它已由其他用户独占打开，或者您需要获得查看和写入其数据的权限。 
 
下面是我的代码：
 
 protected void btnImportcontacts_Click（object sender，EventArgs e）
 {
 string path = FileUploadContacts.PostedFile.FileName; 
 string connString =Provider = Microsoft.ACE.OLEDB.12.0; Data Source =+ path +; Extended Properties = Excel 12.0; 
 //创建连接对象
 OleDbConnection oledbConn = new OleDbConnection（connString）; 
 //试试
 // {
 
 //打开连接
 oledbConn.Open（）; 
 
 
 //创建OleDbCommand对象并从工作表中选择数据Sheet1 
 OleDbCommand cmd = new OleDbCommand（SELECT * FROM [Sheet1 $]，oledbConn）; 
 
 //创建新的OleDbDataAdapter 
 OleDbDataAdapter oleda = new OleDbDataAdapter（）; 
 
 oleda.SelectCommand = cmd; 
 
 //创建一个DataSet，它将保存从工作表中提取的数据。 
 DataSet ds = new DataSet（）; 
 
 //从工作表中提取的数据中填充DataSet。 
 oleda.Fill（ds，客户）; 
 //这里有更多代码..... 
 //代码就在这里.... 

解决方案

< blockquote>，oledbConn）;

//创建新的OleDbDataAdapter
OleDbDataAdapter oleda = new OleDbDataAdapter（）;

oleda.SelectCommand = cmd;

//创建一个DataSet，它将保存从工作表中提取的数据。
DataSet ds = new DataSet（）;

//从提取的数据中填充DataSet工作表。
oleda.Fill（ds，Customer）;
//这里有更多代码.....
//代码就在这里....

我已经尝试过了。



我可以执行，如果我替换下面的数据源;



此代码

  string  path = FileUploadContacts.PostedFile.FileName; 
  string  connString =   Provider = Microsoft.ACE.OLEDB.12.0;数据源= +路径+  ;扩展属性= Excel 12.0; 

如

 string connString =Provider = Microsoft.ACE.OLEDB.12.0;数据源= C：\\Sample.xlsx;扩展属性= Excel 12.0; 

问题出在FileUpload控件上。可能它没有采用完整的路径。

如何解决这个问题？

Hi,

I have an Excel sheet containing data and i want to upload that to the MYSQL database.
I have used FileUpload control to select the Excel file by the user.
While reading the data from Excel sheet the following error occuring at oledb.Open();

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.

Below is my code:

 protected void btnImportcontacts_Click(object sender, EventArgs e)
    {
        string path =FileUploadContacts.PostedFile.FileName;
        string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+path+";Extended Properties=Excel 12.0";
        // Create the connection object
        OleDbConnection oledbConn = new OleDbConnection(connString);
        //try
        //{
          
            // Open connection
            oledbConn.Open();
           
 
            // Create OleDbCommand object and select data from worksheet Sheet1
            OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", oledbConn);
 
            // Create new OleDbDataAdapter
            OleDbDataAdapter oleda = new OleDbDataAdapter();
 
            oleda.SelectCommand = cmd;
 
            // Create a DataSet which will hold the data extracted from the worksheet.
            DataSet ds = new DataSet();
 
            // Fill the DataSet from the data extracted from the worksheet.
            oleda.Fill(ds, "Customer");
            // Some more code here.....
           // code goes here....

解决方案

", oledbConn); // Create new OleDbDataAdapter OleDbDataAdapter oleda = new OleDbDataAdapter(); oleda.SelectCommand = cmd; // Create a DataSet which will hold the data extracted from the worksheet. DataSet ds = new DataSet(); // Fill the DataSet from the data extracted from the worksheet. oleda.Fill(ds, "Customer"); // Some more code here..... // code goes here....

I have already tried.

I am able to execute, if i replace the data source like below;

This code

string path =FileUploadContacts.PostedFile.FileName;
        string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+path+";Extended Properties=Excel 12.0";

as like

string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Sample.xlsx;Extended Properties=Excel 12.0";

The problem is with the FileUpload Control.May be it is not taking the complete Path.
How can i resolve this?

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