Bug 112571 - FILESAVE XLSX New named range is created every time an Excel made spreadsheet that contains print ranges is saved as XLSX
Summary: FILESAVE XLSX New named range is created every time an Excel made spreadsheet...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.1 all versions
Hardware: All All
: medium normal
Assignee: Aron Budea
URL:
Whiteboard: target:6.1.0 target:6.0.0.1 target:5.4.4
Keywords: filter:xlsx
: 112297 114544 (view as bug list)
Depends on:
Blocks: Print-Range XLSX
  Show dependency treegraph
 
Reported: 2017-09-22 12:29 UTC by Gabor Kelemen
Modified: 2018-01-22 23:04 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
The original file. (19.44 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2017-09-22 12:29 UTC, Gabor Kelemen
Details
The spreadsheet after the first save from LO (14.36 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2017-09-22 12:30 UTC, Gabor Kelemen
Details
The spreadsheet after the second save from LO (14.37 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2017-09-22 12:30 UTC, Gabor Kelemen
Details
The spreadsheet after the third save from LO (14.38 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2017-09-22 12:30 UTC, Gabor Kelemen
Details
A screenshot showcasing the issue. (32.79 KB, image/png)
2017-09-22 12:31 UTC, Gabor Kelemen
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Gabor Kelemen 2017-09-22 12:29:20 UTC
Created attachment 136468 [details]
The original file.

A new named range is created every time an XLSX spreadsheet that was created with Microsoft Excel and contains Print Ranges is saved as XLSX in LibreOffice Calc.

Steps to reproduce:
1. Create a new spreadsheet in Microsoft Excel 2013.
2. Select a range of cells.
2. Specify the print ranges (print area in Excel terminology) by clicking on Print Area -> Set Print Area button in the Page Layout tab.
5. Save the file as XLSX.
6. Open the file with LibreOffice Calc.
7. Check the named ranges by clicking on the Sheet -> Named Ranges and Expressions -> Manage option.
8. Save the file as XLSX and click on File -> Reload.
9. Check again the named ranges by clicking on the Sheet -> Named Ranges and Expressions -> Manage option.
10. Every time you repeat the 8. step a new named range is created.

Actual results:
A new named range is created every time the spreadsheet is saved as XLSX.

Expected results:
The number of named ranges should stay constant.
Comment 1 Gabor Kelemen 2017-09-22 12:30:04 UTC
Created attachment 136470 [details]
The spreadsheet after the first save from LO
Comment 2 Gabor Kelemen 2017-09-22 12:30:26 UTC
Created attachment 136471 [details]
The spreadsheet after the second save from LO
Comment 3 Gabor Kelemen 2017-09-22 12:30:41 UTC
Created attachment 136472 [details]
The spreadsheet after the third save from LO
Comment 4 Gabor Kelemen 2017-09-22 12:31:06 UTC
Created attachment 136473 [details]
A screenshot showcasing the issue.
Comment 5 raal 2017-09-23 08:17:39 UTC
I can confirm with Version 4.1.0.0.alpha0+ and Version: 6.0.0.0.alpha0+
Build ID: afeff9102c2935139de4efd40fd2286dce396706
CPU threads: 4; OS: Linux 4.4; UI render: default; VCL: gtk2; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2017-09-17_23:32:41
Comment 6 Aron Budea 2017-11-21 13:48:47 UTC
Same thing happens if there is is a filter on a column. It seems to be the same case that results in bug 90484 (not sure how that got closed as NOTABUG).
Comment 7 Gabor Kelemen 2017-11-21 17:59:30 UTC
(In reply to Aron Budea from comment #6)
> Same thing happens if there is is a filter on a column. 

Named ranges are used for a lot of things apparently. Print ranges, autofilters, even pivot tables contain them. It's a versatile tool :).
Comment 8 Aron Budea 2017-11-23 05:39:01 UTC
The main issue is that eg. print area is exported once as a built-in name:

void XclExpNameManagerImpl::CreateBuiltInNames()
https://opengrok.libreoffice.org/xref/core/sc/source/filter/excel/xename.cxx#575

...and then as a user name as well (as many as there were). This is why after each save there's one extra range. A set of "_0"-s are added during import to avoid clash.

This is what the named range in xl\worksheets.xml looks like after one save (different sample than the attachment, the range is different), at this point the names are still clashing:

<definedNames>
  <definedName function="false" hidden="false" localSheetId="0" name="_xlnm.Print_Area" vbProcedure="false">Sheet1!$A$1:$E$5</definedName>
  <definedName function="false" hidden="false" localSheetId="0" name="_xlnm.Print_Area" vbProcedure="false">Sheet1!$A$1:$E$5</definedName></definedNames>

Note how in XclExpNameManagerImpl::CreateBuiltInNames() other built-in names are created as well.
The quick fix seems to be to skip adding the user-created names separately if they match a built-in name. Maybe they shouldn't even be listed among the user-created names, if it's possible?
Comment 9 Aron Budea 2017-11-29 12:31:59 UTC
Gábor, have you encountered a case when sometimes the built-in range names are also saved without prefix, eg. Print_Area alongside _xlnm.Print_Area, which causes Excel to choke on the file on open with name conflict?

An example was reported in bug 90484, but there are no repro steps.
Comment 10 Gabor Kelemen 2017-11-29 12:46:19 UTC
(In reply to Aron Budea from comment #9)
> Gábor, have you encountered a case when sometimes the built-in range names
> are also saved without prefix, eg. Print_Area alongside _xlnm.Print_Area,
> which causes Excel to choke on the file on open with name conflict?
> 
> An example was reported in bug 90484, but there are no repro steps.

Yes, it is Print_Area when you save in XLS format.
In XLSX it gets the _xlnm prefix.
In both cases the _0 postfixes are added.

I'd guess that in #90484 the file was saved multiple times in both formats.
Comment 11 Aron Budea 2017-11-29 13:24:48 UTC
(In reply to Gabor Kelemen from comment #10)
> Yes, it is Print_Area when you save in XLS format.
It isn't, it is named Excel_BuiltIn_Print_Area.
I tested with saving the XLSX a couple of times ("_0" suffixes were added), then saving to XLS and back to XLSX, and the extra _xlnm.Print_Area entries became _xlnm_Print_Area (plus the suffixes), but I didn't encounter the name Print_Area. Could you please write down the exact steps?
Comment 12 Gabor Kelemen 2017-11-29 14:43:37 UTC
I think I mixed something up, can't find how to do this automatically. Maybe the user manually saved those... no idea.
Comment 13 Commit Notification 2017-12-01 21:04:27 UTC
Aron Budea committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=f8b9d0fb0767d8bbe8477f92abaf6b8e0ff65546

tdf#109240, tdf#112571: don't export dupe built-in named ranges

It will be available in 6.1.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 14 Aron Budea 2017-12-01 21:20:56 UTC
Closing as fixed, backports are in gerrit.

If you manage to reproduce the name conflict with this patch included, please open a new bug report, and add it to the See Also field and/or CC me.
Comment 15 Commit Notification 2017-12-02 23:47:46 UTC
Aron Budea committed a patch related to this issue.
It has been pushed to "libreoffice-6-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=1f5d7c8b986d4839788e3e2f4ac24ab2df25af9c&h=libreoffice-6-0

tdf#109240, tdf#112571: don't export dupe built-in named ranges

It will be available in 6.0.0.1.

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 16 Commit Notification 2017-12-02 23:48:01 UTC
Aron Budea committed a patch related to this issue.
It has been pushed to "libreoffice-5-4":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=d0f5f2bbd2bc6fee5ad4c4031f69c04e1c8fd3fb&h=libreoffice-5-4

tdf#109240, tdf#112571: don't export dupe built-in named ranges

It will be available in 5.4.5.

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 17 Commit Notification 2017-12-05 17:18:16 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=d1cd68fac3a3a521e1f7ebf033b908d67832a0c1

Rather call GetObject() only if IsObject(), tdf#112571 follow-up

It will be available in 6.1.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 18 Commit Notification 2017-12-12 14:59:28 UTC
Aron Budea committed a patch related to this issue.
It has been pushed to "libreoffice-5-4-4":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=3f50e9ccde342259fd6f24c40599d820f21eba7b&h=libreoffice-5-4-4

tdf#109240, tdf#112571: don't export dupe built-in named ranges

It will be available in 5.4.4.

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 19 Eike Rathke 2017-12-18 20:49:54 UTC
*** Bug 114544 has been marked as a duplicate of this bug. ***
Comment 20 Gabor Kelemen 2017-12-18 22:58:24 UTC
*** Bug 112297 has been marked as a duplicate of this bug. ***