Excel export does not use format strings in localisation

Support
#1

Hi

Excel export defines arbitrary and hard-coded type formats, ignoring format strings in configuration.

6.10.9 ExcelExporter.java

protected void createFormats() {
    timeFormatCellStyle = wb.createCellStyle();
    timeFormatCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("h:mm"));

    dateFormatCellStyle = wb.createCellStyle();
    dateFormatCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));

    dateTimeFormatCellStyle = wb.createCellStyle();
    dateTimeFormatCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));

    integerFormatCellStyle = wb.createCellStyle();
    integerFormatCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0"));

    DataFormat format = wb.createDataFormat();
    doubleFormatCellStyle = wb.createCellStyle();
    doubleFormatCellStyle.setDataFormat(format.getFormat("#,##0.################"));
}

Which in addition produces ugly results for decimals.

image

Best Regards
Michael

#3

Hello @michael.renaud

Thank you for reporting the problem.

I’ve created an issue: GitHub.

Regards,
Daniil

#5

Hi @michael.renaud

Could you clarify what docs software do you use and which locale is used? Unfortunately cannot reproduce the problem.

Please share a sample project if possible.

Regards,
Daniil

#6

Hi @tsarev

I’m using French locale but this is precisely the point, ExcelExporter does not care of the locale.

You can change locale or double format string in the app that will have no effect.

Look at the “1000,” figure below, this is a double without decimals, corresponding to the hard coded format string in ExcelExporter.

image

Very easy to reproduce, see project attached.

xlexport.zip (80.6 KB)

#7

I’m talking about office suite (MS Office / Libre Office) locale settings.

I’ve tested the issue in LibreOffice 6 with French locale, and values are ok:

image

Could you clarify what office suite do you use?

#8

MS Excel with French locale

#9

Unfortunately I cannot reproduce the problem.

I suggest you to replace default ExcelAction with your own implementation and use custom ExcelExporter there.

#10

Did you try with MS Excel ? And did you try mixing amounts with decimals and without.

In fact if you look at the screenshot below there are 2 issues:

  • the useless comma at the end of the amount without decimal “1000”
  • hard coded format that is not consistent among cells for decimals

The format yielded in Excel export is a custom one, created in createFormats() method

DataForma format = wb.createDataFormat();
doubleFormatCellStyle = wb.createCellStyle();
doubleFormatCellStyle.setDataFormat(format.getFormat("#,##0.################"));

image

This is weird anyway because this format requires displaying a comma even if there is no decimals. LibreOffice seems to fix that on the fly whereas Excel does exactly what it was required to do, that’s design choice on their end.

image

Moreover, putting aside the comma in excess, this custom format makes Excel behaves like its “standard” cell format (by default) which displays numbers without decimals as integers, and others with decimals. No need to define a custom format then.

In our case we need fixed precision in display (much more readable), which is easy to get in CUBA app, but not in Excel export.

image

image

Which is equivalent to:

image

This is what we have done, using format strings defined in CUBA configuration, I think having this behavior by default would be much more consistent for the platform.

  @Override
    protected void createFormats() {
        super.createFormats();
        String sformat = messages.getMainMessage("timeFormat");
        timeFormatCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(sformat));
        sformat = messages.getMainMessage("dateFormat");
        dateFormatCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(sformat));
        sformat = messages.getMainMessage("dateTimeFormat");
        dateTimeFormatCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(sformat));
        sformat = messages.getMainMessage("integerFormat");
        integerFormatCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(sformat));
        DataFormat format = wb.createDataFormat();
        sformat = messages.getMainMessage("doubleFormat");
        doubleFormatCellStyle.setDataFormat(format.getFormat(sformat));
    }

Michael