12 Reasons to use GrapeCity Documents for Excel Java Edition over Apache POI

GrapeCity Documents for Excel Java Edition (GcExcel) is a fast, full-featured spreadsheet library for Java developers. Compared to Apache POI, the most commonly-used spreadsheet library for Java, GcExcel is far superior in features and performance.

This post presents a comparison of their features and performance, and present the Top 12 Reasons why you should use GcExcel instead of Apache POI.

Builds Used for Comparison

This comparison references the following build versions of GcExcel and Apache POI (excerpt from build.gradle for the benchmark app):

• implementation 'com.grapecity.documents:gcexcel:3.1.0'
• implementation 'org.apache.poi:poi-ooxml:4.1.1'
• implementation 'org.apache.poi:poi-ooxml-schemas:4.1.1'
• implementation 'org.apache.poi:poi:4.1.1'

Performance Sample - download the benchmark sample

Feature Comparison

Here are the Top 12 Reasons to use GcExcel over Apache POI:

1. GcExcel is much faster and uses far less memory – performance comparisons across multiple platforms show GcExcel to be an average of 15-25 times faster and 6 times more efficient than Apache POI (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 Apache POI 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 Apache POI 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 Apache POI 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 Apache POI 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 Apache POI lacks any support for sparklines.
7. GcExcel supports Cut/Copy/Paste for Shapes and Pictures while Apache POI lacks any support for clipboard actions on shapes or pictures.
8. GcExcel supports Applying Advanced Filters including Number, Text, Color, Icon, while Apache POI 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 Apache POI 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 Apache POI has no such support.
11. GcExcel supports easily Importing and Exporting CSV, while Apache POI has no such support.
12. GcExcel supports Gradient Fills, which are not supported in Apache POI – gradient fills are useful for making cells, shapes, and chart series really stand out.

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

GcExcel Java vs. Apache POI - A Detailed Comparison Table

Performance Comparison

Test Machine Configurations

• Linux: Intel i7-8700 @ 3.2 GHz x 12 32GB RAM / Ubuntu 18.04.4
• MacOS: Intel i5 @ 2.6 GHz / MacOS Mojave v10.14.6
• Windows: Intel i7-6600U @ 2.60 GHz 2.81 GHz 16GB RAM / Windows 10 x64 v1803 (Build 17134.1304)

Test Application Type

The performance tests are in the Java Console Benchmark demo code on github.

Test Data

The first 3 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,
                                             RefObject<Double> setTime,
                                             RefObject<Double> getTime,
                                             RefObject<Double> saveTime,
                                             RefObject<Double> usedMem) {


    System.out.println();
    System.out.println(String.format("GcExcel benchmark for double values with %,d rows and %d columns", rowCount, columnCount));

    double startMem = Runtime.getRuntime().totalMemory() - Runtime.getRuntime().freeMemory();

    IWorkbook workbook = new Workbook();
    IWorksheet worksheet = workbook.getWorksheets().get(0);

    long start = System.currentTimeMillis();

    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.getRange(0, 0, rowCount, columnCount).setValue(values);
    long end = System.currentTimeMillis();

    setTime.value = (end - start) * 0.001;
    System.out.println(String.format("GcExcel set double values: %.1f s", setTime.value));

    start = System.currentTimeMillis();
    Object tmpValues = worksheet.getRange(0, 0, rowCount, columnCount).getValue();
    end = System.currentTimeMillis();

    getTime.value = (end - start) * 0.001;
    System.out.println(String.format("GcExcel get double values: %.1f s", getTime.value));

    start = System.currentTimeMillis();
    workbook.save("output/gcexcel-saved-doubles.xlsx");
    end = System.currentTimeMillis();
    saveTime.value = (end - start) * 0.001;
    System.out.println(String.format("GcExcel save doubles to Excel: %.1f s", saveTime.value));

    double endMem = Runtime.getRuntime().totalMemory() - Runtime.getRuntime().freeMemory();
    usedMem.value = (endMem - startMem) / 1024 / 1024;
    System.out.println(String.format("GcExcel used memory: %.1f MB", usedMem.value));
}

