How to Enhance Import and Export Performance for the GCExcel API for C# .NET

GrapeCity Documents for Excel for .NET (GcExcel) offers to generate Excel (XLSX) spreadsheet documents programmatically using a comprehensive set of APIs. It supports creating, manipulating, converting, and sharing Microsoft Excel-compatible spreadsheets for multiple platforms, including .NET Framework, .NET Core, and Mono; thus making it the perfect solution for all your spreadsheet challenges.

GcExcel API allows users to import, calculate, query, generate, and export any spreadsheet scenario, including reference Excel reports, sort & filter tables, and pivot tables, add charts, sparklines, conditional formats, and dashboard reports, etc.

When importing and exporting XLSX files, it may not be required to transfer everything in the same exact way. Sometimes you might need just data, while at other times, you might want to keep only formula results. To let you choose the features to keep and/or exclude, GcExcel provides various import and export options. They are especially useful when dealing with large files containing multiple sheets, several formulas, or many shapes. Thus, providing an optimized import and export experience.

Ready to Try it Out? Download GrapeCity Documents for Excel .NET Today!

This blog presents the time performance for the import options while loading an Excel (XLSX) file and the file size that are optimized using the export options. The tests are conducted on a few Excel documents with real-world functions, formulas, and data in several rows and columns. Check out the details below.

Test Machine Configuration

Windows

• OS - Microsoft Windows 10 Pro
• Version - 21H2 (OS Build 19044.1645)
• System Type - x64
• Processor - Intel(R) Core(TM) i7-10850H CPU @ 2.70GHz 2.71 GHz, 6 Core(s), 12 Logical Processor(s)
• Installed Physical Memory (RAM) - 16.00 GB

Test Application Type

• .NET 6 Application

Product Version

• GcExcel (v6.1.0)

Test data

A few Excel files (download here!) with simple & complex formulas, large data records, data in multiple columns, styles, named objects, etc.

Excel XLSX Import Performance

GcExcel provides two options while importing an Excel XLSX document - DoNotAutoFitAfterOpened and DoNotRecalculateAfterOpened. While the former skips rendering calculation to adjust row and column dimensions, the latter skips formulas calculation again after loading a file, thereby improving the performance.

Test Metrics

• Time taken to load the document

The results for the load time calculated for the above test documents with and without the import options are depicted below:

And below is the code used to take the measurements:

OutFilePath = Path.Combine("Files", "Output", "GcExcel");
InputFilePath = Path.Combine("Files", "Input");

Workbook workbook = new Workbook();
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
  
//NO IMPORT OPTION            
workbook.Open(InputFilePath);

//IMPORT OPTIONS
//XlsxOpenOptions openOptions = new XlsxOpenOptions();
//openOptions.DoNotAutoFitAfterOpened = true;
//openOptions.DoNotRecalculateAfterOpened = true;
//workbook.Open(inputFile, openOptions);

stopwatch.Stop();
openTime = stopwatch.ElapsedMilliseconds / 1000d;
            
workbook.Save(Path.Combine(OutFilePath, fileName)); 

Excel XLSX Export Optimization

GcExcel allows controlling the export of unused styles and named objects, and cell regions without any data using the options - ExcludeUnusedStyles, ExcludeUnusedNames, and ExcludeEmptyRegionCells. These options let you choose whether to keep or discard the items that are not required in the exported document. Thus, it helps in optimizing the size of the saved file.

Test Metrics

• File size after saving the document

The file size after saving these test documents with and without the export options is depicted below:

Here is the detailed code:

OutFilePath = Path.Combine("Files", "Output", "GcExcel");
InputFilePath = Path.Combine("Files", "Input");

Workbook workbook = new Workbook();
workbook.Open(InputFilePath);            

//NO EXPORT OPTION
workbook.Save(Path.Combine(OutFilePath, fileName));

//EXPORT OPTIONS
//XlsxSaveOptions saveOptions = new XlsxSaveOptions();
//saveOptions.ExcludeUnusedNames = true;
//saveOptions.ExcludeUnusedStyles = true;
//saveOptions.ExcludeEmptyRegionCells = true;
//saveOptions.IgnoreFormulas = true;

//workbook.Save(Path.Combine(OutFilePath, fileName), saveOptions);

decimal size = (decimal)new FileInfo(Path.Combine(OutFilePath, fileName)).Length;
decimal sizeInKB = size / 1024;
sizeInMB = Math.Round(sizeInKB / 1024, 2);

Performance Sample

Download the sample and run the tests for GcExcel APIs on your own!

Note: The results were taken on particular machine configurations. The values may differ from our collected results if you run at a different configuration. If you observe any discrepancies in the overall performance, please leave your comments below.

Ready to Try it Out? Download GrapeCity Documents for Excel .NET Today!