FizzBuzz-type program that reads/writes to Excel

Question

I'm new to Groovy, and coming from PHP it's been a rough transition.

The code below works. However, I would appreciate feedback on how better to write it and follow good practice with Groovy.

I anticipate doing a lot of Excel work in the future, and so tried to make this program reusable for more than just FizzBuzz.

/*
 * In Excel: Create a workbook with a blank sheet, "output"
 * and a sheet "input" with the values
 * startValue : 0
 * endValue : 100
 * fizz : 3
 * buzz : 5
 * 
 * In Groovy: Create a program that will
 * - Open an Excel file
 * - Extract the four parameters above
 * - Run a "FizzBuzz" on those parameters
 * - Write the results to the "output" sheet
 * 
 * FizzBuzz: For all values in a range
 *      If the value is divisible by 3, print "fizz"
 *      If the value is divisible by 5, print "buzz"
 *      If the value is divisible by 3 and 5, print "fizzbuzz"
 *      Otherwise print the value
 * 
 * The program below uses Apache POI to parse Excel.
 */

package org.example

import java.io.FileNotFoundException
import java.io.FileOutputStream
import java.io.IOException

import org.apache.poi.ss.usermodel.*
import org.apache.poi.xssf.usermodel.*

class Excel {

    def file;
    def workbook;

    static main(def args) throws Exception {
        new Excel()
    }

    Excel(){
        file = new FileInputStream("input.xlsx")
        workbook = WorkbookFactory.create(file)
        def sheetIn = workbook.getSheet("input")
        def fizzbuzz = new Excel.FizzBuzz()
        def params = fizzbuzz.setFromSheet(sheetIn)
        def sheetOut = workbook.getSheet("output")

        this.doByRow(sheetOut, params.start, params.end, { rowNum, value ->
            def row = row(sheetOut, rowNum)
            setCell(row, 0, value)
            setCell(row, 1, fizzbuzz.calculate(value))
        })
        def fileOut = new FileOutputStream(filename("fizzbuzz"))
        workbook.write(fileOut)
    }

    def filename(value){
        return "out/" + value + "_" + System.currentTimeMillis() + ".xlsx"
    }

    def doByRow(sheet,startValue,endValue,doSomething){
        def rowNum = 0;
        for(
            def value = startValue;
                value <=endValue;
                value = value + 1
            ){
            doSomething(rowNum, value)
            rowNum = rowNum + 1
        }
    }

    def hlookup(sheet, value, toTheRight){
        for(row in sheet){
            for(int index = 0; index < row.getLastCellNum(); index++){
                def cellValue = getCellValue(row, index)
                if(cellValue == value){
                    return getCellValue(row, index + toTheRight)
                }
            }
        }
    }

    def row(sheet,index){
        def rowOut = sheet.getRow(index)
        if(rowOut == null) rowOut = sheet.createRow(index)
        return rowOut
    }

    def getCellValue(row,index){
        def cellOut = row.getCell(index)
        def output
        switch (cellOut.getCellType()){
            case 0: /*Cell type numeric*/
                output = cellOut.getNumericCellValue()
                if(output % 1 == 0) output = output.toInteger()
                else output = output.toFloat()
                break
            case null:
                row.createCell(index).setCellType("string")
                output = ""
                break
            default:
                output = cellOut.getStringCellValue().toString()
                break
        }
        return output
    }

    def setCell(row, index, value){
        def cellOut = row.getCell(index)
        if(cellOut == null){
            cellOut = row.createCell(index)
        }
        cellOut.setCellValue(value)
    }

    class FizzBuzz{
        def params = [
            "start" :0,
            "end"   :0,
            "fizz"  :0,
            "buzz"  :0
            ]

        def setFromSheet(sheet){
            this.params.start   = Excel.this.hlookup(sheet,"startValue",1)
            this.params.end     = Excel.this.hlookup(sheet,"endValue",1)
            this.params.fizz    = Excel.this.hlookup(sheet,"fizz",1)
            this.params.buzz    = Excel.this.hlookup(sheet,"buzz",1)
            return this.params;
        }

        def calculate(num){
            def output = ""
            if(num % this.params.fizz.toFloat() == 0) output += "fizz"
            if(num % this.params.buzz.toFloat() == 0) output += "buzz"
            if(output == "") output = num;
            return output;
        }
    }

}

Answer 1

Why would FizzBuzz need to know about Excel at all?

class FizzBuzz{
    def params = [
        "start" :0,
        "end"   :0,
        "fizz"  :0,
        "buzz"  :0
        ]

    def setFromSheet(sheet){
        this.params.start   = Excel.this.hlookup(sheet,"startValue",1)
        this.params.end     = Excel.this.hlookup(sheet,"endValue",1)
        this.params.fizz    = Excel.this.hlookup(sheet,"fizz",1)
        this.params.buzz    = Excel.this.hlookup(sheet,"buzz",1)
        return this.params;
    }

As it is, you couldn't take your FizzBuzz class and use it in a different context. Your FizzBuzz implementation shouldn't depend on Excel. It should take some values in, do some work on them, and return them in a new form. Your Excel class (bad name by the way) should be responsible for fetching those values and passing them to FizzBuzz. I would recommend making its constructor take in start, end, fizz, and buzz as arguments instead. This breaks the dependency and makes it so you could use the class in a console program (or anywhere really) if you so chose.

Answer 2

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.

1. 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.
2. The eachWithIndex() method is used to iterate through the fizz buzz list and write it to the output sheet.
3. 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.