Excel in Groovy

Vladimír Oraný

Test Facilitator @ Agorapulse

@musketyr

http://spreadsheet.dsl.builders/

@AgoraPulse

Agenda

How to avoid Excel generation
The current approach using Apache POI
Live demo of writing Excel with Spreadsheet Builder
- Tabular data
- Advanced tabular data
Writing advanced Excel files with Spreadsheet Builder
- Non-tabular reports
Reading Excel files with Spreadsheet Criteria

Example Data - Twitter

Unbeatable Excel

https://blog.toggl.com/freelancing-problems-dinosaurs/

Unbeatable Excel

CSV Workaround

TAB Separated File Workaround

HTML Workaround

Apache POI

Apache POI (cont.)

https://blog.toggl.com/freelancing-problems-dinosaurs/

Apache POI (cont.)

Apache POI Export

XSSFWorkbook workbook = new XSSFWorkbook()

XSSFSheet sheet = workbook.createSheet('Tweets')

XSSFRow headerRow = sheet.createRow(0)

['User', 'Favourites', 'Retweets','Text'].eachWithIndex { header, i ->
    XSSFCell headerCell = headerRow.createCell(i)
    headerCell.cellValue = header
}

tweets.eachWithIndex { Status tweet, int i ->
    XSSFRow row = sheet.createRow(i + 1)
    XSSFCell screenNameCell = row.createCell(0)
    screenNameCell.cellValue = tweet.user.screenName

    XSSFCell favouriteCountCell = row.createCell(1)
    favouriteCountCell.cellValue = tweet.favoriteCount

    XSSFCell retweetCountCell = row.createCell(2)
    retweetCountCell.cellValue = tweet.retweetCount

    XSSFCell textCell = row.createCell(3)
    textCell.cellValue = tweet.text
}

workbook.write(out)

Tabular Data

Headlines and status per each row

Spreadsheet Builder

SpreadsheetBuilder builder = PoiSpreadsheetBuilder.create(out)

builder.build {
    sheet 'Tweets', {
        row {
            cell 'User'
            cell 'Favourites'
            cell 'Retweets'
            cell 'Text'
        }
        tweets.each { tweet ->
            row {
                cell tweet.user.screenName
                cell tweet.favoriteCount
                cell tweet.retweetCount
                cell tweet.text
            }
        }
    }
}

Tabular Data

Advanced

Tabular Data

Bold headlines with filter and scroll freeze, status per each row, some data highlighted

Filters and Scroll Lock

builder.build {
    // ...

    sheet 'Tweets', {
        filter auto
        freeze 0, 1

        row {
            // ...
        }

        // ...
       
    }
}

Styles

builder.build {
    style 'headline', {
        font {
            style bold
        }
    }

    style 'text', {
        wrap text
        align center, justify
    }

    style 'highlighted', {
        foreground orange
    }

    sheet 'Tweets', {
        // ...

        row {
            style 'headline'
            // ...
        }
    }
}

Advanced Tabular Data

Unit Testing

criteria.query {
    sheet {
        row {
            cell {
                style {
                    font {
                        style bold
                    }
                }
            }
        }
    }
}.size() == 5

criteria.query {
    sheet {
        row {
            cell {
                style {
                    foreground orange
                }
            }
        }
    }
}.rows.size() == 1

criteria.query {
    sheet {
        row {
            cell {
                value 'yes'
            }
        }
    }
}.size() == 73

Excel Report

Non-tabular structure, different text styles, borders, print setup

Spans


//    | A        | B        | C            |
// -----------------------------------------
//  1 | Name     | Text     | Fav. Count   |
// ----          -          -              -
//  2 | Name     | Text     | Fav. Count   | 
// ---------------          ----------------
//  3 | Handle   | Text     | Rtw. Count   |
// ---------------          -              -
//  4 | Create   | Text     | Rtw. Count   |
// -----------------------------------------

Spans

 sorted.each { tweet ->
    row {
        cell {
            value tweet.user.name
            rowspan 2
        }
        cell {
            value tweet.text
            rowspan 4
        }
        cell {
            value tweet.retweetCount
            rowspan 2
        }
    }

    row()

    row {
        cell { value "@$tweet.user.screenName" }
        cell('C') {
            value tweet.favoriteCount
            rowspan 2
        }
    }

    row { cell { value tweet.createdAt } }
}

Dimensions

sorted.each { tweet ->
    row {
        cell {
            value tweet.user.name
            width 4 cm
        }
        cell {
            value tweet.text
            width 14 cm
        }
        cell {
            value tweet.retweetCount
            width 2 cm
        }
    }

    row()

    row {
        cell { value "@$tweet.user.screenName" }
        cell('C') { value tweet.favoriteCount }
    }

    row {
        cell { value tweet.createdAt }
    }
}

Page

sheet('Tweets') {
    page {
        paper a4
        orientation portrait
    }
}

Date

style 'date', {
    format 'dd.mm.yyyy hh:mm'
}

Borders

style 'border-top', {
    border top, {
        style thick
        color black
    }
}

style 'border-bottom', {
    border bottom, {
        style thick
        color black
    }
}

style 'border-left', {
    border left, {
        style thick
        color black
    }
}

style 'border-right', {
    border right, {
        style thick
        color black
    }
}

Excel Report

Reading Excel

Reading Order

Reading Order (cont.)

Cell productCell = criteria.query {
    sheet {
        row {
            cell {
                value 'Product'
                style { font { style bold } }
            }
        }
    }
}.cell
Cell quantityCell = criteria.query {
    sheet {
        row {
            cell {
                value 'Qty'
                style { font { style bold } }
            }
        }
    }
}.cell
Cell totalRow = criteria.query {
    sheet {
        row {
            cell('A') {
                value 'Total'
                style { font { style bold } }
            }
        }
    }
}.row

Spreadsheet Criteria

Map<String, Integer> quantities = [:]

Cell productNameLineCell = productCell.bellow
Cell quantityLineCell = quantityCell.bellow

while (productNameLineCell && productNameLineCell.row.number < totalRow.number) {
    quantities[productNameLineCell.read(String)] = quantityLineCell.read(Number).intValue()

    productNameLineCell = productNameLineCell.bellow
    quantityLineCell = quantityLineCell.bellow
}