-
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):
<dependency>
<groupId>com.github.jferard</groupId>
<artifactId>fastods</artifactId>
<version>0.6.1</version>
</dependency>
For other build tools, look at: https://search.maven.org/artifact/com.github.jferard/fastods/0.6.1/jar
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 TableRow 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.
Now, we want to write values in other cells that the A1 cell.
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 table1 = document.addTable("direct-access");
Get the first row and the first cell of the first row:
TableRow row = table1.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. You can have direct access to any cell, but it has a cost. If the row doesn't exist yet in the list of rows, it will create all rows between the last row and the actual row.
row = table1.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(table1, 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(table1, "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:
try {
tableHelper.setCellValue(table1, "D4", CellValue.fromObject("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 table2 = document.addTable("relative-access");
We want ten rows of data
for (int r = 0; r < 10; r++) {
The Table object has an internal row index (that is updated by the getRow method).
Just call nextRow to make the index advance by one (you have to call nextRow
before you write data, to get the first row):
final TableRow tableRow = table2.nextRow();
And then create a "walker" for this row:
final TableCellWalker cellWalker = tableRow.getWalker();
Now, we want nine columns for each row:
for (int c = 0; c < 9; c++) {
Add the value to each cell
cellWalker.setStringValue(String.valueOf((char) (c + 'A')) + String.valueOf(r + 1));
And then push one cell right.
cellWalker.next();
}
}
And save the file.
writer.saveAs(new File("generated_files", "b_accessing_example.ods"));
Note 1: There is a slight inconsistency between table.newtRow (before using
the row) and cellWalker.next (after using the cell). Maybe I'll fix it before
version 1.0...
Note 2: We will see how to merge cells in the Advanced part of this tutorial.
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");
We add a header:
TableRow tableRow = table.nextRow();
TableCellWalker cellWalker = tableRow.getWalker();
cellWalker.setStringValue("Type");
cellWalker.next();
cellWalker.setStringValue("Example");
The first row contains a boolean:
tableRow = table.nextRow();
cellWalker = tableRow.getWalker();
cellWalker.setStringValue("Boolean");
cellWalker.next();
cellWalker.setBooleanValue(true);
The second row contains a currency:
tableRow = table.nextRow();
cellWalker = tableRow.getWalker();
cellWalker.setStringValue("Currency");
cellWalker.next();
cellWalker.setCurrencyValue(10.5, "USD");
The third row contains a date:
tableRow = table.nextRow();
cellWalker = tableRow.getWalker();
cellWalker.setStringValue("Date");
cellWalker.next();
cellWalker.setDateValue(new GregorianCalendar(2014, 9, 17, 9, 0, 0));
The fourth row contains a float:
tableRow = table.nextRow();
cellWalker = tableRow.getWalker();
cellWalker.setStringValue("Float");
cellWalker.next();
cellWalker.setFloatValue(3.14159);
The fifth row contains a percentage:
tableRow = table.nextRow();
cellWalker = tableRow.getWalker();
cellWalker.setStringValue("Percentage");
cellWalker.next();
cellWalker.setPercentageValue(0.545);
The sixth row contains...
tableRow = table.nextRow();
cellWalker = tableRow.getWalker();
cellWalker.setStringValue("String");
cellWalker.next();
cellWalker.setStringValue("A String");
The seventh row contains a time (that mean a duration):
tableRow = table.nextRow();
cellWalker = tableRow.getWalker();
cellWalker.setStringValue("Time");
cellWalker.next();
cellWalker.setTimeValue(3600);
The eighth row contains nothing
tableRow = table.nextRow();
cellWalker = tableRow.getWalker();
cellWalker.setStringValue("Void");
cellWalker.next();
cellWalker.setVoidValue();
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.<Object>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(3600), "A String", null);
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...
We skip a row for readability:
table.nextRow();
Now, we can use setValue to take advantage of the type guess:
for (int r = 0; r < A.size(); r++) {
tableRow = table.nextRow();
cellWalker = tableRow.getWalker();
cellWalker.setStringValue(A.get(r));
cellWalker.next();
cellWalker.setCellValue(CellValue.fromObject(B.get(r)));
}
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.
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
TableRow tableRow = table.nextRow();
TableCellWalker cellWalker = tableRow.getWalker();
Standard format:
cellWalker.setFloatValue(123456.789);
And now create a custom data style:
final DataStyle floatDataStyle = new FloatStyleBuilder("float-datastyle", Locale.US)
.decimalPlaces(8).groupThousands(true).build();
cellWalker.next();
cellWalker.setFloatValue(123456.789);
cellWalker.setDataStyle(floatDataStyle);
We can do the same with dates:
tableRow = table.nextRow();
cellWalker = tableRow.getWalker();
A date with the standard format:
final Calendar cal = new GregorianCalendar(2018, 1, 1, 0, 0, 0);
cellWalker.setDateValue(cal);
And a custom format:
final DataStyle dateDataStyle = new DateStyleBuilder("date-datastyle", Locale.US)
.dateFormat(new DateStyleFormat(DateStyleFormat.DAY, DateStyleFormat.DOT,
DateStyleFormat.MONTH, DateStyleFormat.DOT, DateStyleFormat.YEAR)).visible()
.build();
cellWalker.next();
cellWalker.setDateValue(cal);
cellWalker.setDataStyle(dateDataStyle);
A last try with a time (duration):
tableRow = table.nextRow();
cellWalker = tableRow.getWalker();
cellWalker.setTimeValue(10000000);
And:
final DataStyle timeDataStyle = new TimeStyleBuilder("time-datastyle", Locale.US)
.timeFormat(new DateStyleFormat(DateStyleFormat.text("Hour: "),
DateStyleFormat.LONG_HOURS)).visible().build();
cellWalker.next();
cellWalker.setTimeValue(10000000);
cellWalker.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 DateStyleFormat(DateStyleFormat.LONG_DAY, DateStyleFormat.SLASH,
DateStyleFormat.LONG_MONTH, DateStyleFormat.SLASH,
DateStyleFormat.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 TableRow tableRow = table.nextRow();
final TableCellWalker cellWalker = tableRow.getWalker();
cellWalker.setFloatValue(123456.789);
final Calendar cal = new GregorianCalendar(2018, 1, 1, 0, 0, 0);
cellWalker.next();
cellWalker.setDateValue(cal);
And save the file.
writer.saveAs(new File("generated_files", "d_data_style2.ods"));
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");
TableRow tableRow = table.nextRow();
TableCellWalker cellWalker = tableRow.getWalker();
Now, we add a value and set the style
cellWalker.setStringValue("A1");
cellWalker.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();
cellWalker.next();
cellWalker.setStringValue("A2");
cellWalker.setStyle(grayStyle);
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();
cellWalker.next();
cellWalker.setStringValue("A3");
cellWalker.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, BorderAttribute.Style.OUTSET).build();
cellWalker.next();
cellWalker.setStringValue("A4");
cellWalker.setStyle(borderStyle);
I think you get it now.
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();
tableRow = table.nextRow();
tableRow.setStyle(tallRowStyle);
cellWalker = tableRow.getWalker();
cellWalker.setStringValue("B1");
cellWalker.setStyle(borderStyle);
You have to set the height of the row manually. There's nothing like an Optimal height/width in the OpenDocument specification, and FastODS won't provide those features. (Maybe one day I'll write a tool to compute the width/height of a text.) You can also add a column style:
final TableColumnStyle wideColumn = TableColumnStyle.builder("wide-col")
.columnWidth(SimpleLength.cm(9)).build();
table.setColumnStyle(0, wideColumn);
Obvioulsy, you can combine a style and a data style:
final DataStyle timeDataStyle = new TimeStyleBuilder("time-datastyle", Locale.US)
.timeFormat(new DateStyleFormat(DateStyleFormat.text("Hour: "),
DateStyleFormat.LONG_HOURS)).visible().build();
tableRow = table.nextRow();
cellWalker = tableRow.getWalker();
cellWalker.setTimeValue(10000000);
cellWalker.setStyle(rotateStyle);
cellWalker.setDataStyle(timeDataStyle);
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:
TableRow row = table.nextRow();
TableCellWalker walker = row.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)");
row = table.nextRow();
walker = row.getWalker();
walker.setStringValue("B1 (covered)");
walker.next();
walker.setStringValue("B2 (covered)");
walker.next();
walker.setStringValue("B3 (covered)");
walker.next();
walker.setStringValue("B4 (not covered)");
row = table.nextRow();
walker = row.getWalker();
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:
If you split the A1 cell:
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 = row.getWalker();
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 can be absolute or relative. For instance, an absolute Link may be an absolute URL:
walker = row.getWalker();
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 = row.getWalker();
walker.setStringValue("A Cell with a tooltip");
walker.setTooltip("The Tooltip");
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:
Table table = document.addTable("format-page");
TableRow row = table.getRow(0);
TableCellWalker walker = row.getWalker();
walker.setStringValue("Text");
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:
PageStyle pageStyle = PageStyle.builder("page-style").header(header).footer(footer)
.build();
And add the page style into the table style:
TableStyle tableStyle = TableStyle.builder("table-style").pageStyle(pageStyle)
.build();
And set this table style:
table.setStyle(tableStyle);
Create another table:
table = document.addTable("format-page2");
row = table.getRow(0);
walker = row.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:
pageStyle = PageStyle.builder("page-style2").header(minimalHeader).footer(complexFooter).build();
tableStyle = TableStyle.builder("table-style2").pageStyle(pageStyle)
.build();
table.setStyle(tableStyle);
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", "h_named_advanced.ods"));
Then, get the document and the tables as usual.
final NamedOdsDocument document = writer.document();
final Table table = document.addTable("advanced");
You have to register all the styles now:
final TableCellStyle boldCellStyle = TableCellStyle.builder("cell").fontWeightBold()
.fontSize(SimpleLength.pt(24)).build();
document.addObjectStyle(boldCellStyle);
And, if necessary:
document.addPageStyle(aPageStyle);
document.addObjectStyle(aTableStyle);
document.addObjectStyle(aTableRowStyle);
document.addObjectStyle(aTableColumnStyle);
document.addObjectStyle(aTableCellStyle);
document.addObjectStyle(aTextStyle);
An now, you can fill the Spreadsheet as usual.
final TableRow row = table.getRow(0);
final TableCellWalker walker = row.getWalker();
walker.setStringValue("A huge document");
walker.setStyle(boldCellStyle);
When you're finished:
document.save();
We need a ResultSet. Let's use SQLite:
final SQLiteDataSource dataSource = new SQLiteDataSource();
dataSource.setUrl("jdbc:sqlite::memory:");
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:
table.addData(ResultSetDataWrapper.builder(rs).build());
It's possible to add multiple ResultSets:
table.nextRow();
final ResultSet rs2 = s.executeQuery("SELECT * FROM document WHERE LENGTH(file_type) > 7");
table.addData(ResultSetDataWrapper.builder(rs2).build());
FastODS uses the type guess to determine the type of objects.
} finally {
conn.close();
}
If you know what you are doing, you can play with LO settings, for instance:
table.setSettings("View1", "ZoomValue", "150");
For more doc, see:
- [Settings Service Reference](https://api.libreoffice .org/docs/idl/ref/servicecom_1_1sun_1_1star_1_1document_1_1Settings.html)
- [ViewSettings Service Reference](https://api.libreoffice .org/docs/idl/ref/servicecom_1_1sun_1_1star_1_1view_1_1ViewSettings.html)
- [SpreadsheetViewSettings Service Reference](https://api.libreoffice .org/docs/idl/ref/servicecom_1_1sun_1_1star_1_1sheet_1_1SpreadsheetViewSettings.html)