Read Excel file in C# and VB.NET
GemBox.Spreadsheet can read many Excel file formats (including XLS, XLSX, CSV and ODS) in the same manner. Specifically, it uses a Load method to read from a physical Excel file or the Excel file's stream in C# and VB.NET.
The following example demonstrates how to read all ExcelCells, their Value and ValueTypes, in ExcelFile object.
Note that when you iterate through all read cells in an ExcelRow, you should use AllocatedCells collection to prevent an unnecessary allocation of ExcelCell
objects.

using System;
using System.Text;
using GemBox.Spreadsheet;
class Program
{
static void Main(string[] args)
{
// If using Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
ExcelFile ef = ExcelFile.Load("%InputFileName%");
StringBuilder sb = new StringBuilder();
// Iterate through all worksheets in an Excel workbook.
foreach (ExcelWorksheet sheet in ef.Worksheets)
{
sb.AppendLine();
sb.AppendFormat("{0} {1} {0}", new string('-', 25), sheet.Name);
// Iterate through all rows in an Excel worksheet.
foreach (ExcelRow row in sheet.Rows)
{
sb.AppendLine();
// Iterate through all allocated cells in an Excel row.
foreach (ExcelCell cell in row.AllocatedCells)
{
if (cell.ValueType != CellValueType.Null)
sb.Append(string.Format("{0} [{1}]", cell.Value, cell.ValueType).PadRight(25));
else
sb.Append(new string(' ', 25));
}
}
}
Console.WriteLine(sb.ToString());
}
}
Imports System
Imports System.Text
Imports GemBox.Spreadsheet
Module Program
Sub Main()
' If using Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")
Dim ef As ExcelFile = ExcelFile.Load("%InputFileName%")
Dim sb As New StringBuilder()
' Iterate through all worksheets in an Excel workbook.
For Each sheet As ExcelWorksheet In ef.Worksheets
sb.AppendLine()
sb.AppendFormat("{0} {1} {0}", New String("-"c, 25), sheet.Name)
' Iterate through all rows in an Excel worksheet.
For Each row As ExcelRow In sheet.Rows
sb.AppendLine()
' Iterate through all allocated cells in an Excel row.
For Each cell As ExcelCell In row.AllocatedCells
If cell.ValueType <> CellValueType.Null Then
sb.Append(String.Format("{0} [{1}]", cell.Value, cell.ValueType).PadRight(25))
Else
sb.Append(New String(" "c, 25))
End If
Next
Next
Next
Console.WriteLine(sb.ToString())
End Sub
End Module
Check next example or download examples from GitHub.