如何在Windows应用程序中将数据从Excel导出到SQL Server？ [英] How to export data from Excel to sql server in windows application ?

问题描述

Hai

在我的Windows应用程序中，我必须将数据从sql server导出到excel并将数据从excel导入到sqlserver，我完成导出数据从sql server到excel，但我又需要导入该数据进入sql server .i尝试以下代码，但它显示连接Datasouce的错误

像'Microsoft.Jet.OLEDB.4.0'提供程序未在本地计算机上注册。

Hai
In my windows application i have to export data from sql server to excel and import dataa from excel to sqlserver ,i done export data form sql server to excel,but again i need to import that data into sql server .i try the following code,but it show the error in connection of Datasouce
like "The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine."

Private Sub radbtnImport_Click(sender As Object, e As EventArgs) Handles radbtnImport.Click
        Dim objConnection As New SqlConnection
        objConnection = New SqlConnection(My.Settings.CS)
        objConnection.Open()

        Dim MyConnection As System.Data.OleDb.OleDbConnection
        Dim DtSet As System.Data.DataSet
        Dim MyCommand As System.Data.OleDb.OleDbDataAdapter

        Dim fBrowse As New OpenFileDialog
        With fBrowse
            .Filter = "Excel files(*.xlsx)|*.xlsx|All files (*.*)|*.*"
            .FilterIndex = 1
            .Title = "Import data from Excel file"
        End With
        If fBrowse.ShowDialog() = Windows.Forms.DialogResult.OK Then
            Dim fname As String
            fname = fBrowse.FileName
            MyConnection = New System.Data.OleDb.OleDbConnection("Provider= Microsoft.Jet.OLEDB.4.0; Data Source='" & fname & " '; " & "Extended Properties=Excel 8.0;")
            MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Personal]", MyConnection)
            MyCommand.TableMappings.Add("Table", "Test")
            DtSet = New System.Data.DataSet
            MyCommand.Fill(DtSet)'-- here i get error "The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine."
            MyConnection.Close()
            For Each Drr As DataRow In DtSet.Tables(0).Rows
           
 ''Here just i try to retive the data form excel one by one


                Dim Name As String = Drr(0).ToString
                Dim LastName As String = Drr(1).ToString
                Dim Contact As String = Drr(2).ToString
                Dim Phone As String = Drr(3).ToString
             
            Next
          
        End If


    End Sub

MyConnection = New System.Data.OleDb.OleDbConnection("Provider= Microsoft.Jet.OLEDB.4.0; Data Source='" & fname & " '; " & "Extended Properties=Excel 8.0;")

这里我传递的是什么？在数据源中？只有文件名或路径的完整路径？



喜欢



D：/Person.xlxs？或D：/或D：/人？





问候

Aravind

here what i pass ? in Data Source ? full path with filename or path only ?

like

D:/Person.xlxs ? or D:/ or D:/Person ?


Regards
Aravind

推荐答案

似乎是与Excel交互，MS Office访问的常见问题。

看看这些链接：



- Microsoft.Jet.OLEDB.4.0提供程序未在本地计算机上注册 [ ^ ]

- 'Microsoft.ACE.OLEDB.12.0'提供程序未在本地计算机上注册导入过程出错xlsx到sql server [ ^ ]

- OLEDB Provider未在本地计算机上注册 [ ^ ]





如果您收到外部表不符合预期格式错误，您可能应该更改连接：

Seems like a common problem with interacting with Excel, Access of MS Office.
Have a look at those links:

- Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine[^]
- The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine" Error in importing process of xlsx to a sql server[^]
- OLEDB Provider is Not Registered on the Local Machine[^]


If you getting a "External table is not in the expected format" error perhaps you should change the connection from:

MyConnection = New System.Data.OleDb.OleDbConnection("Provider= Microsoft.Jet.OLEDB.4.0; Data Source='" & fname & " '; " & "Extended Properties=Excel 8.0;")

to

to

MyConnection = New System.Data.OleDb.OleDbConnection("Provider = Microsoft.ACE.OLEDB.12.0; Data Source='" & fname & " '; " & "Extended Properties=Excel 12.0;")

祝你好运，

Edo

Good luck,
Edo

这篇关于如何在Windows应用程序中将数据从Excel导出到SQL Server？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！