Skip to content

Tutorial

Jump to bottom
Julien Férard edited this page Sep 4, 2019 · 19 revisions

This tutorial is extracted from https://github.com/jferard/fastods/tree/master/fastods-examples/src/main/java/com/github /jferard/fastods/examples. See the classes in this directory for the full code.

Writing a full documentation would be a considerable work, because every time you change the library, you have to rewrite the doc.

My idea is to provide a set of examples of the features of FastODS.

Before you start, add the following dependency to your POM (Maven users):

com.github.jferard fastods 0.6.2

For other build tools, look at: https://search.maven.org/artifact/com.github.jferard/fastods/0.6.2/jar

Table of Contents

Hello, world!

Let's start with the famous "Hello, World!" example.

As stated in the javadoc, "An OdsFactory is the entry point for creating ODS documents." Every time you want to create an ODS document, you'll start with something like that:

final OdsFactory odsFactory = OdsFactory.create(Logger.getLogger("hello-world"), Locale.US);

Now, you can create an ODS writer. You have the choice: either you give the filename now, or you keep it for the end. Let's create an anonymous writer: the content will live in memory until we save it to a file:

final AnonymousOdsFileWriter writer = odsFactory.createWriter();

The writer owns a document that we need to create a spreadsheet:

final OdsDocument document = writer.document();

Okay, let's go. We create a new table (a new sheet indeed) named "hello-world":

final Table table = document.addTable("hello-world");

We get the first row:

final TableRowImpl row = table.getRow(0);

And the first cell of the first row:

final TableCell cell = row.getOrCreateCell(0);

Note that we could have chained the calls: TableCell cell = document.addTable("hello-world").getRow(0).getOrCreateCell(0)

Finally, we put the famous sentence in this cell A1

cell.setStringValue("Hello, world!");

And save the file.

writer.saveAs(new File("generated_files", "a_hello_world_example.ods"));

With a mvn clean verify at the root of the project, you can check the result in the fastods-examples/generated-files directory.

Accessing Tables, Rows and Cells

Put a text in the A1 cell is interesting but still limited.

We start with the (now) usual boilerplate code to get a document:

final OdsFactory odsFactory = OdsFactory.create(Logger.getLogger("accessing"), Locale.US); final AnonymousOdsFileWriter writer = odsFactory.createWriter(); final OdsDocument document = writer.document();

Direct Access to Cells

You know how to create a table:

final Table table = document.addTable("direct-access");

Get the first row and the first cell of the first row:

TableRowImpl row = table.getRow(0); TableCell cell = row.getOrCreateCell(0);

And set a value.

cell.setStringValue("A1");

Note that the access is a write only access. You can't read the content of a cell. This is by design: FastODS stores the content of the spreadsheet and outputs a file, that's all.

Note that the access to any cell has a cost. If the row doesn't exist yet in the list of rows, FastODS will create all rows between the last row and the actual row.

row = table.getRow(6);

You can have direct access to cells

cell = row.getOrCreateCell(1); cell.setStringValue("B7");

With a TableHelper, direct access might be easier to write...

final TableHelper tableHelper = TableHelper.create();

...but note that the cell is referred by row then column (as matrices in maths). To access the cell "F2", you'll use:

cell = tableHelper.getCell(table, 1, 5); cell.setStringValue("F2");

You can use an address, but there is the cost of parsing that address and a risk of malformed address:

try { cell = tableHelper.getCell(table, "E3"); cell.setStringValue("E3"); } catch (final ParseException e) { /* this won't happen here! */ }

To be (almost) complete, there is another way to write a value to a cell:

final ToCellValueConverter converter = new ObjectToCellValueConverter("USD"); try { tableHelper.setCellValue(table, "D4", converter.from("D4")); } catch (final ParseException e) { /* this won't happen here! */ }

Relative Access

Direct access may be useful, but FastODS was designed for a relative access Create a new table:

final Table table = document.addTable("relative-access");

And then create a "walker" for this table:

final TableCellWalker walker = table.getWalker();

We want ten rows of data

for (int r = 0; r < 10; r++) {

And nine columns for each row:

for (int c = 0; c < 9; c++) {

Add the value to each cell

walker.setStringValue((char) (c + 'A') + String.valueOf(r + 1));

And then push one cell right.

walker.next(); }

Then one cell down.

walker.nextRow();

Remember that walker.nextRow() moves the walker to the first cell of the next row.

}

Deprecated Relative Access

Before version 0.6.2, relative access was different (and slightly inconsistent).

final Table table = document.addTable("relative-access-deprecated");

Our ten rows of data

for (int r = 0; r < 10; r++) {

The Table object had an internal row index (that was updated by the getRow method). A call to the method nextRow to made index advance by one (you had to call nextRow before you start to write data):

final TableRowImpl row = table.nextRow();

And then create a "walker" for this row (there was one walker per row, not per table):

final RowCellWalker walker = row.getWalker(); for (int c = 0; c < 9; c++) {

Add the value to each cell as above

walker.setStringValue((char) (c + 'A') + String.valueOf(r + 1));

And then push one cell right.

walker.next(); } } And save the file.

writer.saveAs(new File("generated_files", "b_accessing_example.ods"));

Note: We will see how to merge cells in the Advanced part of this tutorial.

Setting the cell value

Three elements define the content of a cell:

  • the value and its the type (string, float, boolean, ...)
  • the style (font, background color, border, ...)
  • the format, or data style (number of digits for a float, date format for a date, ...)

So far, we just created cells of type string, with neither style nor data style. Now, we will create cells with a more varied content.

final Table table = document.addTable("types"); final TableCellWalker walker = table.getWalker();

We add a header:

walker.setStringValue("Type"); walker.next(); walker.setStringValue("Example"); walker.nextRow();

The first row contains a boolean:

walker.setStringValue("Boolean"); walker.next(); walker.setBooleanValue(true); walker.nextRow();

The second row contains a currency:

walker.setStringValue("Currency"); walker.next(); walker.setCurrencyValue(10.5, "USD"); walker.nextRow();

The third row contains a date:

walker.setStringValue("Date"); walker.next(); walker.setDateValue(new GregorianCalendar(2014, 9, 17, 9, 0, 0)); walker.nextRow();

The fourth row contains a float:

walker.setStringValue("Float"); walker.next(); walker.setFloatValue(3.14159); walker.nextRow();

