\$\begingroup\$

If you're interested in creating some Groovy mixins, you can make your Fizz Buzz program quite simple, while getting some reusable Excel functionality out of it.

The Fizz Buzz Program

Here's the business logic of the Fizz Buzz program:

@Grab('org.apache.poi:poi-ooxml:3.13-beta1') import org.apache.poi.ss.usermodel.WorkbookFactory import org.apache.poi.ss.usermodel.Cell import org.apache.poi.ss.usermodel.Workbook import org.apache.poi.ss.usermodel.Sheet import org.apache.poi.ss.usermodel.Row /* * Apply pixie dust in the form of custom mixins. */ Integer.mixin FizzBuzzMixin IntRange.mixin FizzBuzzMixin Cell.mixin CellMixin Workbook.mixin WorkbookMixin Sheet.mixin SheetMixin Row.mixin RowMixin WorkbookFactory.metaClass.static.withWorkbook = WorkbookFactoryMixin.&withWorkbook /* * The program's entry point. */ def inputFileName = 'input.xls' def outputFileName = 'fizzbuzz.xls' WorkbookFactory.withWorkbook(inputFileName) {workbook -> def inputSheet = workbook.getSheet('input') def outputSheet = workbook.getSheet('output') /* * Grabs the input parameters from the input sheet * and converts them into a Map. * The input sheet must be in the following format * (here's an ASCII art representation): * * A B * 1 | parameterName | parameterValue | * * All of the parameter names and values don't need to be * in the same columns, but the value must come after the name * in the sheet's row. */ def params = inputSheet.collect {row -> /* * row.collate(2) produces [['fizz', 3.0]] * row.collate(2).flatten() produces ['fizz', 3.0] * Note: It actually produces Cell instances, * not String and Double. */ row.collate(2).flatten() }.inject([:]) { map, pair -> /* * pair[0] is the Cell with the parameter name. * pair[1] is the Cell with the parameter value. */ map[pair[0].value] = pair[1].value.toInteger() map } assert params.keySet().sort() == ['buzz', 'endValue', 'fizz', 'startValue'] def range = (params.startValue)..(params.endValue) /* * Gets fizz buzz results and writes them to the output sheet. */ range.fizzBuzz(params.fizz, params.buzz).eachWithIndex {value, index -> def row = outputSheet.getOrCreateRow(index) row.getOrCreateCell(0).cellValue = index row.getOrCreateCell(1).cellValue = value } workbook.write(outputFileName) }

The program begins by applying some mixins to Groovy and Apache POI classes. Then, it opens the Workbook using the added method WorkbookFactory.withWorkbook() . This method takes care of opening the Workbook and closing it when the Closure exits.

The core of the process happens within the Workbook Closure.

After loading the parameters into a Map, a Range is created from the start and end values. The added method Range.fizzBuzz() calculates the fizz buzz and returns them in a list. The eachWithIndex() method is used to iterate through the fizz buzz list and write it to the output sheet. Finally, the output is saved through the added (and overloaded) method Workbook.write(String filename) .

You'll see the use of with*() methods throughout. It's a practice common in Groovy that reduces the changes of leaving resources, such as files, open. You'll also notice a lack of for-loops. The each() and eachWithIndex() methods do the same thing; and in the case of eachWithIndex() , the index value is set for you :) Inner classes are also gone because Groovy supports multiple classes per file.

The Mixins

Here's the magic you can use to make this program clean and simple.

/* * Mixins for app-specific enhancements to Groovy, * and general enhancements to Apache POI. */ /* * A Mixin to add fizzBuzz() methods to * Range and Integer. */ class FizzBuzzMixin { /* * Calculates the fizz buzz for an Integer. * @param value the integer value * @param fizz the fizz value * @param buzz the buzz value * @return an Integer or a String. */ static Object fizzBuzz(Integer value, int fizz, int buzz) { def output = value def itFizzes = !(value % fizz) def itBuzzes = !(value % buzz) if(itFizzes) output = 'Fizz' if(itBuzzes) output = 'Buzz' if(itFizzes && itBuzzes) output = 'Fizz Buzz' return output } /* * Calculates the fizz buzz for a Range. * @param range the range * @param fizz the fizz value * @param buzz the buzz value * @return a List with the fizz buzz values. */ static List fizzBuzz(Range range, int fizz, int buzz) { range.collect { it.fizzBuzz(fizz, buzz) } } } /* * A mixin for Apache POI WorkbookFactory */ class WorkbookFactoryMixin { /* * Opens the Workbook for the specified file name, * executes the closure with the Workbook as its delegate, * closes the Workbook, and finally, closes the file. * @param filename of the Workbook * @param closure to execute with the Workbook * @return the return value of the Closure */ static Object withWorkbook(String filename, Closure closure) { (new FileInputStream(filename)).withStream {stream -> WorkbookFactory.create(stream).withWorkbook {workbook -> workbook.with(closure) } } } } /* * A mixin for Apache POI Cell. */ class CellMixin { /* * Closures used to retrieve a Cell value * according to the Cell's type. */ static final Map CELL_VALUE_CLOSURES = [ (Cell.CELL_TYPE_BLANK): { null }, (Cell.CELL_TYPE_BOOLEAN): { it.booleanCellValue }, (Cell.CELL_TYPE_ERROR): { it.errorCellValue }, (Cell.CELL_TYPE_FORMULA): { it.cellFormula }, (Cell.CELL_TYPE_NUMERIC): { it.numericCellValue }, (Cell.CELL_TYPE_STRING): { it.stringCellValue } ] /* * Returns the Cell's value * @param cell the cell * @return the Cell's value. Either a boolean, String, Double, or null. */ static Object getValue(Cell cell) { CELL_VALUE_CLOSURES[cell.cellType](cell) } } /* * A mixin for Apache POI Workbook */ class WorkbookMixin { /* * Executes the Closure with the Workbook as it's delegate. * Then closes the Workbook. * @param workbook the Workbook. * @param closure the Closure to execute. * @return the Closure's return value. */ static Object withWorkbook(Workbook workbook, Closure closure) { def output = workbook.with(closure) workbook.close() return output } /* * Writes the Workbook to a file with the specified name. * @param workbook the Workbook * @param filename to write to */ static void write(Workbook workbook, String filename) { (new FileOutputStream(filename)).withStream {stream -> workbook.write(stream) } } } /* * A mixin for Apache PIO Sheet */ class SheetMixin { /* * Returns the Row by its zero-based index, * creating the Row if necessary. * @param sheet the Sheet * @param index of the row * @return the Row */ static Row getOrCreateRow(Sheet sheet, int index) { sheet.getRow(index) ?: sheet.createRow(index) } } /* * A mixin for Apache POI Row */ class RowMixin { /* * Returns the Cell by its zero-based index, * creating it if necessary. * @param row the Row * @param index of the Cell. * @return the Cell */ static Cell getOrCreateCell(Row row, int index) { row.getCell(index) ?: row.createCell(index) } }

I hope you'll be able to reuse the Apache POI mixins in other Groovy programs.