Import an Excel File into a DataGridView in VB.NET — Without Office or OLEDB

· unvell team
Import an Excel File into a DataGridView in VB.NET — Without Office or OLEDB

Last week we exported a DataGridView to Excel. This article goes the other way: a customer sends an Excel file, and the app has to show it on screen. VB.NET developers usually reach for one of two tools. Microsoft.ACE.OLEDB.12.0 reads a sheet with SQL, and Microsoft.Office.Interop.Excel reads it cell by cell.

Both work on the developer’s PC. On a PC without the Access Database Engine, OLEDB stops with “The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.” It also guesses each column’s type from the first eight rows, so in a column that starts with numbers, a later text value such as “TBD” arrives empty. Interop needs Excel installed on every PC that runs the app.

This article reads the .xlsx file with ReoGrid, turns it into a typed DataTable, and shows it in a DataGridView. The code is one module you can paste in, and it copes with what real business workbooks contain: a title row, merged cells, a total row underneath. The V5 code was compiled with Option Strict On and run against .xlsx files laid out the way Excel saves them, and the values and types it returned were checked.

From an Excel orders sheet with a title row, a blank row, a header, a merged Branch column, six orders, a blank row, a total row and a note, only the header and the six orders are read. They become a DataTable whose Order Date is Date, ZIP is String, Amount is Decimal and Shipped is Boolean


Which ReoGrid for your project

Your projectUsePackage
.NET 10ReoGrid V5unvell.ReoGrid.One
.NET Framework 4.8, or .NET 8 (Windows)ReoGrid V4unvell.ReoGrid4

Many VB.NET WinForms apps still run on .NET Framework; for those, use V4. Jump to the V4 module. Both install from the UNVELL package feed (V5 instructions, V4 instructions), and a 30-day trial key covers either.


What an Excel import has to handle

Two nested loops over UsedRange look like ten lines of work. Real files need more care:

PitfallWhat goes wrongWhat the module does
A title row above the headerThe title becomes the column name and the real header lands in the dataTakes the header row as headerRow
DatesIn xlsx, a date is a number with a date format, so reading the value alone gives 46328Turns cells with a date format into Date
Type guessingOLEDB decides a column’s type from eight rows, and values that don’t fit come back emptyLooks at every row before choosing a type
Vertically merged cellsOnly the first cell holds the value, so the branch is empty from the second row onPuts the top-left value into every row of the merge
Leading zeros from a number formatA ZIP code stored as a number with format 00000 arrives as 2134Reads the columns in textColumns as the text Excel displays
Total row and notes underneathThe total is imported as data and every sum doublesStops at the first blank row
Error values#VALUE! ends up in a numeric columnTurns them into DBNull
Blank or duplicate headersDataTable throws DuplicateNameExceptionNames them Column3, Notes (2)
A file still open in ExcelReading it throws IOExceptionOpens it with a share mode that allows other writers

The import module — ReoGrid V5

Add the package to your .vbproj project:

dotnet add package unvell.ReoGrid.One

Apply your license key once at startup. Without a key, reading a file throws ReoGridLicenseException.

Imports unvell.ReoGrid.Core.License

ReoGridLicense.SetLicense(myLicenseKey)

Then add this module:

Imports System.Data
Imports System.IO
Imports unvell.ReoGrid.Core
Imports unvell.ReoGrid.Core.Data
Imports unvell.ReoGrid.Core.Style
Imports unvell.ReoGrid.IO.Excel

