Utilize Apache POI to output spreadsheets

SpreadsheetBuilder.groovy

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()
	}
}