ribbon

Spreadsheet builder provides convenient way how to read and create MS Excel OfficeOpenXML Documents (XSLX) focus not only on content side but also on easy styling.

Installation

Spreadsheet Builder API is available on Maven central and JCenter.

Gradle
compile 'org.modelcatalogue:spreadsheet-builder-poi:1.0.RC2'
Maven
<dependency>
  <groupId>org.modelcatalogue</groupId>
  <artifactId>spreadsheet-builder-poi</artifactId>
  <version>1.0.RC2</version>
  <type>pom</type>
</dependency>

Writing Spreadsheets

Following example creates the basic spreadsheet with two rows and three columns.

Groovy
import org.modelcatalogue.spreadsheet.builder.poi.PoiSpreadsheetBuilder

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

PoiSpreadsheetBuilder.create(file).build {                                              (1)
    sheet('Sample') {                                                                   (2)
        row {                                                                           (3)
            cell 'A'                                                                    (4)
            cell 'B'
            cell 'C'
        }
        row {
            cell 1
            cell 2
            cell 3
        }
    }
}
1 Build new spreadsheet based on Apache POI which will be written into file
2 Create new sheet with the name Sample
3 Create new row
4 Create new cell
Java
import org.modelcatalogue.spreadsheet.builder.poi.PoiSpreadsheetBuilder;

import static org.modelcatalogue.spreadsheet.api.Keywords.*;

public class SpreadsheetWriter {

    public static void main(String... args) {
        File file = new File("spreadsheet.xlsx");

        PoiSpreadsheetBuilder.create(file).build(w -> {                                 (1)
            w.sheet("Sample", s -> {                                                    (2)
                s.row(r -> {                                                            (3)
                    r.cell("A");                                                        (4)
                    r.cell("B");
                    r.cell("C");
                });
                s.row(r -> {
                    r.cell(1);
                    r.cell(2);
                    r.cell(3);
                });
            });
        });
    }

}
1 Build new spreadsheet based on Apache POI which will be written into file
2 Create new sheet with the name Sample
3 Create new row
4 Create new cell

Result

basic sample

You can pass an existing spreadsheet file as a template as the very first argument of the build method.

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.

Groovy
sheet('Sample') {
    row 5, { cell 'Line 5' }
    row()
    row { cell 'Line 7' }
}
Java
w.sheet("Sample", s -> {
    s.row(5, r -> r.cell("Line 5"));
    s.row();
    s.row(r -> r.cell("Line 7"));
}

Result

specific row

Outlines

Rows and columns can be optionally grouped into expanded or collapsed groups for better readability.

Groovy
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' }
    }
}
Java
w.sheet("Sample", s -> {
    s.row(r -> {
        r.cell("Heading 1");
        r.group(g -> {
            g.cell("Heading 2");
            g.cell("Heading 3");
            g.cell("Heading 4");

            g.collapse(cps -> {
                cps.cell("Heading 5");
                cps.cell("Heading 6");
            });
            g.cell("Heading 7");
        });
    });

    // expanded group
    s.group(g -> {
        g.row(r -> r.cell("Heading 2"));
        g.row(r -> r.cell("Heading 3"));
        g.row(r -> r.cell("Heading 4"));

        // collapsed group
        g.collapse(cps -> {
            cps.row(r -> r.cell("Heading 5"));
            cps.row(r -> r.cell("Heading 6"));
        });

        g.row(r -> r.cell("Heading 7"));
    }
});

Result

outline for rows

Freezing columns and rows

You can freeze some rows and or columns to prevent scrolling them.

Groovy
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)
            }
        }
    }
}
Java
w.sheet("Sample", s -> {
    s.freeze(1, 1);

    s.row(r -> {
        r.cell("-");
        r.cell("A");
        r.cell("B");
        r.cell("C");
        r.cell("D");
        r.cell("E");
        r.cell("F");
    });

    for (int i = 0; i < 10 ; i++) {
        s.row(r -> {
            r.cell("Row " + (i + 1));
            for (int j = 0; j < 6; j++) {
                r.cell(10 * i + j);
            }
        });
    });
});

Result

frozen cells

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.

Groovy
sheet('Sample') {
    lock it // 'it' represents the first argument of the closure
    row {
        cell {
            value 'Locked'
        }
    }

}
sheet('Password Protected') {
    password 'p4$$w0rd'
    row {
        cell 'Protected by Password'
    }
}
Java
w.sheet("Sample", s -> {
    s.lock();
    s.row(r -> {
        r.cell(c -> {
            c.value("Locked");
        });
    });
});

w.sheet("Password Protected", s -> {
    s.password("p4$$w0rd");
    s.row(r -> {
        r.cell("Protected by Password");
    });
});

Result

locked

Visibility

You can hide sheet you don’t want to display to the user. If you use hideCompletely the user will not be able to unhide the sheet from the Excel UI.

