Read Excel Sheet Data into DataGridView

Hello,

I tried using the following code from http://vb.net-informations.com/excel...xcel_oledb.htm but I cannot tell what's wrong with it.

What I want to do is to only view excel data from a datagrid in vb.net. I have modified the file path to the one I am using as follows:

Code:

---

Imports System.Data
Public Class TR_Viewer

    Private Sub TR_Viewer_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            Dim MyConnection As System.Data.OleDb.OleDbConnection
            Dim DtSet As System.Data.DataSet
            Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=C:\Users\u618863\Documents\TestFile.xlsx;Extended Properties=Excel 8.0;")
            MyCommand = New System.Data.OleDb.OleDbDataAdapter _
                ("select * from [Sheet1$]", MyConnection)
            MyCommand.TableMappings.Add("Table", "TestTable")
            DtSet = New System.Data.DataSet
            MyCommand.Fill(DtSet)
            DataGridView1.DataSource = DtSet.Tables(0)
            MyConnection.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub
End Class

---

I have attached a pic of what my table looks like and what I get when I press run.

Attachment 158715Attachment 158717


My spreadsheet [Sheet1] contains cells in row 1 only, columns A to D.

Any help would be appreciated

Quote:

---

MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=C:\Users\u618863\Documents\TestFile.xlsx;Extended Properties=Excel 8.0;")

---

I don't have direct experience with what you are doing, but I do know that Excel 8.0 refers to Excel 97, which would just be a plain old .xls file, not an .xlsx file. So, one thing to try would be to save your spreadsheet as the older .xls format of a file and change your connection to point to the .xls version of the file and see if that works. Another option is to change the Extended Properties value to a version that supported .xlsx files, possibly Excel 12.0.

Good luck.

Try the following

Code:

---

Imports System.Data.OleDb
Public Module ExcelSampleReadRange
    Public Function GetSheetData() As DataTable
        Dim fileName As String = "C:\Users\u618863\Documents\TestFile.xlsx"
        Dim dt As New DataTable
        Using cn As New OleDbConnection With
            {
                .ConnectionString = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={fileName};Extended Properties=""Excel 12.0;IMEX=1;HDR=No;"""
            }
            Dim cmd = New OleDbCommand("SELECT * FROM [Sheet1$A1:D1]", cn)

            cn.Open()

            dt.Load(cmd.ExecuteReader)

        End Using

        Return dt

    End Function

End Module

---

Your code is a little confused, but it'll probably work if you remove the TableMappings line:

Code:

---

MyCommand.TableMappings.Add("Table", "TestTable")

---

Thank you all for your replies. Unfortunately none of them have worked so far.

I) Change Excel 8.0 -> 12.0 or the file extension from .xlsx to .xls got me a "Could not find installable ISAM" error.

II) For Karen's code I got an error about "unrecognizable characters" with the dollar sign. I replaced the dollar sign with a " and then "" but to no avail.

III) Remove TableMapping -> Got me an "External table not in expected format" error.

Any ideas?

Quote:

---

Originally Posted by DoubleN

I) Change Excel 8.0 -> 12.0 or the file extension from .xlsx to .xls got me a "Could not find installable ISAM" error.

---

If all you did was change the file extension of your existing file from .xlsx to .xls and try that, it will 100% not work. It's not about the file extension per-se, it is about the format of the file.

You need to open the .xlsx file in Excel and go through the steps to save it as a different file type and select the type of file that is tied to previous versions of Excel. I don't have a version of Office new enough to give you the exact steps, but you would want to save it as a file that is described something like "Excel 97-2003 (.xls)" or something like that.

Thanks Option! I re-saved it as .xls and it worked; however, I only used this .xlsx as an simple example, in reality I need to use an .xlsm file. So the .xls won't do the trick I'm afraid.

The following code does seem to work BUT it puts my values from .xlsx workbook,sheet1, as columns next to the current ones. What I want it to do is to retain the same location as in the excel file, i.e. "1" was in A1 so I need it to be under column 1 in my datatable..

I have attached a pic below.

Attachment 158757

The code I used for this to work is [removing the TableMappings line did not change anything]:

Code:

---

            Try
                Dim MyConnection As System.Data.OleDb.OleDbConnection
                Dim DtSet As System.Data.DataSet
                Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
                MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=C:\Users\u618863\Documents\TestFile.xlsx;Extended Properties=Excel 12.0;")
                MyCommand = New System.Data.OleDb.OleDbDataAdapter _
                    ("select * from [Sheet1$]", MyConnection)
                MyCommand.TableMappings.Add("Table", "TestTable")
                DtSet = New System.Data.DataSet
                MyCommand.Fill(DtSet)
                DataGridView1.DataSource = DtSet.Tables(0)
                MyConnection.Close()
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try

---

The columns are autogenerated. You can either remove your design time columns and it'll add your columns in the correct order, or set your dgv's autogeneratedcolumns property to false, and set the columns from the datatable to the columns in the dgv in your code. I'll find an example for you...

https://msdn.microsoft.com/en-us/lib...or=-2147217396

Have a look at this example...

https://www.codeproject.com/Question...view-in-vb-net