Public Module ExcelImport

    ''' <summary>
    ''' Reads a sheet of an xlsx file into a DataTable, using row headerRow (zero-based) as the header.
    ''' Columns named in textColumns are read as the text Excel displays.
    ''' </summary>
    Public Function LoadDataTable(path As String,
                                  Optional sheetName As String = Nothing,
                                  Optional headerRow As Integer = 0,
                                  Optional textColumns As String() = Nothing) As DataTable

        ' Allow other writers, so a file that is still open in Excel can be read
        Dim book As Workbook
        Using stream As New FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)
            book = XlsxReader.Read(stream)
        End Using

        Dim ws = If(sheetName Is Nothing, book(0), book(sheetName))
        If ws Is Nothing Then Throw New ArgumentException($"No sheet named ""{sheetName}"".", NameOf(sheetName))

        Dim table As New DataTable(ws.Name)
        Dim used As RangePosition
        If Not ws.TryGetUsedRange(used) OrElse used.EndRow <= headerRow Then Return table

        ' Columns: up to the last one with a header
        Dim lastCol = used.EndCol
        Do While lastCol >= 0 AndAlso ws.GetValue(headerRow, lastCol).IsEmpty
            lastCol -= 1
        Loop

        ' Rows: from below the header to just before the first blank row,
        ' so a total or a note underneath is left out
        Dim lastRow = headerRow
        Do While lastRow < used.EndRow AndAlso Not IsBlankRow(ws, lastRow + 1, lastCol)
            lastRow += 1
        Loop

        ' A column's type comes from every row, not just the first few
        For c = 0 To lastCol
            Dim name = UniqueName(table, ws.GetDisplayText(headerRow, c).Trim(), c)
            Dim format As String = Nothing
            Dim type = ColumnType(ws, headerRow + 1, lastRow, c, format)
            If textColumns IsNot Nothing AndAlso textColumns.Contains(name) Then type = GetType(String)
            table.Columns.Add(name, type).ExtendedProperties("NumberFormat") = format
        Next

        For r = headerRow + 1 To lastRow
            Dim row = table.NewRow()
            For c = 0 To lastCol
                row(c) = ToValue(ws, r, c, table.Columns(c).DataType)
            Next
            table.Rows.Add(row)
        Next
        Return table
    End Function

    ' The column's type when all of its values agree; String when they are mixed
    Private Function ColumnType(ws As Worksheet, firstRow As Integer, lastRow As Integer,
                                col As Integer, ByRef format As String) As Type
        Dim found As Type = Nothing
        For r = firstRow To lastRow
            Dim cell = Anchor(ws, r, col)
            Dim t = KindOf(ws, cell.Row, cell.Col)
            If t Is Nothing Then Continue For           ' blank cells and errors don't count
            If found Is Nothing Then
                found = t
                format = ws.GetNumberFormat(cell.Row, cell.Col)
            ElseIf t IsNot found Then
                Return GetType(String)
            End If
        Next
        Return If(found, GetType(String))
    End Function

    Private Function KindOf(ws As Worksheet, row As Integer, col As Integer) As Type
        Select Case ws.GetValue(row, col).Kind
            Case CellValueKind.Number
                ' In xlsx, a date is a number with a date format
                Return If(NumberFormatter.IsDateFormat(ws.GetNumberFormat(row, col)), GetType(Date), GetType(Decimal))
            Case CellValueKind.DateTime : Return GetType(Date)
            Case CellValueKind.Boolean : Return GetType(Boolean)
            Case CellValueKind.Text : Return GetType(String)
            Case Else : Return Nothing
        End Select
    End Function

    Private Function ToValue(ws As Worksheet, row As Integer, col As Integer, type As Type) As Object
        Dim cell = Anchor(ws, row, col)
        Dim v = ws.GetValue(cell.Row, cell.Col)
        If v.IsEmpty OrElse v.Kind = CellValueKind.Error Then Return DBNull.Value
        If type Is GetType(String) Then Return ws.GetDisplayText(cell.Row, cell.Col)
        If type Is GetType(Date) Then Return DateTime.FromOADate(v.AsNumber)
        If type Is GetType(Boolean) Then Return v.AsBoolean
        Return CDec(v.AsNumber)
    End Function

    ' A cell inside a merged range points at the top-left cell, which holds the value
    Private Function Anchor(ws As Worksheet, row As Integer, col As Integer) As CellPosition
        Dim merged As RangePosition
        If ws.Merges.TryGetAt(row, col, merged) Then Return merged.TopLeft
        Return New CellPosition(row, col)
    End Function

    Private Function IsBlankRow(ws As Worksheet, row As Integer, lastCol As Integer) As Boolean
        For c = 0 To lastCol
            If Not ws.GetValue(row, c).IsEmpty Then Return False
        Next
        Return True
    End Function

    ' A blank header becomes "Column3"; a repeated one becomes "Notes (2)"
    Private Function UniqueName(table As DataTable, header As String, col As Integer) As String
        Dim name = If(header = "", $"Column{col + 1}", header)
        Dim candidate = name
        Dim n = 2
        Do While table.Columns.Contains(candidate)
            candidate = $"{name} ({n})"
            n += 1
        Loop
        Return candidate
    End Function

