Performances on large Excel in C# and VB.NET

Following sample 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 sample.

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

Screenshot

Performance Screenshot

See the full code below.

1using System;
2using System.Diagnostics;
3using GemBox.Spreadsheet;
4
5namespace PerformanceCs
6{
7    class Program
8    {
9        static void Main(string[] args)
10        {
11            // If using Professional version, put your serial key below.
12            SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
13
14            // If sample exceeds Free version limitations then continue as trial version:
15            // http://www.gemboxsoftware.com/Spreadsheet/help/html/Evaluation_and_Licensing.htm
16            SpreadsheetInfo.FreeLimitReached += (sender, e) => e.FreeLimitReachedAction = FreeLimitReachedAction.ContinueAsTrial;
17
18            int rowCount = 100000;
19            int columnCount = 10;
20            string fileFormat = "XLSX";
21
22            Console.WriteLine("Performance sample:");
23            Console.WriteLine();
24            Console.WriteLine("Row count: " + rowCount);
25            Console.WriteLine("Column count: " + columnCount);
26            Console.WriteLine("File format: " + fileFormat);
27            Console.WriteLine();
28
29            Stopwatch stopwatch = new Stopwatch();
30            stopwatch.Start();
31
32            ExcelFile ef = new ExcelFile();
33            ExcelWorksheet ws = ef.Worksheets.Add("Performance");
34
35            for (int row = 0; row < rowCount; row++)
36                for (int column = 0; column < columnCount; column++)
37                    ws.Cells[row, column].Value = row.ToString() + "_" + column;
38
39            Console.WriteLine("Generate file (seconds): " + stopwatch.Elapsed.TotalSeconds);
40
41            stopwatch.Reset();
42            stopwatch.Start();
43
44            int cellsCount = 0;
45            foreach (var row in ws.Rows)
46                foreach (var cell in row.AllocatedCells)
47                    ++cellsCount;
48
49            Console.WriteLine("Iterate through " + cellsCount + " cells (seconds): " + stopwatch.Elapsed.TotalSeconds);
50
51            stopwatch.Reset();
52            stopwatch.Start();
53
54            ef.Save("Report." + fileFormat.ToLower());
55
56            Console.WriteLine("Save file (seconds): " + stopwatch.Elapsed.TotalSeconds);
57        }
58    }
59}
1Imports GemBox.Spreadsheet
2
3Module Module1
4
5    Sub Main()
6
7        ' If using Professional version, put your serial key below.
8        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")
9
10        ' If sample exceeds Free version limitations then continue as trial version:
11        ' http://www.gemboxsoftware.com/Spreadsheet/help/html/Evaluation_and_Licensing.htm
12        AddHandler SpreadsheetInfo.FreeLimitReached, Sub(sender, e) e.FreeLimitReachedAction = FreeLimitReachedAction.ContinueAsTrial
13
14        Dim rowCount As Integer = 100000
15        Dim columnCount As Integer = 10
16        Dim fileFormat As String = "XLSX"
17
18        Console.WriteLine("Performance sample:")
19        Console.WriteLine()
20        Console.WriteLine("Row count: " & rowCount)
21        Console.WriteLine("Column count: " & columnCount)
22        Console.WriteLine("File format: " & fileFormat)
23        Console.WriteLine()
24
25        Dim stopwatch As New Stopwatch()
26        stopwatch.Start()
27
28        Dim ef As New ExcelFile()
29        Dim ws As ExcelWorksheet = ef.Worksheets.Add("Performance")
30
31        For row As Integer = 0 To rowCount - 1
32            For column As Integer = 0 To columnCount - 1
33                ws.Cells(row, column).Value = row.ToString() & "_" & column
34            Next
35        Next
36
37        Console.WriteLine("Generate file (seconds): " & stopwatch.Elapsed.TotalSeconds)
38
39        stopwatch.Reset()
40        stopwatch.Start()
41
42        Dim cellsCount As Integer = 0
43        For Each row As ExcelRow In ws.Rows
44            For Each cell As ExcelCell In row.AllocatedCells
45                cellsCount += 1
46            Next
47        Next
48
49        Console.WriteLine("Iterate through " & cellsCount & " cells (seconds): " & stopwatch.Elapsed.TotalSeconds)
50
51        stopwatch.Reset()
52        stopwatch.Start()
53
54        ef.Save("Report." & fileFormat.ToLower())
55
56        Console.WriteLine("Save file (seconds): " & stopwatch.Elapsed.TotalSeconds)
57
58    End Sub
59
60End Module

Check next sample or find out more about GemBox.Spreadsheet and GemBox Software.