The fifth row contains a percentage:

walker.setStringValue("Percentage"); walker.next(); walker.setPercentageValue(0.545); walker.nextRow();

The sixth row contains...

walker.setStringValue("String"); walker.next(); walker.setStringValue("A String"); walker.nextRow();

The seventh row contains a time (that mean a duration):

walker.setStringValue("Time"); walker.next(); walker.setTimeValue(3600); walker.nextRow();

The eighth row contains nothing

walker.setStringValue("Void"); walker.next(); walker.setVoidValue(); walker.nextRow();

Type Guess

FastODS can guess types, based on Java object types. It's useful when we try to auto import typed data, e.g. from a ResultSet. We can use this ability to reduce the boilerplate code.

Let's define two lists:

final List A = Arrays .asList("Type", "Boolean", "Currency", "Date", "Float", "Percentage", "String", "Void"); final List B = Arrays .asList("Type guess example", true, new CurrencyValue(10.5, "USD"), new GregorianCalendar(2014, 9, 17, 9, 0, 0), 3.14159, new PercentageValue(0.545), new TimeValue(false, 0, 0, 0, 0, 0, 3.6), "A String", null);

And a converter:

final ToCellValueConverter converter = new ObjectToCellValueConverter("USD");

As you can see, some types are not guessable: is 0.545 a float or a percentage? For FastODS, it is a float. What Java type will map a currency value? We have to use specific types like CurrencyValue, TimeValue or PercentageValue.

We skip a row for readability:

walker.nextRow();

Now, we can use setValue to take advantage of the type guess:

for (int r = 0; r < A.size(); r++) { walker.setStringValue(A.get(r)); walker.next(); walker.setCellValue(converter.from(B.get(r))); walker.nextRow(); }

Note: We saw all the cell type available in the OpenDocument specification. We'll see that FastODS has another kind of String value in the Text value section.

Setting the Cell Data Style

Data styles are what we call "formats": is your date in plain text or in US format? how many digits have your number? are the negative numbers in red?

A cell may have a style, and this style may have a data style. The OpenDocument specification states that data style can't be attached directly to a cell, but must be embedded in a cell. That's not easy to handle for FastODS, but should not be too complicated for you. As usual, we create a table and get the first cell:

final Table table = document.addTable("data styles");

We'll place a float with the standard format, and a float with a custom format side by side

final TableCellWalker walker = table.getWalker();

Standard format:

walker.setFloatValue(123456.789);

And now create a custom data style:

final DataStyle floatDataStyle = new FloatStyleBuilder("float-datastyle", Locale.US) .decimalPlaces(8).groupThousands(true).build(); walker.next(); walker.setFloatValue(123456.789); walker.setDataStyle(floatDataStyle);

We can do the same with dates:

walker.nextRow();

A date with the standard format:

final Calendar cal = new GregorianCalendar(2018, 1, 1, 0, 0, 0); walker.setDateValue(cal);

And a custom format:

final DataStyle dateDataStyle = new DateStyleBuilder("date-datastyle", Locale.US) .dateFormat( new DateTimeStyleFormat(DateTimeStyleFormat.DAY, DateTimeStyleFormat.DOT, DateTimeStyleFormat.MONTH, DateTimeStyleFormat.DOT, DateTimeStyleFormat.YEAR)).visible().build(); walker.next(); walker.setDateValue(cal); walker.setDataStyle(dateDataStyle);

A last try with a time (duration):

walker.nextRow(); walker.setTimeValue(10000000);

And:

final DataStyle timeDataStyle = new TimeStyleBuilder("time-datastyle", Locale.US) .timeFormat(new DateTimeStyleFormat(DateTimeStyleFormat.text("Hour: "), DateTimeStyleFormat.LONG_HOURS)).visible().build(); walker.next(); walker.setTimeValue(10000000); walker.setDataStyle(timeDataStyle);

Changing the Default Data Styles

Setting the data style for every cell may become cumbersome. Happily, you can decide of the default data styles at the creation of the document.

First, create a DataStyles object (note the "s") with a builder:

final DataStylesBuilder dsb = DataStylesBuilder.create(Locale.US); dsb.floatStyleBuilder().decimalPlaces(0); dsb.dateStyleBuilder().dateFormat( new DateTimeStyleFormat(DateTimeStyleFormat.LONG_DAY, DateTimeStyleFormat.SLASH, DateTimeStyleFormat.LONG_MONTH, DateTimeStyleFormat.SLASH, DateTimeStyleFormat.LONG_YEAR));

You can use the other data style builders if you want, and then build all the data style in one shot:

final DataStyles ds = dsb.build();

Now, create the factory

final OdsFactory odsFactory = OdsFactory.create(Logger.getLogger("cells2"), Locale.US);

and pass the created "data styles" to the factory:

odsFactory.dataStyles(ds);

We can continue as usual:

final AnonymousOdsFileWriter writer = odsFactory.dataStyles(ds).createWriter(); final OdsDocument document = writer.document();

And create the same cells as above:

final Table table = document.addTable("data styles"); final TableCellWalker walker = table.getWalker(); walker.setFloatValue(123456.789); final Calendar cal = new GregorianCalendar(2018, 1, 1, 0, 0, 0); walker.next(); walker.setDateValue(cal);

And save the file.

writer.saveAs(new File("generated_files", "d_data_style2.ods"));

Setting the Cell Style

Let's try to add some shapes and colors. First, we have to create a style for the header:

final TableCellStyle grayStyle = TableCellStyle.builder("gray") .backgroundColor(SimpleColor.GRAY64).fontWeightBold().build();

The functions calls are chained in a fluent style.

We create a table and get the first cell:

final Table table = document.addTable("styles"); final TableCellWalker walker = table.getWalker();

Now, we add a value and set the style

walker.setStringValue("A1"); walker.setStyle(grayStyle);

Common Styles and Automatic Styles

In LO, you'll see a new style named "gray" in the "Styles" window. That's because TableCellStyles are visible by default. We can make a style hidden by adding a hidden() call:

final TableCellStyle hiddenGrayStyle = TableCellStyle.builder("hiddenGray") .backgroundColor(SimpleColor.GRAY64).fontWeightBold().hidden().build(); walker.next(); walker.setStringValue("A2"); walker.setStyle(hiddenGrayStyle);

