-
Notifications
You must be signed in to change notification settings - Fork 6
Tutorial
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.7.2For other build tools, look at: https://search.maven.org/artifact/com.github.jferard/fastods/0.7.2/jar
- Hello, world!
- Accessing Tables, Rows and Cells
- Setting the cell value
- Setting the Cell Data Style
- Setting the Cell Style
- More on Cells
- Page Format
- Auto filters and Data Pilot tables
- Embedding external content
- Miscellanous Features
- A multiplication table
- The periodic table of the elements
- Protection
- Using the FastODS Bus
- Goal
The source code for this section is available here.
Let's start with the famous "Hello, World!" example. Here's the full code:
final OdsFactory odsFactory = OdsFactory.create(Logger.getLogger("hello-world"), Locale.US);
final AnonymousOdsFileWriter writer = odsFactory.createWriter();
final OdsDocument document = writer.document();
final Table table = document.addTable("hello-world");
final TableRowImpl row = table.getRow(0);
final TableCell cell = row.getOrCreateCell(0);
cell.setStringValue("Hello, world!");
writer.saveAs(new File("generated_files", "a_hello_world_example.ods"));
And here are the explanations.
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.
The source code for this section is available here.
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();
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! */
}
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.
}
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.
The source code for this section is available here.
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 (you have to divide the value by 100):
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();
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<String> A =
Arrays.asList("Type", "Boolean", "Currency", "Date", "Float", "Percentage",
"String", "Void");
final List<Object> 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.
The source code for this section is available here.
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);
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 using a factory builder. Pass the created "data styles" to the builder and build the factory :
final OdsFactory odsFactory = OdsFactory.builder(Logger.getLogger("cells2"), Locale.US)
.dataStyles(ds).build();
We can continue as usual:
final AnonymousOdsFileWriter writer = odsFactory.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"));
The source code for this section is available here.
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);
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 class to create the style you need.
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);
But sometimes, the easier is to start from a given style:
final TableCellStyle borderStyleWithRedBG =
borderStyle.toBuilder("border-red").backgroundColor(SimpleColor.RED).build();
walker.next();
walker.setStringValue("A5");
walker.setStyle(borderStyleWithRedBG);
I think you get it now.
What do we see? Yes, the last cells are ugly. But they are 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);
The source code for this section is available here.
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:
final TableCellWalker walker = table.getWalker();
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).
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.
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 = TextStyle.builder("bold").fontWeightBold().build();
text = Text.builder().par().span("This is a ").styledSpan("bold", boldStyle)
.span(" example").build();
walker.to(2);
walker.setColumnsSpanned(2);
walker.setText(text);
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 are LO dependent:
walker.setStringValue("A Cell with a tooltip");
walker.setTooltip("The Tooltip");
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 world!". 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. That's a stupid yet perfectly understandable idea: anyone should be able to create Excel formulas, but it's sooo hard to understand and remember a hundred basic english words.
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...
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.
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.
The source code for this section is available here.
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");
It's easy to set the page size to a standard format:
final PageStyle pageStyle =
PageStyle.builder("page-style").paperFormat(PaperFormat.A3).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);
You can use a custom format:
final PageStyle pageStyle =
PageStyle.builder("page-style").pageWidth(SimpleLength.cm(10)).pageHeight(
SimpleLength.cm(50)).build();
Or change the margins:
final PageStyle pageStyle =
PageStyle.builder("page-style").allMargins(SimpleLength.cm(4)).build();
You can combine margins and page size customization. Please check the
PageStyleBuilder class for other options.
Now, something a little bit harder: we will add a footer and a 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();
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);
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:",
TextStyle.builder("footer1").fontWeightBold().build());
In one call:
textBuilder.parStyledContent(Text.TEXT_DATE,
TextStyle.builder("footer2").fontSize(SimpleLength.pt(25)).fontWeightBold()
.build());
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);
The source code for this section is available here.
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.
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("my_range", 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.)
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);
The source code for this section is available here.
Embedding an external content is a two steps operation: 1. embed the content in the ods archive; 2. hook the content to the document: to a cell, a table, a tooltip, an event...
I kept those two steps in the API for a simple reason: you might want to perform only one of those actions. Sometimes you don't want to really embed the content in the archive, but only to make reference to an URL. And sometimes you don't want to hook the content, just to embed it. To keep the process easy, there is always a tool for grouping those steps.
First, we'll see how to embed a file into the ods archive, then we'll discover the available FastODS functions to hook this file to the document.
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!".getBytes(CharsetUtil.UTF_8));
You can check that the file was added with your favorite file archive viewer.
As usual, we create a document and a table:
final OdsFactory odsFactory = OdsFactory.create(Logger.getLogger("misc"), Locale.US);
final AnonymousOdsFileWriter writer = odsFactory.createWriter();
final OdsDocument document = writer.document();
final Table table = document.addTable("test");
We need to create the "Pictures" directory in the archive
document.addExtraDir("Pictures");
And there is a tool to insert the image:
final InputStream sourceStream;
try {
sourceStream = new URL("https://raw.githubusercontent" +
".com/wiki/jferard/fastods/images/j_periodic_table.png").openStream();
} catch (final IOException e) {
e.printStackTrace(System.err);
return;
}
InsertHelper.create()
.insertImage(document, table, "Frame 1", sourceStream, "periodic_table.png",
SVGRectangle.cm(0, 0, 15, 10));
That's all!
As usual, we create a document and a table:
final OdsFactory odsFactory = OdsFactory.create(Logger.getLogger("misc"), Locale.US);
final AnonymousOdsFileWriter writer = odsFactory.createWriter();
final OdsDocument document = writer.document();
final Table table = document.addTable("test");
We get the input stream:
final InputStream inputStream =
I_Embedding.class.getClassLoader().getResourceAsStream("a_hello_world_example.ods");
And use a tool
final GraphicStyle gs = GraphicStyle.builder("gs").build();
InsertHelper.create().insertObject(document, table, "embed", "Object 1",
"application/vnd.oasis.opendocument.spreadsheet", "1.2", inputStream,
SVGRectangle.cm(1, 1, 7, 2), gs);
That was a feature request. Here's the way to do it.
The process is a little bit more complex, since the background of the tooltip is set with a "fill style".
As usual:
final OdsFactory odsFactory = OdsFactory.create(Logger.getLogger("misc"), Locale.US);
final AnonymousOdsFileWriter writer = odsFactory.createWriter();
final OdsDocument document = writer.document();
final Table table = document.addTable("test");
We create the cell to annotate
final TableCellWalker walker = table.getWalker();
walker.setStringValue("This cell has an interesting annotation");
Again, we need to create the "Pictures" directory in the archive
document.addExtraDir("Pictures");
And there is a tool to create the fill style:
final InputStream sourceStream;
try {
sourceStream = new URL("https://raw.githubusercontent" +
".com/wiki/jferard/fastods/images/j_periodic_table.png").openStream();
} catch (final IOException e) {
e.printStackTrace(System.err);
return;
}
final DrawFillBitmap drawFillImage = InsertHelper.create()
.createDrawFillImage(document, sourceStream, "periodic", "Pictures/periodic.png");
Now that the "fill-style" is created, you just have to set the style of the tooltip.
final GraphicStyle gs = GraphicStyle.builder("gs").drawFill(drawFillImage).build();
final Tooltip tooltip =
Tooltip.builder(XMLUtil.create(), "This the perodic table of elements")
.rectangle(SVGRectangle.cm(9, 1, 15, 10)).graphicStyle(gs).visible()
.build();
And set the tooltip:
walker.setTooltip(tooltip);
Warning: this is not understood by LibreOffice.
As usual, we create a document and a table:
final OdsFactory odsFactory = OdsFactory.create(Logger.getLogger("misc"), Locale.US);
final AnonymousOdsFileWriter writer = odsFactory.createWriter();
final OdsDocument document = writer.document();
Then we create an outer and an inner table:
final Table outerTable = document.createTable("outer");
document.addTable(outerTable);
final TableCellWalker outerWalker = outerTable.getWalker();
outerWalker.setStringValue("I'm the outer table");
final Table innerTable = document.createTable("inner");
final TableCellWalker innerWalker = innerTable.getWalker();
innerWalker.setStringValue("I'm the inner table");
We do not add the inner table to the document as usual, but we place the inner table in the outer table:
outerTable.addShape(
DrawFrame.builder("embed", innerTable, SVGRectangle.cm(1, 1, 15, 10)).build());
The source code for this section is available here.
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", "j_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();
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 rs1 = 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("1", rs1).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("2", 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;
}
};
We pass the converter to the builder.
final ResultSetDataWrapperBuilder builder =
ResultSetDataWrapper.builder("3", rs3).converter(converter);
Note that the wrapper accepts type hints to improve the presentation.
Without hint, the columns 2 (the price) and 3 (the tax) are floats.
Let the column 2 be a currency
builder.typeValue(2, CellType.CURRENCY);
and the column 3 be a percentage.
builder.typeValue(3, CellType.PERCENTAGE);
To be complete, we let the column 0 be a string. It's useless because id has the type CHAR(12) and is
already converted to a string.
builder.typeValue(0, CellType.STRING);
Build the wrapper.
final ResultSetDataWrapper wrapper = builder.build();
Now, skip another row, then write the result:
walker.toRow(12);
walker.addData(wrapper);
} finally {
conn.close();
}
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')");
s.execute("CREATE TABLE document_repr (root TEXT, filename TEXT)");
s.execute("INSERT INTO document_repr VALUES ('<office:document-content>', " +
"'content.xml'), ('<office:document-styles>', 'styles.xml'), " +
"('<office:document-meta>', 'meta.xml'), ('<office:document-settings>', " +
"'settings.xml')");
} finally {
conn.close();
}
Then just use the raw exporter:
DatabaseExporter.exportDatabase(dataSource, document);
There is a command line version of DatabaseExporter. See this class for more
information.
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);
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.
FastODS can't provide support for every conceivable attribute. But you can add easily a custom attribute to a cell.
First, you may need to add a namespace to the content.xml file:
final Map<String, String> namespaceByPrefix = new HashMap<String, String>();
namespaceByPrefix.put("xmlns:myns", "http://myns.xyz/my/namespace");
final OdsFactory odsFactory = OdsFactory.builder(Logger.getLogger("misc"), Locale.US)
.addNamespaceByPrefix(namespaceByPrefix).build();
Now, create the document as usual:
final AnonymousOdsFileWriter writer = odsFactory.createWriter();
final OdsDocument document = writer.document();
final Table table = document.addTable("custom");
And add a cell.
final TableCellWalker walker = table.getWalker();
final TextStyle sourceStyle = TextStyle.builder("source").fontName(
LOFonts.LIBERATION_MONO).build();
walker.setText(Text.builder().par().span("Check custom attributes in the ")
.styledSpan("content.xml", sourceStyle).span(" file!").build());
Then you can add a custom attribute to the table:table, table:table-row,
table:table-column or table:table-cell tags.
table.setAttribute("myns:my-table-attr", "my-table-value");
walker.setRowAttribute("myns:my-row-attr", "my-row-value");
walker.setColumnAttribute("myns:my-col-attr", "my-col-value");
walker.setAttribute("myns:my-cell-attr", "my-cell-value");
Writing a custom cell may be interesting. All you have to do is to derive
AbstractTableCell and provide the implementation of appendXMLToTableRow:
walker.set(new AbstractTableCell() {
@Override
public void appendXMLToTableRow(final XMLUtil util, final Appendable appendable)
throws IOException {
appendable.append("<table:table-cell");
util.appendAttribute(appendable, "office:value-type", "string");
util.appendAttribute(appendable, "office:string-value", "A CUSTOM CELL");
appendable.append("/>");
}
});
The previous example might not be really impressive, because the attributes should be
part of the declared namespaces (xmlns:office, xmlns:ooo, ...). If you want to
use an external namespace, you have to declare it in the OdsFactory:
final Map<String, String> namespaceByPrefix = new HashMap<String, String>();
namespaceByPrefix.put("xmlns:myns", "http://myns.xyz/my/namespace");
final OdsFactory odsFactory = OdsFactory.builder(Logger.getLogger("misc"), Locale.US)
.addNamespaceByPrefix(namespaceByPrefix).build();
Now, create the writer, the document, the table and the walker as usual:
final AnonymousOdsFileWriter writer = odsFactory.createWriter();
final OdsDocument document = writer.document();
final Table table = document.addTable("rs");
final TableCellWalker walker = table.getWalker();
And add the attribute:
walker.set(new AbstractTableCell() {
@Override
public void appendXMLToTableRow(final XMLUtil util, final Appendable appendable)
throws IOException {
appendable.append("<table:table-cell");
util.appendAttribute(appendable, "office:value-type", "string");
util.appendAttribute(appendable, "office:string-value", "A custom cell");
util.appendAttribute(appendable, "myns:tag", "a tag value");
appendable.append("/>");
}
});
walker.nextRow();
The generated XML will be inserted as a child of the current table:row tag, hence you
are not limited to one cell:
walker.set(new AbstractTableCell() {
@Override
public void appendXMLToTableRow(final XMLUtil util, final Appendable appendable)
throws IOException {
appendable.append("<table:table-cell");
util.appendAttribute(appendable, "office:value-type", "string");
util.appendAttribute(appendable, "office:string-value", "First custom cell");
util.appendAttribute(appendable, "myns:tag", "a tag value");
appendable.append("/>");
appendable.append("<table:table-cell");
util.appendAttribute(appendable, "office:value-type", "string");
util.appendAttribute(appendable, "office:string-value", "Second custom cell");
util.appendAttribute(appendable, "myns:another-tag", "another tag value");
appendable.append("/>");
}
});
Two remarks:
- Use this with caution: if the XML is corrupted, your document will be unreadable.
- If you need something that might interest other users, please fill an issue or propose a pull request.
The source code for this section is available here.
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")
.columnWidth(SimpleLength.cm(3.5)).build();
for (int c = 0; c < 6; c++) {
table.setColumnStyle(c, tableColumnStyle);
table.setColumnDefaultCellStyle(c, tableCellStyle);
}
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));
}
}
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 =
TextStyle.builder("title").fontWeightBold().fontSize(SimpleLength.pt(24))
.build();
final TextStyle dedicationStyle =
TextStyle.builder("dedication").fontSize(SimpleLength.pt(8))
.fontStyleItalic().build();
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", "k_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 source code for this section is available here.
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");
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", rs).build());
Ok, that was the easy part, just to show once more how easy it is to write a ResultSet to a sheet.
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).build();
for (int c = 0; c < 18; c++) {
table.setColumnStyle(c, tableColumnStyle);
table.setColumnDefaultCellStyle(c, baseCellStyle);
}
final TableRowStyle tableRowStyle =
TableRowStyle.builder("ro2").rowHeight(CELL_SIZE).build();
We need some styles:
final TextStyle elementStyle =
TextStyle.builder("elementStyle").fontSize(SimpleLength.pt(6)).build();
final TextStyle atomicNumberStyle =
TextStyle.builder("atomicNumberStyle").fontSize(SimpleLength.pt(8)).build();
final TextStyle symbolStyle =
TextStyle.builder("symbolStyle").fontSize(SimpleLength.pt(12))
.fontWeightBold().build();
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);
It's almost over. We just need a footer and a header. It's a copycat from the previous section:
final TextStyle titleStyle = TextStyle.builder("title").fontWeightBold()
.fontSize(SimpleLength.pt(24)).build();
final TextStyle dedicationStyle =
TextStyle.builder("dedication").fontSize(SimpleLength.pt(8))
.fontStyleItalic().build();
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", "l_periodic_table.ods"));
Finally, the expected functions:
private static String resourceToString(final String resourceName) throws IOException {
final InputStream in = L_PeriodicTable.class.getClassLoader().getResourceAsStream(resourceName);
assert in != null : "Can't find resource "+resourceName;
final Reader reader = new InputStreamReader(in, CharsetUtil.UTF_8);
final char[] arr = new char[8 * 1024];
final StringBuilder sb = new StringBuilder();
int count = reader.read(arr, 0, arr.length);
while (count != -1) {
sb.append(arr, 0, count);
count = reader.read(arr, 0, arr.length);
}
return sb.toString();
}
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.
The source code for this section is available here.
There are basically two level of protections: protection against user clumsiness and protection against user curiosity or malice. The former requires a mechanism to prevent user from entering wrong data or modifying existing data, the latter requires encryption.
FastODS provides a fastods-crypto module (that has a dependency to Bouncy Castle APIs)
to encrypt an ODS file. This module is still in beta version.
For Maven users:
̀` <dependency> <groupId>com.github.jferard</groupId> <artifactId>fastods-crypto</artifactId> <version>0.7.4</version> </dependency> ̀`
Validation does not require fastods-crypto
When it comes to user input, it's usually a good idea to check the validity of the data.
In LibreOffice, we are accustomed to drop down lists, but the validation of OpenDocument may involve more complex formulas. Currently, FastODS implements drop down lists of "hard" values, that is values that are not part of the document but manually created.
Create the table:
final Table table = document.addTable("validation");
Here's our validation:
final Validation validation =
Validation.builder("val1").listCondition(Arrays.asList("foo", "bar", "baz"))
.build();
And how to add the validation to a cell.
final TableCellWalker walker = table.getWalker();
walker.setStringValue("A value");
walker.next();
walker.setValidation(validation);
That's all.
Protection does require fastods-crypto because JRE 6 doesn't provide a Base64
encoder.
LibreOffice provides the "Tools > Protect Sheet" option. This is only a protection against clumsiness, because the data is still stored in plain text. The protection is easy to bypass.
For a stronger protection, see next section.
Create the protected table:
final Table table = document.addTable("validation");
final char[] password = {'p', 'a', 's', 's', 'w', 'd'};
table.protect(ProtectionFactory.createSha256(password));
And add the content as usual:
final TableCellWalker walker = table.getWalker();
walker.setStringValue("Try to delete me!");
Encryption does require fastods-crypto.
In LibreOffice, you can protect a file with a password. This is the "Save with password" option in the Save As dialog box. Note that this has nothing to do with the "Tools > Protect Spreadsheet/Protect Sheet" options. The "Save with password" option really encrypts the data, while the "Protect Spreadsheet/Protect Sheet" options are easy to bypass.
The file encryption is regarded as robust: the key derivation function is PBKDF2 using HMAC-SHA-1 and 100,000 iterations; the encryption algorithm is AES256 (Cipher block chaining mode). To make a long story short: if you use a strong password and loose it,
First, we create a simple document, similar to the "Hello, world!" example:
final OdsFactory odsFactory = OdsFactory.create(Logger.getLogger("hello-world"), Locale.US);
final AnonymousOdsFileWriter writer = odsFactory.createWriter();
final OdsDocument document = writer.document();
final Table table = document.addTable("hello-world-crypto");
final TableRowImpl row = table.getRow(0);
final TableCell cell = row.getOrCreateCell(0);
cell.setStringValue("Hello, world!");
Second, we save it with encryption. This is easy: the module provides a class
ZipUTF8CryptoWriterBuilder that implements ZipUTF8WriterBuilder and encrypts the
data:
final char[] password = {'1', '2', '3'};
final ZipUTF8WriterBuilder builder = ZipUTF8CryptoWriterBuilder.create(password);
Note that this method automatically voids the password to clear sensitive data.
Once the builder is created, let's pass it to the saveAs method.
writer.saveAs(new File("generated_files", "m_hello_world_crypto_example.ods"),
builder);
(The password in this example is "123", but you'd better choose another password.)
The source code for this section is available here.
Imagine you have a database, a bunch of predefined group queries, and you want to write
the result of each group of queries in a new table of an ODS document. You take the first
group. You create the first table with the name of the group, and run the first query.
When the result is available, you add this result to the table (you know
ResultSetDataWrapper, don't you?). You run the second query, and wait again for the
result. When the first group of queries is run and all the results are added to the
table, then you continue with the second group. And so on.
At the end of the last result of the last group of queries, you launch a
writer.saveAs and the document is written on disk.
If you have already heard of concurrency, you might want to use a producer-consumer method. The producer queries the database, and when the result is available, puts this result in a queue. The consumer retrieves a result from the queue and writes it to the disk. Let's try.
Don't expect a full production code, this will be a simple sketch. Since I don't have a huge database to provide, I will generate random numbers. And I will use some kind of global variable to maintain the state of the producer.
Since the document needs to be written before it's fully created, we can't use FastODS usual mechanism to store the styles as they are used and write them at the end.
We need to create a "named" document
final OdsFactory odsFactory = OdsFactory.create(Logger.getLogger("bus"), Locale.US);
final NamedOdsFileWriter writer =
odsFactory.createWriter(new File("generated_files", "n_using_the_bus.ods"));
final NamedOdsDocument document = writer.document();
And to register the styles:
final TableCellStyle bold =
TableCellStyle.builder("bold").fontWeightBold().build();
document.addCellStyle(bold);
document.freezeStyles(); // any style used that was not registered will throw an error
First, we need an interface Element to wrap our objects:
private static interface Element {
void write();
}
(You may create regular classes that implement this interface, but I'll just use anonymous classes.)
Now, we create a simple bus:
final FastOdsBus<Element> bus = new FastOdsBus<Element>();
The consumer is not very hard to understand: take the next element and write it, until the bus is closed.
final Thread consumer = new Thread() {
@Override
public void run() {
try {
while (!bus.isClosed()) {
final Element element = bus.get(); // this method is blocking: we'll wait...
if (element == null) { // this is the flag.
break;
}
element.write();
}
writer.save();
writer.close();
} catch (final Exception e) {
/* you'll do something better here, like log the error */
}
}
};
We can start the consumer now.
consumer.start();
The producer is a bit more difficult. First, we need to keep track of the current table and cell. I do not recommend this, but I'll use a "global state":
private static class State {
public Table table;
public TableCellWalker walker;
}
Let's create the instance:
final State state = new State();
An we need random numbers.
final Random random = new Random();
Now, let's start:
for (int i = 0; i < 20; i++) {
final String tableName = "Group " + i;
bus.put(new Element() { // title element
@Override
public void write() throws IOException {
state.table = document.addTable(tableName);
state.walker = state.table.getWalker();
}
});
for (int j = 0; j < 10; j++) {
Our fake query
final String title = "> Query " + j;
final List<List<Integer>> intsRows = new ArrayList<List<Integer>>();
for (int r=0; r<25; r++) {
final List<Integer> ints = new ArrayList<Integer>(10);
for (int c = 0; c < 10; c++) {
ints.add(random.nextInt());
}
intsRows.add(ints);
}
Put the result:
bus.put(new Element() { // result element
@Override
public void write() throws IOException {
state.walker.setStringValue(title);
state.walker.setStyle(bold);
state.walker.nextRow();
state.walker.nextRow();
for (final List<Integer> row : intsRows) {
for (final Integer v : row) {
state.walker.setFloatValue(v);
state.walker.next();
}
state.walker.nextRow();
}
state.walker.nextRow();
}
});
}
}
Now we are done.
bus.put(null);
bus.close();
Just wait for the consumer to finish his job.
try {
consumer.join();
} catch (final InterruptedException e) {
/* log me... */
}
And that's all.