Performances on large Excel in C# and VB.NET

The following example shows how to test performance of GemBox.Spreadsheet component on large spreadsheet files when using the Free version of the component.

If free version limits are exceeded, component will continue to work in Trial mode if FreeLimitReached event is handled as in the example.

For more information about Trial mode limitations, see GemBox.Spreadsheet Evaluation and Licensing help page.

Performance of GemBox.Spreadsheet on large Excel file
Screenshot of performance of GemBox.Spreadsheet on large Excel file
using System;
using System.Diagnostics;
using GemBox.Spreadsheet;

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

        // If example exceeds Free version limitations then continue as Trial version:
        // https://www.gemboxsoftware.com/spreadsheet/help/html/Evaluation_and_Licensing.htm
        SpreadsheetInfo.FreeLimitReached += (sender, e) => e.FreeLimitReachedAction = FreeLimitReachedAction.ContinueAsTrial;

        int rowCount = 100000;
        int columnCount = 10;
        var fileFormat = "XLSX";

        Console.WriteLine("Performance example:");
        Console.WriteLine();
        Console.WriteLine("Row count: " + rowCount);
        Console.WriteLine("Column count: " + columnCount);
        Console.WriteLine("File format: " + fileFormat);
        Console.WriteLine();

        var stopwatch = new Stopwatch();
        stopwatch.Start();

        var workbook = new ExcelFile();
        var worksheet = workbook.Worksheets.Add("Performance");

        for (int row = 0; row < rowCount; row++)
            for (int column = 0; column < columnCount; column++)
                worksheet.Cells[row, column].Value = row.ToString() + "_" + column;

        Console.WriteLine("Generate file (seconds): " + stopwatch.Elapsed.TotalSeconds);

        stopwatch.Reset();
        stopwatch.Start();

        int cellsCount = 0;
        foreach (var row in worksheet.Rows)
            foreach (var cell in row.AllocatedCells)
                ++cellsCount;

        Console.WriteLine("Iterate through " + cellsCount + " cells (seconds): " + stopwatch.Elapsed.TotalSeconds);

        stopwatch.Reset();
        stopwatch.Start();

        workbook.Save("Report." + fileFormat.ToLower());

        Console.WriteLine("Save file (seconds): " + stopwatch.Elapsed.TotalSeconds);
    }
}
Imports System
Imports System.Diagnostics
Imports GemBox.Spreadsheet

Module Program

    Sub Main()

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

        ' If example exceeds Free version limitations then continue as trial version:
        ' https://www.gemboxsoftware.com/spreadsheet/help/html/Evaluation_and_Licensing.htm
        AddHandler SpreadsheetInfo.FreeLimitReached, Sub(sender, e) e.FreeLimitReachedAction = FreeLimitReachedAction.ContinueAsTrial

        Dim rowCount As Integer = 100000
        Dim columnCount As Integer = 10
        Dim fileFormat = "XLSX"

        Console.WriteLine("Performance example:")
        Console.WriteLine()
        Console.WriteLine("Row count: " & rowCount)
        Console.WriteLine("Column count: " & columnCount)
        Console.WriteLine("File format: " & fileFormat)
        Console.WriteLine()

        Dim stopwatch = New Stopwatch()
        stopwatch.Start()

        Dim workbook = New ExcelFile()
        Dim worksheet = workbook.Worksheets.Add("Performance")

        For row As Integer = 0 To rowCount - 1
            For column As Integer = 0 To columnCount - 1
                worksheet.Cells(row, column).Value = row.ToString() & "_" & column
            Next
        Next

        Console.WriteLine("Generate file (seconds): " & stopwatch.Elapsed.TotalSeconds)

        stopwatch.Reset()
        stopwatch.Start()

        Dim cellsCount As Integer = 0
        For Each row As ExcelRow In worksheet.Rows
            For Each cell As ExcelCell In row.AllocatedCells
                cellsCount += 1
            Next
        Next

        Console.WriteLine("Iterate through " & cellsCount & " cells (seconds): " & stopwatch.Elapsed.TotalSeconds)

        stopwatch.Reset()
        stopwatch.Start()

        workbook.Save("Report." & fileFormat.ToLower())

        Console.WriteLine("Save file (seconds): " & stopwatch.Elapsed.TotalSeconds)
    End Sub
End Module

Want more?

GitHub

Check the next example or select an example from the menu. You can also download our examples from the GitHub.


Like it?

Download Buy

If you want to try the GemBox.Spreadsheet yourself, you can download the free version. It delivers the same performance and set of features as the professional version, but with some operations limited. To remove the limitation, you need to purchase a license.