https://bugs.documentfoundation.org/show_bug.cgi?id=154449
Bug ID: 154449
Summary: Calc should support hidden Named Ranges for better MSO
compatibility
Product: LibreOffice
Version: 7.6.0.0 alpha0+ Master
Hardware: All
OS: All
Status: UNCONFIRMED
Severity: normal
Priority: medium
Component: Calc
Assignee: [email protected]
Reporter: [email protected]
Created attachment 186290
--> https://bugs.documentfoundation.org/attachment.cgi?id=186290&action=edit
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
--
You are receiving this mail because:
You are the assignee for the bug.
