Data import from Excel to GridView

Basit Khan

Hi,

with the below code im importing data from excel to gridview, Data is importing successfully, but if column have text and number data then it will take only either number or text data, if take number data then shows blank in text field data.

Example below excel file

Item Code	Item Description
'000001	Item A
2	Item B
3	Item C

In this case First row 000001 shows blank in GridView

Dim conn As OleDbConnection

Dim dtr As OleDbDataReader

Dim dta As OleDbDataAdapter

Dim cmd As OleDbCommand

Dim dts As DataSet

Dim excel As String

Dim OpenFileDialog As New OpenFileDialog

OpenFileDialog.InitialDirectory = My.Computer.FileSystem.SpecialDirectories.MyDocuments

If (OpenFileDialog1.ShowDialog(Me) = System.Windows.Forms.DialogResult.OK) Then

excel = OpenFileDialog1.InitialDirectory + OpenFileDialog1.FileName

conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excel + ";Extended Properties=Excel 12.0;")

dta = New OleDbDataAdapter("Select * From [Sheet1$]", conn)

dts = New DataSet

dta.Fill(dts, "[Sheet1$]")

DataGridView1.DataSource = dts

DataGridView1.DataMember = "[Sheet1$]"

conn.Close()

End If

Thanks

Basit.

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Pusting8y ago
You could replace the usage of OLEDB with GemBox.Spreadsheet, that library can import any cell value into DataGridView, cells in same column do not have to have values of the same type.

For instance, take a look at the example on:
https://www.gemboxsoftware.com/spreadsheet/examples/c-sharp-vb-net-import-export-excel-datagridview/5301

In short, the following is what you need:
Dim openFileDialog = New OpenFileDialog()
openFileDialog.InitialDirectory = My.Computer.FileSystem.SpecialDirectories.MyDocuments
openFileDialog.Filter = "All Files (*.*)|*.*|Excel files (*.xlsx)|*.xlsx|CSV Files (*.csv)|*.csv|XLS Files (*.xls)|*xls"

If (openFileDialog.ShowDialog() = DialogResult.OK) Then
    ' Read Excel file.
    Dim workbook = ExcelFile.Load(openFileDialog.FileName)

    ' Get Excel worksheet.
    Dim worksheet = workbook.Worksheets.ActiveWorksheet

    ' Export Excel worksheet to DataGridView control.
    Dim exportOptions = New ExportToDataGridViewOptions() With {.ColumnHeaders = True}
    DataGridViewConverter.ExportToDataGridView(worksheet, Me.DataGridView1, exportOptions)
End If
+1
Suraj Kumar8y ago
Hello,
Please open that excel and change the format of excel and try again.
+1
Laxmidhar Sahoo8y ago
Hi. Change excel cell format according table structure
+1
Basit Khan8y ago
Thank i changed the string

conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excel + ";Extended Properties=Excel 12.0;HDR=Yes;IMEX=1;")

but showing error

Could not find installable ISAM.

Thanks
Basit.
+2
Suraj Kumar8y ago
Hello,
Can you modify and check the coonectionstring as given in below link

https://alphacharles.wordpress.com/2013/05/02/what-is-hdr-and-imex-in-oledb-connection-string/
+1