Download it now!
Bug 108673 - FILESAVE XLSX: Copy-pasting cell validation may result in a sheet reference error saved as invalid xlsx content
Summary: FILESAVE XLSX: Copy-pasting cell validation may result in a sheet reference e...
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.4.0.0.beta2
Hardware: All All
: medium normal
Assignee: Serge Krot (CIB)
URL:
Whiteboard: target:7.1.0 target:7.0.2 target:6.4.7
Keywords: filter:xlsx
Depends on:
Blocks: XLSX-Corrupted XLSX Cell-Reference
  Show dependency treegraph
 
Reported: 2017-06-21 12:24 UTC by Gabor Kelemen
Modified: 2020-09-11 14:24 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:


Attachments
Example source file (9.64 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-06-21 12:24 UTC, Gabor Kelemen
Details
Cells with validation pasted to the first sheet (9.08 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-06-21 12:27 UTC, Gabor Kelemen
Details
Cells with validation pasted to the first sheet - in XLSX format (4.77 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2017-06-21 12:27 UTC, Gabor Kelemen
Details
Original cell and its pasted version in a new file (116.61 KB, image/png)
2017-06-21 12:28 UTC, Gabor Kelemen
Details
Excel 2013 reacts with an error to opening the attached XLSX (47.62 KB, image/png)
2017-06-21 12:29 UTC, Gabor Kelemen
Details
Copied cell validation saved by Excel 2013 (8.08 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2020-07-13 06:32 UTC, NISZ LibreOffice Team
Details
Copied cell validation saved by Excel 2016 (8.76 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2020-07-13 12:43 UTC, NISZ LibreOffice Team
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Gabor Kelemen 2017-06-21 12:24:23 UTC
Created attachment 134188 [details]
Example source file

Attached file has two sheets, with cell validation in the B1 cells.

When copy-pasting the B1 cell from both sheets to the firsd sheet of a new spreadsheet document, the sheet reference part of the validation source becomes a #REF! error: $Sheet2.$A$1:$A$3 becomes $#REF!.$A$1:$A$3

This may make some sense in itself, as there is no second sheet in the new document. Adding a new sheet and pasting there gives correct results.

The main problem is that the validation source with #REF! error can be saved to both ODS and XLSX format, and in the case of XLSX, Excel 2013 considers it an invalid file.
Comment 1 Gabor Kelemen 2017-06-21 12:27:12 UTC
Created attachment 134189 [details]
Cells with validation pasted to the first sheet

In A2 we can see the #REF! error.
Comment 2 Gabor Kelemen 2017-06-21 12:27:40 UTC
Created attachment 134190 [details]
Cells with validation pasted to the first sheet - in XLSX format
Comment 3 Gabor Kelemen 2017-06-21 12:28:31 UTC
Created attachment 134191 [details]
Original cell and its pasted version in a new file
Comment 4 Gabor Kelemen 2017-06-21 12:29:05 UTC
Created attachment 134192 [details]
Excel 2013 reacts with an error to opening the attached XLSX
Comment 5 Buovjaga 2017-07-01 18:33:04 UTC
Repro.

Win 8.1 32-bit
MSO 2013
LibreOffice Version: 6.0.0.0.alpha0+
Build ID: cac5c9f6081590b0548d3116bc4cd4a2509ec576
CPU threads: 4; OS: Windows 6.29; UI render: default; 
TinderBox: Win-x86@42, Branch:master, Time: 2017-07-01_00:41:48
Locale: fi-FI (fi_FI); Calc: group
Comment 6 QA Administrators 2018-07-04 02:49:26 UTC Comment hidden (obsolete)
Comment 7 Eike Rathke 2020-07-10 10:53:09 UTC
(In reply to Gabor Kelemen from comment #0)
> The main problem is that the validation source with #REF! error can be saved
> to both ODS and XLSX format, and in the case of XLSX, Excel 2013 considers
> it an invalid file.

For ODFF the #REF! is perfectly fine, see
https://docs.oasis-open.org/office/OpenDocument/v1.3/cs01/part4-formula/OpenDocument-v1.3-cs01-part4-formula.html#References

What does Excel save instead for an invalid reference?
Comment 8 NISZ LibreOffice Team 2020-07-13 06:31:38 UTC
(In reply to Eike Rathke from comment #7)
> (In reply to Gabor Kelemen from comment #0)
> > The main problem is that the validation source with #REF! error can be saved
> > to both ODS and XLSX format, and in the case of XLSX, Excel 2013 considers
> > it an invalid file.
> 
> For ODFF the #REF! is perfectly fine, see
> https://docs.oasis-open.org/office/OpenDocument/v1.3/cs01/part4-formula/
> OpenDocument-v1.3-cs01-part4-formula.html#References
> 
> What does Excel save instead for an invalid reference?

My Excel 2013 writes this to xl/worksheets/sheet1.xml when a cell with validation is copied to a new file:

<x14:dataValidation type="list" allowBlank="1" showInputMessage="1" showErrorMessage="1"><x14:formula1><xm:f>[1]Munka2!#REF!</xm:f></x14:formula1><xm:sqref>A1</xm:sqref></x14:dataValidation>

This also happens to be invalid according to Excel 2013 :).

Before saving, the GUI shows the validation condition as:
='[broken-reference-orig.xlsx]Munka2'!#HIV!

While Calc 7.0RC1 writes this:
<dataValidation allowBlank="true" operator="between" showDropDown="false" showErrorMessage="true" showInputMessage="true" sqref="A1" type="list"><formula1>#HIV!!$C$2:$C$4</formula1><formula2>0</formula2></dataValidation>

(#HIV! being Hungarian for #REF!)
Comment 9 NISZ LibreOffice Team 2020-07-13 06:32:32 UTC
Created attachment 162951 [details]
Copied cell validation saved by Excel 2013
Comment 10 NISZ LibreOffice Team 2020-07-13 12:42:47 UTC
Excel 2016 xl/worksheets/sheet1.xml looks like this when a cell with validation is copied to a new file

<x14:dataValidation type="list" operator="equal" allowBlank="1" showErrorMessage="1" xr:uid="{A8E4395C-A788-4CEA-9E70-BD38B4629442}"><x14:formula1><xm:f>'[validation-copypaste.xlsx]Sheet1'!#REF!</xm:f></x14:formula1><xm:sqref>A2</xm:sqref></x14:dataValidation></x14:dataValidations>
Comment 11 NISZ LibreOffice Team 2020-07-13 12:43:36 UTC
Created attachment 162969 [details]
Copied cell validation saved by Excel 2016
Comment 12 Eike Rathke 2020-07-13 13:17:23 UTC
Seeing that in the ODF case
table:condition="of:cell-content-is-in-list([$#REF!.$A$1:.$A$3])"
is written that's also wrong, it should be [#REF!] instead, i.e. not split into particles anymore.

I think we could write a plain #REF! also in the OOXML case, which is what is written also for other reference errors.

(In reply to NISZ LibreOffice Team from comment #8)
> (#HIV! being Hungarian for #REF!)
Really? The translated error keyword is written? Geez.. that's even worse.
Comment 13 Eike Rathke 2020-07-15 19:48:54 UTC
It's getting even worse.. if the original validity (on Sheet2 of a *yet unsaved* document) was Sheet1.A1 or a formula like OFFSET(Sheet1.A2;-1;0) (note relative sheet reference) and copied to new document's Sheet1 it correctly displays #REF!.A1 or OFFSET(#REF!.A2;-1;0) but saved to .xlsx is Sheet1!A1 or OFFSET(Sheet1!A2,-1,0) resulting in a valid but wrong reference when reloading.

Saved to .ods is the correct [#REF!] or OFFSET([#REF!];-1;0)

Funny enough, if in the original bug doc on Sheet2 column A is deleted the validity displays as $Sheet2.$#REF!$1:$#REF!$3 and saving to .xlsx writes an expected <formula1>#REF!</formula1>, so something is already producing the correct string but ignores an invalid sheet reference.
Comment 14 Eike Rathke 2020-07-15 22:22:36 UTC
A hint:
The missing handling and erroneous use of native UI error string
(Hungarian #HIV!) instead of #REF! are in
sc/source/core/tool/compiler.cxx struct ConventionXL_OOX function
makeRefStr() and the ConventionXL_A1::makeRefStr() it is calling and
thereunder. Caveat, the same methods are used for the Excel A1 native UI
formula syntax notation (hence XL_A1).
Comment 15 Commit Notification 2020-09-07 23:20:38 UTC
Serge Krot committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/a3b4831208da615789bd1e2d5660dd130807f504

tdf#108673 XLSX: Don't export invalid sheet references in cell validation

It will be available in 7.1.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 16 Commit Notification 2020-09-09 07:17:33 UTC
Serge Krot committed a patch related to this issue.
It has been pushed to "libreoffice-7-0":

https://git.libreoffice.org/core/commit/03f601d10c8fe8772dcb8542e1d44f2d2e87fbb4

tdf#108673 XLSX: Don't export invalid sheet references in cell validation

It will be available in 7.0.2.

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 17 Commit Notification 2020-09-10 10:10:46 UTC
Serge Krot committed a patch related to this issue.
It has been pushed to "libreoffice-6-4":

https://git.libreoffice.org/core/commit/bd416f4a44acfea54afc377b9880863fd969dbe1

tdf#108673 XLSX: Don't export invalid sheet references in cell validation

It will be available in 6.4.7.

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.