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
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
Proposed WIP patch here: https://gerrit.libreoffice.org/c/core/+/155599
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.