Aspose.Cells for Java Vs Open Source Competitors

Overview

The majority of business scenarios demand sophisticated reports that are rich in content and focused on the needs of the specific decisions or tasks that general management will use them for. They also require some means of data collection, analysis, and interfacing with database systems.

A common solution is using spreadsheets for data collection, analysis, and as a presentation tool. The solution should be able to create fully formatted Microsoft Excel files that can analyze a business scenario and, ideally, extend these basic features to explore the processes of capturing and exporting data to other widely used office applications. These developments may include making use of the built‑in features and functions of Microsoft Excel.

We come across different spreadsheet components in the market today that really promise feature‑rich APIs for spreadsheet management. Here, we do a feature analysis of Aspose.Cells for Java compared to its open‑source competitors JExcelAPI and POI’s HSSF. Let me give a brief introduction to the three first.

Aspose.Cells for Java

Aspose.Cells for Java is a flexible component that enables Java applications to create and manage Excel spreadsheets without Microsoft Excel installed on the system (client or server). Unlike similar products from other vendors, Aspose.Cells for Java not only supports spreadsheet generation and basic file‑formatting features, but also a number of advanced features. These advanced features make it easy for developers to manipulate spreadsheet contents, cell formatting, and file‑protection options.

Aspose.Cells for Java can also import data into spreadsheets from different data sources, add common and complex mathematical, date/time, financial, and text formulas and functions, import pictures (all major formats are supported including BMP, GIF, JPG, PNG, WMF, EMF and more), create comments, create drawing objects and controls, and perform a range of other tasks.

Aspose.Cells for Java also supports add‑ins, VBA, and macros.

Open and Save

Besides supporting common features like creating or reading native Excel files, Aspose.Cells for Java also offers many valuable features such as saving and opening Excel files (Excel 97 – Excel 2007) to and from streams, importing and exporting data from a ResultSet and an array.

Aspose.Cells can import charts, named ranges, headers and footers with all attributes that Microsoft Excel provides, insert hyperlinks and images, and import formulae from a designer spreadsheet.

Formatting

Worksheet data formatting is important because it can change the meaning of your data. If you plan to print a worksheet, email it to clients, or show it to your boss, you need to think about whether it is formatted in a viewer‑friendly way. Careful use of color, shading, borders, fonts, number formatting, alignment, indentation, and orientation can make the difference between a messy glob of data and a worksheet that’s easy to work with and understand.

Aspose.Cells for Java provides the flexibility to draw borders around cells and cell ranges easily. Moreover, the API can apply font settings (family and type, style, size, color, and alignment) and shade cells with background patterns. The API is efficient enough that you can format an entire row or column, set alignments, wrap text, and rotate the text in cells.

Aspose.Cells for Java supports all types of number formats, including general format, numbers in decimal notation, currency symbols, percentages, scientific format, date/time values, and even custom number formats.

Aspose.Cells allows developers to auto‑fit rows and columns in one action, as well as configure all types of page‑setup options in a convenient API: top, left, bottom, right, header and footer margins; orientation (portrait or landscape); scaling; paper size; print area; repeating rows and columns; and many more.

Unique Features

There are also a number of unique features that developers will only find in Aspose.Cells for Java, for example support for a wide range of formats including XLS, XLT, XLSX, CSV, SpreadsheetML, tab‑delimited, TXT, XML, and HTML.

The API also allows developers to add a copy of an existing worksheet (with full contents, images, and charts) to a file, set gradient backgrounds for charts through the API, create comments, set locale and region settings, auto‑filters, and page breaks, set complex formulae, conditional formatting, all types of protection options introduced in Microsoft Excel XP or above, and manipulate named ranges.

Further, Aspose.Cells adds a custom chart API and an efficient formula‑calculation engine.

Try Aspose.Cells for Java

Aspose.Cells for Java has a huge list of features. To find out more about the features and for the Programmer’s Guide, please check the documentation and online demos.

Please try the component to see the difference between it and its competitors. The evaluation version is totally free without any time limitation. Download the evaluation version for free.

Other APIs

JExcelAPI

JExcelAPI is a Java API used to read, write, and modify Excel spreadsheets. It is an open‑source Java API which allows Java developers to read Excel spreadsheets and to generate Excel spreadsheets dynamically. In addition, it contains a mechanism that allows Java applications to read a spreadsheet, modify cells, and write the new spreadsheet.

At the time of writing, it has a limited set of features. It supports reading and writing native Microsoft Excel files (Excel 97‑2003) in XLS format only. It has some limited formula‑calculation support. It can manipulate fonts, support number and date formatting, modify existing worksheets, locale settings, and preserve charts (but does not allow developers to create or manipulate charts), inserting images, and so on.

Limited Chart Support

JExcelAPI has limited support for charts: it does not support creating or manipulating charts. When copying a spreadsheet containing a chart, the chart is written out to the generated spreadsheet (as long as the sheet containing the chart contains other data as well). All image information is preserved when copying Excel files; however, when adding an image to a spreadsheet only images in PNG format are supported.