End Module

headerRow is zero-based. When the header sits on row 3 in Excel, as in the figure above, pass 2. Leave out sheetName to read the first sheet.

Calling it from a button

Imports System.Data
Imports System.IO

Private Sub OpenButton_Click(sender As Object, e As EventArgs) Handles OpenButton.Click
    Using dlg As New OpenFileDialog With {.Filter = "Excel Workbook|*.xlsx"}
        If dlg.ShowDialog(Me) <> DialogResult.OK Then Return

        Dim table As DataTable
        Try
            table = LoadDataTable(dlg.FileName, headerRow:=2, textColumns:={"ZIP"})
        Catch ex As InvalidDataException
            MessageBox.Show("This is not an .xlsx file. Open an .xls file in Excel and save it as .xlsx.")
            Return
        End Try

        DataGridView1.DataSource = table
        For Each col As DataGridViewColumn In DataGridView1.Columns
            col.DefaultCellStyle.Format = DotNetFormatOf(table.Columns(col.DataPropertyName))
        Next
    End Using
End Sub

Given a DataTable as its DataSource, the DataGridView creates the columns itself, and a Boolean column becomes a check-box column. Because the columns are typed, clicking a header sorts amounts by size and dates by date. Imported as text, the same amounts would sort as strings: 1,350, 1,960, 456.

An old .xls file (Excel 97-2003), or a CSV file renamed to .xlsx, throws InvalidDataException. The Catch above turns that into a request to re-save the file.


How column types are chosen

The module looks at every row under the header before it chooses a type:

Excel cellDataTable column type
NumberDecimal
Number with a date formatDate
TRUE / FALSEBoolean
TextString
A column with mixed kindsString (the text Excel displays)
Blank cell, error valueNot used to choose the type; the value is DBNull

Formula cells are read as the result Excel saved with the file. An Amount column of =F4*G4 formulas arrives as numbers such as 456.

Numbers become Decimal because it suits money and because it keeps the same precision as Excel. Excel works to 15 significant digits. Read as a Double, a cell can arrive as 0.30000000000000004; CDec rounds to 15 digits, so you get 0.3, the same as Excel shows.

With types in place, the DataTable can total a column by itself:

Dim total = CDec(table.Compute("SUM(Amount)", ""))   ' 5494

Finding mixed-type columns

A column that holds both numbers and text becomes String. Say the Quotes sheet has “TBD” as the unit price on row 5 in Excel. The Unit Price column then holds the strings "38" and "TBD". That row’s Amount (=F5*G5) is #VALUE! in Excel, so it arrives as DBNull.

It is worth checking columns that should be numeric before you save anything. The data rows run from just under the header with no gaps, so a DataTable row index maps straight to an Excel row number:

Imports System.Globalization

Dim headerRow = 2
Dim table = LoadDataTable(path, "Quotes", headerRow:=headerRow)

' Report values that are not numbers in a column that should be, with the Excel row number
Dim errors As New List(Of String)
If table.Columns("Unit Price").DataType Is GetType(String) Then
    For i = 0 To table.Rows.Count - 1
        Dim text = table.Rows(i)("Unit Price").ToString()
        Dim value As Decimal
        If text <> "" AndAlso Not Decimal.TryParse(text, NumberStyles.Number, CultureInfo.CurrentCulture, value) Then
            errors.Add($"Row {i + headerRow + 2}: Unit Price ""{text}"" is not a number")
        End If
    Next
End If

Here it returns Row 5: Unit Price "TBD" is not a number.


Vertically merged cells

Business workbooks often merge a branch or customer cell down across all of its rows. Only the top-left cell of a merged range holds the value, so a plain read leaves the branch empty from the second row on.

The module’s Anchor maps any cell inside a merged range to its top-left cell. All three Boston rows get “Boston”, so the branch is still there after the DataTable is filtered or sorted.


Leading zeros from a number format

