使用vb.net将数据从excel文件导入到sql数据库

问题描述

我正在尝试开发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 .

推荐答案

错误在该行中

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

但是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数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！