J2EE: This hack on Google Appengine lets you create a HSSF Workbook.

The Problem : Not able to create a HSSFWorkbook on Google App Engine.

But, I was able to receive an Excel sheet uploaded from a browser , parse it into something meaningfull using the POI API http://poi.apache.org/ (Which i should say is really cool, considering that i'm a newbie to web application development, i never knew it was that easy to parse an Excel file).

Now, when i try to create an Excel sheet and send it back to the browser dynamically - google appengine sent back a 500 Internal server error. On further reading i realised that POI Api was not supported on app engine. But half my work was already done, the creation part had to work otherwise the whole effort would be a waste of time. Being an Open Source Software user,  i downloaded POI Sources from the link mentioned above. There was good documentation on how to build it. The idea was i would remove the unnecessary parts if any and rebuild it (Which , i later on figured out was NOT needed ) with the white listed classes , if possible.

First things first. I went and looked at the logs to figure of what exactly went wrong. I saw it to find out which line caused the exception, so that i can start from there. The logs on appengine said :

/GetExcelFile
java.lang.NullPointerException

at org.apache.poi.hssf.record.WriteAccessRecord.setUsername(WriteAccessRecord.java:101)

at org.apache.poi.hssf.model.Workbook.createWriteAccess(Workbook.java:1071)
at org.apache.poi.hssf.model.Workbook.createWorkbook(Workbook.java:335)
at org.apache.poi.hssf.usermodel.HSSFWorkbook.(HSSFWorkbook.java:170)
at com.devicemantra.coordinate.util.CExcelWriter.(CExcelWriter.java:48)
at com.devicemantra.coordinate.ds.tests.GetExcelFile.doGet(GetExcelFile.java:27)

Now,  i fired up vim and opened Workbook.java  +1071. Following is the line of code which caused the exception :


 private static WriteAccessRecord createWriteAccess() {
        WriteAccessRecord retval = new WriteAccessRecord();

        try {
            retval.setUsername(System.getProperty("user.name"));
        } catch (AccessControlException e) {
                // AccessControlException can occur in a restricted context
                // (client applet/jws application or restricted security server)
                retval.setUsername("POI");
        }
        return retval;


The code below tried to get System.getProperty("user.name").  And used this as a parameter to setUserName(). Since this system variable was not set, it sent null and hence the NullPointerException.

I thought that the probable solution was to set this property in appengine-web.xml. So that this call  System.getProperty("user.name") would return something and not raise a Null Pointer Exception. 
I modified appengine-web.xml to have this : 

Now, i presumed that the call System.getProperty("user.name")  would return voicestreams and i would see some other exception. So i searched the files for System.getProperty( to see whether any other calls were made, so that i could add entries in appengine-web.xml. But, i got lucky there were no other call except for this one.  

I built my project and deployed it on appengine. I got my excel file back to the browser, i opened it and i saw what i expected it to have :) !. 

Conclusion: 

A call to the Constructor  new HSSFWorkbook() fails on appengine production, but will not raise an exception on Development server. The above hack solved it. Hope this post helps someone, somewhere. As an aside, the call to HSSFWorkbook(POIFSFileSystem fs) never failed. I had used this as below for receving uploaded excel files.

HSSFWorkbook lExcelWorkBook = new HSSFWorkbook(new POIFSFileSystem(mInputStream)); 

and it worked. But, the call to new HSSFWorkbook() fails on GAE production. 

The other alternative is to upload an Excel file to GAE and store it as a blob, when you need to create an Excel file read the blob into an InputStream and use  new HSSFWorkbook(new POIFSFileSystem(mInputStream));  to create the  workbook. I have not tried this but i think it will work, since it worked for me while reading the uploaded Excel sheet.