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.

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
Performance Screenshot

See the full code below.

using System;
using System.Diagnostics;
using GemBox.Spreadsheet;

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

            // If sample 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;
            string fileFormat = "XLSX";

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

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

            ExcelFile ef = new ExcelFile();
            ExcelWorksheet ws = ef.Worksheets.Add("Performance");

            for (int row = 0; row < rowCount; row++)
                for (int column = 0; column < columnCount; column++)
                    ws.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 ws.Rows)
                foreach (var cell in row.AllocatedCells)
                    ++cellsCount;

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

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

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

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

Module Module1

    Sub Main()

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

        ' If sample 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 As String = "XLSX"

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

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

        Dim ef As New ExcelFile()
        Dim ws As ExcelWorksheet = ef.Worksheets.Add("Performance")

        For row As Integer = 0 To rowCount - 1
            For column As Integer = 0 To columnCount - 1
                ws.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 ws.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()

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

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

    End Sub

End Module

Check next sample.