Comparing NPOI Apache POI to GcExcel.NET - An Alternative

NPOI, a .NET version of Apache POI Java API, is an open source API that can help you read/write xls, doc, and ppt files. GrapeCity Documents for Excel (GcExcel) is a high-speed, small-footprint spreadsheet API that requires no dependencies on Excel. With full .NET 5 support, you can generate, load, modify, and convert spreadsheets in .NET Framework, .NET Core, Mono, and Xamarin. Apps using this spreadsheet API can be deployed to the cloud, Windows, Mac, or Linux. Its powerful calculation engine and breadth of features mean you'll never have to compromise design or requirements.

This blog compares the performance of the two products while reading and writing Excel files with double, string, date values, and formulas. In addition, the performance tests measure Open, Calculate and Save operations on a large Excel file. The article also compares Excel features supported in the two products.

From our rigorous performance tests and feature comparisons as detailed below, we are confident GcExcel will be a great replacement for those looking for an alternative to NPOI or to enhance their current applications' performance and feature set.

Try GcExcel free for 30 days

Download the latest version of GrapeCity Documents for Excel, .NET

Download Now!

Feature Comparison

Here are the Top 12 Reasons to use GcExcel over NPOI:

  1. GcExcel is much faster and uses far less memory – performance comparisons across multiple platforms show GcExcel .NET to be an average of 190 times faster and 22 times more efficient than NPOI (see performance results below). Some operations such as setting formulas for a large range were more than 100 times faster.
  2. GcExcel supports 452 Calculation Functions in cell formulas, while NPOI is lacking in support for many newer functions, with only 157-280 calculation functions available and other functions requiring implementation by the developer.
  3. GcExcel supports Exporting to PDF including full support for controlling Page Setup options and built-in support for PDF Security Options and Document Properties, while NPOI lacks any support or examples for exporting PDF.
  4. GcExcel supports Conditional Formatting Rules consistent with Excel VSTO APIs including Icon Sets, Above Average, Date Occurring, Top/Bottom, and Duplicate/Unique, while NPOI support for conditional formatting is lacking and requires using low-level classes marked for internal purposes.
  5. GcExcel supports 53 Chart Types consistent with Excel VSTO APIs including all the newer chart types such as BoxWhisker, Funnel, Histogram, Pareto, Sunburst, Treemap, and Waterfall, while NPOI has limited support for only Bar, Column, Line, Radar, and Scatter charts.
  6. GcExcel supports Sparklines and Sparkline Groups for visualizing your data directly in cells with little charts drawn directly in the cell, while NPOI lacks any support for sparklines.
  7. GcExcel supports Cut/Copy/Paste for Shapes and Pictures while NPOI lacks any support for clipboard actions on shapes or pictures.
  8. GcExcel supports Applying Advanced Filters including Number, Text, Color, Icon, while NPOI has only very limited support for basic AutoFilter.
  9. GcExcel supports Sorting including By Value, By Multiple Values, Custom Sort, By Interior, By Font Color, and By Icon, while NPOI has no built-in support for sorting.
  10. GcExcel supports Slicers with Tables and with Pivot Tables, including Applying Built-in Slicer Styles and Custom Slicer Styles, Cut/Copy and Duplicate, and Do Filter Operation, while NPOI has no such support.
  11. GcExcel supports easily Importing and Exporting CSV, while NPOI has no such support.
  12. GcExcel supports Gradient Fills, which are not supported in NPOI – gradient fills are useful for making cells, shapes, and chart series really stand out.

There are many more reasons to use GcExcel over NPOI listed in the table below.

GcExcel .NET vs. NPOI - A Detailed Comparison Table

