Bug 109240 - FILESAVE XLSX: Use of Data > AutoFilter provoke auto-creation of multiple defined Name ranges over multiple consecutive save in XLSX format of the same file causing compatibility issue with Excel 2016 (file cannot be opened in Excel 2016)
Summary: FILESAVE XLSX: Use of Data > AutoFilter provoke auto-creation of multiple def...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.2.7.2 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: target:6.1.0 target:6.0.0.1 target:5.4.4
Keywords:
Depends on:
Blocks: AutoFilter Cell-Name Excel-Default-Names
  Show dependency tree / graph
 
Reported: 2017-07-20 20:06 UTC by Richard Léger
Modified: 2019-09-02 23:21 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:

Attachments
Sample file showing range created after two consecutive save following activation of AutoFilter (64.88 KB, image/png)
2017-07-20 20:06 UTC, Richard Léger 		Details
View All Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description Richard Léger 2017-07-20 20:06:00 UTC 
Created attachment 134752 [details]
Sample file showing range created after two consecutive save following activation of AutoFilter

Hi,

Here is the scenario leading to bug...

01. Open new workbook and create a simple table with header columns
02. Rename the sheet: e.g 'Product Sheet'
03. Save file as in XLSX format 
04. Close file
05. Re-open file
06. Above sheet, in cell name selection box (on the top left), select 'Manage Names...' ==> no named ranges defined
07. Activate Data > AutoFilter
08. Above sheet, in cell name selection box (on the top left), select 'Manage Names...' ==> no named ranges defined
09. Save file (keep XSLX format if prompted)
10. Close file
11. Re-open file
12. Above sheet, in cell name selection box (on the top left), select 'Manage Names...' ==> a new named range now appear as defined in the list. 1 in total listed.
09. Save file (keep XSLX format if prompted)
10. Close file
11. Re-open file
12. Above sheet, in cell name selection box (on the top left), select 'Manage Names...' ==> another new named range (same as above but suffixed with _0) now appear as defined. 2 in total listed.
13. If you keep repeating 09,10,11 new range keep being defined and added...
14. Close file
15. Open file in Excel 2016, it will chock on the file saying it is corrupted! With inability to repair it... most likely because various named range refer to the same range...

This seems to be a major compatibility issue...

May be related to bug 109144 (FILESAVE XLSX: Resaving file with autofilter on conditional formatting results in invalid XLSX) ...
Comment 1 Richard Léger 2017-07-20 20:12:26 UTC 
FYI, if you delete all defined Names... (named ranges) and save, they are recreated automatically at next opening of the file, one by one... duplicate of each others... after consecutive save/opening...
Comment 2 m_a_riosv 2017-07-22 11:45:05 UTC 
Reproducible.
Version: 6.0.0.0.alpha0+
Build ID: 403ddd6cb1c0d6a0e0db105e68f58fe40057cb42
CPU threads: 4; OS: Windows 6.19; UI render: default; 
TinderBox: Win-x86@42, Branch:master, Time: 2017-07-14_23:37:46
Locale: es-ES (es_ES); Calc: group

It is the filter range that is recreated even if it exist.
Comment 3 Richard Léger 2017-07-24 09:44:59 UTC 
FYI it seems Excel also prefix such named range names with '_xlnm.' (without quotes)... while LO does not use that prefix when saving in .xlsx format.

Dupicate named range names seems recreated(or loaded) when file is opened... clicking Save button does not recreate the range (at least not apearing in LO interface) until the file is closed/reopened.

Hope that help.
Comment 4 Aron Budea 2017-11-29 00:42:18 UTC 
I can reproduce the multiplication of named ranges, but not the corruption. Can you or anyone share a simple file that was created with these steps in Calc (or similar steps involving autofilter), and Excel complains about it?
Comment 5 Kevin Suo 2017-12-01 00:18:20 UTC 
Set platform to ALL.Xlsx files produced this way have the same problem when open with MSO in Win. I have encountered this issue many times.
Comment 6 Richard Léger 2017-12-01 09:12:01 UTC 
(In reply to Aron Budea from comment #4)
> I can reproduce the multiplication of named ranges, but not the corruption.
> Can you or anyone share a simple file that was created with these steps in
> Calc (or similar steps involving autofilter), and Excel complains about it?

Strangely I don't seem to be able to reproduce the corruption... but I no longer have access to original machines where it appeared at the time. The duplicate range still appears.

Though I may have another production file that crash Excel and that LO complain about range when opening. I may provide it in a private msg if that is possible for testing purpouse as I don't want to publish it on a public forum.

Would that do?
Comment 7 Commit Notification 2017-12-01 21:04:20 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 8 Aron Budea 2017-12-01 21:25:05 UTC 
If you don't mind sending it to me, I can take a quick look at it (can't guarantee anything). What would definitely be helpful is reproduction steps on how to get to a bad file.

In the meantime, the posted fix will prevent creation of duplicate built-in named ranges and remove existing ones upon save. 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 9 Commit Notification 2017-12-02 23:47:39 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 10 Commit Notification 2017-12-02 23:47:54 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 11 Commit Notification 2017-12-12 14:59:21 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.