asked on July 24, 2023
Hi,
I am currently implementing the customize cell function to format the comma separator for Indian currency format like lakhs, crores etc. But exporting the same format in excel is causing the values to be treated as text. Which impacts doing some operations in excel like SUM etc.
Is there anyway to customize the cell without effecting the datatype when exporting to excel.
Any approach would be appreciated.
⋅ July 26, 2023
Hello, Subramanian!
Thank you for your question.
Please note that converting numbers to strings during export is required to preserve the format applied by the customizeCell function. To export the cells as numbers, you can use the exportTo function with useCustomizeCellForData: false export option. With this setting, the formatting of `customizeCell` will not be applied in the exported file:
pivot.exportTo("excel", {
useCustomizeCellForData: false
})
In addition, you can use the number formatting feature for other modifications, for example, changing the currency symbol. These formats would be preserved after export without changing the data type. However, adding comma separators required for the Indian currency is out of number formatting's functionality scope.
Our team prepared an example where the toolbar's Excel export handler is changed to disable customizeCell modifications: https://jsfiddle.net/flexmonster/y3oxn61L/
Please let us know if such an approach would work for you.
Best Regards,
Maksym
⋅ July 27, 2023
Hi Maksym,
Thank you for the reply. I have already implemented the approach in the given example. I was hoping we could format the number with commas while preserving the datatype. Please consider adding this functionality.
Regards
⋅ July 27, 2023
Hello, Subramanian!
Thank you for your feedback.
We agree that having more diverse settings of the number formatting would be helpful. Our team added this functionality to our customer wishlist. You will get a notification when the feature is released.
Feel free to contact us if any other questions arise.
Best Regards,
Maksym