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

Feature GcExcel Apache POI
Values Rich Text
Style Excel Like Number Formatter
Pattern Fill
Gradient Fill
Get Display Format Styles
workbook.Styles.Add(string name, IStyle baseOn).html)
Formula Total Formulas supported 452 157
Get Is External Reference
ArrayFormula
All Excel Functions
Full Excel2016 functions
Support FormulaArrayR1C1
Provide APIs to Get Dependencies of Formula
Table Table Style
Custom Table Style
Table Formula
Copy/Move Cells
Conditional Format Data Bar
Cell Value
Icon Sets
Color Scale
Custom Icon Sets
Above Average
Date Occurring
Top/Bottom10
Duplicate/Unique
Formula Expression Rule
Chart Column Chart
Line Chart
Pie Chart
Bar Chart
Area Chart
Scatter Chart
Stock Chart
Bubble Chart
Radar Chart
Combination Chart
Dual Axis Chart
Chart Sheet
New Excel 2016 Statistical and Specialized Chart Types
Sparkline
Shape Shape
Shape Adjustments
Shape Coordinates (move, resize, row/column changed)
Shape(Chart/Picture/Comment/Slicer) cut/copy/paste
Support ShapeStyle
Shape group apis
Shape Hyperlink
Picture
Filter Text Filter
Number Filter
Date Filter
Icon Filter
Color Filter
Group Grouping data
Sort Row Sort
Multiple Column Sort
Custom Sort
Font Color Sort
Interior Color Sort
Icon Sort
Data Validation
Table Table

Slicer
PivotTable Pivot Table
Slicer
Comments
Importing CSV
XLSX
XSLM
Exporting CSV
XLSX
XSLM
PDF
PDF Security options
Sheet/Range/Shapes to image
Print & view PrintSettings
Excel view settings
Themes Themes
Enhance themes
Hyperlink Support Hyperlink
Custom functions Support Custom functions
Worksheet Copy and Move worksheet

Copy or move worksheets within or between workbooks.html)
Search Find and replace text
VBA Macros Preserve or remove add-in, VBA, macros
Template Language Support Template Language to fill data in Excel sheets from database
Others Freeze/Unfreeze panes
Options for read/write Excel
PageSetup

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:

Performance Sample

Overall, GcExcel is faster and uses less memory.

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.

Custom Code Your Spreadsheets in Java

Download the latest version of GrapeCity Documents for Excel, Java Edition

Download Now!