Groovy
sheet('Hidden') {
    hide it
}
sheet('Very hidden') {
    hideCompletely it
}
Java
w.sheet("Hidden", s ->
    s.hide()
);
w.sheet("Very hidden", s ->
    s.hideCompletely()
);

Automatic Filters

You can create an automatic filter for all data in given sheet.

Groovy
sheet('Filtered') {
    filter auto
    row {
        cell 'Name'
        cell 'Profession'
    }
    row {
        cell 'Donald'
        cell 'Sailor'
    }
    row {
        cell 'Bob'
        cell 'Builder'
    }
}
Java
w.sheet("Filtered", s -> {
    s.filter(auto);
    s.row(r -> {
        r.cell("Name");
        r.cell("Profession");
    });
    s.row(r -> {
        r.cell("Donald");
        r.cell("Sailor");
    });
    s.row(r -> {
        r.cell("Bob");
        r.cell("Builder");
    });
});

Result

filtered

Page Settings

You can define the paper size, orientation and on how many pages should the sheet be printed within the page closure of the sheet.

Groovy
sheet('Custom Page') {
    page {
        paper a5
        fit width to 1
        orientation landscape
    }
    row {
        cell 'A5 Landcapse'
    }
}
Java
w.sheet("Custom Page", s -> {
    s.page(p -> {
        p.paper(A5);
        p.fit(width).to(1);
        p.orientation(landscape);
    });
    s.row(r -> {
        r.cell("A5 Landcapse");
    });
});

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 A1 references in formulas with name references. Use #{Name} syntax inside the formula definition if you don’t want to compute the A1 references yourself e.g. SUM(#{Cell1},#{Cell2}). The name can be assigned later.

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.

Groovy
sheet('Sample') {
    row {
        cell 'First'
        cell()
        cell 'Third'
        cell(5) {
 value 'Fifth' }
        cell('G') { formula 'YEAR(TODAY())' }
    }
}
Java
w.sheet("Sample", s -> {
    s.row(r -> {
        r.cell("First");
        r.cell();
        r.cell("Third");
        r.cell(5, c -> c.value("Fifth"));
        r.cell("G", c -> c.formula("YEAR(TODAY())"));
    });
});

Result

basic cells

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.

Date and Calendar values needs to have data format assigned otherwise they will appear in the generated spreadsheet as plain numbers.

Merging cells

You can set rowspan and/or colspan of any cell to merge multiple cells together.

Groovy
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' }
    }
}
Java
w.sheet("Sample", s -> {
    s.row(r -> {
        r.cell(c -> {
            c.value("Columns");
            c.colspan(2);
        });
    });
    s.row(r -> {
        r.cell(c -> {
            c.value("Rows");
            c.rowspan(3);
        });
        r.cell("Value 1");
    });
    s.row(r -> {
        r.cell("B", c -> c.value("Value 2"));
    }
    s.row(r -> {
        r.cell("B", c -> c.value("Value 3"));
    });
});

Result

spans

Images

You can insert an image calling one of png, jpeg, emf, wmf, pict, dib method inside the cell definition.

Groovy
cell ('C') {
    png image from 'https://goo.gl/UcL1wy'
}
Java
r.cell("C", c -> {
    c.png(image).from("https://goo.gl/UcL1wy");
});

Result

image

The source of the image can be String which either translates to URL if it starts with https:// or http:// or a file path otherwise. For advanced usage it can be also byte array or any InputStream.

Resizing images with API is not reliable so you need to resize your image properly before inserting into the spreadsheet.

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.

Groovy
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"
            }
        }
    }
}
Java
w.sheet("Sample", s -> {
    s.row(r -> {
        r.cell(c -> {
            c.value("Commented 1");
            c.comment("This is a comment 1");
        });
    });
    s.row(r -> {
        r.cell(c -> {
            c.value("Commented 2");
            c.comment(cmt -> {
                cmt.author("musketyr");
                cmt.text("This is a comment 2");
            });
        });
    });
});

Result

comments

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 use names to create link to the particular cell.

Groovy
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})'
        }
    }
}
Java
w.sheet("Sample", s -> {
    s.row(r -> {
        r.cell("A");
        r.cell("B");
        r.cell("A + B");
    });
    s.row(r -> {
        r.cell(c -> {
            c.value(10);
            c.name("CellA");
        });
        r.cell(c -> {
            c.value(20);
            c.name("CellB");
        });
        r.cell(c -> {
            c.formula("SUM(#{CellA},#{CellB})");
        });
    });
});

Result

names

You can create for types of links

  • link to name 'A Name' will create link other parts of the document using cell name A Name

  • link to url 'http://www.example.com' will create link to open URL http://www.example.com

  • link to email 'musketyr@example.com' will create link to send mail to musketyr@example.com

  • link to file 'README.txt' will create link to open file README.txt

