Get Free Trial

Excel export not capturing data format

Answered
raviteja asked on September 20, 2022

Hi Team,
I have formatted number with thousand separator as comma(,) , but export excel not seperated with comma(,).
Please find attachment
 
Thanks,
Ravi
 

Attachments:
export_excel.PNG

5 answers

Public
Maksym Diachenko Maksym Diachenko Flexmonster September 20, 2022

Hello, Ravi!

Thank you for writing to us.
Please note that a thousand separators are taken from your system’s settings.
Check out Microsoft’s guide on changing the formats for different data types.

Best Regards,
Maksym 

Public
raviteja September 21, 2022

Hi Maksym,
 
I tried changing formats and I didn’t see any changes on Excel.
I understood, By default thousand separators are taken from windows settings but here I am doing some number formatting in flexmonster and it is not reflecting in export Excel.
I tried thousand separator with comma(,) dot(.) and space ( ) but nothing came out on export Excel.
Same thing works well other exports(HTML,CSV,PDF,Image).
It could be great if u give some work around for my issue.
 
Thanks,
Ravi

Public
Maksym Diachenko Maksym Diachenko Flexmonster September 22, 2022

Hello, Ravi!

Thank you for sharing more details with us.

In case changing the system’s separator does not affect the exported file – we recommend setting the decimalPlaces inside the format object. This parameter is an exact number of decimals to show after the decimal separator. For the case of integer measures like “Count”, this value can be set as zero. You are welcome to check the JSFiddle example: https://jsfiddle.net/flexmonster/4rn28tz5/. In this example, you should be able to see the separator from your system’s settings after exporting the pivot to Excel.

We understand that you would like to keep the Flexmonster’s separator after export, and this requirement sounds reasonable. However, Microsoft Excel uses the system separators that are defined in the regional settings in Control Panel. This is the default setting applied to all .xlsx files opened in Excel. If needed, you can check out Microsoft’s guide about changing separators in Excel.

Please let us know in case of any other questions.

Best Regards,
Maksym

Public
raviteja 6 days ago

Hi Maksym,
Thank you for the response.
I tried format the object by setting decimalPlaces to 0 then Excel export is working fine.
Any positive number for decimalPlaces is working fine for export excel.
But I am missing my decimal numbers. https://jsfiddle.net/flexmonster/4rn28tz5/ In this example also just try adding some decimal values count (2222.03454) and in formats set decimalPlaces to 0 and export works where as if u keep decimalPlaces to -1 then we can see count with decimal but export wont work. Kindly check.
 
Thanks,
Ravi
 
 

Public
Maksym Diachenko Maksym Diachenko Flexmonster 6 days ago

Hello, Ravi!

Thank you for the reply. 

Kindly note that Flexmonster number formatting rules are a bit different from Excel. In the described scenario, the Excel number format patterns require setting the decimal places as greater or equal to zero. As a result, number formatting with thousand separators can not be combined with showing the number without an exact decimal places value.

A possible solution is setting the decimalPlaces as a positive number. This will round the numbers to the specified precision or append zeros to the number part after the point. Please see different cases of number behavior with decimalPlaces: 2 on this example: https://jsfiddle.net/flexmonster/omhsb5x8/ 

Our team hopes that this solution will work for you.

Best Regards,
Maksym

Please login or Register to Submit Answer