Introduction

 

This article measures feature comparison of Aspose.Cells for Java with its open source Competitors. The document compares Aspose.Cells for Java with JExcelAPI and POI’s HSSF for some common set of features.

 

Overview

 

We all know that the majority of business scenarios demands sophisticated reports that are rich in content and focused on the needs of specific decisions or tasks which may occur in general management. They also require some means for Data collection, Analysis and interfacing with Database systems. A common solution can be utilizing spreadsheet formats for data collection, analysis and presentation tool. The solution should have the feasibility to create fully formatted excel files to perform analysis of a business scenario and will extend these basic skills 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 MS Excel.

 

We come across different spreadsheet components in the market today that really promise to have feature-rich API for spreadsheet management. Here, we shall examine feature analysis of Aspose.Cells for Java with its open source competitors i.e., JExcelAPI and POI’s HSSF. Let me give some introduction of 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 requiring Microsoft Excel to be installed on the system (client / server). Unlike similar products from other vendors, Aspose.Cells for Java not only supports spreadsheet generation and other basic file formatting features, but also supports a number of advanced features. These advanced features make it much more convenient for the developers to manipulate spreadsheet contents, cells formatting and files protection options. Aspose.Cells for Java also allows to import data into spreadsheets from different data sources, add common and complex Mathematical, DateTime, Financial, Text Formulas / Functions, import pictures (all major formats are supported here including bmp, gif, jpg, png, wmf, emf etc. ), create comments, creating drawing objects and controls etc.

 

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 (Excel97-Excel2007) to and from streams, importing and exporting data from a ResultSet and Array, importing charts, named ranges, headers / footers with all attributes which MS Excel provides, hyperlinks, auto-fitting rows and columns with one action, inserting images through API, importing formulae from a designer spreadsheet and configuring all types of page setup options (Top, Left, Bottom, Right, Header and Footer margins, Orientation - Portrait or Landscape, Scaling, Paper Size, Printing Area, Repeating Rows and Columns etc.) through API. Aspose.Cells for Java also supports Add-ins, VBA and macros.

 

Moreover, there are also a number of unique features that the developers can only find in Aspose.Cells for Java such as the support of all the formats including XLS, XLT, XLSX, CSV, SpreadsheetML, TabDelimited, TXT, XML and HTML, adding the copy of an existing worksheet (with full contents, images and charts etc.) to a file, setting gradient background for charts through API, creating comments, set locale and region settings, auto-filters and page breaks through API, setting complex formulae, supporting conditional formatting, supporting all types of protection options introduced in Microsoft Excel XP or above, manipulating named ranges, locating APIs, supporting custom chart API and formula calculation engine etc.

 

Since, worksheet data formatting is in many ways more significant because it can change the meaning of your data. If you plan to print your worksheet, email it to clients, or show it off to your boss, you need to think about whether your worksheet is formatted in a viewer-friendly way. A 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 you the flexibility and feasibility to draw borders around cells and range of cells with ease. Moreover the component is well versed to apply fonts (Family/Type, Style, Size, Color and Alignment) with attributes and shade the cells with background patterns. The component is efficient enough that you can format a complete row or column, set alignments, wrap and rotate the text in the cells. Aspose.Cells for Java supports to apply all types of Number Formats including General format, numbers in Decimal notations, numbers with Currency symbols, numbers as a Percentage of 100, numbers in scientific format, numbers in DateTime values and Custom Number format.

Aspose.Cells for Java has a huge list of features. To know more about the product information, features and for a programmer’s guide, please check Documentation and online featured demos.  So I strongly recommend you and invite you to try the component one time at least and see the difference. You can try its evaluation version; it is totally free without any time limitation what so ever. You may download its evaluation version for free.

 

 

JExcelAPI

 

JExcelAPI - A Java API used to read, write and modify Excel spreadsheets. Java Excel API 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 which allows java applications to read a spreadsheet, modify cells, and write the new spreadsheet. Well, frankly, it has a limited set of features (although we might have more by the passage of time). It does support: read / write native excel file (Excel97-2003) in XLS file format only, limited formula calculation support. It can manipulate fonts, support number and date formatting, modify existing worksheets, locale settings, preserving charts (does not support to create or manipulate charts), inserting images etc.

 

JExcelApi has limited support for charts: It does not support to create / manipulate 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 as the chart). All image information is preserved when copying excel files, however, when adding an image to a spreadsheet only images in PNG format are supported.

 

There is no API for copying worksheets within / between workbooks directly. This task can be done in an indirect way, but requires some work to be done. For example, using some loops you have to copy cell by cell 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….so, quite a big job to be done.

 

Another limitation is JExcelAPI does not perform optimizations to reduce file size, we 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/Columns for you. You'll need to write code that scans the cells in each column, calculates the maximum length, and then make calls to WritableSheet.setColumnView() and WritableSheet.setRowView() accordingly.

 

There are also a few more hurdles: The API does not support PivotTables and DropDownLists. It has limited Validation options to set on the cells. The Page Setup and Printing options are not completely supported e.g., Repeating Rows and Columns and not all types of protection options (including password protection related sheets) are supported. It also does not support data sorting, auto-filter data, conditional formatting, drawing objects, controls and many more valuable features.

 

 

 

 

POI-HSSF

 

HSSF is the component of POI that reads & writes Excel spreadsheets. It does have extended set of features as compared to JExcelAPI including reading / writing native excel file (Excel97-2003), since 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 / footers, hyperlinks, auto-fitting rows and columns etc.

 

There are a few known limitations for the POI-HSSF API. For example, you can not 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 one is Pivot Tables support, generating pivot tables is not possible.

 

Although POI’s org.apache.poi.hssf.usermodel does support formulas but it lacks of 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 3D References etc.

 

HSSF API does not support PivotTables either. It has limited Data Validation options to set on the cells. It also does not support the features like data sorting and auto-filtering data.

 

 

 

Feature Comparison

 

The following table attempts to provide a feature overview on how Aspose.Cells for Java matches up to the open source components (mentioned above) although it does not challenge to cover all features provided by the products involved. This is just an outline which is taken at some specific time and it is quite possible that the missing features could be supported when you will be reading out the document.

 

 

Feature	JExcelAPI	POI’s HSSF	Aspose.Cells for Java
File Formats
Read / Write File Formats (XLS, XLT, XLSX, CSV, SpreadsheetML, Tab Delimited, TXT, XML and 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, Collection			Supported
Add a Copy of Existing Worksheet (all contents including Images, Charts etc.)	Partially Supported	Partially Supported	Supported
Import Images			Supported
Import Charts			Supported
Set Gradient Background for Charts using API			Supported
Protect Worksheet i.e., 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 - Portrait or Landscape, Scaling, Paper Size, Printing Area, Repeating Rows and Columns)	Partially Supported	Supported	Supported
Horizontal and Vertical Page Breaks through API	Supported	Supported	Supported
Copy and Move Worksheets within / between  Workbooks		Supported	Supported
Inserting / Deleting Rows and Columns	Supported