Links does not appear blue and underline by default You need to style them appropriately yourself.

Groovy
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'
        }
    }
}
Java
w.sheet("Sample", s -> {
    s.row(r -> {
        r.cell(c -> {
            c.value("Hello World");
            c.name("Salutation");
        });
    });
});

w.sheet("Links", s -> {
    s.row(r -> {
        r.cell(c -> {
            c.value("Document");
            c.link(to).name("Salutation");
            c.width(auto);
        });
        r.cell(c -> {
            c.value("File");
            c.link(to).file("text.txt");
        });
        r.cell(c -> {
            c.value("URL");
            c.link(to).url("https://www.example.com");
        });
        r.cell(c -> {
            c.value("Mail (plain)");
            c.link(to).email("musketyr@example.com");
        });
        r.cell(c -> {
            c.value("Mail (with subject)");
            Map<String, String> email = new LinkedHashMap<>();
            email.put("subject", "Testing Excel Builder");
            email.put("cc", "tester@example.com");
            email.put("body", "It is really great tools");
            c.link(to).email(email, "vladimir@orany.cz");
        });
    });
});

You can add arbitrary attributes to the email link such as cc, body or subject.

Dimensions

You can set the width of the cells as the multiple of standard character width, centimeters or inches. 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, centimeters or inches. The height applies to whole row.

Groovy
sheet('Dimensions') {
    row {
        cell {
            value 'cm'
            height 1 cm
            width 1 cm
        }
    }

    row {
        cell('B') {
            value 'inches'
            width 1 inch
            height 1 inch
        }
    }
    row {
        cell('C') {
            value 'points'
            width 10
            height 50
        }
    }
}
Java
w.sheet("Dimensions", s -> {
    s.row(r -> {
        r.cell(c -> {
            c.value("cm");
            c.height(1).cm();
            c.width(1).cm();
        });
    });

    s.row(r -> {
        r.cell("B", c -> {
            c.value("inches"
            c.width(1).inch();
            c.height(1).inch();
        });
    });
    s.row(r -> {
        r.cell("C", c -> {
            c.value("points");
            c.width(10);
            c.height(50);
        });
    });
});

Result

dimensions

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.

Groovy
style ('headers') {
    border(bottom) {
        style thick
        color black
    }
    font {
        make 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 {
                    make bold
                }
            }
        }
        cell "Helps building nice spreadsheet reports"
    }
}
Java
w.style ("headers", st -> {
    st.border(bottom, b -> {
        b.style(thick);
        b.color(black);
    });
    st.font(f -> {
        f.make(bold);
    });
    st.background(whiteSmoke);
});

w.sheet("Sample", s -> {
    s.row(r -> {
        r.style("headers");
        r.cell(c -> {
            c.value("No.");
            c.width(5);
        });
        r.cell(c -> {
            c.value("Name");
            c.width(30);
        });
        r.cell(c -> {
            c.value("Description");
            c.width(auto);
        });
    });
    s.row(r -> {
        r.cell(c -> 1
        r.cell(c -> {
            c.value("Groovy Spreadsheet Builder");
            c.style(st -> {
                st.font(f -> {
                    f.make(bold);
                });
            });
        });
        r.cell("Helps building nice spreadsheet reports");
    });
});

Result

styles

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.

Groovy
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
 }
Java
w.sheet("Sample", s -> {
    s.row(r -> {
        r.cell(c -> {
            c.value("Top Left");
            c.style(st -> {
                st.align(top, left);
            });
            c.width(20);
            c.height(50);
        });
        r.cell(c -> {
            c.value("Top Center");
            c.style(st -> {
                st.align(top, center);
            });
            c.width(20);
        });
        r.cell(c -> {
            c.value("Top Right");
            c.style(st ->{
                st.align(top, right);
            });
            c.width(20);
        });
    });
    // rest skipped
 });

Result

alignment

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.

Groovy
cell {
    style {
        background '#FF8C00' // darkOrange
        foreground brown
        fill square
    }
}
Java
r.cell(c -> {
    c.style(st -> {
        st.background("#FF8C00"); // darkOrange
        st.foreground(brown);
        st.fill(square);
    });
});

Available predefined colors

colors

Available fill values

fills

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.

Groovy
cell {
    style {
        border top, bottom, {
            style solid
            color gray
        }
    }
}
Java
r.cell(c -> {
    c.style(st -> {
        st.border(top, bottom, b -> {
            b.style(solid);
            b.color(gray);
        });
    });
});

Available border styles

borders

Fonts

You can customize the font size, name and color of the text in the cell. You also can make it bold, italic, underline or strikeout. See Available predefined colors.