The "gray2" style is not present in the Style window of LO. This distinction between "visible" and "hidden" styles matches the distinction between common and automatic styles in the OpenDocument specification (3.15.3):

Note: Common and automatic styles behave differently in OpenDocument editing consumers. Common styles are presented to the user as a named set of formatting properties. The formatting properties of an automatic style are presented to a user as properties of the object to which the style is applied.

This distinction is sometimes hard to handle. FastODS tries to make things easy: each style is, by default, either visible or hidden (depending on the kind of the style), but you can always override the default choice. For instance, TableCellStyles, are visible by default, but TableRowStyles and data styles are hidden by default. In most of the cases, you can simply ignore the distinction.

Let's continue with a new style:

final TableCellStyle rotateStyle = TableCellStyle.builder("rotate") .fontColor(SimpleColor.RED).textRotating(Angle.deg(37)).build(); walker.next(); walker.setStringValue("A3"); walker.setStyle(rotateStyle);

You can explore the TableCellStyle to create the style you need. A last example:

final TableCellStyle borderStyle = TableCellStyle.builder("border") .fontName(LOFonts.DEJAVU_SANS).fontSize(SimpleLength.pt(24)) .borderAll(SimpleLength.mm(2), SimpleColor.BLUE, BorderStyle.OUTSET) .build(); walker.next(); walker.setStringValue("A4"); walker.setStyle(borderStyle);

I think you get it now.

Rows and Columns Styles

What do we see? Yes, the last cell is ugly. But it is also partially hidden because the height of the row was not adapted. You have to adapt it yourself. Let's try with another row:

final TableRowStyle tallRowStyle = TableRowStyle.builder("tall-row") .rowHeight(SimpleLength.cm(3)). build(); walker.nextRow();

The walker position is now: row 1, column 0. Hence, walker.setRowStyle(...) is equivalent here to table.getRow(1).setRowStyle(...) because the walker inherits the methods from the cell and from the row (and from the column too):

walker.setRowStyle(tallRowStyle);

