Vaadin Spreadsheet formatting/corruption issue.

Flow
1

In a nutshell consider the scenario:

  2. one creates a workbook in memory using POI and applies some cell styling (cell aligning and bold font)
  3. download the created workbook and workbook looks and behaves as it should in MS Excel
  4. instead of downloading the workbook we first build a vaadin Spreadsheet with said workbook in the constructor and display it in a page. Then without any modifications one requests the workbook object from the spreadsheet component and downloads it. Then MS Excel said that file has errors and after replying yes on trying to recover prompt you get the data without any formatting. Sample code follows:

[code]
XSSFWorkbook workbook = new XSSFWorkbook();

    CellStyle dateStyle = workbook.createCellStyle();
    DataFormat format = workbook.createDataFormat();
    dateStyle.setDataFormat(format.getFormat("yyyy-MM-dd HH:mm"));

    CellStyle boldAndCenteredCellStyle = workbook.createCellStyle();
    boldAndCenteredCellStyle.setAlignment(HorizontalAlignment.CENTER);
    
    Font font = workbook.createFont();
    font.setBold(true);        
    boldAndCenteredCellStyle.setFont(font);

    //filling the workbook here with data and apply only one cellstyle (the boldAndCenteredCesllStyle)

    Spreadsheet vaadinSpreadsheet = new Spreadsheet(workbook);

    XSSFWorkbook workbookFromVaadinSpreadsheet = vaadinSpreadsheet.getWorkbook();

[/code]workbook object works fine when downloaded and opened in excel.

workbookFromVaadinSpreadsheet is corrupt and ms excel can only open it after giving warning and strips all formatting out of it.

After looking into it it seems that what causes the issue is the font part

Font font = workbook.createFont(); font.setBold(true); boldAndCenteredCellStyle.setFont(font); if the font is not applie to the style everything works as it should. as soon as fonts are used then the whole thing breaks down.

I made sure that I use the latest Vaadin Spreadsheet for Vaadin 7 (1.3.1) and I use its own embedded POI (3.1.5) to avoid any conflicts.

I hope I am missing something and this is not a bug because this is one of the simplest tasks one should expect from the addon and even if that won’t work well what can one do with it?

It seems that we bump into some error in every single thing we use:

grid inline editing has issues so we use our own editing forms ( here https://github.com/vaadin/framework/issues/7882 ) here , lazyquerycontainer has issues with eclipselink and we only made it work properly with hibernate after modifying its code (instead of JPA compliant now its only hibernate compliant in our code but hey at least it works), google maps had scrolling issues (plus they don’t work with chinese version of the maps - and there was a bug with satellite view that got fixed) and we reimplemented our mapping needs with v-leaflet. Sorry for bringing all my dissapointment here but it seems that with most components we touch we stumble upon some serious issue that requires us going through the code and try to patch things up or find workarounds.

Regards,
George

2

To add more info:

Excel error message says

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

and the detailed log file contains

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>error147000_01.xml</logFileName><summary>Errors were detected in file 'C:\Users\gbougiakas\Downloads\AutomationData_20170331_151735.xlsx'</summary><removedParts summary="Following is a list of removed parts:"><removedPart>Removed Part: /xl/styles.xml part with XML error.  (Styles) Load error. Line 2, column 353.</removedPart></removedParts><repairedRecords summary="Following is a list of repairs:"><repairedRecord>Repaired Records: Cell information from /xl/worksheets/sheet1.xml part</repairedRecord></repairedRecords></recoveryLog>
3

Also original POI generated Excel file has colored fonts on some cells. These all come up black in Spreadsheet.

I think there is an issue with fonts styling in general.

4

Ok, I figured out what the bug is.

Let’s say you create a workbook using POI. This has one font in it by default.
When we create another font on the workbook using workbook.createFont(); a second font is added in the Workbook.

Now lets inspect those 2 fonts:

<xml-fragment xmlns:main="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <main:sz val="11.0"/>
  <main:color indexed="8"/>
  <main:name val="Calibri"/>
  <main:family val="2"/>
  <main:scheme val="minor"/>
</xml-fragment>


<xml-fragment xmlns:main="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <main:name val="ARIAL"/>
  <main:sz val="11.0"/>
  <main:b val="true"/>
</xml-fragment>

then we create a vaadin spreadsheet object by feeding it the workbook which we just created.

then lets do a vaadinSpreadsheet.getWorkbook() and inspect the 2 fonts again:

<xml-fragment xmlns:main="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <main:sz val="11.0"/>
  <main:color indexed="8"/>
  <main:name val="Calibri"/>
  <main:family val="2"/>
  <main:scheme val="minor"/>
</xml-fragment>


<xml-fragment xmlns:main="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <main:name val="ARIAL"/>
  <main:sz val="11.0"/>
  <main:b val="true"/>
  <main:family/>
</xml-fragment>

the only difference on the second font (the one we created) is the main:family/ tag that
lacks value, hence it is invalid
and Excel whines about it and strips all formatting xml from the file.

now Font class does not have an .setFamily() method but XSSFFont does.

Hence I wrote a static method that I use passing it my current workbook and a current cellstyle that will create a Font with family for me that will be rendered correctly!

[code]
public static XSSFFont getNewXSSFFont(XSSFWorkbook workbook, XSSFCellStyle cellStyle){
XSSFFont currentFont = workbook.getFontAt(cellStyle.getFontIndex());
int currentFontFamily = currentFont.getFamily();

    XSSFFont newFont = workbook.createFont();
    newFont.setFamily(currentFontFamily);
    
    return newFont;
}

[/code]So that’s what the bug is

today I learned to appreciate the value of family (pun intended).