i have a situation in which i have to inport the Excel sheet or file to my database using vb.net i know the code and the process for doing this but the issue is
and i don't want to change my Configuration from to 86bit due to some othere reason so now what should i do any prefer solution any one face same solution like this
my code is
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
but in this code i got error of
Microsoft.ace.oledb.12.0 not registered on the local machine
i solve my problem time ago but i saw many user visiting this Question so i think i should answer my question to help others
I have 64 bit operating system
having 32 bit MS Office so for this we can not sure that what oledb connection version we have to use so for this we have alternate library from Microsoft to integrate ms office products to our applications.
Microsoft.Office.Interop
to download and install this library follow this link Interop
and bellow is my code sample for further help
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
by doing this you will have you excel data in your DataTable and then its on you where you want to save like save it in the sql server or storage area. let me know for further clarification.