Microsoft.ace.oledb.12.0未在本地机器上注册

Question

我有一种情况，我必须使用vb.net导入Excel表或文件到我的数据库，我知道代码和这样做的过程，但问题是

我有64位的操作系统
1. 拥有32位MS Office

和我不想改变我的配置从86位由于一些其他的原因那么现在我该怎么做呢有更喜欢的解决方案吗?任何一个都有这样的解决方案

我的代码是

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.ACE.OLEDB.12.0; Data Source='" & fname & " '; " & "Extended Properties=Excel 8.0;")
        MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection)
        MyCommand.TableMappings.Add("Table", "CurrencyRate")
        DtSet = New System.Data.DataSet
        MyCommand.Fill(DtSet)
        MyConnection.Close()
        For Each Dr As DataRow In DtSet.Tables(0).Rows
        Next
        MsgBox("Successfully Saved")
    End If

但是在这段代码中我得到了

的错误

Microsoft.ace.oledb.12.0未在本地机器上注册

Answer 1

我通过安装2007 Office系统驱动程序和Microsoft Access Database Engine 2010 Redistributable解决了这个问题。即使我用的是32位的Office和64位的OS，也运行得很好。下载链接http://www.microsoft.com/en-in/download/details.aspx?id=13255和http://www.microsoft.com/en-in/download/confirmation.aspx?id=23734

使用的连接代码:

OleDbConnection myConnection = new OleDbConnection(
                                "Provider=Microsoft.ACE.OLEDB.12.0; " +
                                "data source='" + path + "';" +
                                "Extended Properties="Excel 12.0;HDR=YES;IMEX=1" ");

Answer 2

我很久以前就解决了我的问题，但是我看到很多用户访问这个问题，所以我想我应该回答我的问题来帮助别人

1. 我有64位操作系统

2. 有32位的MS Office因此，我们不能确定我们必须使用的oledb连接版本，因此我们有微软的替代库来将ms office产品集成到我们的应用程序中。

   Microsoft.Office.Interop

要下载并安装此库，请遵循此链接

和下面是我的代码示例为进一步的帮助

    Dim table As New DataTable("CurrencyRate")
    Dim OFD As New OpenFileDialog
    Dim strDestination As String
    With OFD
        .Filter = "Excel Office|*.xls;*.xlsx"
        .FileName = ""
        If .ShowDialog() <> Windows.Forms.DialogResult.OK Then
            Return False
        End If
        strDestination = .FileName
    End With
    Dim xlApp As Microsoft.Office.Interop.Excel.Application
    Dim xlWorkbook As Microsoft.Office.Interop.Excel.Workbook
    Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
    Dim xlRange As Microsoft.Office.Interop.Excel.Range
    Dim xlCol As Integer
    Dim xlRow As Integer
    Dim Data(0 To 3) As String
    With table
        .Clear()
        If strDestination <> "" Then
            xlApp = New Microsoft.Office.Interop.Excel.Application
            xlWorkbook = xlApp.Workbooks.Open(strDestination)
            xlWorkSheet = xlWorkbook.ActiveSheet()
            xlRange = xlWorkSheet.UsedRange
            If xlRange.Columns.Count > 0 Then
                If xlRange.Rows.Count > 0 Then
                    For xlRow = 2 To xlRange.Rows.Count 'here the xlRow is start from 2 coz in exvel sheet mostly 1st row is the header row
                        For xlCol = 1 To xlRange.Columns.Count  
                            Data(xlCol - 1) = xlRange.Cells(xlRow, xlCol).text
                        Next
                        .LoadDataRow(Data, True)
                    Next
                    xlWorkbook.Close()
                    xlApp.Quit()
                    KillExcelProcess()
                End If
            End If
        Else
            MessageBox.Show("Please Select Excel File", "Information", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        End If
    End With

通过这样做，你将有你的excel数据在你的数据表，然后它在你想要保存的地方，如保存在sql server或存储区域。

Answer 3

我使用如下所示的"Microsoft.Jet.OLEDB.4.0"解决。希望这对别人有帮助。

    Public OledbString32Bit As String = "Provider=Microsoft.ACE.OLEDB.12.0;" ' 32 Bit
    Public OledbString64Bit As String = "Provider=Microsoft.Jet.OLEDB.4.0;" ' 64 Bit
    For value As Integer = 0 To 1
        vCNNstring = OledbString & _
                                    "Data Source= " & vPath & ";" & _
                                    "Extended Properties=""Excel 8.0;HDR=YES;IMEX=1"""
        ExcelCNN = New System.Data.OleDb.OleDbConnection(vCNNstring)
        ExcelCMD = New System.Data.OleDb.OleDbDataAdapter(vSQL, ExcelCNN)
        If SheetName = "Sheet2" Then
            Dim a As Integer = 0
        End If
        Try
            ExcelCNN.Open()
            Exit For
        Catch ex As Exception
            ' If using Default OledbString32Bit not work , change to use OledbString64Bit and save for further call
            OledbString = OledbString64Bit
            If value = 1 Then
                MsgBox("Error in mc_ExcelTableToDataTable using : " & OledbString & ", Error : " & ex.ToString())
            End If
        End Try
    Next