Spreadsheet builder provides convenient way how to create MS Excel OfficeOpenXML Documents (XSLX) focus not only on content side but also on easy styling.
Getting Started
Following example creates the basic spreadsheet with two rows and three columns.
@Grab(group='org.modelcatalogue', module='spreadsheet-builder-poi', version='0.1.6')
// fixes bugs on Groovy 2.4.x
@Grab(group='commons-codec', module='commons-codec', version='1.10')
@GrabExclude('org.codehaus.groovy:groovy-all')
import org.modelcatalogue.builder.spreadsheet.poi.PoiSpreadsheetBuilder
def builder = new PoiSpreadsheetBuilder()
File file = new File('spreadsheet.xlsx')
file.withOutputStream { out ->
builder.build(out) {
sheet('Sample') {
row(2) {
style {
background whiteSmoke
border top, bottom {
style thin
color black
}
}
cell('B') {
value 'A'
style {
border left, {
style thin
color black
}
}
}
cell 'B'
cell {
value 'C'
style {
border right, {
style thin
color black
}
}
}
}
row {
cell('B') { value 1 }
cell 2
cell 3
}
}
}
}Result
Sheets and Rows
Each sheet needs to have a name provided. Rows are defined inside the sheets.
You can specify the row number when creating a row. Another rows created without the number specified will be placed
after this row. The row number is the same as in the spreadsheet e.g. the index of the rows is starting with number 1.
To skip a single row, just use row() without any parameters.
sheet('Sample') {
row 5, { cell 'Line 5' }
row()
row { cell 'Line 7' }
}Result
Outlines
Rows and columns can be optionally grouped into expanded or collapsed groups for better readability.
sheet('Sample') {
row {
cell 'Heading 1'
group {
cell 'Heading 2'
cell 'Heading 3'
cell 'Heading 4'
collapse {
cell 'Heading 5'
cell 'Heading 6'
}
cell 'Heading 7'
}
}
// expanded group
group {
row { cell 'Heading 2' }
row { cell 'Heading 3' }
row { cell 'Heading 4' }
// collapsed group
collapse {
row { cell 'Heading 5' }
row { cell 'Heading 6' }
}
row { cell 'Heading 7' }
}
}Result
Freezing columns and rows
You can freeze some rows and or columns to prevent scrolling them.
sheet('Sample') {
freeze 1, 1
row {
cell '-'
cell 'A'
cell 'B'
cell 'C'
cell 'D'
cell 'E'
cell 'F'
}
10.times { int i ->
row {
cell "Row ${i + 1}"
6.times { int j ->
cell (10 * i + j)
}
}
}
}Result
Protection
You can either lock the sheet or you can protect it with password. Then the users won’t be able to edit any cells or view any formulas. This can emphasize that user changes are not desired.
sheet('Sample') {
locked
row {
cell {
value 'Locked'
}
}
}
sheet('Password Protected') {
password 'p4$$w0rd'
row {
cell 'Protected by Password'
}
}Result
Cells
Cells are defined within rows. The simples example to create a cell is providing its value as a method call argument.
Additionally you can customize more details when you pass a closure with the cell definition. Then you can either
set the value of the cell using the value method or the formula using the formula method.
|
You can substitute the |
You can either specify the column for the cell as number starting from 1 or alphabetically as it will appear
in the generated spreadsheet e.g. C. Otherwise the cells will be generated one after another. To create empty cell
call the cell method without any parameters.
sheet('Sample') {
row {
cell 'First'
cell()
cell 'Third'
cell(5) {
value 'Fifth'
}
cell('G') {
formula 'YEAR(TODAY())'
}
}
}Result
Type handling
Any cell values which are not instances of Number, Boolean, Date or Calendar are handled as String using a toString()
method. For any instance of Number, Date or Calendar the type of cell is set to NUMERIC.
For Boolean the type of the cell is set automatically to BOOLEAN.
|
|
Merging cells
You can set rowspan and/or colspan of any cell to merge multiple cells together.
sheet('Sample') {
row {
cell {
value "Columns"
colspan 2
}
}
row {
cell {
value 'Rows'
rowspan 3
}
cell 'Value 1'
}
row {
cell ('B') { value 'Value 2' }
}
row {
cell ('B') { value 'Value 3' }
}
}Result
Comments
You can set comment of any cell using the comment method. Use the variant accepting closure If you want to specify
the author of the comment as well. The author only appears in the status bar of the application.
sheet('Sample') {
row {
cell {
value "Commented 1"
comment "This is a comment 1"
}
}
row {
cell {
value "Commented 2"
comment {
author "musketyr"
text "This is a comment 2"
}
}
}
}Result
Names
Naming cells helps you to refer them with links or you can use them to expand the formula definitions. To declare name
of the cell simply call the name method inside the cell definition. See Links how to use names to create link
to the particular cell.
sheet('Sample') {
row {
cell 'A'
cell 'B'
cell 'A + B'
}
row {
cell {
value 10
name 'CellA'
}
cell {
value 20
name 'CellB'
}
cell {
formula 'SUM(#{CellA},#{CellB})'
}
}
}Result
Links
You can create for types of links
-
link to name 'A Name'will create link other parts of the document using cell nameA Name -
link to url 'http://www.example.com'will create link to open URLhttp://www.example.com -
link to email 'musketyr@example.com'will create link to send mail tomusketyr@example.com -
link to file 'README.txt'will create link to open fileREADME.txt
|
Links does not appear blue and underline by default You need to style them appropriately yourself. |
sheet('Sample') {
row {
cell {
value 'Hello World'
name 'Salutation'
}
}
}
sheet('Links') {
row {
cell {
value 'Document'
link to name 'Salutation'
width auto
}
cell {
value 'File'
link to file 'text.txt'
}
cell {
value 'URL'
link to url 'https://www.example.com'
}
cell {
value 'Mail (plain)'
link to email 'musketyr@example.com'
}
cell {
value 'Mail (with subject)'
link to email 'musketyr@example.com',
cc: 'tester@example.com'
subject: 'Testing Excel Builder',
body: 'It is really great tools'
}
}
}|
You can add arbitrary attributes to the email link such as |
Dimensions
You can set the width of the cells as the multiple of standard character width. You can also set the column to accommodate
the width automatically using the auto keyword but it may slow down the generation. The width is defined inside cells
(usually header cells) but applies to the whole column.
You can set the height of the cell in points. The height applies to whole row.
sheet('Sample') {
row {
cell {
value 'No.'
width 5
}
cell {
value 'Name'
width 30
}
cell {
value 'Description'
width auto
}
}
row {
cell 1
cell {
value "Groovy Spreadsheet Builder"
height 30
}
cell "Helps building nice spreadsheet reports"
}
}Result
Styles
Cell styles are defined either for a whole row or a particular cell. You can define a named style on the top level along with sheets and than refer to it from cell or row.
style ('headers') {
border(bottom) {
style thick
color black
}
font {
bold
}
background whiteSmoke
}
sheet('Sample') {
row {
style 'headers'
cell {
value 'No.'
width 5
}
cell {
value 'Name'
width 30
}
cell {
value 'Description'
width auto
}
}
row {
cell 1
cell {
value "Groovy Spreadsheet Builder"
style {
font {
bold
}
}
}
cell "Helps building nice spreadsheet reports"
}
}Result
Alignments
Use align method to align the cells horizontally or vertically. You place the vertical alignment first and then
the horizontal. Use default value bottom if you dont want to change the vertical alignment but you want to change
the horizontal one.
Horizontal alignment options are: center, centerSelection, distributed, fill, justify, left and right.
See HorizontalAlignment for
full description of horizontal alignment options.
Vertical alignment options are: bottom, center, distributed, justified and top.
See VerticalAlignment for
full description of vertical alignment options.
sheet('Sample') {
row {
cell {
value 'Top Left'
style {
align top left
}
width 20
height 50
}
cell {
value 'Top Center'
style {
align top center
}
width 20
}
cell {
value 'Top Right'
style {
align top right
}
width 20
}
}
// rest skipped
}Result
Fills
You can set the background color or combination of foreground color, background color and fill to customize cells'
appearance. Color can be set as hexadecimal string starting with # or you can use one of predefined colors
which are exactly the same as HTML predefined colors.
cell {
style {
background '#FF8C00' // darkOrange
foreground brown
fill square
}
}Available predefined colors
Available fill values
Borders
You can define a color and style of the cell border. To address which color to change, use top, bottom, left
and/or right keywords when calling the border method. See Available predefined colors.
Colors can be defined as hexadecimal string as well.
cell {
style {
border top, bottom, {
style solid
color gray
}
}
}Available border styles
Fonts
You can customize the font size and color of the text in the cell. You also can make it bold, italic, underline or
strikeout. See Available predefined colors.
row {
cell {
width auto
value 'Bold Red 22'
style {
font {
bold
color red
size 22
}
}
}
cell {
width auto
value 'Underline'
style {
font {
underline
}
}
}
cell {
width auto
value 'Italic'
style {
font {
italic
}
}
}
cell {
width auto
value 'Strikeout'
style {
font {
strikeout
}
}
}
}Result
Data formats
You can assing a data format using the format method. Detailed guide how the format works can be found in
How to control and understand settings in the Excel for Format Cells
dialog box. Apache POI Builtin Formats
are great way how to find some of the most common formats.
Following example will print current date as e.g. 31.12.15.
cell {
style {
value new Date()
format 'dd/mm/yy'
}
}Indentations
You can set the indentation in number of characters from the beginning of the cell.
sheet('Sample') {
7.times { int i ->
row {
cell {
value 'x'
style {
indent i
}
}
}
}
}Result
Rotation
You can rotate the text in the cell using the rotation method. It accepts number from 0 to 180.
Numbers lower from 1 to 90 will produces text going uphill and from 91 to 180 text going downhill
sheet('Sample') {
row {
cell {
height 150
width 20
value 'From bottom to top (90)'
style { rotation 90 }
}
cell {
width 20
value 'From bottom to top (45)'
style { rotation 45 }
}
cell {
width 20
value 'Normal (0)'
}
cell {
width 20
value 'From top to bottom (135)'
style { rotation 135 }
}
cell {
width 20
value 'From top to bottom (180)'
style { rotation 180 }
}
}
}Result
Text wrap
By default the text is not wrapped. This mean that the new lines characters present in the string are ignored.
You can update this by writing wrap text line in the style definition closure.
sheet('Sample') {
row {
cell {
height 100
width auto
value '''
This text will be wrapped.
To the next line.
And another as well.
'''
style {
wrap text
}
}
cell {
width auto
value '''
This text will not be wrapped.
Not even to to the next line.
Even another one.
'''
}
}
}Result