Open and read Excel files in C# and VB.NET

GemBox.Spreadsheet provides fast reading of Excel files from your C# or VB.NET application. It can read a million rows in less than four seconds and with a low memory footprint (small memory consumption). For more information, see the Performance example.

There are multiple ways you can iterate through Excel cells and retrieve their data using the optimized performance of GemBox.Spreadsheet. The following example shows how you can read all cells' data (their values and types) in any spreadsheet.

Opening and reading Excel workbook's cell values in C# and VB.NET
Screenshot of read cells data from the input Excel workbook
Upload your file (Drag file here)
using System;
using GemBox.Spreadsheet;

class Program
{
    static void Main()
    {
        // If using the Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

        // Load Excel workbook from file's path.
        ExcelFile workbook = ExcelFile.Load("%InputFileName%");

        // Iterate through all worksheets in a workbook.
        foreach (ExcelWorksheet worksheet in workbook.Worksheets)
        {
            // Display sheet's name.
            Console.WriteLine("{1} {0} {1}\n", worksheet.Name, new string('#', 30));

            // Iterate through all rows in a worksheet.
            foreach (ExcelRow row in worksheet.Rows)
            {
                // Iterate through all allocated cells in a row.
                foreach (ExcelCell cell in row.AllocatedCells)
                {
                    // Read cell's data.
                    string value = cell.Value?.ToString() ?? "EMPTY";

                    // For merged cells, read only the first cell's data.
                    if (cell.MergedRange != null && cell.MergedRange[0] != cell)
                        value = "MERGED";

                    // Display cell's value and type.
                    value = value.Length > 15 ? value.Remove(15) + "…" : value; 
                    Console.Write($"{value} [{cell.ValueType}]".PadRight(30));
                }

                Console.WriteLine();
            }
        }
    }
}
Imports System
Imports GemBox.Spreadsheet

Module Program

    Sub Main()

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

        ' Load Excel workbook from file's path.
        Dim workbook As ExcelFile = ExcelFile.Load("%InputFileName%")

        ' Iterate through all worksheets in a workbook.
        For Each worksheet As ExcelWorksheet In workbook.Worksheets

            ' Display sheet's name.
            Console.WriteLine("{1} {0} {1}" & vbLf, worksheet.Name, New String("#"C, 30))

            ' Iterate through all rows in a worksheet.
            For Each row As ExcelRow In worksheet.Rows

                ' Iterate through all allocated cells in a row.
                For Each cell As ExcelCell In row.AllocatedCells

                    ' Read cell's data.
                    Dim value As String = If(cell.Value?.ToString(), "EMPTY")

                    ' For merged cells, read only the first cell's data.
                    If cell.MergedRange IsNot Nothing AndAlso cell.MergedRange(0) <> cell Then value = "MERGED"

                    ' Display cell's value and type.
                    value = If(value.Length > 15, value.Remove(15) & "…", value)
                    Console.Write($"{value} [{cell.ValueType}]".PadRight(30))
                Next

                Console.WriteLine()
            Next
        Next
    End Sub
End Module

You can open and read many Excel formats (like XLSX, XLS, XLSB, ODS, CSV, XML and HTML) in the same manner. The spreadsheets can be loaded using one of the ExcelFile.Load methods. These methods enable you to work with a physical file (when providing the file's path) or with an in-memory file (when providing the file's Stream).

You can specify the format of your Excel file by providing an object from the LoadOptions derived class (like XlsxLoadOptions, XlsLoadOptions, XlsbLoadOptions, OdsLoadOptions, CsvLoadOptions, XmlLoadOptions, and HtmlLoadOptions). Or you can let GemBox.Spreadsheet choose the appropriate options for you when opening the file by omitting the LoadOptions.

Reading Excel cells with the row and column index

When you iterate through ExcelCell objects in an ExcelRow, you should use ExcelRow.AllocatedCells instead of ExcelRow.Cells to prevent unnecessary memory allocations.

You should also prefer iterating through cells in ExcelRow rather than ExcelColumn because cells are internally allocated in rows and not in columns.

The following example shows how you can iterate through cells using the row and column index.

Reading names of Excel objects (sheets, rows, and cells) with index in C# and VB.NET
Screenshot of sheet, row, and cell names read using zero-based indexers
Upload your file (Drag file here)
using System;
using GemBox.Spreadsheet;

