Bug 112567 - FILESAVE XLSX Rows to Repeat and Columns to Repeat properties in XLSX files created with Calc are saved as incorrect Named ranges (w Hungarian locale)
Summary: FILESAVE XLSX Rows to Repeat and Columns to Repeat properties in XLSX files c...
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.4.1.2 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: target:6.3.0 target:6.4.0 target:6.3.0.1
Keywords: filter:xlsx
Depends on:
Blocks: XLSX-Corrupted Excel-Default-Names
  Show dependency treegraph
 
Reported: 2017-09-22 09:56 UTC by Gabor Kelemen (allotropia)
Modified: 2019-09-02 23:21 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
The file showcasing the bug. (12.31 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2017-09-22 09:56 UTC, Gabor Kelemen (allotropia)
Details
A spreadsheet with the same property, created with Mirosoft Excel. (17.75 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2017-09-22 09:56 UTC, Gabor Kelemen (allotropia)
Details
A screenshot showcasing the error report. (134.68 KB, image/png)
2017-09-22 09:56 UTC, Gabor Kelemen (allotropia)
Details
A screenshot showcasing the named range. (125.73 KB, image/png)
2017-09-22 09:57 UTC, Gabor Kelemen (allotropia)
Details
Example file saved from 6.0 alpha (8.01 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-10-02 15:59 UTC, Gabor Kelemen (allotropia)
Details
The previous file saved as xlsx from LO 6.0alpha (11.67 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2017-10-02 16:03 UTC, Gabor Kelemen (allotropia)
Details
The xlsx file in Calc looks fine. (93.20 KB, image/jpeg)
2017-10-02 16:05 UTC, Gabor Kelemen (allotropia)
Details
File exported by a current LO6 master build (4.89 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2017-10-03 11:21 UTC, Gabor Kelemen (allotropia)
Details
Original ods from the current LO6 master (8.01 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-10-03 11:22 UTC, Gabor Kelemen (allotropia)
Details
XLSX exported with LO 6.3 alpha (2019-05-27_23:46:36, hu-HU) (4.88 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2019-05-30 07:44 UTC, NISZ LibreOffice Team
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Gabor Kelemen (allotropia) 2017-09-22 09:56:05 UTC
Created attachment 136461 [details]
The file showcasing the bug.

Rows to Repeat and Columns to Repeat properties are stored as named ranges in XLSX files. When a spreadsheet with both Rows to Repeat and Columns to Repeat properties is saved as XLSX in Calc, the resulting file loses the Columns to Repeat and Rows to Repeat property of the document but still retains an incorrect named range. The following error message appears when the document is opened in Microsoft Excel 2013:
Removed Records: Named range from /xl/workbook.xml part (Workbook).

Steps to reproduce:
1. Create a new spreadsheet in LibreOffice Calc.
2. Click Format -> Print Ranges -> Edit.
3. Set the value of Rows to Repeat to $1.
4. Set the value of Columns to Repeat to $A.
5. Save the file as XLSX.
6. Open the file both in LibreOffice Calc and Microsoft Excel 2013.

Actual results:
The rows to repeat and columns to repeat property isn't retained in Calc or Excel and the following error message appears when the document is opened with Microsoft Excel 2013: Removed Records: Named range from /xl/workbook.xml part (Workbook).

Expected results:
Both the rows to repeat and columns to repeat properties should be retained and the spreadsheet should open in Excel without displaying an error message.
Comment 1 Gabor Kelemen (allotropia) 2017-09-22 09:56:29 UTC
Created attachment 136462 [details]
A spreadsheet with the same property, created with Mirosoft Excel.
Comment 2 Gabor Kelemen (allotropia) 2017-09-22 09:56:53 UTC
Created attachment 136463 [details]
A screenshot showcasing the error report.
Comment 3 Gabor Kelemen (allotropia) 2017-09-22 09:57:18 UTC
Created attachment 136464 [details]
A screenshot showcasing the named range.
Comment 4 Aschalew 2017-09-29 07:39:42 UTC
I cannot Reproduce

Windows 10

Version: 5.4.1.2 (x64)
Build ID: ea7cb86e6eeb2bf3a5af73a8f7777ac570321527
CPU threads: 8; OS: Windows 6.19; UI render: default; 
Locale: en-US (en_US); Calc: group


Description:
I tried it but never seen the error message saying "Removed Records: Named range from /xl/workbook.xml part (Workbook)".
Comment 5 Aron Budea 2017-10-01 00:14:09 UTC
I can't reproduce it either with LO 5.4.1.2 / Windows 7.
No error in Excel, and there is an entry with the name Print_Titles
Comment 6 Gabor Kelemen (allotropia) 2017-10-02 15:59:03 UTC
Created attachment 136700 [details]
Example file saved from 6.0 alpha

Attached ods file should show the symptoms.

Open Format - Print Ranges - Edit.
You should see it has Rows to Repeat set to $1, Columns to Repeat set to $A.

Save this to xlsx and you should get an error from Excel 2013.
Comment 7 Gabor Kelemen (allotropia) 2017-10-02 16:03:50 UTC
Created attachment 136701 [details]
The previous file saved as xlsx from LO 6.0alpha

The previous file exported from Calc 6.0 alpha.

Open in Calc, then go to Sheet - Named Ranges and Expressions - Manage.
You should see there is a Name _xlnm.Print_Titles and Range Sheet1!$A:$A;Sheet1!$1:$1 with the Repeat column and Repeat row Range Options checked. Also Calc highlights the first row and column - so it looks fine.

Open in Excel 2013 - it should say it has invalid content and removes all named ranges.
Comment 8 Gabor Kelemen (allotropia) 2017-10-02 16:05:33 UTC
Created attachment 136702 [details]
The xlsx file in Calc looks fine.
Comment 9 Aron Budea 2017-10-03 03:10:52 UTC
No repro with LO 5.4.2.2 & 6.0 daily build (2017-09-28_03:27:43, 0fa21336428b286d69684cfbb7b845f123657041).
I used the file from comment 6, saved as XLSX and opened in Excel 2013, no error message in either case.

Gábor, please specify exact build id you tested with in case of the 6.0 master build, and results both with master build and with a 5.4 release (preferably 5.4.2.2).
Comment 10 Gabor Kelemen (allotropia) 2017-10-03 11:15:37 UTC
We used the 6.0 build f70e0ec6b3c61a7c7caa469949b0ac8016c89854 this is from 09-13, to generate the example files.

Retried with f50bf3c5225b49b3c6f77f882e35305e5dc5ccd3 from 10-03 and it still exports a faulty xlsx.
Comment 11 Gabor Kelemen (allotropia) 2017-10-03 11:21:46 UTC
Created attachment 136728 [details]
File exported by a current LO6 master build
Comment 12 Gabor Kelemen (allotropia) 2017-10-03 11:22:29 UTC
Created attachment 136729 [details]
Original ods from the current LO6 master
Comment 13 Aron Budea 2017-10-06 12:05:27 UTC
Ha, you need to set Hungarian locale (possibly other non-English locales are sufficient as well), it doesn't occur with English (USA).

Once I set Hungarian locale, and saved attachment 136729 [details] as XLSX in Calc, it gave an error in Excel.
Comment 14 Aron Budea 2017-10-10 08:47:10 UTC
The difference is in xl\workbook.xml. See the separator being ";" instead of "," in Hungarian.

In US English:
<definedName function="false" hidden="false" localSheetId="0" name="_xlnm.Print_Titles" vbProcedure="false">Sheet1!$A:$A,Sheet1!$1:$1</definedName>

In Hungarian:
<definedName function="false" hidden="false" localSheetId="0" name="_xlnm.Print_Titles" vbProcedure="false">Sheet1!$A:$A;Sheet1!$1:$1</definedName>
Comment 15 QA Administrators 2018-10-11 03:03:15 UTC Comment hidden (obsolete)
Comment 16 Commit Notification 2019-05-02 06:28:22 UTC
László Németh committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/+/19976f079800ec4c1d0d5d7e226986cb41f834c2%5E%21

tdf#112567 XLSX export: fix broken built-in names

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.
Comment 17 NISZ LibreOffice Team 2019-05-30 07:40:07 UTC
I exported the original ODS file: https://bugs.documentfoundation.org//attachment.cgi?id=136729 with LibreOffice 6.3 alpha 1+ (Build ID: 37103a3f008c13dee21d55c8da9e6af807b1997c, Branch:master, Time: 2019-05-27_23:46:36, Locale: hu-HU) and the problem still persists. I was also able to replicate a bug from scratch, without using the original ODS.

The relevant part of the workbook.xml still looks like this:
<definedName function="false" hidden="false" localSheetId="0" name="_xlnm.Print_Titles" vbProcedure="false">Sheet1!$A:$A;Sheet1!$1:$1</definedName>

Tested file:
https://bugs.documentfoundation.org//attachment.cgi?id=136729

LibreOffice details:
Version: 6.3.0.0.alpha1+ (x64)
Build ID: 37103a3f008c13dee21d55c8da9e6af807b1997c
CPU threads: 4; OS: Windows 6.3; UI render: GL; VCL: win; 
TinderBox: Win-x86_64@42, Branch:master, Time: 2019-05-27_23:46:36
Locale: hu-HU (hu_HU); UI-Language: en-US
Calc: threaded
Comment 18 NISZ LibreOffice Team 2019-05-30 07:44:03 UTC
Created attachment 151774 [details]
XLSX exported with LO 6.3 alpha (2019-05-27_23:46:36, hu-HU)

Here is the XLSX file exported with LibreOffice 6.3 alpha 1+ (Build ID: 37103a3f008c13dee21d55c8da9e6af807b1997c, Branch:master, Time: 2019-05-27_23:46:36, Locale: hu-HU).
Comment 19 Commit Notification 2019-06-03 05:24:07 UTC
László Németh committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/+/32a11727a0f709c11685d4200b1db08dac211dec%5E%21

tdf#112567 XLSX export: correct built-in names

It will be available in 6.4.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.
Comment 20 Commit Notification 2019-06-03 19:39:17 UTC
László Németh committed a patch related to this issue.
It has been pushed to "libreoffice-6-3":

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

tdf#112567 XLSX export: correct built-in names

It will be available in 6.3.0.1.

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.
Comment 21 Gabor Kelemen (allotropia) 2019-06-12 08:12:06 UTC
Checked with 

Version: 6.4.0.0.alpha0+ (x86)
Build ID: 5e74085e07bbeb23c7d29fc3c5f6f6d2b7ff97e7
CPU threads: 4; OS: Windows 6.3; UI render: GL; VCL: win; 
TinderBox: Win-x86@42, Branch:master, Time: 2019-06-10_00:14:28
Locale: hu-HU (hu_HU); UI-Language: en-US
Calc: threaded

Now attachment #136462 [details] roundtrips correctly (Excel - LO - Excel, Excel - LO - LO does not cause invalid file either), thanks!
Comment 22 László Németh 2019-06-24 10:04:34 UTC
@Gábor: thanks for the verification!