Apache POI with Grails
Many people have asked how to use Apache POI with Grails. A quick search on the Internet
reviews bits and pieces but no official documentation or tutorial. So I decided two quick ways of incorporating POI
with your Grails applications.
In a nutshell, POI provides a Java API for reading and writing MS documents such as Word and Excel documents.
You can find the library and full details here.
Write directly out to the output stream
The first method involves obtaining a handle to the response output stream and writing directly to it. You must
remember to set the content response header and content type appropriately or the user will see garbage.
package com.ex
import javax.servlet.ServletOutputStream
import org.apache.poi.hssf.usermodel.*
class ShelfController {
def index = {
HSSFWorkbook wb = generateWorkbook();
response.setHeader("Content-disposition", "attachment; filename=serverList.xls");
response.setContentType("application/excel");
// Write the output to the OutputStream
ServletOutputStream f = response.getOutputStream();
wb.write(f);
f.close();
}
private HSSFWorkbook generateWorkbook() {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("new sheet");
// populate the spreadsheet
HSSFRow row = sheet.createRow((short)0);
.
.
.
return wb
}
}
Use AbstractExcelView
Because Grails is built on Spring and your Grails controllers can return Spring’s ModelAndView objects, you can
extend Spring’s AbstractExcelView and implement its buildExcelDocument method. Your Grails controller will simply
return this Excel view as the one to render. It will set the header and content type for you.
package com.ex
import javax.servlet.ServletOutputStream
import org.apache.poi.hssf.usermodel.*
class ShelfController {
def index = {
// forward to the list view to show them
return new ModelAndView(new SheflExcelView(), [ bookList : "abc" ])
}
}
Implement the buildExcelDocument method in your Excel view:
package com.ex
import javax.servlet.http.HttpServletRequest
import javax.servlet.http.HttpServletResponse
import org.apache.poi.hssf.usermodel.*
import org.springframework.web.servlet.view.document.AbstractExcelView
class SheflExcelView extends AbstractExcelView{
protected void buildExcelDocument(Map<String, Object> model,
HSSFWorkbook workbook, HttpServletRequest request,
HttpServletResponse response) throws Exception {
HSSFWorkbook wb = generateWorkbook(workbook);
}
private HSSFWorkbook generateWorkbook(HSSFWorkbook wb) {
HSSFSheet sheet = wb.createSheet("new sheet");
.
.
.
}
}
I personally prepare the second method. The Excel document is a “view” so I don’t want to pollute the controller
with a whole lot of code generate the Excel report. In addition, AbstractExcelView also sets your response and content
type headers on your behalf.