Excel can keep the leading zero of a ZIP code or an employee number in two ways:

  • Entered as text ('02134, or typed into a cell already formatted as Text). The module reads it as a String column with 02134 and needs nothing more.
  • Entered as a number with the format 00000. The cell’s value is 2134; the zero exists only on screen. Read as a number, the column gives you 2134.

For files of the second kind, pass the column name in textColumns. Those columns are read as the text Excel displays, so you get 02134.

LoadDataTable(path, headerRow:=2, textColumns:={"ZIP", "Employee No"})

Excel number format codes, decoded covers how formats like this work.


Carrying the formats over to the grid

The module stores the format of each column’s first value in DataColumn.ExtendedProperties("NumberFormat"). DefaultCellStyle.Format takes a .NET format string, so the mapping table from last week’s article runs in reverse:

' Turn an Excel format code into the .NET format string a DataGridView uses
Private Function DotNetFormatOf(column As DataColumn) As String
    If column.DataType Is GetType(Date) Then Return "yyyy-MM-dd"
    Select Case TryCast(column.ExtendedProperties("NumberFormat"), String)
        Case "#,##0" : Return "N0"
        Case "#,##0.00" : Return "N2"
        Case "0%" : Return "P0"
        Case "0.0%" : Return "P1"
    End Select
    Return ""
End Function
Excel format codeDefaultCellStyle.Format
#,##0N0
#,##0.00N2
0%P0
0.0%P1
Any date formatyyyy-MM-dd

Every date column gets yyyy-MM-dd. Excel’s standard short date changes with the PC’s regional settings, so one fixed form on screen is easier to read.


A file that is still open in Excel

XlsxReader.Read also accepts a file path. But when the user still has that file open in Excel, the read stops with IOException: Excel holds the file open for writing, and opening it with a share mode that refuses other writers fails.

The module opens the stream with FileShare.ReadWrite and passes it to XlsxReader.Read, so a file that is open in Excel can still be read. You get the last saved version; changes not yet saved in Excel are not included.


Large files

A 100,000-row × 9-column xlsx file (about 5 MB) goes into a DataTable the same way. In our measurements the first load took about 1.7 seconds and later loads 0.6 to 0.8 seconds (Apple Silicon Mac, .NET 10).


On .NET Framework 4.8, do the same with V4

The V4 package is unvell.ReoGrid4, which runs on .NET Framework 4.8 and .NET 8 (Windows). The license call is the same, with Imports unvell.ReoGrid.License. The module keeps its shape; only the way cells are read changes, and the form code works unchanged.

Imports System.Data
Imports System.IO
Imports unvell.ReoGrid
Imports unvell.ReoGrid.IO

Public Module ExcelImport

    Public Function LoadDataTable(path As String,
                                  Optional sheetName As String = Nothing,
                                  Optional headerRow As Integer = 0,
                                  Optional textColumns As String() = Nothing) As DataTable

        Dim book = ReoGridControl.CreateMemoryWorkbook()
        Using stream As New FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)
            book.Load(stream, FileFormat.Excel2007)
        End Using

        Dim ws = If(sheetName Is Nothing, book.Worksheets(0), book.Worksheets(sheetName))
        If ws Is Nothing Then Throw New ArgumentException($"No sheet named ""{sheetName}"".", NameOf(sheetName))

        Dim table As New DataTable(ws.Name)
        Dim used = ws.UsedRange
        If used.EndRow <= headerRow Then Return table

        Dim lastCol = used.EndCol
        Do While lastCol >= 0 AndAlso ws.GetCellData(headerRow, lastCol) Is Nothing
            lastCol -= 1
        Loop

        Dim lastRow = headerRow
        Do While lastRow < used.EndRow AndAlso Not IsBlankRow(ws, lastRow + 1, lastCol)
            lastRow += 1
        Loop

        For c = 0 To lastCol
            Dim name = UniqueName(table, ws.GetCellText(headerRow, c).Trim(), c)
            Dim type = ColumnType(ws, headerRow + 1, lastRow, c)
            If textColumns IsNot Nothing AndAlso textColumns.Contains(name) Then type = GetType(String)
            table.Columns.Add(name, type)
        Next

        For r = headerRow + 1 To lastRow
            Dim row = table.NewRow()
            For c = 0 To lastCol
                row(c) = ToValue(ws, r, c, table.Columns(c).DataType)
            Next
            table.Rows.Add(row)
        Next
        Return table
    End Function

    Private Function ColumnType(ws As Worksheet, firstRow As Integer, lastRow As Integer, col As Integer) As Type
        Dim found As Type = Nothing
        For r = firstRow To lastRow
            Dim t = KindOf(ws.GetCellData(Anchor(ws, r, col)))
            If t Is Nothing Then Continue For
            If found Is Nothing Then
                found = t
            ElseIf t IsNot found Then
                Return GetType(String)
            End If
        Next
        Return If(found, GetType(String))
    End Function

    ' V4 reads cells with a date format as DateTime
    Private Function KindOf(data As Object) As Type
        If TypeOf data Is Double Then Return GetType(Decimal)
        If TypeOf data Is Date Then Return GetType(Date)
        If TypeOf data Is Boolean Then Return GetType(Boolean)
        If TypeOf data Is String Then Return GetType(String)
        Return Nothing
    End Function

    Private Function ToValue(ws As Worksheet, row As Integer, col As Integer, type As Type) As Object
        Dim cell = Anchor(ws, row, col)
        Dim data = ws.GetCellData(cell)
        If KindOf(data) Is Nothing Then Return DBNull.Value
        If type Is GetType(String) Then Return ws.GetCellText(cell)
        If type Is GetType(Decimal) Then Return CDec(data)
        Return data
    End Function

    Private Function Anchor(ws As Worksheet, row As Integer, col As Integer) As CellPosition
        Return ws.GetRangeIfMergedCell(New CellPosition(row, col)).StartPos
    End Function

    Private Function IsBlankRow(ws As Worksheet, row As Integer, lastCol As Integer) As Boolean
        For c = 0 To lastCol
            If ws.GetCellData(row, c) IsNot Nothing Then Return False
        Next
        Return True
    End Function

    Private Function UniqueName(table As DataTable, header As String, col As Integer) As String
        Dim name = If(header = "", $"Column{col + 1}", header)
        Dim candidate = name
        Dim n = 2
        Do While table.Columns.Contains(candidate)
            candidate = $"{name} ({n})"
            n += 1
        Loop
        Return candidate
    End Function

