Bug 123939 - Excel emits content warning opening LO-generated XLSX, when pivot table references columns with only strings and errors
Summary: Excel emits content warning opening LO-generated XLSX, when pivot table refer...
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) rc
Hardware: All All
: medium normal
Assignee: Mike Kaganski
Whiteboard: target:6.3.0
Keywords: dataLoss, filter:xlsx
Depends on:
Blocks: XLSX-Corrupted Pivot-Table-XLSX
  Show dependency treegraph
Reported: 2019-03-08 10:17 UTC by Mike Kaganski
Modified: 2019-04-30 13:42 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:

Pivot table references a column with a string and an error (9.20 KB, application/vnd.oasis.opendocument.spreadsheet)
2019-03-08 10:17 UTC, Mike Kaganski

Note You need to log in before you can comment on or make changes to this bug.
Description Mike Kaganski 2019-03-08 10:17:28 UTC
Created attachment 149815 [details]
Pivot table references a column with a string and an error

If a column only contains strings *and* errors, and is referenced in a pivot table, then XLSX generated from such a spreadsheet emits a warning when opened with Excel:

> 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.

This is because LibreOffice writes containsMixedTypes="1" attribute to corresponding sharedItems element in the pivot cache definition. But Excel treats string and error as the same data type, so in this case, the attribute should be omitted.

Saving attached sample to XLSX would produce such a document giving warning when opened with Excel. It contains a 3-column 2-row sample data, first column of it has one string and one formula giving an error (=NA()). All three columns are referenced in the pivot table on the second sheet (but only two other are used).

Tested with Version: (x64)
Build ID: fcd633fb1bf21b0a99c9acb3ad6e526437947b01
CPU threads: 12; OS: Windows 10.0; UI render: GL; VCL: win; 
Locale: ru-RU (ru_RU); UI-Language: en-US
Calc: CL
Comment 1 Mike Kaganski 2019-03-08 11:02:37 UTC
Comment 2 Commit Notification 2019-03-08 13:22:51 UTC
Mike Kaganski committed a patch related to this issue.
It has been pushed to "master":


tdf#123939: string and error are same type for pivot cache in XLSX

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:

Affected users are encouraged to test the fix and report feedback.