Created attachment 137914 [details] Sample ODS - Define a named range in an empty spreadsheet, and delete the sheet reference from the range. Eg. $A$1:$A$3 instead of $Sheet1.$A$1:$A$3 - Save as XLSX. - Open in Excel. => In Excel the file opens with repair, and the named range is removed. Apparently in Excel the ranges have to include the sheet name. Attaching an ODS sample, and the corrupted XLSX saved from it. Note how because the range always refers to the current sheet, and the range A1-A3 (test_range) contains different values on Sheet1 and Sheet2, the result of SUM(test_range) is different as well. Not sure what would be the preferre fix... possibly add as many different ranges as there are sheets? Observed using 6.0 daily build (2017-11-06_23:18:19, a5af0fd9f27af42cf2e8571f659cdad6e606215b), 3.3.0 / Windows 7.
Created attachment 137915 [details] Exported XLSX (corrupted)
An omitted sheet reference means an implicit relative "same sheet where used" reference, something Excel doesn't seem to know in named expressions. I think there's only this solution: * if the named expression is in sheet-local scope, add the absolute sheet self-reference to such references * if it is in global scope, create a sheet-local expression for each sheet and add the absolute sheet self-reference (effectively that's how a global expression with a relative sheet self-reference works) * if there is a name clash omit creating such a named expression because any formula using the name on that sheet was already using the sheet-local expression
Created attachment 141868 [details] named_range2.ods: this sample has local, relative ranges, not a global one. XLS is also broken, so test fixes against that format too.
Created attachment 141971 [details] tdf113991.patch: set of rough patches that mostly fix the problems. I'd like to wait until 6.2 master before attempting to submit these. I noticed some minor formatting mistakes for example. These patches handle the non-GLOBAL situation fairly well. Emulation of XLSX seems OK, although I may have broken some coding rules. All this unique_ptr and const stuff is rough. As noted in the emulation patch, GLOBAL XLS is not working.
Created attachment 141993 [details] XLS patch: missing piece that enables global emulation to work for XLS
Justin Luth committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=b7a30d4bfbcf6b4c4fd773e8e1a436928e1224e3 tdf#113991 xls/xlsx export: no relative sheet in named ranges It will be available in 6.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Justin Luth committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=e1a77d0affef507d597e7dceb5514073658332df tdf#113991 xls/xlsx export: emulate relative GLOBAL named ranges It will be available in 6.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
supporting commit c8a040faa218959c23adaac1e0dbe9d679a0bd9e was also part of this patchset. No plans to backport. This is an uncommon situation, and the known instigating factor has been backported already.