Created attachment 149786 [details] Exporting this to XLSX makes Excel to drop pivot table When a pivot table is based on data including a formula like this: > =COUNTIF(#REF!;0) then saving the spreadsheet to XLSX produces a file that MS Excel opens with error like > We found a problem with some content in `file.xlsx`. Do you want us to try to > recover as much as we can? If you trust the source of this workbook, click Yes. And confirming this drops the pivot table. The attachment contains a minimal reproducer. Tested with Version: 6.2.2.1 (x64) Build ID: fcd633fb1bf21b0a99c9acb3ad6e526437947b01 CPU threads: 12; OS: Windows 10.0; UI render: default; VCL: win; Locale: ru-RU (ru_RU); UI-Language: en-US Calc: CL
The problem here is that when the first parameter to COUNTIF is a #REF!, COUNTIF returns Err:504. That is written into the pivot table cache definition (xl/pivotCache/pivotCacheDefinition1.xml) into <e v="Err:504"> under <sharedItems> element, and then Excel fails to use the cache. The funny thing is, ECMA-376 Part 1, 18.10.1.27 e (Error Value), says: > v (Value) Specifies the value of the item. This attribute depends on how the > application records errors. > [Note: While the error values are determined by the application, > the following are some example error values that could be used: > · #DIV/0! > · #NAME? > · #VALUE! > · #NULL! > · #NUM! > · #REF! > · #N/A > · #GETTING_DATA > end note] > The possible values for this attribute are defined by the > ST_Xstring simple type (§22.9.2.19). So, the value is *application-defined*, and so it's unclear why would Excel fail on some error string here.
(In reply to Mike Kaganski from comment #1) > So, the value is *application-defined*, and so it's unclear why would Excel > fail on some error string here. Because... Excel. ;)
https://gerrit.libreoffice.org/68868
Mike Kaganski committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/+/1f970c20a25deed282dbaf08a4be5af25e4951db%5E%21 tdf#123923: make COUNTIF propagate errors in its first parameter It will be available in 6.3.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Mike Kaganski committed a patch related to this issue. It has been pushed to "libreoffice-6-2": https://git.libreoffice.org/core/+/0a23bfacc46cb7c66ceb1bbec972cdcfa9afa50b%5E%21 tdf#123923: make COUNTIF propagate errors in its first parameter It will be available in 6.2.3. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.