Download it now!
Bug 131424 - FILEOPEN XLSX Table reference broken if named table header contains comma
Summary: FILEOPEN XLSX Table reference broken if named table header contains comma
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.0.0.5 release
Hardware: All All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: target:7.1.0 target:7.0.0.1 target:6.4.6
Keywords: filter:xlsx, implementationError
Depends on:
Blocks: XLSX XLSX-DataRange
  Show dependency treegraph
 
Reported: 2020-03-19 13:21 UTC by NISZ LibreOffice Team
Modified: 2020-10-27 16:09 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
Example file from Excel (9.35 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2020-03-19 13:21 UTC, NISZ LibreOffice Team
Details
Screenshot of the original document side by side in Excel and Calc (118.08 KB, image/png)
2020-03-19 13:21 UTC, NISZ LibreOffice Team
Details

Note You need to log in before you can comment on or make changes to this bug.
Description NISZ LibreOffice Team 2020-03-19 13:21:35 UTC
Created attachment 158809 [details]
Example file from Excel

User made Excel table contained a Table, with some simple formulas referring to the table headers. 
One of these headers contained a comma, which confused Calc and made Err:507 errors.

Steps to reproduce:
    1. Open attached XLSX file. Note the Table in it, and in B1 the header is “This is the,second column” with a comma.
    2. In Excel the C column contains a simple addition formula, referring to table rows and headers by name.
    3. This is converted in Calc incorrectly, causing Err:507 messages in the C column.

Actual results:
The formula is converted to this: 
=MyTable1[[#This Row],[This is the first column]]+MyTable1[[#This Row],[this is the,second column]]
The comma in the second column header confuses Calc, but removing it from B1 and from the formula, then changing back the first letter to be capital fixes the calculation
With localized UI the problem is more visible. In localized UI the “#This Row” specifier is normally translated, but after a comma in column header, it’s no longer. 
Also the comma is changed to semicolon in the formula: “this is the;second column”

Expected results:
Correct calculation.

LibreOffice details:
Version: 7.0.0.0.alpha0+ (x64)
Build ID: ec85ed21a960a198a01778cdb89a91fabcb5d0c0
CPU threads: 4; OS: Windows 6.3 Build 9600; UI render: GL; VCL: win; 
Locale: en-US (hu_HU); UI-Language: en-US
Calc: CL

Also happens in:
Verzió: 6.0.0.3
Build az.: 64a0f66915f38c6217de274f0aa8e15618924765
CPU szálak: 4; OS: Windows 6.3; Felületmegjelenítés: GL; 
Területi beállítások: hu-HU (hu_HU); Calc: CL

Version: 5.0.0.5
Build ID: 1b1a90865e348b492231e1c451437d7a15bb262b
Locale: hu-HU (hu_HU)

Err:509 in: 
Version: 4.4.0.3
Build ID: de093506bcdc5fafd9023ee680b8c60e3e0645d7
Locale: hu_HU

and earlier, back to 3.5.0rc3
Comment 1 NISZ LibreOffice Team 2020-03-19 13:21:54 UTC
Created attachment 158810 [details]
Screenshot of the original document side by side in Excel and Calc
Comment 2 Timur 2020-03-20 10:29:17 UTC
Repro 70+. Only xlsx because named columns not MSO supported in xls. 
Basically from OO, but let's mark implementationError from when named columns supported in 5.0.
Comment 3 NISZ LibreOffice Team 2020-06-29 13:04:51 UTC
@Eike, thought you might be interested in this one...
Comment 4 Commit Notification 2020-06-30 20:12:44 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/31951265f541c9f5acf424674e2d1d97c0f3b8c2

Resolves: tdf#131424 separator in TableRef column specifier is valid

It will be available in 7.1.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 5 Eike Rathke 2020-06-30 21:14:31 UTC
Pending CI https://gerrit.libreoffice.org/c/core/+/97506 for 7-0
Pending review https://gerrit.libreoffice.org/c/core/+/97507 for 6-4
Comment 6 Commit Notification 2020-07-01 08:11:51 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-7-0":

https://git.libreoffice.org/core/commit/5637b974912b29805be8af85d39086bab5697aa1

Resolves: tdf#131424 separator in TableRef column specifier is valid

It will be available in 7.0.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 7 Commit Notification 2020-07-01 13:27:22 UTC
Xisco Fauli committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/c131d6c6b935617fa84286a9e441c1c791520445

tdf#131424: sc: Add unittest

It will be available in 7.1.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 8 Commit Notification 2020-07-07 09:16:52 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-6-4":

https://git.libreoffice.org/core/commit/56641c9717f4fadcc81248c58c7762314270d721

Resolves: tdf#131424 separator in TableRef column specifier is valid

It will be available in 6.4.6.

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.