Cannot Copy Worksheets

There is no API for copying worksheets within or between workbooks directly. This task can be done in an indirect way, but it requires some work. For example, using loops, copy cell by cell with the WritableCell.copyTo() method, which will produce a deep copy. However, the format is only shallow‑copied, so you will need to get the cell format and use the copy constructor of that, and then call WritableCell.setCellFormat() on the cell you have just copied. It’s quite a big job.

No Optimization

Another limitation is that JExcelAPI does not perform optimizations to reduce file size. You should not be surprised to see an output file generated by the component with a huge size in MBs.

JExcelAPI has no API for auto‑fitting rows or columns. You’ll need to write code that scans the cells in each column, calculates the maximum length, and then makes calls to WritableSheet.setColumnView() and WritableSheet.setRowView() accordingly.

Missing Features

There are also a few more hurdles: the API does not support pivot tables and drop‑down lists. It has limited validation options for cells. The page‑setup and printing options are not completely supported—for example, repeating rows and columns and not all types of protection options (including password‑protected sheets) are supported. It also does not support data sorting, auto‑filtering, conditional formatting, drawing objects, controls, and many other valuable features.

POI‑HSSF

HSSF is the component of POI that reads and writes Excel spreadsheets. It has an extended set of features compared to JExcelAPI, including reading and writing native Microsoft Excel files (Excel 97‑2003; the XLSX OOXML file format is not supported yet), formatting cells (number formats, fonts, colors, borders, alignments, etc.), merging cells, page‑setup options, importing images, shapes, named ranges, creating comments, headers and footers, hyperlinks, auto‑fitting rows and columns, etc.

Limited Chart Support

There are a few known limitations for the POI‑HSSF API. For example, you cannot currently create charts. You can, however, create a chart in Excel, modify the chart data values using HSSF, and write a new spreadsheet out. Another limitation is pivot‑table support; generating pivot tables is not possible.

Formula Calculation

Although POI’s org.apache.poi.hssf.usermodel does support formulas, it lacks a rich formula‑calculation engine. It supports formulas containing cell references, string, integer, and floating‑point literals, relative or absolute references, arithmetic and logical operators, but it does not support array formulas, unary operators, and 3‑D references.

Missing Features

The HSSF API does not support PivotTables either. It has limited data‑validation options for cells. It also does not support features such as data sorting and auto‑filtering.

Feature Comparison

The following table attempts to provide a feature overview of how Aspose.Cells for Java matches up to the open‑source components (mentioned above). It does not claim to cover every feature provided by the products involved; it is an outline taken at a specific point in time, and it is possible that missing features could be supported when you read this document.

Feature JExcelAPI POI’s HSSF Aspose.Cells for Java
File formats
Read and write file formats (XLS, XLT, XLSX, CSV, SpreadsheetML, tab‑delimited, TXT, XML, HTML) Partially supported Partially supported Supported
Open file and save to a stream Supported
Convert Excel file to PDF document Supported
Password‑protected files Supported Supported
Manipulate spreadsheet content
Modify the document properties of Excel files Supported
Export worksheet data to an array Supported
Import data from a ResultSet Supported
Import data from an array or collection Supported
Add a copy of an existing worksheet (all contents including images and charts) Partially supported Partially supported Supported
Import images Supported
Import charts Supported
Set gradient background for charts using API Supported
Protect worksheet, including contents, objects, and scenarios Partially supported Supported Supported
Create auto‑filters using API Supported
Page‑setup features (top, left, bottom, right, header and footer margins; orientation; scaling; paper size; print area; repeating rows and columns) Partially supported Supported Supported
Horizontal and vertical page breaks through the API Supported Supported Supported
Copy and move worksheets within and between workbooks Supported Supported
Insert and delete rows and columns Supported Supported
Auto‑fit rows and columns Supported Supported
Copy rows and columns Supported
Data sorting Supported
Trace precedents and dependents Supported
Auto‑filtering Supported
Data validation (all types) Partially supported Partially supported Supported
Import formulae from designer spreadsheet Partially supported Partially supported Supported
Set complex formulae through API Supported
Conditional formatting Supported Supported
Activate sheets and make a cell active in the workbook Supported Supported Supported
Advanced Features
Smart markers Supported
Create standard charts (column, bar, line, pie, scatter, area, doughnut, radar, surface 3D, bubble, stock, cylinder, cone, pyramid, etc.) Supported
Custom chart API Supported
Document‑properties settings Supported
Advanced Excel XP protection options Supported
Add‑ins, VBA, macros Supported
Manipulate named ranges Supported Supported Supported
Pivot tables Supported
Create common drawing objects, shapes, and controls Supported Supported
Insert controls into charts Supported
Formula calculation engine Supported
Find API Supported Supported Supported
Utilize Aspose.Cells in other Programming Languages Aspose.Cells for Java - Interruptible Library