Introduction

This module exposes a Groovy DSL to create real Excel documents using Apache POI under the hood. The goal of this module is to abstract away the most common uses of Apache POI to make creating Excel documents very easy, while providing a hook into the underlying API to allow users to do anything that is not provided automatically.

If you encounter any issues or inconsistencies while using this library, or if you have a great idea for improving this library, please create an issue!

Common Problems

If you encounter a class not found exception for org.openxmlformats.schemas.spreadsheetml.x2006.main.CTExtensionList, try adding org.apache.poi:ooxml-schemas:1.3 and org.apache.xmlbeans:xmlbeans:2.6.0 to your dependencies.

Getting Started

Installation

To get started using this great module, include it into your build:

Maven
<dependency>
    <groupId>com.jameskleeh</groupId>
    <artifactId>excel-builder</artifactId>
    <version>0.4.1</version>
</dependency>
Gradle
compile 'com.jameskleeh:excel-builder:0.4.1'
If you want to use a snapshot version, add the jfrog snapshot repository to your build: http://oss.jfrog.org/oss-snapshot-local. Normal releases are available in maven central and jcenter.

Creating your first Excel document

Here is the simplest example to create an Excel document:

package demo

import com.jameskleeh.excel.ExcelBuilder

File file = new File('test.xlsx')

ExcelBuilder.output(new FileOutputStream(file)) {
    sheet {
        row("test")
    }
}
Groovy Excel Builder only supports XLSX documents, not the old XLS format.

As you might imagine from that example, a single sheet will be created with the value of "test" in cell A1.

In addition to writing the excel document directly to an output stream, you can also just build the document and do what you want with it afterwards.

package demo

import com.jameskleeh.excel.ExcelBuilder
import org.apache.poi.xssf.usermodel.XSSFWorkbook

XSSFWorkbook workbook = ExcelBuilder.build {
    sheet {
        row {
            cell("Test 1")
            cell("Test 2")
        }
    }
}

//Modify the workbook further

workbook.write(someOutputStream)

The Groovy Excel Builder provides many options and features for creating documents. To learn more, continue on reading this documentation.

Sheets

With the Groovy Excel Builder, you can create documents with as many sheets as you like. In addition, there are several options you have at the sheet level for configuring your document.

Underlying API

To get access to the Apache POI API for a given sheet, simply receive an argument to the sheet closure:

import org.apache.poi.xssf.usermodel.XSSFSheet

ExcelBuilder.build {
    sheet { XSSFSheet mySheet ->
        //Modify mySheet as needed
    }
}

Sheet Name

You can configure what name you would like to give each sheet by executing the sheet method with a String argument.

ExcelBuilder.build {
    sheet("My Sheet") {
    }
}

Defaults

You can configure defaults for column width and row height in a given sheet by passing a Map to the sheet method.

ExcelBuilder.build {
    sheet("My Sheet") {
    }
    sheet("My Other Sheet", [width: 20, height: 14F]) {
    }
    sheet([width:20]) {
    }
}

The width must be an Integer and the height can be a Float if you want to set the height in points, or Short if you do not.

Styling

You can set default styles for a sheet by passing a map of options into the defaultStyle method. To see what options are available, visit the Styles section of this document.

ExcelBuilder.build {
    sheet {
        defaultStyle([:])
    }
}

Rows

In addition to creating cells, there are also a few other methods available to you to make documents easier.

Underlying API

To get access to the Apache POI API for a given row, simply receive an argument to the row closure:

import org.apache.poi.xssf.usermodel.XSSFRow

ExcelBuilder.build {
    sheet {
        row { XSSFRow myRow ->
            //Modify myRow as needed
        }
    }
}

Defaults

You can apply default styling at the row level in addition to the sheet level. Styles that conflict with defaults at the sheet level will override.

ExcelBuilder.build {
    sheet {
        defaultStyle [:]
        row {
            defaultStyle [:]
        }
    }
}

Header Row

It is possible to define a header row and assign an identifier to each cell for later use.

ExcelBuilder.build {
    sheet {
        columns {
            column("Foo", "foo")
            column("Bar", "bar")
        }
    }
}

