Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
Utilize Apache POI to output spreadsheets
package gvl.ss
import org.apache.poi.ss.usermodel.*
import org.apache.poi.hssf.usermodel.*
//import org.apache.poi.xssf.usermodel.*
//import org.apache.poi.POIXMLDocument
class SpreadsheetBuilder {
/* Builds a new .xls file from well formatted data array
* Example:
* def testData = [[1,2,3], ['four','five','six'], ['7','8','9']]
* def ssb = SpreadsheetBuilder.buildXlsFromData(ssData)
*/
static Byte[] buildXlsFromData(List data) {
def baos = new ByteArrayOutputStream()
Workbook wb = new HSSFWorkbook()
def sheet = wb.createSheet() // clearly this will be a one sheet only afair
def (row, cell) = [null,null]
data.eachWithIndex{ rowData, rowNum ->
row = sheet.createRow(rowNum)
rowData.eachWithIndex{ cellData, colNum ->
if (cellData != null) {
cell = row.createCell(colNum)
cell.setCellValue(cellData)
}
}
}
wb.write(baos)
return baos.toByteArray()
}
/* given an .xls file as a template this produces a version that is filled in with the given data
* Rows or Columns that are handled (filled in) within the template should be excluded in the case
* rows and included as `null` in the case of colums. Then they will be skipped over for being
* created or filled in.
* Example:
* def testData = [3:[1,2,3], 4:['four','five','six'], 5:['7','8','9',null,' ',0,false,'something'], 7:[nul,null,'100lbs']]
* def ssb = SpreadsheetBuilder.fillInXlsTemplateWithData('/path/to/file.xls', ssData)
* response.contentType = 'application/vnd.ms-excel'
* response.setHeader("Content-disposition", "attachment; filename=someFileName.xls")
* response.setContentLength(b.length)
* response.getOutputStream().write(b)
*/
static Byte[] fillInXlsTemplateWithData(String templateUrl, Map data){
def baos = new ByteArrayOutputStream()
Workbook wb = new HSSFWorkbook(new FileInputStream(templateUrl))
//Workbook wb = new XSSFWorkbook(templateUrl) // would need to include extra POI jars to make this work
def sheet = wb.getSheetAt(0) // TODO: make data have sheet dimension and add loop
def (row, cell) = [null,null]
data.each { rowName, rowData ->
//println "rowName: ${rowName}"
//println "rowData: ${rowData}"
// rows are counted in indexed form in POI but not in human view... row 1 in the ss in MsOffice is row 0 to POI
row = sheet.getRow(rowName-1) ?: sheet.createRow(rowName-1)
rowData.eachWithIndex{ cellData, colNum ->
if (cellData != null) {
cell = row.getCell(colNum) ?: row.createCell(colNum)
cell.setCellValue(cellData)
}
}
}
wb.write(baos)
return baos.toByteArray()
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment