Decimal mark in wrong place when exporting to XLSX

[amiart]

When I export HTML table to XLSX, the numbers have decimal mark in wrong place. For example, the number 15,1 is exported as 151,00. The problem does not occur when exporting to CSV.

Here is my data: data.txt

And here is the exported XLSX file: data.xlsx

I use this configuration: { tableName: 'Raport przepływu', displayTableName: true, csvEnclosure: '"', csvSeparator: ';', csvUseBOM: true, mso: { fileFormat: 'xlsx', worksheetName: 'Raport przepływu' }, numbers: { html: { decimalMark: ',', thousandsSeparator: ' ' }, output: { decimalMark: ',', thousandsSeparator: '' } }, preventInjection: false }

[hhurz]

For XLSX format please try these options:

      {
        type: 'excel',
        tableName: 'Raport przepływu',
        displayTableName: true,
        mso: {
          fileFormat: 'xlsx',
          worksheetName: 'Raport przepływu',
          xslx: {
            formatId: {
              numbers: 4
            }
          }
        },
        numbers: {
          html: {
            decimalMark: ',',
            thousandsSeparator: ' '
          },
          output: {
            decimalMark: '.',
            thousandsSeparator: ''
          }
        }
      }

[amiart]

Thank you for a quick response.

The configuration you presented works for XLSX, but now when exporting to CSV I get a dot as a decimal mark instead of a comma.

I would also like to format numbers to one decimal place in one column and to two decimal places in another column. Would it be possible to add an option, e.g. mso.xslx.onCellFormat, in which we could return the custom format used for a given cell ?

The function could return xlsxformatid (number):

onCellFormat: function (cell, row, col) {return col == 0 ? 1: 2; }

... or custom format (string):

onCellFormat: function (cell, row, col) {return col == 0 ? '0.0': '0.00'; }

And the last thing is that when exporting to XLSX, the displayTableName parameter is ignored.

[amiart]

Hi, I've just tested the last change and number formatting works fine, but date formatting doesn't work. When I set:

xslx: {
    formatId: {
        date: function(cell, row, col) { return 'mmmm yyyy'; }
    }
}

the date function is not called at all.

I also noticed that when I specify the HTML date format:

date: {
    html: 'yyyy-mm-dd'
}

the text '2019-11-07' is not recognized as a date. And there is no option to specify how to parse dates with time like '2019-11-07 12:10:02.123'.

[hhurz]

Hi, thanks for testing. I think the issues that you reported should be fixed now. The displayTableName option is out dated. I documented it as deprecated. You are right: there is no option to specify how to parse DateTimes. I put this on the growing list of feature request... When further testing succeed, I'll publish a new release during the next days.

[amiart]

Now the export works correctly. Thank you for your help !