My Blog

Get column names from a specific sheet

In continuation from here

We created a file earlier here.

To retrieve the column names, we store the data from the relevant sheet in a Datatable and then loop though it to get the column names. One thing to note here is that we are assuming that Row 1 is not empty and has column headers else we can’t use this.

This is how our code will look like

Imports System.Data.OleDb

Public Class Form1
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim olecon As OleDbConnection = New OleDbConnection
        Dim olecmd As OleDbCommand = New OleDbCommand
        Dim FilePath As String = "C:\Temp\"
        Dim FileName As String = "EmployeeDatabase.xlsx"

        '~~> Construct your connection string
        Dim connstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;" &
                                   "Data Source=" & FilePath & FileName & ";" &
                                   "Extended Properties=""Excel 12.0 Xml;HDR=YES;"""

        olecon.ConnectionString = connstring
        olecon.Open()

        '~~> Add data from Sheet1 to a Datatable
        Dim adapter = New OleDbDataAdapter("SELECT * FROM [Sheet1$]", olecon)
        Dim ds = New DataSet()
        adapter.Fill(ds, "myTable")
        Dim dt As DataTable = ds.Tables("myTable")

        '~~> Loop through the data table to get the column names
        For Each column As DataColumn In dt.Columns
            MessageBox.Show(column.ColumnName.ToString)
        Next

        '~~> Close the connection
        olecon.Close()
    End Sub
End Class