使用vb.net将数据从excel文件导入到sql数据库 [英] import data from excel file to sql database using vb.net

问题描述

我正在尝试开发Windows应用程序，其中希望有一个选项可以将数据从excel文件复制到sql数据库表.

我从互联网上复制了一个代码，但是却出现了一些错误.

这是我的代码:

Hi,

I''m trying to develop a windows application in which I want to have an option to copy the data from an excel file to the sql database table.

I copied a code from the internet but I''m getting some errors in it.

This is my code:

Private Sub Label1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label1.Click
        Dim ExcelConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=example.xls;Extended Properties=""Excel 12.0 Xml;HDR=Yes""")
        ExcelConnection.Open()

       <big>Dim expr As String = "SELECT * FROM [Sheet1$]" </big>  
      Dim objCmdSelect As OleDbCommand = New OleDbCommand(expr, ExcelConnection)
        Dim objDR As OleDbDataReader

        Dim SQLconn As New SqlConnection()
        Dim ConnString As String = "Data Source=ASPIRE5741G-PC;Initial Catalog=exceltosql;Integrated Security=True"
        SQLconn.ConnectionString = ConnString
        SQLconn.Open()


        Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(SQLconn)
            bulkCopy.DestinationTableName = "emp"

            Try
                objDR = objCmdSelect.ExecuteReader
                bulkCopy.WriteToServer(objDR)
                objDR.Close()
                SQLconn.Close()

            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
        End Using


    End Sub

错误在该行中

the error is in the line

Dim expr As String = "SELECT * FROM [Sheet1$]"

错误消息是:

the error message is:

The Microsoft Jet database engine could not find the object 'Sheet1$'.  Make sure the object exists and that you spell its name and the path name correctly.

但是Excel工作表名称是相同的，我尝试了google搜索结果中几乎所有可用的选项.

请帮忙.

如果没有这个项目，我将无法继续我的项目.

But the Excel sheet name is the same, and I tried almost all the options available in the google search results.

Please help.

I am not able to continue my project without this .

推荐答案

"</big> Dim objCmdSelect As OleDbCommand =新的OleDbCommand(expr，ExcelConnection) 昏暗的objDR作为OleDbDataReader 昏暗的SQLconn作为新的SqlConnection() Dim ConnString As 字符串 = " SQLconn.ConnectionString = ConnString SQLconn.Open() 使用bulkCopy作为SqlBulkCopy = New SqlBulkCopy(SQLconn) bulkCopy.DestinationTableName = " 尝试 objDR = objCmdSelect.ExecuteReader bulkCopy.WriteToServer(objDR) objDR.Close() SQLconn.Close() 异常捕获 MsgBox(例如ToString) 结束尝试 最终使用 结束Sub

" </big> Dim objCmdSelect As OleDbCommand = New OleDbCommand(expr, ExcelConnection) Dim objDR As OleDbDataReader Dim SQLconn As New SqlConnection() Dim ConnString As String = "Data Source=ASPIRE5741G-PC;Initial Catalog=exceltosql;Integrated Security=True" SQLconn.ConnectionString = ConnString SQLconn.Open() Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(SQLconn) bulkCopy.DestinationTableName = "emp" Try objDR = objCmdSelect.ExecuteReader bulkCopy.WriteToServer(objDR) objDR.Close() SQLconn.Close() Catch ex As Exception MsgBox(ex.ToString) End Try End Using End Sub

错误在该行中

the error is in the line

Dim expr As String = "SELECT * FROM [Sheet1

& quot ;

&quot;

错误消息是:

the error message is:

The Microsoft Jet database engine could not find the object 'Sheet1

'.确保对象存在 and ，并正确拼写其名称&路径名.

'. Make sure the object exists and that you spell its name and the path name correctly.

但是Excel工作表名称是相同的，我尝试了google搜索结果中几乎所有可用的选项.

请帮忙.

没有这个，我将无法继续我的项目.

But the Excel sheet name is the same, and I tried almost all the options available in the google search results.

Please help.

I am not able to continue my project without this .

这篇关于使用vb.net将数据从excel文件导入到sql数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！