You have to set the height of the row manually. There's an optimal height/width in the OpenDocument specification (20.383 and 20.384) but LO does not understand it, and FastODS won't compute this optimal value from the cell contents. (Maybe one day I'll write a tool to compute the width/height of a text.) You can also add a column style. walker.setColumnStyle(...) is equivalent here to `table.setColumnStyle(0, ...):

final TableColumnStyle wideColumn = TableColumnStyle.builder("wide-col") .columnWidth(SimpleLength.cm(9)).build(); walker.setColumnStyle(wideColumn);

We add a content and a style to the cell.

walker.setStringValue("B1"); walker.setStyle(borderStyle);

Obviously, you can combine a style and a data style:

final DataStyle timeDataStyle = new TimeStyleBuilder("time-datastyle", Locale.US) .timeFormat(new DateTimeStyleFormat(DateTimeStyleFormat.text("Hour: "), DateTimeStyleFormat.LONG_HOURS)).visible().build(); walker.nextRow(); walker.setTimeValue(10000000); walker.setStyle(rotateStyle); walker.setDataStyle(timeDataStyle);

More on Cells

We know how to access a cell, set a value, a data style (format) and a style. But there is more on cells: first, we sometimes need to merge cells; second, some cells contains a formatted text.

final Table table = document.addTable("more");

We add a header:

TableCellWalker walker = table.getWalker();

Merging Cells

Cells can be merged easily:

walker.setStringValue("A1 (merged cells)"); walker.setCellMerge(2, 3);

Here, the cells A2, A3, B1, B2 and B3 are covered. You can assign value to those cells, but the values are not visible:

walker.next(); walker.setStringValue("A2 (covered)"); walker.next(); walker.setStringValue("A3 (covered)"); walker.next(); walker.setStringValue("A4 (not covered)"); walker.nextRow(); walker.setStringValue("B1 (covered)"); walker.next(); walker.setStringValue("B2 (covered)"); walker.next(); walker.setStringValue("B3 (covered)"); walker.next(); walker.setStringValue("B4 (not covered)"); walker.nextRow(); walker.setStringValue("C1 (not covered)"); walker.next(); walker.setStringValue("C2 (not covered)"); walker.next(); walker.setStringValue("C3 (not covered)"); walker.next(); walker.setStringValue("B4 (not covered)");

If you open the document in LO, you'll see something like this:

------------------------------------------------------------------------------
| A1 (merged cells)                                       | A4 (not covered) |
|                                                         |-------------------
|                                                         | B4 (not covered) |
------------------------------------------------------------------------------
| C1 (not covered)  | C2 (not covered) | C3 (not covered) | B4 (not covered) |
------------------------------------------------------------------------------

If you split the A1 cell:

------------------------------------------------------------------------------
| A1 (merged cells) | A2 (covered)     | A3 (covered)     | A4 (not covered) |
------------------------------------------------------------------------------
| B1 (covered)      | B2 (covered)     | B3 (covered)     | B4 (not covered) |
------------------------------------------------------------------------------
| C1 (not covered)  | C2 (not covered) | C3 (not covered) | B4 (not covered) |
------------------------------------------------------------------------------

It's possible to merge only one cells one one row or one column with walker .setRowsSpanned(m) or walker.setColumnsSpanned(n) (see below).

You can also merge cells from the row with: row.setCellMerge(cell_index, m, n), row .setRowsSpanned(cell_index, m) or row.setColumnsSpanned(cell_index, n).

Formatted text in a cell

I listed the cell types in a previous section. But the String cell type is not limited to a plain String. It can contain formatted text. Let's learn how it works.

Multiline text

We need some room:

walker.setRowsSpanned(3);

Let's start with something simple. First, we build a text:

Text text = Text.builder().parContent("This is a").parContent("multiline") .parContent("cell").build();

Second, we set the text:

walker.setText(text);

We can use some styles:

final TextStyle boldStyle = TextProperties.builder().fontWeightBold() .buildHiddenStyle("bold"); text = Text.builder().par().span("This is a ").styledSpan("bold", boldStyle) .span(" example").build(); walker.to(2); walker.setColumnsSpanned(2); walker.setText(text);

Links

Links can be absolute or relative. For instance, an absolute Link may be an absolute URL:

walker.setText(Text.builder().par().span("Hello, ") .link("FastODS", new URL("https://www.github.com/jferard/fastods")).span("!") .build());

A relative link:

walker.to(2); walker.setText(Text.builder().par().span("Check ") .link("Hello World example", new URI("../a_hello_world_example.ods")).build());

Or a link to a table:

final Table table2 = document.addTable("target"); walker.to(4); walker.setText( Text.builder().par().span("A link to ").link("target table", table2).build());

Tooltips

Tooltips are LO dependent:

walker.setStringValue("A Cell with a tooltip"); walker.setTooltip("The Tooltip");

Formulas

Warning

First, FastODS won't parse formulas, check syntax or semantic, evaluate results or anything like that. FastODS will write your formula in the document and that's all, even if your formula is "I'm the King of the wold!". That may be frustrating, but FastODS is not an OpenDocument consumer, just a producer.

Second, it's important to understand that LibreOffice, as OpenOffice and Excel before, have made the choice to translate the formula language in various human languages. Let's think about that: imagine that Sun decided to translate the Java keywords in every language, and then to translate the libraries in every language. I'm not talking about internationalization of localization, but about translation! Programming would be harder, a lot harder... That's a stupid yet perfectly understandable idea: anyone can use Excel formulas, but anyone can't understand and remember hundred basic english words.

But, as you may know, no matter what interface language you have selected, formulas are always stored in a document in the Recalculated Formula (OpenFormula) Format, which is basically the syntax of english written LibreOffice formulas. In French, we write SOMME.SI but the internal name of the function is SUMIF whereas SOMME.SI is the display name. And the formula attribute of the cell will contain SUMIF, not SOMME.SI.

As stated above, FastODS does not care about the content of the formula. FastODS won't complain if you write formulas in french, dutch or chinese, but LibreOffice will! If you write a formula in a language that is not english, the LibreOffice engine won't understand your formula and will return an error.

To summarize: you are responsible for writing your formulas in english and to write them correctly.

Some basic examples

Let's start!

We have to remember the address of the current cell. It's easy here: A1.

walker.setStringValue("1"); walker.next(); walker.setFormula("IF(A1=1;1;0)"); walker.next(); walker.setFormula("IF(A1="1";1;0)");

Formula are typed, hence you have the value 0 in B1 and 1 in C1.

Now, something more interesting with a matrix formula:

walker.nextRow(); walker.setFloatValue(1); walker.nextRow(); walker.setFloatValue(2); walker.nextRow(); walker.setFloatValue(3); walker.nextRow(); walker.setFloatValue(4); walker.nextRow(); walker.setFloatValue(5); walker.nextRow(); walker.setFloatValue(6); walker.nextRow(); walker.setFloatValue(7); walker.nextRow(); walker.setFloatValue(8); walker.nextRow(); walker.setMatrixFormula("SUM((MOD(A2:A9;2)=0)*(A2:A9))");

The formula sums the cell A2:A9 with an even value.

Page Format

We know how to access to cells and how to format those cells. We still have to format the pages.

Let's start with a new table:

final Table table = document.addTable("format-page"); final TableCellWalker walker = table.getWalker(); walker.setStringValue("Text");

We will add a footer and a header.

Header

First, we build the three parts of a simple header:

final Text leftHeaderContent = Text.content("left header"); final Text centerHeaderContent = Text.builder().par().span("center header, page ") .span(Text.TEXT_PAGE_NUMBER).build(); final Text rightHeaderContent = Text.content("right header");

Then we build the header itself:

final Header header = PageSection.regionBuilder().region(PageSectionContent.Region.LEFT) .text(leftHeaderContent).region(PageSectionContent.Region.CENTER) .text(centerHeaderContent).region(PageSectionContent.Region.RIGHT) .text(rightHeaderContent).allMargins(SimpleLength.cm(2)) .minHeight(SimpleLength.cm(5)).buildHeader();

Footer

For the footer, let's use the one part format:

final Footer footer = PageSection.simpleBuilder().text(Text.content("footer")) .buildFooter();

We now insert the header and the footer in a page style:

final PageStyle pageStyle = PageStyle.builder("page-style").header(header) .footer(footer).build();

And add the page style into the table style:

final TableStyle tableStyle = TableStyle.builder("table-style").pageStyle(pageStyle) .build();

And set this table style:

table.setStyle(tableStyle);

Styles

Create another table:

final Table table = document.addTable("format-page2"); final TableCellWalker walker = table.getWalker(); walker.setStringValue("Text");

We can create a very simple header:

final Header minimalHeader = PageSection.simpleBuilder().content("minimal header") .buildHeader();

Or a complex footer:

final TextBuilder textBuilder = Text.builder();

par() means a new paragraph, span a new portion of text:

textBuilder.par().span("complex");

Both can be used in one call:

textBuilder.parContent("footer");

Text can be styled:

textBuilder.par().styledSpan("date is:", TextProperties.builder().fontWeightBold().buildHiddenStyle("footer1"));

In one call:

textBuilder.parStyledContent(Text.TEXT_DATE, TextProperties.builder().fontSize(SimpleLength.pt(25)).fontWeightBold() .buildHiddenStyle("footer2"));

And build the text:

final Text footerContent = textBuilder.build(); final Footer complexFooter = PageSection.simpleBuilder().text(footerContent) .buildFooter();

As above:

final PageStyle pageStyle = PageStyle.builder("page-style2").header(minimalHeader) .footer(complexFooter).build(); final TableStyle tableStyle = TableStyle.builder("table-style2").pageStyle(pageStyle) .build(); table.setStyle(tableStyle);

BEGIN TUTORIAL (directive to extract part of a tutorial from this file)

}

Auto filters and Data Pilot tables

Auto filters and Data Pilot tables are defined in the OpenDocument specification. However, settings a specific filter in an Auto Filter, or defining an Data Pilot table is not sufficient: the line filtered lines won't be hidden by magic, the Data Pilot table won't be filled out by magic.

FastODS is not a data processor and won't become one. Hence, it won't compute the result of a filter or a Pilot table. There's a workaround: let LibreOffice do the job. The idea is to trigger a refresh of the data that will update the filters and the Pilot tables.

A Simple Auto filter

It's easy to add manually an autofilter. Let's create some content:

walker.setStringValue("File Type"); walker.next(); walker.setStringValue("Extension"); walker.nextRow(); walker.setStringValue("Text"); walker.next(); walker.setStringValue(".odt"); walker.nextRow(); walker.setStringValue("Spreadsheet"); walker.next(); walker.setStringValue(".ods"); walker.nextRow(); walker.setStringValue("Presentation"); walker.next(); walker.setStringValue(".odp"); walker.nextRow(); walker.setStringValue("Drawing"); walker.next(); walker.setStringValue(".odg"); walker.nextRow(); walker.setStringValue("Chart"); walker.next(); walker.setStringValue(".odc"); walker.nextRow(); walker.setStringValue("Formula"); walker.next(); walker.setStringValue(".odf"); walker.nextRow(); walker.setStringValue("Image"); walker.next(); walker.setStringValue(".odi"); walker.nextRow(); walker.setStringValue("Master Document"); walker.next(); walker.setStringValue(".odm"); walker.nextRow(); walker.setStringValue("Database"); walker.next(); walker.setStringValue(".odb");

Now we need to set the filter. It's possible to preset some filter with the filter method of the builder.

document.addAutoFilter(AutoFilter.builder(table, 0, 0, walker.rowIndex(), walker.colIndex()) .filter(new FilterEnumerate(0, "Spreadsheet", "Presentation", "Master Document")) .build());