Groovy
row {
    cell {
        width auto
        value 'Bold Red 22'
        style {
            font {
                make bold
                color red
                size 22
            }
        }
    }
    cell {
        width auto
        value 'Underline Courier New'
        style {
            font {
                make underline
                name 'Courier New'
            }
        }
    }
    cell {
        width auto
        value 'Italic'
        style {
            font {
                make italic
            }
        }
    }
    cell {
        width auto
        value 'Strikeout'
        style {
            font {
                make strikeout
            }
        }
    }
}
Java
s.row(r -> {
    cell(c -> {
        c.width(auto);
        c.value("Bold Red 22");
        c.style(st -> {
            st.font(f -> {
                f.make(bold);
                f.color(red);
                f.size(22);
            });
        });
    });
    cell(c -> {
        c.width(auto);
        c.value("Underline Courier New");
        c.style(st -> {
            st.font(f -> {
                f.make(underline);
                f.name("Courier New");
            });
        });
    });
    cell(c -> {
        c.width(auto);
        c.value("Italic");
        c.style(st -> {
            st.font(f -> {
                f.make(italic);
            });
        });
    });
    cell(c -> {
        c.width(auto);
        c.value("Strikeout");
        c.style(st -> {
            st.font(f -> {
                f.make(strikeout);
            });
        });
    });
});

Result

fonts

Rich Texts

Apart from setting the font for the whole cell you can create a rich text cell content as well. Instead of value use multiple calls to text method which takes optional closure to define the font for the current text run. The font definition is the same as above.

Groovy
cell {
    text 'Little'
    text ' '
    text 'Red', {
        color red
        size 22
    }
    text ' '
    text 'Riding', {
        make italic
        size 18
    }
    text ' '
    text 'Hood', {
        make bold
        size 22
    }

}
Java
r.cell(c -> {
    c.text("Little");
    c.text(" ");
    c.text("Red", f -> {
        f.color(red);
        f.size(22);
    });
    c.text(" ");
    c.text("Riding", f -> {
        f.make(italic);
        f.size(18);
    });
    c.text(" ");;
    c.text("Hood", f -> {
        f.make(bold);
        f.size(22);
    });
});

Result

rich text

Some older versions that 3.13 of Apache POI does not handle rich texts well. Please, make sure you are using at least version 3.13.

Data formats

You can assign 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.

Groovy
cell {
    value new Date()
    style {
        format 'dd/mm/yy'
    }
}
Java
r.cell(c -> {
    c.value(new Date());
    c.style(st -> {
        st.format("dd/mm/yy");
    });
});

Each of the formats can contain up to four parts separated by semicolon: <POSITIVE>;<NEGATIVE>;<ZERO>;<TEXT>. The excerpt from the official documentation follows:

Format Symbol      Description/result
   ------------------------------------------------------------------------

   0                  Digit placeholder. For example, if you type 8.9 and
                      you want it to display as 8.90, then use the
                      format #.00

   #                  Digit placeholder. Follows the same rules as the 0
                      symbol except Excel does not display extra zeros
                      when the number you type has fewer digits on either
                      side of the decimal than there are # symbols in the
                      format. For example, if the custom format is #.## and
                      you type 8.9 in the cell, the number 8.9 is
                      displayed.

   ?                  Digit placeholder. Follows the same rules as the 0
                      symbol except Excel places a space for insignificant
                      zeros on either side of the decimal point so that
                      decimal points are aligned in the column. For
                      example, the custom format 0.0? aligns the decimal
                      points for the numbers 8.9 and 88.99 in a column.

   . (period)         Decimal point.

   %                  Percentage. If you enter a number between 0 and 1,
                      and you use the custom format 0%, Excel multiplies
                      the number by 100 and adds the % symbol in the cell.

   , (comma)          Thousands separator. Excel separates thousands by
                      commas if the format contains a comma surrounded by
                      '#'s or '0's. A comma following a placeholder
                      scales the number by a thousand. For example, if the
                      format is #.0,, and you type 12,200,000 in the cell,
                      the number 12.2 is displayed.

   E- E+ e- e+        Scientific format. Excel displays a number to the
                      right of the "E" symbol that corresponds to the
                      number of places the decimal point was moved. For
                      example, if the format is 0.00E+00 and you type
                      12,200,000 in the cell, the number 1.22E+07 is
                      displayed. If you change the number format to #0.0E+0
                      the number 12.2E+6 is displayed.

   $-+/():space       Displays the symbol. If you want to display a
                      character that is different than one of these
                      symbols, precede the character with a backslash (\)
                      or enclose the character in quotation marks (" ").
                      For example, if the number format is (000) and you
                      type 12 in the cell, the number (012) is displayed.

   \                  Display the next character in the format. Excel does
                      not display the backslash. For example, if the number
                      format is 0\! and you type 3 in the cell, the value
                      3! is displayed.

   *                  Repeat the next character in the format enough times
                      to fill the column to its current width. You cannot
                      have more than one asterisk in one section of the
                      format. For example, if the number format is 0*x and
                      you type 3 in the cell, the value 3xxxxxx is
                      displayed. Note, the number of "x" characters
                      displayed in the cell vary based on the width of the
                      column.

   _ (underline)      Skip the width of the next character. This is useful
                      for lining up negative and positive values in
                      different cells of the same column. For example, the
                      number format _(0.0_);(0.0) align the numbers
                      2.3 and -4.5 in the column even though the negative
                      number has parentheses around it.

   "text"             Display whatever text is inside the quotation marks.
                      For example, the format 0.00 "dollars" displays
                      "1.23 dollars" (without quotation marks) when you
                      type 1.23 into the cell.

   @                  Text placeholder. If there is text typed in the
                      cell, the text from the cell is placed in the format
                      where the @ symbol appears. For example, if the
                      number format is "Bob "@" Smith" (including
                      quotation marks) and you type "John" (without
                      quotation marks) in the cell, the value
                      "Bob John Smith" (without quotation marks) is
                      displayed.

   DATE FORMATS

   m                  Display the month as a number without a leading zero.

   mm                 Display the month as a number with a leading zero
                      when appropriate.

   mmm                Display the month as an abbreviation (Jan-Dec).

   mmmm               Display the month as a full name (January-December).

   d                  Display the day as a number without a leading zero.

   dd                 Display the day as a number with a leading zero
                      when appropriate.

   ddd                Display the day as an abbreviation (Sun-Sat).

   dddd               Display the day as a full name (Sunday-Saturday).

   yy                 Display the year as a two-digit number.

   yyyy               Display the year as a four-digit number.

   TIME FORMATS

   h                  Display the hour as a number without a leading zero.

   [h]                Elapsed time, in hours. If you are working with a
                      formula that returns a time where the number of hours
                      exceeds 24, use a number format similar to
                      [h]:mm:ss.

   hh                 Display the hour as a number with a leading zero when
                      appropriate. If the format contains AM or PM, then
                      the hour is based on the 12-hour clock. Otherwise,
                      the hour is based on the 24-hour clock.

   m                  Display the minute as a number without a leading
                      zero.

   [m]                Elapsed time, in minutes. If you are working with a
                      formula that returns a time where the number of
                      minutes exceeds 60, use a number format similar to
                      [mm]:ss.

   mm                 Display the minute as a number with a leading zero
                      when appropriate. The m or mm must appear immediately
                      after the h or hh symbol, or Excel displays the
                      month rather than the minute.

   s                  Display the second as a number without a leading
                      zero.

   [s]                Elapsed time, in seconds. If you are working with a
                      formula that returns a time where the number of
                      seconds exceeds 60, use a number format similar to
                      [ss].

   ss                 Display the second as a number with a leading zero
                      when appropriate.

                      NOTE: If you want to display fractions of a second,
                      use a number format similar to h:mm:ss.00.

   AM/PM              Display the hour using a 12-hour clock. Excel
   am/pm              displays AM, am, A, or a for times from midnight
   A/P                until noon, and PM, pm, P, or p for times from noon
   a/p                until midnight.

Indentations

You can set the indentation in number of characters from the beginning of the cell.

Groovy
sheet('Sample') {
    7.times { int i ->
        row {
            cell {
                value 'x'
                style {
                    indent i
                }
            }
        }
    }
}
Java
w.sheet("Sample", s -> {
    for (int i = 0; i < 7; i++) {
        s.row(r -> {
            r.cell(c -> {
                c.value("x");
                c.style(st -> {
                    st.indent(i);
                });
            });
        });
    });
});

Result

indent

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

Groovy
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 }
        }
    }
}
Java
w.sheet("Sample", s -> {
    s.row(r -> {
        r.cell(c -> {
            c.height(150);
            c.width(20);
            c.value("From bottom to top (90)");
            c.style(st -> st.rotation(90));
        });

        r.cell(c -> {
            c.width(20);
            c.value("From bottom to top (45)");
            c.style(st -> st.rotation(45));
        });

        r.cell(c -> {
            c.width(20);
            c.value("Normal (0)");
        });

        r.cell(c -> {
            c.width(20);
            c.value("From top to bottom (135)");
            c.style(st -> st.rotation(135));
        });

        r.cell(c -> {
            c.width(20);
            c.value("From top to bottom (180)");
            c.style(st -> st.rotation(180));
        });
    });
});

Result

rotation

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.

