Download it now!
Bug 115159 - FILESAVE XLSX Print Titles are duplicated every time we export an XLSX file
Summary: FILESAVE XLSX Print Titles are duplicated every time we export an XLSX file
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.4.4.2 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: target:6.3.0
Keywords: filter:xlsx
Depends on:
Blocks: XLSX Excel-Default-Names
  Show dependency treegraph
 
Reported: 2018-01-22 20:57 UTC by Gabor Kelemen
Modified: 2019-09-02 23:24 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
Example file made with Excel 2013 (16.83 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2018-01-22 20:57 UTC, Gabor Kelemen
Details
The example file saved by LO 5.4 (15.94 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2018-01-22 20:58 UTC, Gabor Kelemen
Details
The example file saved again by LO 5.4 (15.95 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2018-01-22 20:58 UTC, Gabor Kelemen
Details
Print titles after first save (270.80 KB, image/png)
2018-01-22 20:59 UTC, Gabor Kelemen
Details
Print titles after second save (278.79 KB, image/png)
2018-01-22 20:59 UTC, Gabor Kelemen
Details
The example file saved by LO 6 RC (15.94 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2018-01-22 21:00 UTC, Gabor Kelemen
Details
The example file saved again by LO 6 RC (15.95 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2018-01-22 21:01 UTC, Gabor Kelemen
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Gabor Kelemen 2018-01-22 20:57:50 UTC
Created attachment 139270 [details]
Example file made with Excel 2013

If we create simple Print Titles with Microsoft Excel 2010/2013/2016 and export the file with LibreOffice Calc, the Print Titles are duplicated.

Steps to reproduce:
1. Create a new spreadsheet with Microsoft Excel 2010/2013/2016.
2. Fill the first row with some data.
3. Select the worksheet range that you want to include titles when print.
4. Go to Page Layout tab, click Print Titles. 
5. Click on the select button in Rows to repeat at top area and select a title row (the first row you fill with data.)
6. Press OK to finish setting.
7. Check the new Print titles which you have set, at the Formulas, Name Manager tab.
8. Save the file as *.xlsx.
9. Open the file with LibreOffice Calc 5.4.4 and save as Print_Title_LO-54.xlsx and reload it.
10. Check the Print Titles at the Sheet-→Named Ranges and Expressions-→Manager tab. (There will be a duplicated Print Titles)


Actual results:
After we reload the file there will be a duplicated Print Titles (_xlnm.Print_Tiles_0). If we open the exported file with Microsoft Excel and check the Name Manager, there will be only the original Print Titles, but the duplicated will not be there.
If we export the Print_Title_LO-54.xlsx file with LibreOffice Calc again (this is the second time), at the Sheet-→Named Ranges and Expressions-→Manager will be two duplicated Print Titles (_xlnm.Print_Tiles_0 and _xlnm.Print_Tiles_0_0). If we open this file with Microsoft Excel and check the Name Manager, we will see one duplicated Print Titles (Print_Tiles_0). And so it goes on.
Comment: This bug can be produced only with these settings and steps. In the same way you can set column(s) to repeat at the left of each page, but in this case the Print Titles will not be duplicated. The Print Areas not duplicated at LibreOffice 5.4.4.


Expected results:
The Print Titles should not be duplicated after we export the *.xlsx file with LibreOffice Calc.


Version: 5.4.4.2
Build az.: 2524958677847fb3bb44820e40380acbe820f960
CPU szálak: 4; OS: Windows 6.1; Felületmegjelenítés: alapértelmezett; 
Területi beállítások: hu-HU (hu_HU); Calc: group

Version: 6.0.0.1.0+
Build ID: c678dc5309741097d9b0265f03dd279a8794d256
CPU threads: 4; OS: Windows 6.1; UI render: default; 
TinderBox: Win-x86@42, Branch:libreoffice-6-0, Time: 2017-12-28_04:21:33
Locale: hu-HU (hu_HU); Calc: group
Comment 1 Gabor Kelemen 2018-01-22 20:58:15 UTC
Created attachment 139271 [details]
The example file saved by LO 5.4
Comment 2 Gabor Kelemen 2018-01-22 20:58:46 UTC
Created attachment 139272 [details]
The example file saved again by LO 5.4
Comment 3 Gabor Kelemen 2018-01-22 20:59:13 UTC
Created attachment 139273 [details]
Print titles after first save
Comment 4 Gabor Kelemen 2018-01-22 20:59:36 UTC
Created attachment 139274 [details]
Print titles after second save
Comment 5 Gabor Kelemen 2018-01-22 21:00:13 UTC
Created attachment 139275 [details]
The example file saved by LO 6 RC
Comment 6 Gabor Kelemen 2018-01-22 21:01:18 UTC
Created attachment 139276 [details]
The example file saved again by LO 6 RC
Comment 7 Kevin Suo 2018-01-24 06:44:43 UTC
Reproduced in
Version: 6.1.0.0.alpha0+
Build ID: 3a2a430ae8e2c1647c18d8904477949f6e2e7941
CPU threads: 4; OS: Linux 4.14; UI render: default; VCL: gtk3; 
Locale: zh-CN (zh_CN.UTF-8); Calc: group threaded

Fedora 27 x64
Comment 8 Aron Budea 2018-02-14 05:51:54 UTC
Eike, here are my thoughts on this bug, but the big picture is still unclear, can you please advise on how to approach it?

The immediate cause of this is that when exporting the built-in defined range, it takes Excel's limits for colummns, which is 16384...

https://opengrok.libreoffice.org/xref/core/sc/source/filter/excel/xename.cxx#613
// *** 2) print titles *** ----------------------------------------
...


...then later, when XclExpNameManagerImpl::CreateName(...) is called, it checks for an existing built-in range name, but also tries to match the range, which is still 1024 here, so they won't match.
If the two matched, then as far as I can see, it would update the built-in range based on the defined one. However, first it checks if the two token arrays match... when could this work sensibly?

https://opengrok.libreoffice.org/xref/core/sc/source/filter/excel/xename.cxx#555
/*  Try to replace by existing built-in name - complete token array is
	needed for comparison, and due to the recursion problem above this
	cannot be done earlier. If a built-in name is found, the created NAME
	record for this name and all following records in the list must be
	deleted, otherwise they may contain wrong name list indexes. */
sal_uInt16 nBuiltInIdx = FindBuiltInNameIdx( rName, *xTokArr );


It's not clear to me why the built-in named ranges are gathered both directly from the respective spreadsheet settings, and also from regular named ranges (as imported). Would it make sense to either only match based on names, or even do away with the double handling of built-in names?

And indeed, when revisiting bug 112571 after its fix, as soon as the user updates the print ranges, those names are duplicated again because of the double handling and range-checking.
Comment 9 László Németh 2019-04-10 15:13:17 UTC
@Áron: many thanks for the useful code pointers!
Comment 10 Commit Notification 2019-04-10 15:13:32 UTC
László Németh committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/+/9d63c7035a86bb3a355433b567d2d8cd53d582fa%5E%21

tdf#115159 XLSX export: don't duplicate print titles

It will be available in 6.3.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.