Bug 123923 - Exporting to XLSX makes Excel show error and drop pivot table, if table's data has Err:504 instead of #REF!
Summary: Exporting to XLSX makes Excel show error and drop pivot table, if table's dat...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
6.2.1.1 rc
Hardware: All All
: medium normal
Assignee: Mike Kaganski
URL:
Whiteboard: target:6.3.0 target:6.2.3
Keywords: dataLoss, filter:xlsx
Depends on:
Blocks: XLSX-Corrupted Pivot-Table-XLSX
  Show dependency treegraph
 
Reported: 2019-03-07 12:17 UTC by Mike Kaganski
Modified: 2019-04-30 13:42 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Exporting this to XLSX makes Excel to drop pivot table (9.14 KB, application/vnd.oasis.opendocument.spreadsheet)
2019-03-07 12:17 UTC, Mike Kaganski
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Mike Kaganski 2019-03-07 12:17:47 UTC
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
Comment 1 Mike Kaganski 2019-03-07 12:59:31 UTC
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.
Comment 2 Aron Budea 2019-03-07 13:24:18 UTC
(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. ;)
Comment 3 Mike Kaganski 2019-03-07 14:56:09 UTC
https://gerrit.libreoffice.org/68868
Comment 4 Commit Notification 2019-03-08 13:22:41 UTC
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.
Comment 5 Commit Notification 2019-03-16 23:08:49 UTC
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.