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: 6.2.2.1 (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
https://gerrit.libreoffice.org/68911
Mike Kaganski committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/+/44a524379548c916d304a71980f6911231768018%5E%21 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: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.