Open and read XLSX/OpenXML files from your C#/VB.NET applications

The following example shows how you can read XLSX (Microsoft Excel Open XML Spreadsheet) and other Excel files by using only the GemBox.Spreadsheet .NET component.

GemBox.Spreadsheet can open many Excel file formats (including XLSX, XLS, XLSB, ODS, CSV, XML and HTML) in the same manner. Once you load your file from a disk, stream, database or some other source, you can read it, import its content, or modify it.

Steps for reading XLSX files

You can read XLSX files with just a few lines of code. You only need an IDE like Visual Studio or JetBrains Rider and .NET Framework, .NET Core, or any other platform that implements .NET Standard.

Follow these steps to load a file from a disk and read its content:

Create or use an existing C# or VB.NET project.
Download and install GemBox.Spreadsheet Setup.
Add a reference to GemBox.Spreadsheet.dll within your C# or VB.NET project.
Optionally, you can add GemBox.Spreadsheet Nuget package to your C# or VB.NET project.
Execute the C#/VB.NET code below.

Try reading XLSX files online

You can test reading the content of your XLSX files with the interactive example below. Just upload your file and click on Run Example. After clicking on Run example, the C#/VB.NET code will be compiled, and the content of your file will be read with only .NET framework and the GemBox.Spreadsheet component.

The following example loads the Excel file, iterates through its cells, and appends the content of the cells to StringBuilder.

C#
VB.NET

using System;
using System.Text;
using GemBox.Spreadsheet;

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

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

        var sb = new StringBuilder();

        // Iterate through all worksheets in an Excel workbook.
        foreach (var worksheet in workbook.Worksheets)
        {
            sb.AppendLine();
            sb.AppendFormat("{0} {1} {0}", new string('-', 25), worksheet.Name);

            // Iterate through all rows in an Excel worksheet.
            foreach (var row in worksheet.Rows)
            {
                sb.AppendLine();

                // Iterate through all allocated cells in an Excel row.
                foreach (var 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 you are using the Professional version, enter your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")

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

        Dim sb = New StringBuilder()

        ' Iterate through all worksheets in an Excel workbook.
        For Each worksheet In workbook.Worksheets

            sb.AppendLine()
            sb.AppendFormat("{0} {1} {0}", New String("-"c, 25), worksheet.Name)

            ' Iterate through all rows in an Excel worksheet.
            For Each row In worksheet.Rows

                sb.AppendLine()

                ' Iterate through all allocated cells in an Excel row.
                For Each cell 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

Sheet values read with GemBox.Spreadsheet — Screenshot of sheet values read with GemBox.Spreadsheet

Import data from XLSX to DataTable

With GemBox.Spreadsheet you can also import data from any sheet to a DataTable. GemBox.Spreadsheet offers lots of options, such as the possibility to detect the columns data type, that can simplify your code.

The following example shows how you can load XLSX or any other Excel file and create a DataTable from a specific cell range in the Excel worksheet in C# and VB.NET.

C#
VB.NET

using System;
using System.Data;
using System.Text;
using GemBox.Spreadsheet;

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

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

        // Select the first worksheet from the file.
        var worksheet = workbook.Worksheets[0];

        // Create DataTable from an Excel worksheet.
        var dataTable = worksheet.CreateDataTable(new CreateDataTableOptions()
        {
            ColumnHeaders = true,
            StartRow = 1,
            NumberOfColumns = 5,
            NumberOfRows = worksheet.Rows.Count - 1,
            Resolution = ColumnTypeResolution.AutoPreferStringCurrentCulture
        });

        // Write DataTable content
        var sb = new StringBuilder();
        sb.AppendLine("DataTable content:");
        foreach (DataRow row in dataTable.Rows)
        {
            sb.AppendFormat("{0}\t{1}\t{2}\t{3}\t{4}", row[0], row[1], row[2], row[3], row[4]);
            sb.AppendLine();
        }

        Console.WriteLine(sb.ToString());
    }
}

Imports System
Imports System.Data
Imports System.Text
Imports GemBox.Spreadsheet

Module Program

    Sub Main()

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

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

        ' Select the first worksheet from the file.
        Dim worksheet = workbook.Worksheets(0)

        ' Create DataTable from an Excel worksheet.
        Dim dataTable = worksheet.CreateDataTable(New CreateDataTableOptions() With
        {
            .ColumnHeaders = True,
            .StartRow = 1,
            .NumberOfColumns = 5,
            .NumberOfRows = worksheet.Rows.Count - 1,
            .Resolution = ColumnTypeResolution.AutoPreferStringCurrentCulture
        })

        ' Write DataTable content
        Dim sb = New StringBuilder()
        sb.AppendLine("DataTable content:")
        For Each row As DataRow In dataTable.Rows

            sb.AppendFormat("{0}" & vbTab & "{1}" & vbTab & "{2}" & vbTab & "{3}" & vbTab & "{4}", row(0), row(1), row(2), row(3), row(4))
            sb.AppendLine()
        Next

        Console.WriteLine(sb.ToString())
    End Sub
End Module

XLSX file format

XLSX is the default format when creating a spreadsheet using modern versions of Excel. It was first introduced with Microsoft Office 2007 in the Office Open XML standard, a zipped, XML-based file format developed by Microsoft.

Since XLSX is in essence a set of zipped XML files, there are a lot of free tools that can read it, such as Open XML SDK. But they usually don't support other file formats, or advanced features like calculation engine, and don't have a high level model that supports reading, modifying, or creating files without the need to study OpenXML specification.

GemBox.Spreadsheet makes working with XLSX files a lot easier. It offers a high level model, and lots of utility functions like calculation engine and auto fit. Besides the XLSX file format, you can also read the following file formats: XLS, XLSB, ODS, CSV, TXT, XLSM, XLTX, XLTM, XML HTML, MHTML.

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