Value formatting via aggregates do not export to excel correctly in 5.4.0

[Austinb]

After some trial and error and looking at the exported XLSX code it appears the exported aggregate summary rows are not properly formatted when exported to excel via the exportToExcel command.

Easiest example would be using the built in currency formatters.

aggregates: [
            {
              formatter: 'integer',
              formatoptions: {
                thousandsSeparator: '',
              },
              label: 'Total',
              width: 120,
              align: 'right',
              aggregator: 'sum',
              summaryType: 'sum',
              member: 'total',
            },
            {
              formatter: 'currency',
              formatoptions: {
                decimalSeparator: '.',
                thousandsSeparator: ',',
                decimalPlaces: 5,
                prefix: '$',
                defaultValue: '0.00',
              },
              label: 'Revenue',
              width: 80,
              align: 'right',
              aggregator: 'sum',
              summaryType: 'sum',
              member: 'revenue',
            },
            ...
]

When exporting to excel using these aggregates the 'Revenue' column for the aggregate row is tagged as <c t="inlineStr" r="CX"> instead of the format <c r="EX" s="57"> which is for currency. All of the normal data rows before the aggregate sum rows show up properly. Issue also happens on columns using custom aggregator and summaryType functions to manipulate the cell's data.

Also using the default integer format with a defined thousandsSeparator or the default format causes the cell to be tagged as a string as well.

aggregates: [
            {
              formatter: 'integer',
              formatoptions: {
                thousandsSeparator: ',',
              },
              label: 'Total',
              width: 120,
              align: 'right',
              aggregator: 'sum',
              summaryType: 'sum',
              member: 'total',
            },
]

This same issue happens apparently only with small integers < 10. For the Total column any single digit numbers are also flagged as strings. I would assume that issue is related to the above.

Any help would be appreciated.

[tonytomov]

Thanks.

I will check with the demo provided from you

Kind Regards

[tonytomov]

The problem exists, but the fix is not trivial. In most cases the footer(header) column with a number can contain text like Total and etc. In this case applying the excel number formatter will cause bad output.

We need a good auto detect number parser for footer (header) row.

Will see what I can do.

[tonytomov]

I speak about summary footer(header) row

[tonytomov]

Hello,

I have do some fix on this. Can you please check if it is ok for you?
Thanks

[Austinb]

Sorry for the delayed test, been on other projects.

I got back to this and downloaded 5.5.0 via the download link on the main site. Exporting looks good for the most part except sometimes items are are percentages are not always marked as percentages in the exported sheet. It seems to be random from what I can tell. What is the excel_format supposed to be for a percentage field? I do not have one defined. The issue seems to affect any type of number, 0, 2-digit, etc... This is a custom formater to make the field into a %.

formatter: function (cellvalue, options, rowObject) {
    cellvalue = cellvalue || 0.00
    return `${parseFloat(cellvalue).toFixed(1)}%`
},

There is another issue with overriding the totals in 5.5.0 but that maybe a separate issue.

[Austinb]

I just tried using the currency formater and defining it to look like a percentage. It seems to work better but the same issue with random values being strings still persists.

formatter: 'currency',
formatoptions: {
    decimalSeparator: '.',
    thousandsSeparator: ',',
    decimalPlaces: 2,
    prefix: '',
    suffix: '%',
    defaultValue: '0.00',
},

Thanks for your help on this!

[Austinb]

I downloaded the version from github for 5.5.1 and it seems to be the same as above. It all works except the random percentage formatting issuer. Thanks again.