November 2^nd,2007

Apache POI and JExcelAPI are the best known Java libraries for processing Excel files. Both can read and write write Excel 95 or Excel 97 files, which are supported many versions of Excel and OpenOffice. After spending some time on various forums the Web's opinion seemed to be that without a doubt JExcel API was the way to go, main arguments in its favor being a cleaner API and a smaller JAR footprint. I tried JExcelAPI first but after a promising start I've got stocked when I tried to use more advanced cell formatting fatures. Apache POI proved to be equally easy to use and the cell formatting features I needed worked immediately and without any hassle.

I downloaded JExcelAPI and gave it a try. The website is quite clean, the documentation seemed explicit. I quickly managed to write an Excel 95 workbook. The code fragment bellow shows how easy it is to write a workbook using JExcelAPI:

WritableWorkbook workbook = Workbook.createWorkbook(new File("output.xls"));

WritableSheet sheet = workbook.createSheet("First Sheet", 0);

Label label = new Label(0, 2, "A label record");
sheet.addCell(label);

Number number = new Number(3, 4, 3.1459);
sheet.addCell(number);

workbook.write();
workbook.close();
        

If you need to write Excel files using JExcelAPI, the code snippet above is all the documentation you'll need. My decision to use JExelAPI was looking very promising until I ran into a blocking issue: When opening up the XLS file in Open Office, all the dates in the spreadsheet were shifted in the 19^nd century. I admit, I didn't try with Excel itself, but that wasn't the point. I was looking for a solution to output files that would load properly in Excel and OpenOffice. The fact that it didn't work is OpenOffice was reason enough for me to look for another solution. Plus, I couldn't get the cell formatting API to do what I wanted - especially when dealing with currencies. After a few hours of trying various code samples from the website and digging into the documentation I proclaimed JExcelAPI a dead end and decided to try Apache POI.

I admit that after reading all the comments on the web about how cleaner the JExcel API was, I wasn't very cheerful about going over to the Apache POI website and download the jars. I was expecting a cluttered and complicated API, so imagine my surprise when I realized that Apache POI is the exact opposite of that. Not only the API is as clean and easy to use as JExcelAPI, but Apache POI fixed my date and currency formattng isuues in no time.

If you decide to go for it, you should know that Apache POI covers not only Excel, but also Word and PowerPoint. If you only need to work with Excel files, you'll only need the POI-HSSF module. The Apache POI API is very easy to use, as illustrated in the code snippet bellow:

HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("new sheet");

// Create a row and put some cells in it. Rows are 0 based.
HSSFRow row = sheet.createRow((short)0);
// Create a cell and put a value in it.
HSSFCell cell = row.createCell((short)0);
cell.setCellValue(1);

// Or do it on one line.
row.createCell((short)1).setCellValue(1.2);
row.createCell((short)2).setCellValue("This is a string");
row.createCell((short)3).setCellValue(true);

// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();            
        

As you can see from the two code fragments, the APIs proposed by JExcelAPI and Apache POI are quite similar. The only difference is that while in JExcelAPI you must instantiate a cell yourself and than add it to the sheet, in Apache POI you call a factory method on the sheet to accomplish the same thing. I really don't think that claims about JExcelAPI being a cleaner API than Apache POI are founded. The two APIs are equally clean and work in a similar way. The two libraries are 100% equivalent when it comes to writing simple Excel workbooks.

Both libraries let you read and modify existing Excel files and offer support for fomulas and charts. Unfortunately I didn't have the time to evaluate those features.

Apache POI worked better for me when I needed to access more advanced features such as date and currency formatting. The Apache POI Busy Developers' Guide contains code examples that worked out of the box, while I strugelled to get the same results with JExcel API in a reasonable ammount of time. Maybe I was going the wrong way about it, but the JExcel API website failed to put me back on the tracks.