Excel Reader .NET component - C# / VB.NET Examples & Samples Code

Imports Docs.Excel

Public Class Form1

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

' TODO: If using Excel Reader .NET Full Version, put YOUR-LICENSE-CODE below.
        ' Otherwise, if you are using Excel Reader .NET Trial, comment out the following line
        ' (Free version doesn't have LicenseCode method). 
        ' Docs.Excel.ExcelWorkbook.SetLicenseCode("YOUR-LICENSE-CODE")

Dim Wbook = ExcelWorkbook.ReadXLS("..\..\..\ColorAndFill.xls")

' Uncomment for XLSX reading
        ' Dim Wbook = ExcelWorkbook.ReadXLSX("..\..\..\ColorAndFill.xlsx")

' Create DataTabe object
        Dim DataTable1 = New DataTable
        Dim NumberRow = Wbook.Worksheets(0).Rows.Count * Wbook.Worksheets(0).Columns.Count
        For i As Integer = 0 To NumberRow - 1
            DataTable1.Rows.Add(DataTable1.NewRow())
        Next i

DataTable1.Columns.Add("Cells ID")
        DataTable1.Columns.Add("Text")
        DataTable1.Columns.Add("BackGroundColor")
        DataTable1.Columns.Add("PatternFillStyle")
        DataTable1.Columns.Add("PatternForeColor")

' Get first Worksheet name
        Dim WorksheetName As String = Wbook.Worksheets(0).Name

' Filling DataTable
        Dim Count As Integer = 0
        For i As Integer = 0 To Wbook.Worksheets(0).Rows.Count - 1
            For j As Integer = 0 To Wbook.Worksheets(0).Columns.Count - 1
                DataTable1.Rows(Count)(0) = ExcelColumn.ColumnIndexToName(j + 1) + Convert.ToString(i + 1)
                DataTable1.Rows(Count)(1) = Wbook.Worksheets(0).Columns(j).Cells(i).ValueWithFormat()
                DataTable1.Rows(Count)(2) = Wbook.Worksheets(WorksheetName).Cells(i, j).Style.BackgroundColor
                DataTable1.Rows(Count)(3) = Wbook.Worksheets(WorksheetName).Cells(i, j).Style.PatternFillStyle
                DataTable1.Rows(Count)(4) = Wbook.Worksheets("ColorAndFill").Rows(i).Cells(j).Style.PatternForeColor
                Count = Count + 1
            Next j
        Next i

DataGridView1.DataSource = DataTable1
    End Sub

End Class