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.

See the full code below, use Run Example to execute.

C#
VB.NET

using System;
using System.Collections.Generic;
using System.Drawing;
using System.Text;
using GemBox.Spreadsheet;

class Sample
{
    [STAThread]
    static void Main(string[] args)
    {
        // If using Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

        ExcelFile ef = ExcelFile.Load("SimpleTemplate.xlsx");

        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.Text
Imports System.Drawing
Imports GemBox.Spreadsheet

Module Samples

    Sub Main()

        ' If using Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")

        Dim ef As ExcelFile = ExcelFile.Load("SimpleTemplate.xlsx")

        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 sample.