🍉 Meet Flexmonster Pivot Table & Charts 2.9.Check out all the hot features!
Get Free Trial

Excel exporting 0 as blank

Benjamin Kidder asked on January 9, 2017

When using a custom format that replaces blank/null values with “0”, the export to Excel feature exports them as “blank” – how can I ensure that the formatting on the Excel export matches what is displayed in the Pivot Grid?

3 answers

Iryna Kulchytska Iryna Kulchytska Flexmonster January 10, 2017

Hello Benjamin,
Thank you for your question.
We cannot reproduce the blank cells in exported Excel with the newest 2.306 version of the component if nullValue is set to “0” in a custom format, as follows:

"formats": [
    "name": "",
    "thousandsSeparator": " ",
    "decimalSeparator": ".",
    "currencySymbol": "",
    "currencySymbolAlign": "left",
    "nullValue": "0",
    "textAlign": "right",
    "isPercent": false

Could you please let me know the version of the component you are using – click on the grid and press Ctrl+Alt+i?
Kind regards,

Benjamin Kidder January 10, 2017

Hi Iryna,
I’m using version Version 2.3 (build 28/10/2016 22:34:05), and I think the problem only occurs with OLAP connections. To recreate, I’ve used a dimension hierarchy in the rows that has some nulls for measures midway down the hierarchy. When you expand all, the child members of that hierarchy display as 0 in the pivot grid on the web site, but as blank in the Excel export.
I’ve tried to recreate this issue using the demo you have up on the website, but it appears to function correctly there – I’ll try downloading the newer subversion and see if that fixes it!

Dmytro Zvazhii Dmytro Zvazhii Flexmonster January 23, 2017

Hello Ben,
How is everything going?  Does the last version work correctly for you?

Please login or Register to Submit Answer