Open and read Excel files from your .NET Core applications

The following example shows how you can read Excel files by using only the GemBox.Spreadsheet .NET Core 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, modify it, or export to PDF.

GemBox.Spreadsheet can be used on various .NET platforms such as .NET Framework, .NET Core, Xamarin, and Universal Windows Platform (UWP).

Steps for reading Excel files

You can read Excel 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 .NET Core project.
  • Download and install GemBox.Spreadsheet Setup.
  • Add a reference to GemBox.Spreadsheet.dll within your .NET Core 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 Excel files online

You can test reading the content of your Excel 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.

Upload your file (Drag file here)
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 Excel 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 Excel file and create a DataTable from a specific cell range in the Excel worksheet in .NET Core.

Upload your file (Drag file here)
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

To see how to use GemBox.Spreadsheet in an ASP.NET Core application, check the following example.

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