Bug 154449

Summary:	Calc should support hidden Named Ranges for better MSO compatibility
Product:	LibreOffice	Reporter:	Rafael Lima <rafael.palma.lima>
Component:	Calc	Assignee:	Rafael Lima <rafael.palma.lima>
Status:	RESOLVED FIXED
Severity:	enhancement	CC:	himajin100000, ilmari.lauhakangas, mgb1508
Priority:	medium	Keywords:	filter:xlsx
Version:	7.6.0.0 alpha0+
Hardware:	All
OS:	All
See Also:	https://bugs.documentfoundation.org/show_bug.cgi?id=108516 https://bugs.documentfoundation.org/show_bug.cgi?id=38948
Whiteboard:	target:24.2.0 inReleaseNotes:24.2
Crash report or crash signature:		Regression By:
Bug Depends on:
Bug Blocks:	108237, 127300
Attachments:	XLSX file with hidden named ranges

Description Rafael Lima 2023-03-29 14:46:55 UTC

Created attachment 186290 [details]
XLSX file with hidden named ranges

Excel uses hidden named ranges to store info for some of its features (see f.i. the discussion in bug 38948 about how Excel saves its solver settings).

Users can also create hidden named ranges via VBA macros (but not via the UI), which is very useful for advanced users. See below how to create hidden named ranges in Excel:

https://professor-excel.com/named-ranges-excel-hidden-names/

However, in LO Calc, when we open a XLSX file with hidden named ranges, they are visible to the user. The reason is that we simply do not support hidden named ranges.

Open the attached XLSX file, which has 2 hidden named ranges (I also added 2 macros to hide/show them). If you open this file in Excel, the named ranges will be hidden, whereas in Calc you'll be able to see them in the UI.

This issue has become more important since bug 38948 has been fixed. Now Calc can stores solver settings to the file using named ranges (as in Excel), but we are unable to hide them to avoid showing users a long list of named ranges that should be hidden (which is what Excel does).

Also, when doing a round trip of hidden named ranges from Excel -> Calc -> Excel, the hidden named ranges will be visible in Excel at the end of the round trip, hence causing a compatibility problem.

I'm not sure how we should tackle this problem, but my initial suggestion would be to add the possibility of hiding a named ranges via the flag defined in XNamedRanges::addNewByName() method.

https://api.libreoffice.org/docs/idl/ref/interfacecom_1_1sun_1_1star_1_1sheet_1_1XNamedRanges.html

We could have a new HIDDEN flag in the NamedRangeFlag constant group.

https://api.libreoffice.org/docs/idl/ref/namespacecom_1_1sun_1_1star_1_1sheet_1_1NamedRangeFlag.html

The main issue is that ODF does not support the hidden property in <table:named-range> and <table:named-expression>.

https://docs.oasis-open.org/office/OpenDocument/v1.3/os/part3-schema/OpenDocument-v1.3-os-part3-schema.html#__RefHeading__1415664_253892949

Comment 1 Buovjaga 2023-04-11 11:26:04 UTC

Confirmed

Arch Linux 64-bit, X11
Version: 7.6.0.0.alpha0+ (X86_64) / LibreOffice Community
Build ID: 8029a239cf294d06bb9f29aeb9b6897cb422a880
CPU threads: 8; OS: Linux 6.2; UI render: default; VCL: kf5 (cairo+xcb)
Locale: fi-FI (fi_FI.UTF-8); UI: en-US
Calc: threaded
Built on 11 April 2023

Comment 2 Rafael Lima 2023-08-14 00:19:53 UTC

Proposed WIP patch here:
https://gerrit.libreoffice.org/c/core/+/155599

Comment 3 Commit Notification 2023-10-01 11:02:30 UTC

Rafael Lima committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/8938bc703980e3bdf4a32863f3e0193302a08cde

tdf#154449 Add support for hidden named ranges

It will be available in 24.2.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.