What is the better API to Reading Excel sheets in java - JXL or Apache POI

Question

Which of the 2 APIs is simpler to read/write/edit excel sheets ? Do these APIs not support CSV extensions ?

Using JXL for file.xls and file.xlsx, I get an exception like:

jxl.read.biff.BiffException: Unable to recognize OLE stream
    at jxl.read.biff.CompoundFile.<init>(CompoundFile.java:116)
    at jxl.read.biff.File.<init>(File.java:127)
    at jxl.Workbook.getWorkbook(Workbook.java:268)
    at core.ReadXLSheet.contentReading(ReadXLSheet.java:46)
    at core.ReadXLSheet.init(ReadXLSheet.java:22)
    at core.ReadXLSheet.main(ReadXLSheet.java:72)

Both for .xls and .xlsx extensions. Java Version I am using is : JDK1.6

Solution 1

I have used both JXL (now "JExcel") and Apache POI. At first I used JXL, but now I use Apache POI.

First, here are the things where both APIs have the same end functionality:

• Both are free
• Cell styling: alignment, backgrounds (colors and patterns), borders (types and colors), font support (font names, colors, size, bold, italic, strikeout, underline)
• Formulas
• Hyperlinks
• Merged cell regions
• Size of rows and columns
• Data formatting: Numbers and Dates
• Text wrapping within cells
• Freeze Panes
• Header/Footer support
• Read/Write existing and new spreadsheets
• Both attempt to keep existing objects in spreadsheets they read in intact as far as possible.

However, there are many differences:

• Perhaps the most significant difference is that Java JXL does not support the Excel 2007+ ".xlsx" format; it only supports the old BIFF (binary) ".xls" format. Apache POI supports both with a common design.
• Additionally, the Java portion of the JXL API was last updated in 2009 (3 years, 4 months ago as I write this), although it looks like there is a C# API. Apache POI is actively maintained.
• JXL doesn't support Conditional Formatting, Apache POI does, although this is not that significant, because you can conditionally format cells with your own code.
• JXL doesn't support rich text formatting, i.e. different formatting within a text string; Apache POI does support it.
• JXL only supports certain text rotations: horizontal/vertical, +/- 45 degrees, and stacked; Apache POI supports any integer number of degrees plus stacked.
• JXL doesn't support drawing shapes; Apache POI does.
• JXL supports most Page Setup settings such as Landscape/Portrait, Margins, Paper size, and Zoom. Apache POI supports all of that plus Repeating Rows and Columns.
• JXL doesn't support Split Panes; Apache POI does.
• JXL doesn't support Chart creation or manipulation; that support isn't there yet in Apache POI, but an API is slowly starting to form.
• Apache POI has a more extensive set of documentation and examples available than JXL.

Additionally, POI contains not just the main "usermodel" API, but also an event-based API if all you want to do is read the spreadsheet content.

In conclusion, because of the better documentation, more features, active development, and Excel 2007+ format support, I use Apache POI.

Solution 2

I am not familiar with JXL and but we use POI. POI is well maintained and can handle both the binary .xls format and the new xml based format that was introduced in Office 2007.

CSV files are not excel files, they are text based files, so these libraries don't read them. You will need to parse out a CSV file yourself. I am not aware of any CSV file libraries, but I haven't looked either.

Solution 3

For reading "plain" CSV files in Java, there is a library called OpenCSV, available here: http://opencsv.sourceforge.net/

Comments

[Swagatika]

Thanks for the elaborate explanation.

[Ron]

+1 for clear, concise, and extremely helpful

[LiuYan 刘研]

the dirty getContents() method in JExcelAPI save me a lot of time. With POI, you must check it's cell type, then get it's value (if it's Numeric cell, you need to check if it's a Date cell) according it's type, and finally convert it to String value with different methods, that's so inconvenient. Can't imagine POI doesn't provides such a dirty but convenient method as JExcelAPI does.

[dave_thompson_085]

A very positive thing if POI is the event-based reading. Especially on mobile devices (=Android), this helps a lot when dealing with limited heap sizes and GC. Reading a simple XLS with JXL often reached the app memory limit, causing the app to crash.

[Ashok Koyi]

One of the important factor that made me to migrate to POI is the flexibility to use steaming API which is a must when you want to read excel with huge amount of data. You would not want the wole data to be loaded into memory when you open the excel, if the data in the excel is huge. With streaming, the whole content of your excel/any office document will not be loaded into memory immediately after you parse the sheet.

[trognanders]

Apache has a Commons offering for CSV now too, commons.apache.org/proper/commons-csv . It works pretty good, and has that nice name brand.

[kerberos84]

@LiuYan刘研 is it so difficult to write your own dirty getContents() method and use it everytime you need, if this is the only problem?

[Automationtested]

Thank you for that explanation. I was under the impression that the answer would be obvious but I see that there was a lot that I missed. Always learning.