Download it now!
Bug 113991 - Named ranges without sheet reference are exported as corrupted XLS(X)
Summary: Named ranges without sheet reference are exported as corrupted XLS(X)
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Justin L
URL:
Whiteboard: target:6.2.0
Keywords: filter:xls, filter:xlsx
Depends on:
Blocks: XLSX-Corrupted Cell-Name
  Show dependency treegraph
 
Reported: 2017-11-22 12:08 UTC by Aron Budea
Modified: 2018-06-27 04:15 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Sample ODS (9.11 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-11-22 12:08 UTC, Aron Budea
Details
Exported XLSX (corrupted) (6.03 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2017-11-22 12:09 UTC, Aron Budea
Details
named_range2.ods: this sample has local, relative ranges, not a global one. (8.67 KB, application/vnd.oasis.opendocument.spreadsheet)
2018-05-03 13:10 UTC, Justin L
Details
tdf113991.patch: set of rough patches that mostly fix the problems. (28.46 KB, patch)
2018-05-08 13:56 UTC, Justin L
Details
XLS patch: missing piece that enables global emulation to work for XLS (25.81 KB, patch)
2018-05-09 06:08 UTC, Justin L
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Aron Budea 2017-11-22 12:08:48 UTC
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.
Comment 1 Aron Budea 2017-11-22 12:09:39 UTC
Created attachment 137915 [details]
Exported XLSX (corrupted)
Comment 2 Eike Rathke 2017-11-22 14:21:55 UTC
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
Comment 3 Justin L 2018-05-03 13:10:04 UTC
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.
Comment 4 Justin L 2018-05-08 13:56:00 UTC
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.
Comment 5 Justin L 2018-05-09 06:08:56 UTC
Created attachment 141993 [details]
XLS patch: missing piece that enables global emulation to work for XLS
Comment 6 Commit Notification 2018-06-11 21:19:02 UTC
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.
Comment 7 Commit Notification 2018-06-27 04:00:18 UTC
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.
Comment 8 Justin L 2018-06-27 04:15:56 UTC
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.