The filter will be set (the little square appears and if you click on the arrow, only Spreadsheet", "Presentation" and "Master Document" are checked. But... the rows remain visible, making the function of very limited interest.

As written in the introduction of this section, to hide the filtered rows, FastODS should apply (and not just declare) the filter to mark the rows as "filtered". But that's really overkill. There's an alternative solution: it's possible to add a macro to the document, and to trigger that macro on document load.

new MacroHelper().addRefreshMacro(document);

This macro will refresh all autofilters and hide the columns. (Note that adding this macro is not mandatory.)

A Data Pilot table

Let's start with some data:

walker.setStringValue("File Type"); walker.next(); walker.setStringValue("Extension"); walker.next(); walker.setStringValue("Length of type"); walker.next(); walker.setStringValue("Long or short"); walker.nextRow(); walker.setStringValue("Text"); walker.next(); walker.setStringValue(".odt"); walker.next(); walker.setFormula("LEN(A2)"); walker.next(); walker.setFormula("IF(C2 >= 8;"long";"short")"); walker.nextRow(); walker.setStringValue("Spreadsheet"); walker.next(); walker.setStringValue(".ods"); walker.next(); walker.setFormula("LEN(A3)"); walker.next(); walker.setFormula("IF(C3 >= 8;"long";"short")"); walker.nextRow(); walker.setStringValue("Presentation"); walker.next(); walker.setStringValue(".odp"); walker.next(); walker.setFormula("LEN(A4)"); walker.next(); walker.setFormula("IF(C4 >= 8;"long";"short")"); walker.nextRow(); walker.setStringValue("Drawing"); walker.next(); walker.setStringValue(".odg"); walker.next(); walker.setFormula("LEN(A5)"); walker.next(); walker.setFormula("IF(C5 >= 8;"long";"short")"); walker.nextRow(); walker.setStringValue("Chart"); walker.next(); walker.setStringValue(".odc"); walker.next(); walker.setFormula("LEN(A6)"); walker.next(); walker.setFormula("IF(C6 >= 8;"long";"short")"); walker.nextRow(); walker.setStringValue("Formula"); walker.next(); walker.setStringValue(".odf"); walker.next(); walker.setFormula("LEN(A7)"); walker.next(); walker.setFormula("IF(C7 >= 8;"long";"short")"); walker.nextRow(); walker.setStringValue("Image"); walker.next(); walker.setStringValue(".odi"); walker.next(); walker.setFormula("LEN(A8)"); walker.next(); walker.setFormula("IF(C8 >= 8;"long";"short")"); walker.nextRow(); walker.setStringValue("Master Document"); walker.next(); walker.setStringValue(".odm"); walker.next(); walker.setFormula("LEN(A9)"); walker.next(); walker.setFormula("IF(C9 >= 8;"long";"short")"); walker.nextRow(); walker.setStringValue("Database"); walker.next(); walker.setStringValue(".odb"); walker.next(); walker.setFormula("LEN(A10)"); walker.next(); walker.setFormula("IF(C10 >= 8;"long";"short")");

and a simple sheet pilot to host the Data Pilot table:

final Table pilotTable = document.addTable("pilot"); document.setActiveTable(1);

Now the we have to build the Data Pilot table. This is not an trivial task. A Data Pilot table has several attributes, mainly:

  • a name
  • a source range
  • a target range

final PositionUtil positionUtil = PositionUtil.create(); final PilotTable pilot = PilotTable .builder("DataPilot1", positionUtil.toRangeAddress(dataTable, 0, 0, 9, 3), positionUtil.toRangeAddress(pilotTable, 0, 0, 0, 0), Arrays.asList(positionUtil.toCellAddress(pilotTable, 1, 0), positionUtil.toCellAddress(pilotTable, 0, 1)))

And some field. First, the column and row fields. The isDataLayout sets the orientation of the table.

.field(new PilotTableField("", FieldOrientation.COLUMN, -1, true, PilotStandardFunction.AUTO, new PilotTableLevel(true))) .field(new PilotTableField("Long or short", FieldOrientation.ROW, 0, false, PilotStandardFunction.AUTO, new PilotTableLevel(false)))

Then the data fields:

.field(new PilotTableField("Length of type", FieldOrientation.DATA, 0, false, PilotStandardFunction.COUNT, new PilotTableLevel(false))) .field(new PilotTableField("Length of type", FieldOrientation.DATA, 0, false, PilotStandardFunction.SUM, new PilotTableLevel(false))) .field(new PilotTableField("Length of type", FieldOrientation.DATA, 0, false, PilotStandardFunction.AVERAGE, new PilotTableLevel(false))).build();

Add the Data Pilot table to the document

document.addPilotTable(pilot);

And force the refresh at start (not mandatory):

new MacroHelper().addRefreshMacro(document);

Miscellanous Features

A Named Writer

You can create a named writer to write large files. This feature is experimental, because LO will never be able to open large files.

Here's a sketch of how it works:

  • When you create a NamedOdsFileWriter (instead of an anonymous one), the writer is registered as an observer by the inner document.
  • When a new table is added, remaining rows of the previous table are flushed.
  • When a new row is created, if the buffer of rows is full, rows are flushed.

That's why all styles have to be registered before the content is added.

In practice, you have to give the name of the file at the writer creation:

final OdsFactory odsFactory = OdsFactory.create(Logger.getLogger("advanced"), Locale.US); final NamedOdsFileWriter writer = odsFactory .createWriter(new File("generated_files", "i_named_misc.ods"));

Then, get the document and the tables as usual.

final NamedOdsDocument document = writer.document();

You have to register all the styles now:

final TableCellStyle boldCellStyle = TableCellStyle.builder("cell").fontWeightBold() .fontSize(SimpleLength.pt(24)).build(); document.addContentStyle(boldCellStyle); document.freezeStyles();

And, if necessary:

document.addPageStyle(aPageStyle); document.addContentStyle(aTableStyle); document.addContentStyle(aTableRowStyle); document.addContentStyle(aTableColumnStyle); document.addContentStyle(aTableCellStyle); document.addContentStyle(aTextStyle);

An now, you can fill the Spreadsheet as usual.

final Table table = document.addTable("advanced"); final TableCellWalker walker = table.getWalker(); walker.setStringValue("A huge document"); walker.setStyle(boldCellStyle);

When you're finished:

document.save();

Writing a ResultSet to the Spreadsheet

We need a ResultSet. Let's use H2:

final JdbcDataSource dataSource = new JdbcDataSource(); dataSource.setUrl("jdbc:h2:mem:test"); final Connection conn = dataSource.getConnection(); try { final Statement s = conn.createStatement(); s.execute("CREATE TABLE document (file_type TEXT, extension TEXT)"); s.execute("INSERT INTO document VALUES ('Text', '.odt'), ('Spreadsheet', '.ods'), " + "('Presentation', '.odp'), ('Drawing', '.odg'), ('Chart', '.odc'), " + "('Formula', '.odf'), ('Image', '.odi'), ('Master Document', '.odm')" + ", ('Database', '.odb')"); final ResultSet rs = s.executeQuery("SELECT * FROM document");

Now, we can write the result on a document. It will use the current row of the table:

walker.addData(ResultSetDataWrapper.builder(rs).build());

It's possible to add multiple ResultSets:

walker.toRow(0); walker.to(3); final ResultSet rs2 = s.executeQuery( "SELECT file_type as file_type7, extension FROM document WHERE LENGTH" + "(file_type) > 7"); walker.addData(ResultSetDataWrapper.builder(rs2).build());

Let's create another table to test data types:

s.execute("CREATE TABLE item (id CHAR(12), name TEXT, price DECIMAL, tax DECIMAL, " + "high_quality BOOLEAN, lifespan INTERVAL DAY TO HOUR, image BLOB, " + "creation_date TIMESTAMP)"); s.execute("INSERT INTO item VALUES ('01234789', 'toothbrush', 3, 0.6, True, '30 8', " + "RAWTOHEX('FastODS'), '2019-01-01')"); final ResultSet rs3 = s.executeQuery("SELECT * FROM item");

FastODS uses the type guess to determine the type of objects. But the jdbc API does not provide a class for SQL's INTERVAL object. Hence, FastODS provides a class to define a cast try to an INTERVAL.

final SQLToCellValueConverter.IntervalConverter converter = new SQLToCellValueConverter.IntervalConverter() { @Override public TimeValue castToInterval(final Object o) { if (o instanceof Interval) { final Interval interval = (Interval) o; final boolean neg = interval.isNegative(); switch (interval.getQualifier()) { case YEAR: return new TimeValue(neg, interval.getLeading(), 0, 0, 0, 0, 0); case MONTH: return new TimeValue(neg, 0, interval.getLeading(), 0, 0, 0, 0); case YEAR_TO_MONTH: return new TimeValue(neg, interval.getLeading(), interval.getRemaining(), 0, 0, 0, 0); case DAY: return new TimeValue(neg, 0, 0, interval.getLeading(), 0, 0, 0); case HOUR: return new TimeValue(neg, 0, 0, 0, interval.getLeading(), 0, 0); case MINUTE: return new TimeValue(neg, 0, 0, 0, 0, interval.getLeading(), 0); case SECOND: return new TimeValue(neg, 0, 0, 0, 0, 0, interval.getLeading()); case DAY_TO_HOUR: return new TimeValue(neg, 0, 0, interval.getLeading(), interval.getRemaining(), 0, 0); case DAY_TO_MINUTE: return new TimeValue(neg, 0, 0, interval.getLeading(), 0, interval.getRemaining(), 0); case DAY_TO_SECOND: return new TimeValue(neg, 0, 0, interval.getLeading(), 0, 0, interval.getRemaining() / NANOSECONDS_PER_SECONDS); case HOUR_TO_MINUTE: return new TimeValue(neg, 0, 0, 0, interval.getLeading(), interval.getRemaining(), 0); case HOUR_TO_SECOND: return new TimeValue(neg, 0, 0, 0, interval.getLeading(), 0, interval.getRemaining() / NANOSECONDS_PER_SECONDS); case MINUTE_TO_SECOND: return new TimeValue(neg, 0, 0, 0, 0, interval.getLeading(), interval.getRemaining() / NANOSECONDS_PER_SECONDS); } } return null; } };

And skip another row, then write the result:

walker.toRow(12); walker.addData(ResultSetDataWrapper.builder(rs3).converter(converter).build()); } finally { conn.close(); }

LO features

Freeze cells

Let's create some content:

walker.setStringValue("File Type"); walker.next(); walker.setStringValue("Extension"); walker.nextRow(); walker.setStringValue("Text"); walker.next(); walker.setStringValue(".odt"); walker.nextRow(); walker.setStringValue("Spreadsheet"); walker.next(); walker.setStringValue(".ods"); walker.nextRow(); walker.setStringValue("Presentation"); walker.next(); walker.setStringValue(".odp");

It's easy to freeze the first row:

document.freezeCells(table, 1, 0);

Other features

If you know what you are doing, you can play with LO settings, for instance:

table.updateConfigItem(ConfigElement.ZOOM_VALUE, "150");

You can discover the configuration attributes in the ConfigElement enum.

Add files to the ods archive

Remember the method to add an auto update to the document? That was:

new MacroHelper().addRefreshMacro(document);

Under the hood, this function adds some files to the ods archive. The ods archive contains a manifest.xml that lists the files. If a file was added without a matching entry in the manifest, LibreOffice will bark and refuse to open the file.

Let's add a file for the fun:

document.addExtraDir("FastODS"); document.addExtraFile("FastODS/fast.txt", "text/plain", "Hello from FastODS!");

You can check that the file was added with your favorite file achive viewer.

A multiplication table

Let's create a new document and a new table:

final OdsFactory odsFactory = OdsFactory .create(Logger.getLogger("multiplication"), Locale.US); final AnonymousOdsFileWriter writer = odsFactory.createWriter(); final OdsDocument document = writer.document(); final Table table = document.addTable("multiplication-table");

## The spreadsheet First, we need to set a default font that is monospaced and a text that is centered, to ensure a nice alignment of the operations:

final TableCellStyle tableCellStyle = TableCellStyle.builder("c2") .fontName(LOFonts.LIBERATION_MONO).textAlign(CellAlign.CENTER) .fontSize(SimpleLength.pt(10)).build();

All columns will have the same format:

final TableColumnStyle tableColumnStyle = TableColumnStyle.builder("co2") .defaultCellStyle(tableCellStyle).columnWidth(SimpleLength.cm(3.5)).build(); for (int c = 0; c < 6; c++) { table.setColumnStyle(c, tableColumnStyle); }

Now, we need a little maths (this is not surprising for a multiplication table). We want to display 12 x 12 operations i x j = k:

final int MAX = 12;

Operations are grouped by j, e.g. 1 x 1, 2 x 1, 3 x 1, ... on the first column, 1 x 2, 2 x 2, 3 x 2, ... on the second column, etc. and displayed in two stacked blocks:

final int BLOCK_COUNT = 2;

Thus, we need 6 columns:

final int COLS = MAX / BLOCK_COUNT;

We use two imbricated loops:

for (int i = 1; i <= MAX; i++) { for (int j = 1; j <= MAX; j++) {

The value of j is used to find the column. Since operations are in two stacked blocks (1-6 and 7-12), the column is j-1 % 6, that is: 1->0, 2->1, ..., 6->5, 7->0, 8->1, ..., 12->5.

final int c = (j - 1) % COLS;

The row is: i-1 if this is the first block, or 13 + i-1 (13 for 12 operations + a blank line). The block is (j-1) / 6:

final int r = ((j - 1) / COLS) * (MAX + 1) + (i - 1);

We use String.format and set a width (2 for operands, 3 for the result

table.getRow(r).getOrCreateCell(c) .setStringValue(String.format("%2d \u00D7 %2d = %3d", i, j, i * j)); } }

Footer and header

A multiplication table should be printable. We'll add a header and a footer.

For the header, we need a style for the title and another for a discreet dedication (note the use of buildHiddenStyle: common styles in footer/header are ignored by LO):

final TextStyle titleStyle = TextProperties.builder().fontWeightBold() .fontSize(SimpleLength.pt(24)).buildHiddenStyle("title"); final TextStyle dedicationStyle = TextProperties.builder().fontSize(SimpleLength.pt(8)) .fontStyleItalic().buildHiddenStyle("dedication");

Now, we create the text of the header:

final Text headerText = Text.builder().parStyledContent("Multiplication Table", titleStyle) .parStyledContent("For Léon", dedicationStyle).build();

And the header itself:

final Header header = PageSection.simpleBuilder().text(headerText) .minHeight(SimpleLength.cm(2)).buildHeader();

The footer is simple, but we need to escape the content because of the < and >:

final String footerText = XMLUtil.create().escapeXMLContent( "Copyright (C) 2019 J. Férard https://github.com/jferard " + "Creative Commons BY-SA / created with FastODS " + "(https://github.com/jferard/fastods)"); final Footer footer = PageSection.simpleBuilder().styledContent(footerText, dedicationStyle) .buildFooter();

Let's gather the footer and the header in a page style. We center the table and set a zoom:

final PageStyle pageStyle = PageStyle.builder("page").header(header).footer(footer) .printOrientationHorizontal().scaleTo(125).centering(PageCentering.BOTH) .build();

We set set the style of the current table.

final TableStyle tableStyle = TableStyle.builder("table").pageStyle(pageStyle).build(); table.setStyle(tableStyle);

And save the file.

writer.saveAs(new File("generated_files", "i_multiplication_table.ods"));

As you see, it's possible to create a nice document in roughly 40 lines of code (I don't count the imports)

The periodic table of the elements

We will store the data in a small database

We need a sheet table to create the table:

final Logger periodicLogger = Logger.getLogger("periodic"); final OdsFactory odsFactory = OdsFactory.create(periodicLogger, Locale.US); final AnonymousOdsFileWriter writer = odsFactory.createWriter(); final OdsDocument document = writer.document(); final Table table = document.addTable("table");

a simple sheet data to write the data:

final Table dataTable = document.addTable("data");

The data

We'll use h2 again (see Advanced part of the tutorial). The content of the resources files can be found at https://github .com/jferard/fastods/blob/master/fastods-examples/src/test/resources/create.sql and https://github.com/jferard/fastods/blob/master/fastods-examples/src/test/resources /insert.sql.

The results where parsed from the article https://en.wikipedia .org/wiki/List_of_chemical_elements.

We open a connection and populate the database:

final JdbcDataSource dataSource = new JdbcDataSource(); dataSource.setUrl("jdbc:h2:mem:test"); try { final Connection connection = dataSource.getConnection(); try { final Statement s = connection.createStatement(); s.execute(resourceToString("create.sql")); s.execute(resourceToString("insert.sql"));

The function resourceToString is defined at the bottom of the section

Now, we have a database and we can build the data table as in the Advanced section but we don't need the intervals:

ResultSet rs = s.executeQuery("SELECT * FROM chemical_element"); final TableCellWalker walker = dataTable.getWalker(); walker.addData(ResultSetDataWrapper.builder(rs).build());

The table

Ok, that was the easy part, just to show once more how easy it is to write a ResultSet to a sheet.

Style

Cells have a color that depends on the subcategory:

final TableCellStyle baseCellStyle = TableCellStyle.builder("ce3") .textAlign(CellAlign.CENTER) .verticalAlign(VerticalAlign.MIDDLE).build();

We put those styles in a map:

final Map<String, TableCellStyle> cellStyleBySubcategory = new HashMap<String, TableCellStyle>(); cellStyleBySubcategory .put("alkali metal", getCellStyle("alkalimetal", SimpleColor.ORANGERED)); cellStyleBySubcategory.put("alkaline earth metal", getCellStyle("alkalineearthmetal", SimpleColor.ORANGE)); cellStyleBySubcategory.put("transition metal", getCellStyle("transitionmetal", SimpleColor.YELLOW)); cellStyleBySubcategory.put("actinide", getCellStyle("actinide", SimpleColor.GREEN)); cellStyleBySubcategory .put("metalloid", getCellStyle("metalloid", SimpleColor.LIGHTCYAN)); cellStyleBySubcategory .put("noble gas", getCellStyle("noblegas", SimpleColor.VIOLET)); cellStyleBySubcategory.put("post-transition metal", getCellStyle("posttransitionmetal", SimpleColor.STEELBLUE)); cellStyleBySubcategory.put("reactive nonmetal", getCellStyle("reactivenonmetal", SimpleColor.BLUE)); cellStyleBySubcategory .put("lanthanide", getCellStyle("lanthanide", SimpleColor.YELLOWGREEN)); final TableCellStyle unknownStyle = getCellStyle("other", SimpleColor.WHITE);

The function getCellStyle is defined at the bottom of the section Cells must be square:

final SimpleLength CELL_SIZE = SimpleLength.cm(1.5); final TableColumnStyle tableColumnStyle = TableColumnStyle.builder("co2") .columnWidth(CELL_SIZE).defaultCellStyle(baseCellStyle).build(); for (int c = 0; c < 18; c++) { table.setColumnStyle(c, tableColumnStyle); } final TableRowStyle tableRowStyle = TableRowStyle.builder("ro2") .rowHeight(CELL_SIZE).build();

We need some styles:

final TextStyle elementStyle = TextProperties.builder().fontSize(SimpleLength.pt(6)) .buildHiddenStyle("elementStyle"); final TextStyle atomicNumberStyle = TextProperties.builder() .fontSize(SimpleLength.pt(8)).buildHiddenStyle("atomicNumberStyle"); final TextStyle symbolStyle = TextProperties.builder().fontSize(SimpleLength.pt(12)) .fontWeightBold().buildHiddenStyle("symbolStyle");

Cells content

The row of the element is given by its period and the column is the pt_group.

Let's execute the same query again:

rs = s.executeQuery("SELECT * FROM chemical_element ORDER BY atomic_number"); while (rs.next()) {

And retrieve the interesting parts

final String symbol = rs.getString("symbol"); final String elementName = rs.getString("element_name"); final int atomicNumber = rs.getInt("atomic_number"); final String subcategory = rs.getString("subcategory"); final int period = rs.getInt("period"); final int ptGroup = rs.getInt("pt_group"); final float atomicWeight = rs.getFloat("atomic_weight");

First we ned to compute the row and the column. If the element is part of the main block, it's easy, but things get complicated when the element is part of the f-block.

final int r; final int c; if (ptGroup == 0) { // the f-block r = period + 2; c = (atomicNumber - 58) % 32 + 3; } else { r = period - 1; c = ptGroup - 1; }

If we write something like:

table.getRow(r).getOrCreateCell(c).setStringValue(symbol);

We'll get a first draft. But we want something nicer.

Let's look at a cell of the periodic table of elements:

-----------------
| element_name  |
|               |
|               |
| atomic_number |
|     symbol    |
| atomic_weight |
-----------------

The background color depends on the subcategory.

final TableRowImpl row = table.getRow(r); row.setRowStyle(tableRowStyle); final Text text = TextBuilder.create() .parStyledContent(elementName, elementStyle) .parStyledContent(String.valueOf(atomicNumber), atomicNumberStyle) .parStyledContent(symbol, symbolStyle) .parStyledContent(String.format("%.3f", atomicWeight), elementStyle) .build(); final TableCell cell = row.getOrCreateCell(c); cell.setText(text); TableCellStyle cellStyle = cellStyleBySubcategory.get(subcategory); if (cellStyle == null) { cellStyle = unknownStyle; } cell.setStyle(cellStyle);

Printing

It's almost over. We just need a footer and a header. It's a copycat from the previous section:

final TextStyle titleStyle = TextProperties.builder().fontWeightBold() .fontSize(SimpleLength.pt(24)).buildHiddenStyle("title"); final TextStyle dedicationStyle = TextProperties.builder() .fontSize(SimpleLength.pt(8)).fontStyleItalic() .buildHiddenStyle("dedication"); final Text headerText = Text.builder() .parStyledContent("Periodic Table", titleStyle) .parStyledContent("For Maia", dedicationStyle).build(); final Header header = PageSection.simpleBuilder().text(headerText) .minHeight(SimpleLength.cm(2)).buildHeader(); final String footerText = XMLUtil.create().escapeXMLContent( "Copyright (C) 2019 J. Férard https://github.com/jferard " + "Creative Commons BY-SA / created with FastODS " + "(https://github.com/jferard/fastods)"); final Footer footer = PageSection.simpleBuilder() .styledContent(footerText, dedicationStyle).buildFooter(); final PageStyle pageStyle = PageStyle.builder("page").header(header) .footer(footer).printOrientationHorizontal().scaleTo(95) .centering(PageCentering.BOTH).build(); final TableStyle tableStyle = TableStyle.builder("table").pageStyle(pageStyle) .build(); table.setStyle(tableStyle); } } catch (final SQLException e) { periodicLogger.log(Level.SEVERE, "", e); } finally { connection.close(); } } catch (final SQLException e) { periodicLogger.log(Level.SEVERE, "", e); throw e; }

And save the file.

writer.saveAs(new File("generated_files", "j_periodic_table.ods")); Finally, the expected functions: I use Guava to convert this resources to Strings:

private static String resourceToString(final String resourceName) throws IOException { final Reader reader = Resources .asCharSource(Resources.getResource(resourceName), Charsets.UTF_8).openStream(); return CharStreams.toString(reader); }

And to produce similar cell styles:

private static TableCellStyle getCellStyle(final String name, final Color color) { return TableCellStyle.builder(name).textAlign(CellAlign.CENTER) .verticalAlign(VerticalAlign.MIDDLE).backgroundColor(color) .borderAll(SimpleLength.pt(2), SimpleColor.BLACK, BorderStyle.SOLID) .build(); }

Note: The code of this section is badly structured because of the tutorial format. I don't want to create a lot a small functions because it would be harder for the reader to follow the logic.

Clone this wiki locally