Bug 139499 - FILESAVE XLSX Mangled structured references in Data Validity cell range criterion (column name saved in lowercase)
Summary: FILESAVE XLSX Mangled structured references in Data Validity cell range crite...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: x86-64 (AMD64) All
: medium minor
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: filter:xlsx
Depends on:
Blocks: XLSX-Corrupted Cell-Validity XLSX-DataRange
  Show dependency treegraph
 
Reported: 2021-01-08 18:41 UTC by jocn
Modified: 2023-01-26 14:25 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
OOXML spreadsheet created in Libre Calc exhibiting the issue (6.19 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2021-01-08 18:41 UTC, jocn
Details
Slidghtly modified example file before saving as XLSX (50.14 KB, image/png)
2023-01-26 13:24 UTC, Gabor Kelemen (allotropia)
Details
Slidghtly modified example file after saving as XLSX and reloading (52.40 KB, image/png)
2023-01-26 13:25 UTC, Gabor Kelemen (allotropia)
Details

Note You need to log in before you can comment on or make changes to this bug.
Description jocn 2021-01-08 18:41:00 UTC
Created attachment 168773 [details]
OOXML spreadsheet created in Libre Calc exhibiting the issue

LibreOffice Calc has a feature to restrict the values of a cell to the values present in a specified cell range (the "source"), by way of the Data -> Validity... modal.

The source can be specified using the usual cell reference syntax without issue.
The source may also be specified using a structured reference to a database range. I have encountered issues with the persistence of these structured references after saving and loading a document.

In the OOXML and 2007-365 Excel formats, the structured reference's column name is not saved in a case-sensitive manner. The table name is saved correctly, but the column name is saved in lowercase. Since column names are case sensitive, the structured reference breaks, and only Err. 509 is available from the dropdown menu.

In ODF the structured reference decays to an absolute cell reference. This is also not ideal, because a spreadsheet author might expect the validation to change when the database range is redefined. 

To reproduce, create and populate a database range with column headers, say table Foo with field Bar (uppercase B). Then, for some cell outside the table, perhaps B9, specify a cell range data validation using the syntax Foo[Bar]. Save the document, restart Calc and reopen the document, and check the validity of B9. I have done this in the attached OOXML spreadsheet.
Comment 1 Buovjaga 2021-12-02 12:30:39 UTC
Reproduced with file. Also with oldest of 6.3 Linux bibisect repo and 3.3.0 on Windows 10.

Arch Linux 64-bit
Version: 7.4.0.0.alpha0+ / LibreOffice Community
Build ID: ac21d937690c1ef995df22e11384e0fab226472e
CPU threads: 8; OS: Linux 5.15; UI render: default; VCL: kf5 (cairo+xcb)
Locale: fi-FI (fi_FI.UTF-8); UI: en-US
Calc: threaded
Built on 2 December 2021
Comment 2 Gabor Kelemen (allotropia) 2023-01-26 13:24:55 UTC
Created attachment 184928 [details]
Slidghtly modified example file before saving as XLSX
Comment 3 Gabor Kelemen (allotropia) 2023-01-26 13:25:22 UTC
Created attachment 184929 [details]
Slidghtly modified example file after saving as XLSX and reloading