Feature GcExcel NPOI
Values Rich Text Yes Yes
Style Excel Like Number Formatter Yes Yes
Pattern Fill Yes Yes
Gradient Fill Yes No
Get Display Format Styles Yes No
Add String,IStyle Method Yes No
Formula Total Formulas supported 452 157
Get Is External Reference Yes No
ArrayFormula Yes Yes
All Excel Functions Yes No
Full Excel2016 functions Yes No
Support FormulaArrayR1C1 Yes No
Provide APIs to Get Dependencies of Formula Yes No
Table Table Style Yes Yes
Custom Table Style Yes Yes
Table Formula Yes Yes
Copy/Move Cells Yes Yes
Conditional Format Data Bar Yes Yes
Cell Value Yes Yes
Icon Sets Yes Yes
Color Scale Yes Yes
Custom Icon Sets Yes No
Above Average Yes No
Date Occurring Yes No
Top/Bottom10 Yes No
Duplicate/Unique Yes Yes
Formula Expression Rule Yes Yes
Chart Column Chart Yes Yes
Line Chart Yes Yes
Pie Chart Yes No
Bar Chart Yes Yes
Area Chart Yes No
Scatter Chart Yes Yes
Stock Chart Yes No
Bubble Chart Yes No
Radar Chart Yes Yes
Combination Chart Yes No
Dual Axis Chart Yes No
Chart Sheet Yes No
New Excel 2016 Statistical and Specialized Chart Types Yes No
Sparkline Yes No
Shape Shape Yes Yes
Shape Adjustments Yes Yes
Shape Coordinates (move, resize, row/column changed) Yes Yes
Shape(Chart/Picture/Comment/Slicer) cut/copy/paste Yes No
Support ShapeStyle Yes Yes
Shape group apis Yes Yes
Shape Hyperlink Yes No
Picture Yes Yes
Filter Text Filter Yes No
Number Filter Yes No
Date Filter Yes No
Icon Filter Yes No
Color Filter Yes No
Group Grouping data Yes Yes
Sort Row Sort Yes No
Multiple Column Sort Yes No
Custom Sort Yes Yes
Font Color Sort Yes No
Interior Color Sort Yes No
Icon Sort Yes No
Data Validation Yes Yes
Table Table Yes Yes
Slicer Yes No
PivotTable Pivot Table Yes Yes
Slicer Yes No
Comments Yes Yes
Importing CSV Yes No
XLSX Yes Yes
XSLM Yes Yes
Exporting CSV Yes No
XLSX Yes Yes
XSLM Yes Yes
PDF Yes No
PDF Security options Yes Yes
Sheet/Range/Shapes to image Yes No
Print & view PrintSettings Yes Yes
Excel view settings Yes Yes
Themes Themes Yes Yes
Enhance themes Yes No
Hyperlink Support Hyperlink Yes Yes
Custom functions Support Custom functions Yes Yes
Worksheet Copy and Move worksheet Yes Yes
Copy or move worksheets within or between workbooks Yes No
Search Find and replace text Yes No
VBA Macros Preserve or remove add-in, VBA, macros Yes Yes
Template Language Support Template Language to fill data in Excel sheets from database Yes No
Generate Fillable PDF Forms Build PDF forms with various form fields including HTML5 input types Yes No
Barcodes Add barcodes to worksheets Yes No
Others Freeze/Unfreeze panes Yes Yes
Options for read/write Excel Yes Yes
PageSetup Yes Yes

Performance Comparison

Test Machine Configurations
  • Linux: CPU: 4 core Intel(R) Xeon(R) CPU E5-2683 v3 @ 2.00GHz, 4GB
  • MacOS: Intel i9 @ 2.3 GHz / MacOS Catalina v10.15.6
  • Windows: Intel(R) Core(TM) i7-8700 CPU @ 3.20GHz 3.19 GHz, x64-based processor, Windows 10 Pro 20H2 Build 19042.1052.
Builds Used for Comparison

This comparison references the following build versions of GcExcel and NPOI -

Test Sample

Download the sample here.

Test Data

The first three tests populate 100,000 rows x 30 columns with values of type double, string, and date, measuring the performance for Set Values, Get Values, and Save to XLSX, and the total Memory Used (which is difficult to measure accurately since garbage collection can happen at any time).

These tests look like this (the string test uses the hard-coded string value "ABCDEFGHIJKLMNOPQRSTUVWXYZ" and the date test creates a new Date() object, but are otherwise the same):

GcExcel Double Performance Test

public static void TestSetRangeValues_Double(int rowCount, int columnCount, ref double setTime, ref double getTime, ref double saveTime, ref double usedMem)
        {

            Console.WriteLine();
            Console.WriteLine(string.Format("GcExcel benchmark for double values with {0} rows and {1} columns", rowCount, columnCount));

            IWorkbook workbook = new Workbook();
            IWorksheet worksheet = workbook.Worksheets[0];

            DateTime start = DateTime.Now;
            double[,] values =new double[rowCount, columnCount];

            for (int i = 0; i < rowCount; i++)
            {
                for (int j = 0; j < columnCount; j++)
                {
                    values[i,j] = i + j;
                }
            }

            worksheet.Range[0, 0, rowCount, columnCount].Value = values;
            DateTime end = DateTime.Now;

            setTime = (end - start).TotalSeconds;
            Console.WriteLine(string.Format("GcExcel set double values: {0:N3} s", setTime));

            start = DateTime.Now;
            object tmpValues = worksheet.Range[0, 0, rowCount, columnCount].Value;
            end = DateTime.Now;

            getTime = (end - start).TotalSeconds;;
            Console.WriteLine(string.Format("GcExcel get double values: {0:N3} s", getTime));

            start = DateTime.Now;
            workbook.Save("../../output/gcexcel-saved-doubles.xlsx");
            end = DateTime.Now;
            saveTime = (end - start).TotalSeconds;;
            Console.WriteLine(string.Format("GcExcel save doubles to Excel: {0:N3} s", saveTime));


        }

