Performances on large Excel in C# and VB.NET

Following example demonstrates 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.

Screenshot of performance of GemBox.Spreadsheet on large Excel file
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

Download examples from GitHub.