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.
Created attachment 136470 [details] The spreadsheet after the first save from LO
Created attachment 136471 [details] The spreadsheet after the second save from LO
Created attachment 136472 [details] The spreadsheet after the third save from LO
Created attachment 136473 [details] A screenshot showcasing the issue.
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
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).
(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 :).
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?
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.
(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.
(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?
I think I mixed something up, can't find how to do this automatically. Maybe the user manually saved those... no idea.
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.
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.
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.
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.
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.
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.
*** Bug 114544 has been marked as a duplicate of this bug. ***
*** Bug 112297 has been marked as a duplicate of this bug. ***