The last two tests focus more on calculation performance.

The following test populates 100,000 rows x 2 columns of double values, then sets formulas to a range of 100,000 rows x 30 columns adjacent to and referencing those double values in the first 2 columns, measuring the performance for Set Formulas, Calculate, Save to XLSX, and the total Memory Used.

This test looks like this:

GcExcel Open Large XLSX Performance Test

public static void TestBigExcelFile(int rowCount, int columnCount, ref double openTime, ref double calcTime, ref double saveTime, ref double usedMem)
        {

            Console.WriteLine();
            Console.WriteLine(string.Format("GcExcel benchmark for test-performance.xlsx which is 20.5MB with a lot of values, formulas and styles"));

            IWorkbook workbook = new Workbook();

            DateTime start = DateTime.Now;
            workbook.Open("../../files/test-performance.xlsx");
            DateTime end = DateTime.Now;

            openTime = (end - start).TotalSeconds;;
            Console.WriteLine(string.Format("GcExcel open big Excel: {0:N3} s", openTime));

            start = DateTime.Now;
            workbook.Dirty();
            workbook.Calculate();
            end = DateTime.Now;

            calcTime = (end - start).TotalSeconds;;
            Console.WriteLine(string.Format("GcExcel calculate formulas for big Excel: {0:N3} s", calcTime));

            start = DateTime.Now;
            workbook.Save("../../output/gcexcel-saved-test-performance.xlsx");
            end = DateTime.Now;
            saveTime = (end - start).TotalSeconds;;
            Console.WriteLine(string.Format("GcExcel save back to big Excel: {0:N3} s", saveTime));

        }

The workbook loads a large XLSX which contains many formulas using volatile functions that require recalculation each time the worksheet is calculated and measures the performance for Open File, Calculate, Save to XLSX, and the total Memory Used.

This test looks like this:

GcExcel Formulas Performance Test

public static void TestSetRangeFormulas(int rowCount, int columnCount, ref double setTime, ref double calcTime, ref double saveTime, ref double usedMem)
        {
            Console.WriteLine(string.Format("GcExcel benchmark for double values with {0} rows and {1} columns", rowCount, columnCount));

            IWorkbook workbook = new Workbook();
            workbook.ReferenceStyle = ReferenceStyle.R1C1;
            IWorksheet worksheet = workbook.Worksheets[0];

            double[,] values = new double[ rowCount, 2];

            for (int i = 0; i < rowCount; i++)
            {
                for (int j = 0; j < 2; j++)
                {
                    values[i,j] = i + j;
                }
            }
            worksheet.Range[0, 0, rowCount, 2].Value = values;

            DateTime start = DateTime.Now;
            worksheet.Range[0, 2, rowCount-2, columnCount].Formula = "=SUM(RC[-2],RC[-1])";
            DateTime end = DateTime.Now;

            setTime = (end - start).TotalSeconds;;
            Console.WriteLine(string.Format("GcExcel set formulas: {0:N3} s", setTime));

            start = DateTime.Now;
            workbook.Calculate();
            end = DateTime.Now;

            calcTime = (end - start).TotalSeconds;;
            Console.WriteLine(string.Format("GcExcel calculates formula: {0:N3} s", calcTime));

            workbook.ReferenceStyle = ReferenceStyle.A1;

            start = DateTime.Now;
            workbook.Save("../../output/gcexcel-saved-formulas.xlsx");
            end = DateTime.Now;
            saveTime = (end - start).TotalSeconds;;
            Console.WriteLine(string.Format("GcExcel save formulas to Excel: {0:N3} s", saveTime));

        }

Performance Results

Following are the full performance details:

GcExcel performs better than NPOI in various operations on loading, modifying and saving the Excel file. Have a look at the numbers below.

Excel

Time taken in seconds:

Windows

MacOS

Linux

Memory consumption (MB)

MemoryConsumption

Note: The results were taken on particular machine configurations. If you are running at a different configuration, the values may not exactly match with our collected results. In case you observe any discrepancies in the overall performance, please leave your comments below.

Overall, GcExcel is faster and uses less memory. See for yourself, download the sample here.

We'll continue to monitor and improve the performance of GcExcel with every release. Stay tuned for the next release!

Try GcExcel free for 30 days

Download the latest version of GrapeCity Documents for Excel, .NET

Download Now!