
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

Unbeatable Excel

CSV Workaround

TAB Separated File Workaround

HTML Workaround

Apache POI

Apache POI (cont.)

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

The End?

More Features?

More Features!

Grouping

Sheet Protection

Pictures

Comments

Named Cells & Formulas

Borders

Rich Text

Text Rotation

Excel in Groovy
By musketyr
Excel in Groovy
- 3,683