Loading workbook in Vaadin Spreadsheet breaks Excel compatibility

[ianscriven]

Vaadin Spreadsheet seems to be breaking Excel compatibility for any sort of cell styling/formatting. See the following simple code snippet:

try (Workbook workbook = new XSSFWorkbook()) {
    Sheet sheet = workbook.createSheet();
    Row row = sheet.createRow(0);
    Cell cell = row.createCell(0);
    cell.setCellValue("Hello");
    Font font = workbook.createFont();
    font.setBold(true);
    CellUtil.setFont(cell, font);

    // this is the line that breaks it - removing it fixes the problem
    spreadsheet.setWorkbook(workbook);

    ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
    workbook.write(outputStream);
    
    // doing the following works the same way, for the same result
    // spreadsheet.write(outputStream);

    // nothing tricky here, just sends the file as a download
    view.provideDownload("test.xlsx", outputStream.toByteArray());
} catch (Exception e) {
    e.printStackTrace();
}

The above code simply creates a blank workbook, creates a single cell, and bolds that cell. If the workbook is downloaded at that point (i.e., before loading it into Vaadin Spreadsheet), it opens fine in Excel 2016.

However, after loading the workbook into the Vaadin Spreadsheet component, the downloaded workbook doesn't work properly. Upon opening Excel says it found a problem, and asks if it should attempt to fix it. "Fixing" the problem removes the bold styling from the cell (and all other styling, if there are other cells). The following message is provided (from Excel):

Removed Part: /xl/styles.xml part with XML error.  (Styles) Load error. Line 2, column 290.
Repaired Records: Cell information from /xl/worksheets/sheet1.xml part

The file: test.xlsx

Vaadin Spreadsheet is obviously doing something to stuff up the styling.

[WoozyG]

This is actually a bug with Apache POI. I think this could affect me too, so I tracked it down.

Vaadin is checking the "font family" when reading workbook styles, in order to create the closest match possible in CSS styles.

However, POI is automatically adding an empty family element when XSSFFont.getFamily() is called, if there are no families defined yet. This element does not have it's font family index property set, so when it is written out, it is an empty tag, which is invalid per the OOXML spec. It can be omitted entirely, or defined with a value, but not defined without a value.

Vaadin filed an issue already with POI:

https://bz.apache.org/bugzilla/show_bug.cgi?id=60184

Which I didn't see. I'm going to fix it, as I've tracked down where it really stems from.

[WoozyG]

This is now fixed in POI trunk, will be in the 3.17 release, most likely this summer, as well as nightlies starting tonight.