class Program
{
    static void Main()
    {
        // If using the Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

        ExcelFile workbook = ExcelFile.Load("%InputFileName%");

        for (int sheetIndex = 0; sheetIndex < workbook.Worksheets.Count; sheetIndex++)
        {
            // Get Excel worksheet using zero-based index.
            ExcelWorksheet worksheet = workbook.Worksheets[sheetIndex];
            Console.WriteLine($"Sheet name: \"{worksheet.Name}\"");
            Console.WriteLine($"Sheet index: {worksheet.Index}\n");

            for (int rowIndex = 0; rowIndex < worksheet.Rows.Count; rowIndex++)
            {
                // Get Excel row using zero-based index.
                ExcelRow row = worksheet.Rows[rowIndex];
                Console.WriteLine($"Row name: \"{row.Name}\"");
                Console.WriteLine($"Row index: {row.Index}");

                Console.Write("Cell names:");
                for (int columnIndex = 0; columnIndex < row.AllocatedCells.Count; columnIndex++)
                {
                    // Get Excel cell using zero-based index.
                    ExcelCell cell = row.Cells[columnIndex];
                    Console.Write($" \"{cell.Name}\",");
                }
                Console.WriteLine("\n");
            }
        }
    }
}
Imports System
Imports GemBox.Spreadsheet

Module Program

    Sub Main()

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

        Dim workbook As ExcelFile = ExcelFile.Load("%InputFileName%")

        For sheetIndex As Integer = 0 To workbook.Worksheets.Count - 1

            ' Get Excel worksheet using zero-based index.
            Dim worksheet As ExcelWorksheet = workbook.Worksheets(sheetIndex)
            Console.WriteLine($"Sheet name: ""{worksheet.Name}""")
            Console.WriteLine($"Sheet index: {worksheet.Index}" & vbLf)

            For rowIndex As Integer = 0 To worksheet.Rows.Count - 1

                ' Get Excel row using zero-based index.
                Dim row As ExcelRow = worksheet.Rows(rowIndex)
                Console.WriteLine($"Row name: ""{row.Name}""")
                Console.WriteLine($"Row index: {row.Index}")

                Console.Write("Cell names:")
                For columnIndex As Integer = 0 To row.AllocatedCells.Count - 1

                    ' Get Excel cell using zero-based index.
                    Dim cell As ExcelCell = row.Cells(columnIndex)
                    Console.Write($" ""{cell.Name}"",")
                Next
                Console.WriteLine(vbLf)
            Next
        Next
    End Sub
End Module

Reading Excel cells with enumerator

Another way how you can iterate through cells is by using the CellRange.GetReadEnumerator method. The difference between the GetReadEnumerator and GetEnumerator methods is that the first one will iterate only through allocated cells.

You can use the GetReadEnumerator method on ExcelWorksheet.Cells, ExcelRow.Cells, ExcelColumn.Cells, and any other arbitrary CellRange that you retrieve from the Excel file. For more information, see the Cell Referencing example.

The following example shows how you can read existing cells (only allocated cells) using the CellRangeEnumerator.

Reading Excel cells value and location (row, column) with enumerator in C# and VB.NET
Screenshot of cell positions and values read using enumerator
Upload your file (Drag file here)
using System;
using GemBox.Spreadsheet;

class Program
{
    static void Main()
    {
        // If using the Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

        ExcelFile workbook = ExcelFile.Load("%InputFileName%");

        foreach (ExcelWorksheet worksheet in workbook.Worksheets)
        {
            CellRangeEnumerator enumerator = worksheet.Cells.GetReadEnumerator();
            while (enumerator.MoveNext())
            {
                ExcelCell cell = enumerator.Current;
                Console.WriteLine($"Cell \"{cell.Name}\" [{cell.Row.Index}, {cell.Column.Index}]: {cell.Value}");
            }
        }
    }
}
Imports System
Imports GemBox.Spreadsheet

Module Program

    Sub Main()

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

        Dim workbook As ExcelFile = ExcelFile.Load("%InputFileName%")

        For Each worksheet As ExcelWorksheet In workbook.Worksheets

            Dim enumerator As CellRangeEnumerator = worksheet.Cells.GetReadEnumerator()
            While enumerator.MoveNext()

                Dim cell As ExcelCell = enumerator.Current
                Console.WriteLine($"Cell ""{cell.Name}"" [{cell.Row.Index}, {cell.Column.Index}]: {cell.Value}")
            End While
        Next
    End Sub
End Module

See also


Next steps

GemBox.Spreadsheet is a .NET component that enables you to read, write, edit, convert, and print spreadsheet files from your .NET applications using one simple API.

Download Buy