An NPOI Apache POI Alternative - GcExcel.NET

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.

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

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 -

• GcExcel - v4.1.2
• NPOI - v2.5.3

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.

Time Taken in Seconds:

Memory Consumption (MB)

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!

Download Now!