End Module

Three things differ from V5:

  • V4 reads cells with a date format as DateTime from the start, so the module skips the date check on the format.
  • V4 recalculates the formulas it loads. A cell whose formula ends in an error holds Nothing, which becomes DBNull like a blank cell.
  • The V4 module does not carry formats into the DataTable. The form’s DotNetFormatOf then formats only the date columns; give numeric columns a Format of their own if you want thousands separators.

How it compares with OLEDB and Interop

OLEDB (Microsoft.ACE.OLEDB.12.0)Office InteropReoGrid
Needed on the user’s PCThe Access Database Engine, matching the app’s bitnessExcelNothing beyond the package built into the app
Column typesGuessed from the first eight rows; values that don’t fit come back emptyEach cell arrives as an ObjectChosen from every row
In a service or Task SchedulerThe bitness has to matchNot supported by MicrosoftRuns without a UI

If users should edit the imported data in place, you can also put a ReoGridControl where the DataGridView was. It shows the Excel sheet with its borders and formats, and formulas and filters keep working (Reading, Writing, and Displaying Excel Files in VB.NET).


Summary

  • When a title sits above the header, name the header row with headerRow
  • An xlsx date is a number with a date format: spot it with NumberFormatter.IsDateFormat and convert it with DateTime.FromOADate
  • Choose each column’s type from every row; make mixed columns String and check them before saving
  • Fill each row of a vertically merged range with the value of its top-left cell
  • Read leading zeros that come from a number format as display text with GetDisplayText
  • Stopping at the first blank row keeps the total row and notes out of the data
  • Opened with FileShare.ReadWrite, a file that is still open in Excel can be read
  • V5 for .NET 10, V4 for .NET Framework 4.8 — the module has nearly the same shape in both

See what’s new in V5 / Start a 30-day trial


Try ReoGrid in your own project

The Excel-compatible spreadsheet component for .NET WinForms, WPF and Avalonia. 30-day free trial — no credit card required.

Newsletter

New releases, straight to your inbox

Occasional updates on ReoGrid releases, features, and technical articles. Unsubscribe anytime.

Related articles