Search code examples
javaexcelapache-poijxl

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

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

  • 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.