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("..\..\..\FontAndFormat.xls")

' Uncomment for XLSX reading
        ' Dim Wbook = ExcelWorkbook.ReadXLSX("..\..\..\FontAndFormat.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("Name")
        DataTable1.Columns.Add("Size")
        DataTable1.Columns.Add("Color")
        DataTable1.Columns.Add("Bold")
        DataTable1.Columns.Add("Italic")
        DataTable1.Columns.Add("Underline")
        DataTable1.Columns.Add("Strikeout")
        DataTable1.Columns.Add("Subscript")
        DataTable1.Columns.Add("Superscript")
        DataTable1.Columns.Add("Rotation")
        DataTable1.Columns.Add("StringFormat")
        DataTable1.Columns.Add("HorizontalAlignment")
        DataTable1.Columns.Add("VerticalAlignment")

' 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).Cells(i, j).ValueWithFormat()
                DataTable1.Rows(Count)(2) = Wbook.Worksheets(0).Cells(i, j).Style.Font.Name
                DataTable1.Rows(Count)(3) = Wbook.Worksheets(0).Cells(i, j).Style.Font.Size
                DataTable1.Rows(Count)(4) = Wbook.Worksheets(0).Cells(i, j).Style.Font.Color
                DataTable1.Rows(Count)(5) = Wbook.Worksheets(0).Cells(i, j).Style.Font.Bold
                DataTable1.Rows(Count)(6) = Wbook.Worksheets(WorksheetName).Columns(j).Cells(i).Style.Font.Italic
                DataTable1.Rows(Count)(7) = Wbook.Worksheets(WorksheetName).Columns(j).Cells(i).Style.Font.Underline
                DataTable1.Rows(Count)(8) = Wbook.Worksheets(WorksheetName).Columns(j).Cells(i).Style.Font.Strikeout
                DataTable1.Rows(Count)(9) = Wbook.Worksheets(WorksheetName).Columns(j).Cells(i).Style.Font.Subscript
                DataTable1.Rows(Count)(10) = Wbook.Worksheets("FontAndFormat").Rows(i).Cells(j).Style.Font.Superscript
                DataTable1.Rows(Count)(11) = Wbook.Worksheets("FontAndFormat").Rows(i).Cells(j).Style.Rotation
                DataTable1.Rows(Count)(12) = Wbook.Worksheets("FontAndFormat").Rows(i).Cells(j).Style.StringFormat
                DataTable1.Rows(Count)(13) = Wbook.Worksheets("FontAndFormat").Rows(i).Cells(j).Style.HorizontalAlignment
                DataTable1.Rows(Count)(14) = Wbook.Worksheets("FontAndFormat").Rows(i).Cells(j).Style.VerticalAlignment
                Count = Count + 1
            Next j
        Next i

DataGridView1.DataSource = DataTable1
    End Sub

End Class