- changed status to open
Excel export does not keep number formatting
Exporting a table to excel does not export the percentage formatting. See attached picture for details.
Comments (9)
-
-
- changed component to Widget - Excel Import/Export
-
- changed title to Excel export does not keep number formatting
-
Looking at the screenshot it isn’t just the percentage format. The other price fields have also lost the thousand separator and no longer in #,##0.00 style. Although none of the numbers are unformatted and seem to have 0.00 format.
@Radu Cantor can you check what number formatting is set in the Excel file? It’s possible that the format set in TM1 or UX isn’t recognized by Excel, or only a part of it is.
No commitment on priority, the most important is that values are correct. Reformatting is always possible in Excel.
-
reporter The number format is defined in UX as 0.00.
"exportToExcelNumberFormat": "0.00"
If I leave this empty, then the export will show the percentages as blanks or in wrong format. See the attached picture
-
The number format is defined in UX as 0.00.
"exportToExcelNumberFormat": "0.00"
OK so now we are getting somewhere. In the first example you are actually seeing the expected behaviour as whatever number formatting would normally be applied is overwritten for all columns by the exportToExcelNumberFormat setting. So it’s correct that all cells have 0.00 formatting.
In the 2nd example where you remove this setting it looks like the format applied to all columns is “#,##0”. Which doesn’t seem to be the format applied to any of the columns in UX. Does the row dimension or any of the filter dimensions have this formatting?
-
reporter The column dimensions have the followign format attribute: #,##0.00;(#,##0.00) for the first two columns and 0.00% for the last one. This is how the table is displayed in the browser in Apliqo UX.
My expectation is that the Excel export will match the format shown in the browser.
The reason why I did set the "exportToExcelNumberFormat": "0.00" was because if I leave it empty the percentages would have an unusable format.
Does this issue have something to do with the config option in default.js which defines: "exportToExcelNumberFormat" : "#,###;(#,###);0;-", ? Any reason why Apliqo UX puts a default number format in config?
-
reporter - changed status to resolved
If i leave both "exportToExcelNumberFormat" in default.js and in settings empty, then the export behaves as expected.
I don’t think this is a product issue, but rather some confusion because of the various parameters in the configuration.
-
I would have expected the exportToExcelNumberFormat from the default constants file to be empty by default. Since usually you wouldn’t want the number format being overwritten by some arbitrary default format.
- Log in to comment
Hi Radu, is the Percentage an inserted column?