The last 2 tests focus more on calculation performance. Test 4 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,
                                             RefObject<Double> openTime,
                                             RefObject<Double> calcTime,
                                             RefObject<Double> saveTime,
                                             RefObject<Double> usedMem) {


    System.out.println();
    System.out.println(String.format("GcExcel benchmark for test-performance.xlsx which is 20.5MB with a lot of values, formulas and styles"));

    double startMem = Runtime.getRuntime().totalMemory() - Runtime.getRuntime().freeMemory();

    IWorkbook workbook = new Workbook();

    long start = System.currentTimeMillis();
    workbook.open("files/test-performance.xlsx");
    long end = System.currentTimeMillis();

    openTime.value = (end - start) * 0.001;
    System.out.println(String.format("GcExcel open big Excel: %.1f s", openTime.value));

    start = System.currentTimeMillis();
    workbook.dirty();
    workbook.calculate();
    end = System.currentTimeMillis();

    calcTime.value = (end - start) * 0.001;
    System.out.println(String.format("GcExcel calculate formulas for big Excel: %.1f s", calcTime.value));

    start = System.currentTimeMillis();
    workbook.save("output/gcexcel-saved-test-performance.xlsx");
    end = System.currentTimeMillis();
    saveTime.value = (end - start) * 0.001;
    System.out.println(String.format("GcExcel save back to big Excel: %.1f s", saveTime.value));

    double endMem = Runtime.getRuntime().totalMemory() - Runtime.getRuntime().freeMemory();
    usedMem.value = (endMem - startMem) / 1024 / 1024;
    System.out.println(String.format("GcExcel used memory: %.1f MB", usedMem.value));
}

Test 5 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,
                                             RefObject<Double> setTime,
                                             RefObject<Double> calcTime,
                                             RefObject<Double> saveTime,
                                             RefObject<Double> usedMem) {


    System.out.println();
    System.out.println(String.format("GcExcel benchmark for double values with %,d rows and %d columns", rowCount, columnCount));

    double startMem = Runtime.getRuntime().totalMemory() - Runtime.getRuntime().freeMemory();

    IWorkbook workbook = new Workbook();
    workbook.setReferenceStyle(ReferenceStyle.R1C1);
    IWorksheet worksheet = workbook.getWorksheets().get(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.getRange(0, 0, rowCount, 2).setValue(values);

    long start = System.currentTimeMillis();
    worksheet.getRange(0, 2, rowCount, columnCount).setFormula("=SUM(RC[-2],RC[-1])");
    long end = System.currentTimeMillis();

    setTime.value = (end - start) * 0.001;
    System.out.println(String.format("GcExcel set formulas: %.1f s", setTime.value));

    start = System.currentTimeMillis();
    workbook.calculate();
    end = System.currentTimeMillis();

    calcTime.value = (end - start) * 0.001;
    System.out.println(String.format("GcExcel calculates formula: %.1f s", calcTime.value));

    workbook.setReferenceStyle(ReferenceStyle.A1);

    start = System.currentTimeMillis();
    workbook.save("output/gcexcel-saved-formulas.xlsx");
    end = System.currentTimeMillis();
    saveTime.value = (end - start) * 0.001;
    System.out.println(String.format("GcExcel save formulas to Excel: %.1f s", saveTime.value));

    double endMem = Runtime.getRuntime().totalMemory() - Runtime.getRuntime().freeMemory();
    usedMem.value = (endMem - startMem) / 1024 / 1024;
    System.out.println(String.format("GcExcel used memory: %.1f MB", usedMem.value));
}

Performance Results

Charts:

Linux: Performance: Avg 23.6 X FASTER, Memory: Avg 5.7 X MORE EFFICIENT

Windows: Performance: Avg 24.7 X FASTER, Memory: Avg 6.3 X MORE EFFICIENT

MacOS: Performance: Avg 15.3 X FASTER, Memory: Avg 6.0 X MORE EFFICIENT

Performace Spreadsheet Data

The full performance details from which the calculations are made are in the following spreadsheets:

• benchmarks_ubuntu.xlsx
• benchmarks_pc.xlsx
• benchmarks_mac.xlsx

Performance Sample

Overall, GcExcel is faster and uses less memory.

• See for yourself - download the benchmark sample

With GcExcel, you can command total control of your spreadsheets—with no dependencies on Excel.

If you have a comment or question about this tutorial, please leave your comments in the thread below.