Groovy
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.
            '''
        }
    }
}
Java
w.sheet("Sample", s-> {
    s.row(r -> {
        r.cell(c -> {
            c.height(100);
            c.width(auto);
            c.value("\nThis text will be wrapped.\nTo the next line.\n\nAnd another as well.");
            c.style(st -> {
                st.wrap(text);
            });
        });
        r.cell(c -> {
            c.width(auto);
            c.value("\nThis text will not be wrapped.\nNot even to to the next line.\n\nEven another one.");
        });
    });
}

Result

wrap

Reusing Styles

You can externalize your styles configuration into class implementing org.modelcatalogue.spreadsheet.builder.api.Stylesheet interface to maximize code reuse or report customization.

Groovy
class MyStyles implements Stylesheet {

    void declareStyles(CanDefineStyle stylable) {
        stylable.style('h1') {
            foreground whiteSmoke
            fill solidForeground
            font {
                size 22
            }
        }
        stylable.style('h2') {
            base 'h1'
            font {
                size 16
            }
        }
        stylable.style('red') {
            font {
                color red
            }
        }
    }
}
// usage
builder.build(out) {
    apply MyStyles // or apply(new MyStyles())
    sheet('Sample') {
        row {
            cell {
                value 'Hello'
                style 'h1'
            }
            cell {
                value 'World'
                style 'h2'
            }
            cell {
                value '!!!'
                styles 'h2', 'red'
            }
        }
    }
}
Java
// stylesheet class definition
public class MyStyles implements Stylesheet {

    public void declareStyles(CanDefineStyle stylable) {
        stylable.style("h1", st -> {
            st.foreground(whiteSmoke);
            st.fill(solidForeground);
            st.font(f -> {
                f.size(22);
            });
        });
        stylable.style("h2") {
            st.base( "h");
            st.font(f -> {
                f.size(16);
            });
        });
        stylable.style("red") {
            st.font(f -> {
                f.color(red);
            });
        });
    }
}
// usage
builder.build(out, w -> {
    w.apply(MyStyles.class); // or w.apply(new MyStyles());
    w.sheet("Sample", s -> {
        s.row(r -> {
            r.cell(c -> {
                c.value("Hello");
                c.style("h1");
            });
            r.cell(c -> {
                c.value("World");
                c.style("h2");
            });
            r.cell(c -> {
                c.value("!!!");
                c.styles("h2", "red");
            });
        });
    });
});

Result

stylesheets

Reading and Querying spreadsheets

You can query the spreadsheet with similar syntax as you build it.

Groovy
import org.modelcatalogue.spreadsheet.query.api.SpreadsheetCriteria
import org.modelcatalogue.spreadsheet.query.api.SpreadsheetCriteriaResult
import org.modelcatalogue.spreadsheet.query.poi.PoiSpreadsheetCriteria

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

SpreadsheetCriteria query = PoiSpreadsheetCriteria.FACTORY.forFile(file)                (1)

SpreadsheetCriteriaResult result = query.query {                                        (2)
    sheet {                                                                             (3)
        row {                                                                           (4)
            cell {
                value 'B'                                                               (5)
            }
        }
    }
}

assert result.cells.size() == 1
assert result.cells.first().value == 'B'
1 Create new spreadsheet query for given file
2 Start a query
3 Query any sheet
4 Query any row
5 Query every cell containing value 'B'
Java
import org.modelcatalogue.spreadsheet.query.api.SpreadsheetCriteria;
import org.modelcatalogue.spreadsheet.query.api.SpreadsheetCriteriaResult;
import org.modelcatalogue.spreadsheet.query.poi.PoiSpreadsheetCriteria;

import java.io.File;

import static org.junit.Assert.*;

public class CriteriaDemo {

    public static void main(String[] args) throws Exception {
        File file = new File("spreadsheet.xlsx");

        SpreadsheetCriteria query = PoiSpreadsheetCriteria.FACTORY.forFile(file);       (1)

        SpreadsheetCriteriaResult result = query.query(w -> {                           (2)
            w.sheet(s -> {                                                              (3)
                s.row(r -> {                                                            (4)
                    r.cell(c -> {
                        c.value("B");                                                   (5)
                    });
                });
            });
        });

        assertEquals(1, result.getCells().size());
        assertEquals("B", result.getCell().getValue());
    }

}
1 Create new spreadsheet query for given file
2 Start a query
3 Query any sheet
4 Query any row
5 Query every cell containing value 'B'

The query method returns collections of every cell found matching given criteria. There is also shortcut methods all(), find() and exists() to return every cell, find single cell or just test for cell presence.

Data Model

The Cell objects provides getters counterparts to methods supported by query such as getName(). You can also easily navigate into all eight directions with methods like getLeft(), getAboveLeft() etc. or access the whole row with getRow(). Rows can be also easily navigated with getAbove() and getbellow methods.

Groovy
Cell a = query.find {
    sheet {
        row {
            cell {
                value 'A'
            }
        }
    }
}

assert a.right.value == 'B'
assert a.row.bellow.cells.first().value == a.bellow.value
Java
Cell a = query.find(w -> {
    w.sheet(s -> {
        s.row(r -> {
            r.cell(c -> {
                c.value("A");
            });
        });
    });
});

assert a.right.value == 'B'
assert a.row.bellow.cells.first().value == a.bellow.value

Sheets and Rows

You can query only specific sheet or row

Groovy
Sheet content = query.query {
    sheet('Content')
}.sheet
Java
Sheet content = query.query(w -> {
    w.sheet("Content");
}).getSheet();

Result: Sheet Content if present.

You can query by sheet visibility or lock state:

Groovy
Collection<Sheet> a5s = query.query {
    sheet {
        state hidden
    }
}.sheets
Java
Collection<Sheet> a5s = query.query(w -> {
    w.sheet(s -> {
        s.state(hidden);
    });
}).getSheets();

Result: Every hidden sheet in the workbook.

You can query sheet by its page setting:

Groovy
Collection<Sheet> a5s = query.query {
    sheet {
        page {
            paper a5
            orientation landscape
        }
    }
}.sheets
Java
Collection<Sheet> a5s = query.query(w -> {
    w.sheet(s -> {
        s.page(p -> {
            p.paper(A5);
            p.orientation(landscape);
        });
    });
}).getSheets();

Result: Every sheet having the page settings set to A5 paper and with landscape orientation.

You can use predicates in many places in the Query API if the simple condition does not met your needs. You can address rows by it number. The number starts with one so it corresponds the one displayed in Excel.

Groovy
Collection<Row> rows = query.query {
    sheet {
        having {
            it.name.startsWith('Con')
        }
        row(1)
    }
}.rows
Java
Collection<Row> rows = query.query(w -> {
    w.sheet(s -> {
        s.having(it -> {
            it.getName().startsWith("Con");
        });
        s.row(1);
    });
}).getRows();

Result: Every first row of every sheet which name starts with Con.

If you repeat the call to either sheet or row query method you it’s understood as and condition but you can also create or condition by wrapping the calls with or method.

Groovy
query.query {
    or {
        sheet {
            having {
                it.name.startsWith('Con')
            }
            row(1)
        }
        sheet {
            having {
                it.name.endsWith('Air')
            }
            row(2)
        }
    }
}
Java
query.query(w -> {
    or(disj -> {
        disj.sheet(s -> {
            s.having(it -> {
                it.getName().startsWith("Con");
            });
            s.row(1);
        });
        disj.sheet(s -> {
            s.having(it -> {
                it.getName().endsWith("Air");
            });
            s.row(2);
        });
    });
});

Result: Every cell from every first row of every sheet which name starts with Con and every cell from every second row of sheet which name ends with Air. If the one sheet fits both criteria the cells are not returned twice.

Groovy
query.query {
    sheet {
        row(1,10)
    }
}
Java
query.query(w -> {
    w.sheet(s -> {
        s.row(1,10);
    });
});

Result: Every cell from first ten rows of every sheet in the spreadsheet.

If you have a row containing headers to the following rows you can convert any of following rows to data row which provide convenient way how to retrieve cells using the label specified in the header

Groovy
Row manyRowsHeader = matcher.query {
    sheet('many rows') {
        row(1)
    }
}.row

Row manyRowsDataRow= matcher.query {
    sheet('many rows') {
        row(2)
    }
}.row

DataRow dataRow = DataRow.create(manyRowsDataRow, manyRowsHeader)
assert dataRow['One']
assert dataRow['One'].value == 1
Java
Row manyRowsHeader = matcher.query(w -> {
    w.sheet("many rows", s -> {
        s.row(1);
    });
}).getRow();

Row manyRowsDataRow= matcher.query(w -> {
    w.sheet("many rows", s -> {
        s.row(2);
    });
}).getRow();

DataRow dataRow = DataRow.create(manyRowsDataRow, manyRowsHeader);
assertNotNull(dataRow.get("One"));
assertEquals(1, dataRow.get("One"));

Cells

You can easily specify which cells you want to return from the query. For example by specifying desired value. You can also query for more specific type with date, number, string, bool methods.

Groovy
query.query {
    sheet {
        row {
            cell {
                value 'B'
            }
        }
    }
}
Java
query.query(w -> {
    w.sheet(s -> {
        s.row(r -> {
            r.cell(c -> {
                c.value("B");
            });
        });
    });
});

Result: Every cell from any sheet and row with value 'B'

You can only return cells from specific column or column range specified by both number or string.

Groovy
query.query {
    sheet {
        row {
            cell('B')
        }
    }
}
Java
query.query(w -> {
    w.sheet(s -> {
        s.row(r -> {
            r.cell("B");
        });
    });
});

Result: Every cell from column 'B' of any sheet and row

Merged Cells

You can query for merged cells only.

Groovy
query.query {
    sheet {
        row {
            cell {
                colspan 5
                rowspan 2
            }
        }
    }
}
Java
query.query(w -> {
    w.sheet(s -> {
        s.row(r -> {
            r.cell(c -> {
                c.colspan(5);
                c.rowspan(2);
            });
        });
    });
});

Result: Every cell in the spreadsheet which are merged over five cells horizontally and two cells vertically.

Comments

You can query for cells with given comment.

Groovy
query.query {
    sheet {
        row {
            cell {
                comment "This is interesting"
            }
        }
    }
}
Java
query.query(w -> {
    w.sheet(s -> {
        s.row(r -> {
            r.cell(c -> {
                c.comment("This is interesting");
            });
        });
    });
});

Result: Every cell in the spreadsheet which has a comment "This is interesting".

Again you can use predicate closure to be more specific.

Groovy
query.query {
    sheet {
        row {
            cell {
                comment { it.author == 'Steve' }
            }
        }
    }
}
Java
query.query(w -> {
    w.sheet(s -> {
        s.row(r -> {
            r.cell(c -> {
                c.comment(it -> "Steve".equals(it.author));
            });
        });
    });
});

Names

You can query for specific named cell.

Groovy
query.find {
    sheet {
        row {
            cell {
                name "THE_CELL"
            }
        }
    }
}
Java
query.find(w ->{
    w.sheet(s -> {
        s.row(r -> {
            r.cell(c -> {
                c.name("THE_CELL");
            });
        });
    });
});

Result: The cell with the named reference 'THE_CELL'.

Be careful as the names can be different than the one you’ve used in the builder as some implementation has certain naming limitations.

Styles and Fonts

You can query cells by style parameters such as foreground color or font height. All the options from the style builder is currently supported except text wrapping, rich texts and alignment options. All font options are supported. See the guide above for reference.

Groovy
query.find {
    sheet {
        row {
            cell {
                style {
                    foreground red
                    font {
                        style bold
                        color white
                    }
                }
            }
        }
    }
}
Java
query.find(w -> {
    w.sheet(s -> {
        s.row(r -> {
            r.cell(c -> {
                c.style(st -> {
                    st.foreground(red);
                    st.font(f -> {
                        f.style(bold);
                        f.color(white);
                    });
                });
            });
        });
    });
});

Result: Every cell from the spreadsheet which has red foreground and white font color with bold style.

The color you usually see in the cell is foreground not a background as you may think!

Changelog

1.0

Java-only API which can be used with Java 8.

Builder and criteria definition chaining - methods returning self instead of void.

Added ability to hide sheets #19.

Breaking Changes

PoiSpradsheetBuilder will no longer allow to create cell names which are invalid by Excel. Use PoiCellDefinition#fixName(String) manually to ensure no exception will be thrown.

Colors made constants of Color and generally removed keywords getters from the interfaces but they are still available in Groovy as they are injected through extension class.

Criteria will no longer accepts predicates in the same position as configuration but there is having method inside each criterion where can one write predicate for given surrounding element. Method shortcuts which helped created predicates has been removed.

Some methods has been renamed so it make sense to use them in both builder and query

  • Font style is now set using method style instead of make

  • To lock the sheet use state locked instead of lock it

Builder and query API is completely decoupled from the way how the spreadsheet is persisted therefore SpreadsheetDefinition and SpreadsheetCriteriaFactory interfaces has been removed. This opens possibilities to create file-free implementation such as Google Sheets in future.

0.4.0

Ability to also retrieve sheet or rows from the criteria query.

Breaking Changes

Criteria methods query and all no longer returns collections of cells but object of type SpreadsheetCriteriaResult. This object itself implements at least Iterable<Cell> interface allowing to use the results directly in the for loops and call most of the default groovy method available on collections. Collection<Cell> as well as Collection<Row> and Collection<Sheet> can be accessed using the result object properties cells, rows and sheets.

0.3.7

Simple page settings, ability to set cells' width and height in centimeters or inches and fixed merging row and cell styles.

0.3.6

Minor improvement in combination of the styles.

0.3.5

Added ability to defined named styles for whole row and particular cell as well and fixed problem with merged cells formatting.

0.3.4

Added automatic filtering and allowed casting the api objects to their POI equivalents.

0.3.3

Fixed cells with value 0 rendered as empty cell.

0.3.2

Ability to use existing spreadsheet as a template in builder’s build method.

0.3.0

Breaking Changes

No more single word mutating DSL statements such as locked or bold as they conflicts with newly introduced getters. Keywords which are already getters such as black for color of same name remain unaffected.

sheet {
    lock it // instead of "locked"
}

font {
    make italic          // instead of "italic"
    make bold, underline // you can supply more than one style
}

The package and names for interfaces has been changed. Most of the previous API now resides in org.modelcatalogue.spreadsheet.builder.api package. The name of the interfaces used by builder closures got *Definition suffix (e.g. SheetDefinition) to distinguish them from the ones returned from queries.

The way how the builder is initialized has shifted to Factory patten to distinguish between writing and building

0.2.0

Introduced getters for parent such as workbook for sheet etc.