Generating Excel Files On Google App Engine For A Wicket Application

We are porting an existing Wicket application on Google App Engine. This application uses Wicket, Spring and Hibernate. If you read will it play in app engine, you will notice that Wicket is semi compatible and it does work with workarounds. Hibernate on the other hand is incompatible and Spring is fully supported. We began porting this application after changing persistence to use JPA instead of hibernate. We used workarounds for wicket described here.

This application generates Excel sheets and used Apache POI for generating Excel reports. Apache POI is not supported in Google App engine and it seems that Apache POI will not change their implementation to make it work for Google App Engine in near future.

We started looking at ways to generate Excel sheets. We decided on using java Excel API for our application. Let’s see how can we generate excel files for a Wicket application on Google App engine.

First of all add the maven dependency for JExcel in the application:

<dependency>
    <groupId>net.sourceforge.jexcelapi</groupId>
    <artifactId>jxl</artifactId>
    <version>2.6.10</version>
</dependency>

Start by mounting the shared resource in wicket Application class in its init() method. Suppose we have an EhourWebApplication class then its init() method will look like this:

public class EhourWebApplication extends AuthenticatedWebApplication {
  public void init() {
	String id = ExportReportExcel.getId();
	mountSharedResource("/" + id, new ResourceReference(id).getSharedResourceKey());
  }
}

ExportReportExcel class extends DynamicWebResource class and specifically overrides the getResourceState() and setHeaders(). The code listing for ExportReportExcel:

public class ExportReportExcel extends DynamicWebResource {
	@Override
	protected ResourceState getResourceState() {
		ExcelResourceState state = new ExcelResourceState();
		byte[] data = getExcelData();
		state.setData(data);
	}

	@Override
	protected void setHeaders(WebResponse response) {
		response.setAttachmentHeader(getFilename());
	}

	private class ExcelResourceState extends ResourceState {
		private byte[] data;

		@Override
		public String getContentType() {
			return "application/x-ms-excel";
		}

		@Override
		public byte[] getData() {
			return data;
		}

		void setData(byte[] data) {
			this.data = data;
		}

		@Override
		public int getLength() {
			return data.length;
		}

		@Override
		public Time lastModifiedTime() {
			return Time.now();
		}
	}
}

ExcelResourceState is an inner class extending ResourceState. If you look closely this ResourceState class overrides methods like getContentType(), getData(), getLength() and lastModifiedTime(). The getExcelData() method in this class will use the JExcel Api’s to generate Excel sheet. The code listing for the getExcelData() method using JExcel Api’s is given below.

public byte[] getExcelData() throws IOException, WriteException {

  // Stream containing excel data
  ByteArrayOutputStream outputStream = new ByteArrayOutputStream();

  // Create Excel WorkBook and Sheet
  WritableWorkbook workBook = Workbook.createWorkbook(outputStream);
  WritableSheet sheet = workBook.createSheet("User List", 0);

  // Generates Headers Cells
  WritableFont headerFont = new WritableFont(WritableFont.TAHOMA, 12, WritableFont.BOLD);
  WritableCellFormat headerCellFormat = new WritableCellFormat(headerFont);
  headerCellFormat.setBackground(Colour.PALE_BLUE);
  sheet.addCell(new Label(1, 1, "LastName", headerCellFormat));
  sheet.addCell(new Label(2, 1, "FirstName", headerCellFormat));

  // Generates Data Cells
  WritableFont dataFont = new WritableFont(WritableFont.TAHOMA, 12);
  WritableCellFormat dataCellFormat = new WritableCellFormat(dataFont);
  int currentRow = 2;
  for (User user : getUsers()) {
    sheet.addCell(new Label(1, currentRow, user.getLastName(),dataCellFormat));
    sheet.addCell(new Label(2, currentRow, user.getFirstName(),dataCellFormat));
    currentRow++;
  }

  // Write & Close Excel WorkBook
  workBook.write();
  workBook.close();

  return outputStream.toByteArray();
}

Let us suppose we have a form ExcelReportForm and we want to generate the Excel report on its submit event. Then we can use the mounted URL to set the request target. The code snippet for the form and its submit method .

private class ExcelReportForm extends Form {
	@Override
	protected void onSubmit() {
		ResourceReference excelResource = new ResourceReference(ExportReportExcel.getId());
		excelResource.bind(getApplication());
		CharSequence url = getRequestCycle().urlFor(excelResource, params);
		getRequestCycle().setRequestTarget(new RedirectRequestTarget(url.toString()));
	}
}

That is all that is required to generate Excel sheets in a Wicket application.

JExcel API’s do have some limitations as well. It does not generate or chart, graph or macro information. This information is however preserved when spreadsheets are copied. When adding images to a sheet, only PNG image formats are supported. JExcel was a right choice for our application, we had to change a small set of files in our application to use JExcel API’s instead of Apache POI API’s.