Formatting cells with Apache ODF Toolkit Simple API and Java

Question

I need to create a ods document using the Apache ODF Toolking and to format the content of its cells.

I was able to set a format for dates and simple numbers, but for some reason when I try to format with scientific notation it does not work. It fails to parse the string or it wraps the E00 into "E00"

SpreadsheetDocument document = SpreadsheetDocument.newSpreadsheetDocument();
    document.removeSheet(0);

    document.setLocale(Locale.US);

    Table sheet = document.appendSheet("My chart");
    List<Row> rows = sheet.appendRows(10);

    sheet.getColumnByIndex(0).setWidth(27.06);
    Row headerRow = rows.get(0);
    headerRow.getCellByIndex(0).setStringValue("This is a string");

    Row dateRow = rows.get(1);

    Calendar date = GregorianCalendar.getInstance();
    dateRow.getCellByIndex(0).setDateValue(date);
    dateRow.getCellByIndex(0).setFormatString("yyyy-MM-dd");

    Row numRow = rows.get(1);
    numRow.getCellByIndex(0).setDoubleValue(9.12345678);

    //numRow.getCellByIndex(0).setFormatString("0.000"); // works
    //numRow.getCellByIndex(0).setFormatString("0.00E+00"); // crashes
    numRow.getCellByIndex(0).setFormatString("0.00E00"); // does not work, it becomes 0.00"E00"

    document.save(new File("c:\\Users\\enrico\\Desktop\\openoffice.ods"));

I'd really appreciate if anyone could help, thanks.

Full code here https://gist.github.com/EnricoScantamburlo/b06d3a9e52682276b8ca4f6bf51e6f6a

What happens if you use `"0.###E0"` as format string? – IQV Feb 12 '18 at 13:38 — IQV, Feb 12 '18 at 13:38
it becomes 0.000"E0" when I open the file – Enrico Scantamburlo Feb 12 '18 at 15:02 — Enrico Scantamburlo, Feb 12 '18 at 15:02

score 2 · Accepted Answer · answered Feb 13 '18 at 12:58

Basically what you want to do is not supported by Apache ODF Toolkit (as of version 0.8.2). To be able to use Scientific Number formats in .ods files the underlying library (in your case Apache ODF Tool) should be able to produce the <number:scientific-number> number style. For more information on Scientific Number styling see http://docs.oasis-open.org/office/v1.1/OS/OpenDocument-v1.1-html/OpenDocument-v1.1.html#14.7.1.Number%20Style|outline.

Seems Apache ODF Toolkit treats the E0 as a currency or other special symbol, and instead of generating something like

<number:number-style style:name="N117">
    <number:scientific-number number:decimal-places="2" number:min-exponent-digits="2" number:min-integer-digits="1">
    </number:scientific-number>
</number:number-style>

it just generates this

<number:number-style style:name="n3cf821">
    <number:number number:decimal-places="2" number:min-integer-digits="1"></number:number>
    <number:text>E0</number:text>
</number:number-style>

Good news, Apache ODF Toolkit is opensource so you can contribute the fix, which will be fairly easy, just fix OdfNumberStyle.buildFromFormat.

Formatting cells with Apache ODF Toolkit Simple API and Java

1 Answers1