A key feature in one of my Android projects is the ability to export the app’s data into a Microsoft Excel® readable spreadsheet. Unfortunately the app’s exports require formatting so a simple CSV file is out of the question. I need true to form .xlsx (or .xls) files.

Some quick research yielded The Apache POI Project. I proceeded to download the library and import into my existing Android project. Much to my dismay, Eclipse began throwing build errors stating something like ‘Conversion to Dalvik format failed with error 2’. As it turns out, the Apache POI library as downloaded is not compatible with Android and the Dalvik VM.

The problem is that Apache POI makes use of several API’s that are not available in Android (StAX, java.awt, others?). You see in order to fit and perform well on mobile devices, Android makes use of a smaller subset of standard Java packages¹. A few posts here and there hinted that it may be possible to build Apache POI for Android, but I simply do not have the experience building such a massive project for a different platform. Note: If anyone has Apache POI built and working for Android please share your experiences. I will gladly provide what little information I have on the task.

Not to be deterred I kept searching for a suitable alternative and was relieved to find JExcelApi. This library turned out to be a great fit for my project since it was much smaller than Apache POI having only bits necessary to create spreadsheet files (as opposed to the full Microsoft Office® suite in Apache POI). Perhaps the only disadvantage of JExcelApi is its inability to create the newer .xlsx files using the ooxml file format. I was limited to the binary .xls format. This is a minor issue at the moment since all major office suites plan to continue .xls support.

How do you use JExcelApi in an Android project? I’m glad you asked. Let’s get to it.

1. Download JExcelApi.

Navigate to jexcelapi.sourceforge.net and download the latest release.

2. Add the library to your Android project.

Add the downloaded .jar file like any other. For Eclipse users here is a brief how-to. Detailed instructions are beyond the scope of this post.

3. Create a Workbook object.

a. Use a temp file!

It turns out that JExcelApi is a resource hog when creating files. This is no good in a mobile environment so some quick research yielded this fix. WorkbookSettings.setUseTemporaryFileDuringWrite(true);

b. Create standard java.io.File.
c. Create a new WritableWorkbook.

/**
 * 
 * @param fileName - the name to give the new workbook file
 * @return - a new WritableWorkbook with the given fileName 
 */
public WritableWorkbook createWorkbook(String fileName){
    //exports must use a temp file while writing to avoid memory hogging
    WorkbookSettings wbSettings = new WorkbookSettings(); 				
    wbSettings.setUseTemporaryFileDuringWrite(true);   
 
    //get the sdcard's directory
    File sdCard = Environment.getExternalStorageDirectory();
    //add on the your app's path
    File dir = new File(sdCard.getAbsolutePath() + "/JExcelApiTest");
    //make them in case they're not there
    dir.mkdirs();
    //create a standard java.io.File object for the Workbook to use
    File wbfile = new File(dir,fileName);
 
    WritableWorkbook wb = null;
 
    try{
	//create a new WritableWorkbook using the java.io.File and
	//WorkbookSettings from above
	wb = Workbook.createWorkbook(wbfile,wbSettings); 
    }catch(IOException ex){
	Log.e(TAG,ex.getStackTrace().toString());
	Log.e(TAG, ex.getMessage());
    }
 
    return wb;	
}

4. Create a Sheet

Given a WritableWorkbook, creating a new sheet in that workbook is as trivial as providing a sheet name and index. The index is used to place the sheet along the bottom tabs in the workbook.

/**
 * 
 * @param wb - WritableWorkbook to create new sheet in
 * @param sheetName - name to be given to new sheet
 * @param sheetIndex - position in sheet tabs at bottom of workbook
 * @return - a new WritableSheet in given WritableWorkbook
 */
public WritableSheet createSheet(WritableWorkbook wb, 
    String sheetName, int sheetIndex){
    //create a new WritableSheet and return it
    return wb.createSheet(sheetName, sheetIndex);
}

5. Write Cells

Now that we have a worksheet within a workbook, we can write data to the cells. The most simple way of doing this in JExcelApi is to create a new Label object.

/**
 * 
 * @param columnPosition - column to place new cell in
 * @param rowPosition - row to place new cell in
 * @param contents - string value to place in cell
 * @param headerCell - whether to give this cell special formatting
 * @param sheet - WritableSheet to place cell in
 * @throws RowsExceededException - thrown if adding cell exceeds .xls row limit
 * @throws WriteException - Idunno, might be thrown
 */
public void writeCell(int columnPosition, int rowPosition, String contents, boolean headerCell,
  	WritableSheet sheet) throws RowsExceededException, WriteException{
    //create a new cell with contents at position
    Label newCell = new Label(columnPosition,rowPosition,contents);
 
    if (headerCell){
    	//give header cells size 10 Arial bolded 	
    	WritableFont headerFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
    	WritableCellFormat headerFormat = new WritableCellFormat(headerFont);
    	//center align the cells' contents
    	headerFormat.setAlignment(Alignment.CENTRE);
        newCell.setCellFormat(headerFormat);
    }
 
    sheet.addCell(newCell);
}

Note the use of WritableFont and WritableCellFormat to influence the appearance of the cells. JExcelApi provides all the necessary methods to format your sheets. To see all of the formatting possibilites JExcelApi offers, consult the documentation downloaded in step 1.

And that’s it!

Nothing complicated is needed to use JExcelApi in an Android project. It happily (unlike Apache POI) just works. Just remember to make use of WorkbookSettings.setUseTemporaryFileDuringWrite(true) as mentioned above. I found that it doesn’t take many files/exports to eat up all available memory on a Motorola Droid. This fix is sure to have a performance hit since it must write to device storage. That being said, JExcelApi has handled exporting 5-8 files with 20-40 rows times 4 sheets of data each. Extensive testing with sufficiently large data sets has not yet been performed. Testing was done on a Motorola Droid and lesser phones; adjust performance expectations accordingly when trying on your device. It does seem beneficial to place file creation and writing on a separate thread. For the use case I mentioned above I haven’t felt obligated to do so, but this feature will likely make its way into a future release.

1 – http://www.zdnet.com/blog/burnette/java-vs-android-apis/504