In future rows, you can do things like skip to a given column or reference that column in a formula. The first argument is assigned to the cell value and the second argument is the unique identifier.

Creating Rows

You can easily create rows with the following syntax:

ExcelBuilder.build {
    sheet {
        //Empty row
        row {
        }

        //Values in cells A2, B2, C2
        row(1,2,3)

        //Empty row with height specified
        //Height can be a Short if you want to set the height in "twips"
        row([height: 10F] {
        }

        //Empty row
        row()
    }
}

To reference everything you can do in a row, see the section in the documentation on Rows.

Skipping Rows

It is very easy to skip any number of rows. See the following example:

ExcelBuilder.build {
    sheet {
        row("A", "B", "C")
        skipRows(2)
        row("A", "B", "C")
    }
}

The above example will produce a sheet that looks something like this:

A B C

1

A

B

C

2

3

4

A

B

C

Skipping Cells

ExcelBuilder.build {
    sheet {
        row {
            cell("foo")
            skipCells(2)
            cell("bar")
        }
    }
}

The previous example will produce a document with "foo" in cell A1 and "bar" in D1.

Skipping to a column

ExcelBuilder.build {
    sheet {
        columns {
            column("Foo", "foo")
            skipCells(1)
            column("Bar", "bar")
        }
        row {
            skipTo("bar")
            cell("C2")
        }
    }
}

The example above will put the text "C2" in cell C2.

The skipTo method will set the cell index so the next cell call will output data into the desired cell. Subsequent cells will start from that index. View the example below:
ExcelBuilder.build {
    sheet {
        columns {
            column("Foo", "foo")
            skipCells(1)
            column("Bar", "bar")
        }
        row {
            cell("A2")
            cell("B2")
            skipTo("foo")
            cell("replaces A2")
        }
    }
}

The first call to cell sets the value to "A2". The skipTo call sets the index so the next call will set the cell value to "replaces A2".

A B C

1

Foo

Bar

2

replaces A2

B2

Columns

If you wish to build your document by column instead of by row, you can do so with the column method.

Underlying API

There is no such thing as a column object in the Apache POI API, however you can get a reference to each cell which will allow you to access the row.

import org.apache.poi.xssf.usermodel.XSSFCell
import org.apache.poi.xssf.usermodel.XSSFRow

ExcelBuilder.build {
    sheet {
        column {
            XSSFCell myCell = cell("A1")
            XSSFRow rowOne = myCell.row
        }
    }
}

Defaults

You can apply default styling at the column level in addition to the sheet level. Styles that conflict with defaults at the sheet level will override.

ExcelBuilder.build {
    sheet {
        defaultStyle [:]
        column {
            defaultStyle [:]
        }
    }
}

Skipping Cells

ExcelBuilder.build {
    sheet {
        column {
            cell("foo")
            skipCells(2)
            cell("bar")
        }
    }
}

The previous example will produce a document with "foo" in cell A1 and "bar" in A4.

Usage with a header row

You can create columns with a header row. The columns will start after rows previously created.

ExcelBuilder.build {
    sheet {
        columns {
            column("Foo", "foo")
            skipCells(1)
            column("Bar", "bar")
        }
        column {
            cell("A2")
            cell("A3")
        }
    }
}

The example above will put the text "A2" in cell A2.

Skipping Columns

It is very easy to skip any number of columns. See the following example:

ExcelBuilder.build {
    sheet {
        column {
            cell("A")
            cell("B")
            cell("C")
        }
        skipColumns(2)
        column {
            cell("A")
            cell("B")
            cell("C")
        }
    }
}

The above example will produce a sheet that looks something like this:

A B C D

1

A

A

2

B

B

3

C

C

Cells

Cell Values

This module supports setting basic and custom types to cell values. The following types are supported out of the box:

  • String

  • Calendar

  • Date

  • Number

  • Boolean

Support for additional types can be easily added without the need to convert your custom objects each time.

Underlying API

To get access to the Apache POI API for a given cell, simply store the return value of the cell method:

import org.apache.poi.xssf.usermodel.XSSFCell

ExcelBuilder.build {
    sheet {
        row {
            XSSFCell customCell = cell("Foo")
            //Modify customCell as needed
        }
    }
}

Custom Renderers

You can register a custom cell renderer to marshal your custom type into a type that is supported by Excel.

MyCustomClass.groovy
class MyCustomClass {
    String name
}
import com.jameskleeh.excel.Excel

//Register a renderer for your class
Excel.registerCellRenderer(MyCustomClass) { MyCustomClass c ->
    c.name
}

Now when you are making calls to cell(), you can pass your entire MyCustomClass instance as the value and it will be converted to just use the name.

def c = new MyCustomClass(name: "Sally")

ExcelBuilder.build {
    sheet {
        row(c) //The value "Sally" will be put into cell A1
    }
}
If a renderer is registered for a superclass, it will resolve for subclasses. If you wish to provide customized functionality for a subclass you can register a renderer for that class with a higher priority. The default is -1.
class Foo {
    String name
}
class Bar extends Foo {
    String title
}

Excel.registerCellRenderer(Foo) { it.name }

// With the current behavior, all values of type "Foo" or any subclass of "Foo" will be rendered with the "name" property. If you wish to override the rendering for a given type, you can specify another renderer with a higher priority.

Excel.registerCellRenderer(Bar, 0) { it.title }

Cell Styling

All calls to cell can be accompanied with a map of arguments to style the cell. The arguments passed will be merged with the default styles of the row and sheet, if provided.

def c = new MyCustomClass(name: "Sally")

ExcelBuilder.build {
    sheet {
        row {
            cell("Test", [:])
        }
    }
}

See the section in this guide on styling cells to learn what options are available.

Cell Formatting

It is not necessary for you to pre format your data before creating cells. You can apply a format to any given class and that format will be used when data of that type is written to a cell. There are several data types that are already configured. You can override that configuration and add your own.

The following classes to format mappings are created by default.

Class Format

java.math.BigDecimal

$#,##0.00_);[Red]($#,##0.00)

java.lang.Double

#,##0.00

java.lang.Float

#,##0.00

java.lang.Integer

#,##0

java.lang.Long

#,##0

java.lang.Short

#,##0

java.math.BigInteger

#,##0

java.util.Date

m/d/yyyy

To override or add additional format mappings, register them with the Excel class.

import com.jameskleeh.excel.Excel
import java.time.OffsetDateTime

//Override the existing format for BigDecimal
Excel.registerCellFormat(BigDecimal, "\"$\"#,##0.00_);(\"$\"#,##0.00)")

//Create a new format mapping
Excel.registerCellFormat(OffsetDateTime, "m/d/yy h:mm")

There are a list of built in formats you can reference by their index. The list is documented in a comment in the Apache POI BuiltinFormats class.

To register one of the built in formats, simply use the Integer representation.

import com.jameskleeh.excel.Excel

Excel.registerCellFormat(Float, 10) // "0.00%"

In addition to providing global formats by class, you can also override the format for any given cell.

ExcelBuilder.build {
    sheet {
        row {
            cell(0.105F, [format: 10])
        }
    }
}

The following result will be produced.

A

1

10.50%

If a format is registered for a superclass, it will resolve for subclasses. If you wish to provide customized functionality for a subclass you can register a format for that class with a higher priority. The default is -1.
import java.time.temporal.TemporalAccessor
import java.time.LocalTime

Excel.registerCellFormat(TemporalAccessor, "m/d/yy h:mm")

// With the current behavior, all Java 8 date types will be rendered with the supplied format. If you wish to override the format for a given type, you can specify another format with a higher priority.

Excel.registerCellFormat(LocalTime, 0, "h:mm:ss")

Merging Cells

This module supports merging cells with an easy to use API.

row {
    merge {
        //Anything you can do inside row directly
        cell("Foo")
        skipCells(5)
    }
}
Only one call to cell() is allowed inside a merge.

You can also apply default styling to the merged cells

row {
    merge(font: Font.BOLD) {
        //Anything you can do inside row directly
        cell("Foo")
        skipCells(3)
    }
}

If you only have one piece of data, but want that data to account for multiple columns, you can pass a number to the merge method.

row {
    //The value "Foo" will span 3 columns
    merge("Foo", 3)
}

You can also apply default styling to the merged cells created in that way

row {
    merge("Foo", 3, [font: Font.BOLD])
}

The link method allows you to create a cell that will contain a link. Links can link to files, cells, web addresses, or email addresses.

To create a link to a URL, simply create a link like the following example:

import org.apache.poi.xssf.usermodel.XSSFCell
import org.apache.poi.common.usermodel.Hyperlink

ExcelBuilder.build {
    sheet {
        row {
            XSSFCell myCell = link("Google", "http://www.google.com", Hyperlink.LINK_URL)
            link("John Smith", "mailto:john@smith.com", Hyperlink.LINK_EMAIL)
            link("Open File", "localFile.docx", Hyperlink.LINK_FILE)
            link("Go To Cell", "'Sheet Name'!A2", Hyperlink.LINK_DOCUMENT)
        }
    }
}

There is an additional link method to assist you in creating links to cells in the current document. The method takes in a closure that has the same methods available when creating formulas. See the section on formulas for the full documentation.

ExcelBuilder.build {
    sheet {
        row {
            link("Go To Cell") {
                "'${sheetName}'!${exactCell(0,0}"
            }
        }
    }
}

Formulas

The Groovy Excel Builder supports formulas in excel documents by providing an easy way to create formulas with support for helper methods to get cell references.

Simple String

You can specify the exact text of your formula using the example below.

ExcelBuilder.build {
    sheet {
        row {
            formula("SUM(1,2)")
        }
    }
}

Closure Customization

An additional way to create a formula is to supply a closure to the formula method. The provided closure will have access to a few methods to make referencing cells easier. Whatever gets returned from the closure will be set as the formula.

ExcelBuilder.build {
    sheet {
        row {
            formula {
                "SUM(1,2)"
            }
        }
    }
}

Exact Cell Reference

Inside of the formula closure you can get a reference to an exact cell by providing the index of the row and column.

...
formula {
    "SUM(${exactCell(0,1)},2)"
}
...

The call to exactCell will return the string "A2" because the 0 represents the column index ("A") and the 1 represents the row index ("2").

If you have previously defined columns, you can reference exact cells based on the column id.

ExcelBuilder.build {
    sheet {
        columns {
            column("Foo", "foo")
        }
        row {
            formula {
                "SUM(${exactCell("foo")}, ${exactCell("foo", 1)})"
            }
        }
    }
}

The first call to exact cell will return "A1" because the foo column is in column "A" in the first row. The second call to exact cell is also specifying a row index and will return "A2" because foo is column "A" and the row index of 1 resolves to row "2".

Relative Cell References

In addition to retrieving exact cell references, you can also retrieve references relative to the formula.

ExcelBuilder.build {
    sheet {
        row()
        row {
            cell("A2")
            formula {
                "SUM(${relativeCell(-1)}, ${relativeCell(-1, -1)})"
            }
        }
    }
}

The first call to relativeCell will reference the cell in the previous column in the same row. In that example that would be "A2". The second call to relativeCell will resolve to "A1" because the first argument indicates to go back 1 column and the second argument says to go back one row. The formula will be put in cell "B2", so minus one column and minus one row would be "A1".

Anchoring Cells

Once you have a reference to any cell, you can anchor the column or row or both.

...

formula {
    "SUM(${exactCell(0,1).anchorColumn()}, ${exactCell(0,2).anchorRow()}, ${exactCell(0,3).anchor()})"
}
...

The resulting formula will be:

SUM($A2, A$3, $A$4)

Formula Styling

Formulas support a Map parameter that is the styling options

formula("CONCATENATE(A1,A2)", [font: Font.BOLD])
formula([font: Font.BOLD]) {
    ...
}

Styling Cells

There are many options available for styling cells to meet your requirements. Any method that creates a cell can have styling applied to it.

Examples:

  • column(value, id, style)

  • cell(value, style)

  • formula(value, style)

  • formula(style, closure)

In addition, you can apply default styling to other elements.

  • row { defaultStyle(style) …​ }

  • sheet { defaultStyle(style) …​ }

  • merge(style) { }

Font

You can set the font decoration and color by passing a few simple options:

import com.jameskleeh.excel.Font

...
cell("A", [font: Font.BOLD]) // View the Font class for options
...

There are also several types of underline that can be set:

...
cell("A", [font: [underline: 'single']) //One of ['single', 'singleAccounting', 'double', 'doubleAccounting']
...

You can also specify the font color:

import java.awt.Color

...
cell("A", [font: [bold: true, color: Color.RED])
...
Any option that expects a color supports colors specified by an instance of java.awt.Color or the string hex value ("#FFFFFF")

The font size can also be specified:

...
cell("A", [font: [size: 12.5])
...

The font name can be set as well:

...
cell("A", [font: [name: "Arial"]])
...

Hidden

Cells can be hidden by passing the hidden argument:

...
cell("A", [hidden: true])
...

Locked

Cells can be locked by passing the locked argument:

...
cell("A", [locked: true])
...

Wrap Text

Cells can have their text wrapped by passing the wrapped argument:

...
cell("Some really long string", [wrapped: true])
...

Horizontal Alignment

You can set the horizontal alignment of a cell by passing the alignment argument:

import org.apache.poi.ss.usermodel.HorizontalAlignment

...
cell("A", [alignment: HorizontalAlignment.CENTER]) // View the HorizontalAlignment class for options
...

Any horizontal alignment can also be passed as a string:

...
cell("A", [alignment: "center"])
...

Vertical Alignment

You can set the vertical alignment of a cell by passing the verticalAlignment argument:

import org.apache.poi.ss.usermodel.VerticalAlignment

...
cell("A", [verticalAlignment: VerticalAlignment.TOP]) // View the VerticalAlignment class for options
...

Any vertical alignment can also be passed as a string:

...
cell("A", [verticalAlignment: "top"])
...

Rotation

To set the rotation of a cell, pass a value that can be cast to a Short as the rotation argument:

...
cell("A", [rotation: 90]) //Any value between 0 and 180
...

Indention

To indent a cell, pass the number of spaces you would like to indent to the indent argument:

...
cell("A", [indent: 4])
...

Border

Border style and color can be set for any given cell.

To set the style for all borders (left, top, bottom, right):

import org.apache.poi.ss.usermodel.BorderStyle

...
cell("A", [border: BorderStyle.THIN]) // View the BorderStyle class for options
...

To set the style for only the specified edge:

import org.apache.poi.ss.usermodel.BorderStyle

...
cell("A", [border: [left: BorderStyle.THIN]])
...

To override the border for any given edge:

import org.apache.poi.ss.usermodel.BorderStyle

...
cell("A", [border: [style: BorderStyle.THIN, left: BorderStyle.THICK]])
...

To also specify a border color:

import org.apache.poi.ss.usermodel.BorderStyle

...
cell("A", [border: [style: BorderStyle.THIN, color: Color.RED]])
...

To override the border color for any given edge:

import org.apache.poi.ss.usermodel.BorderStyle

...
cell("A", [border: [style: BorderStyle.THIN, color: Color.RED, left: [color: Color.BLUE]]])
...

Here is a summary of the long form of the possible keys supported:

border:
    style:
    color:
    left:
        style:
        color:
    right:
        style:
        color:
    bottom:
        style:
        color:
    top:
        style:
        color:

Fill

You can set the fill pattern of a cell using the 'fill' argument:

To set the style for all borders (left, top, bottom, right):

import org.apache.poi.ss.usermodel.FillPatternType

...
cell("A", [fill: FillPatternType.DIAMONDS]) // View the FillPatternType class for options
...

Any fill pattern can also be passed as a string:

...
cell("A", [fill: "diamonds"])
...
You must set a foreground or background color in order to see any result from this style attribute

Foreground Color

If you have a fill pattern set, the foregroundColor attribute will set the foreground color. If you don’t have a fill pattern specified this does the same thing as if you specified a background color.

import java.awt.Color

...
cell("A", [foregroundColor: "#000000"])
cell("B", [foregroundColor: Color.BLACK])
...

Background Color

To set the background color of a cell, use the backgroundColor argument:

import java.awt.Color

...
cell("A", [backgroundColor: "#000000"])
cell("B", [backgroundColor: Color.BLACK])
...