Bug 104186 - FILESAVE: XLSX - Export fails to comply with ECMA-376 paragraph 18.17.2 concerning forbidden space characters between function name and left parenthesis
Summary: FILESAVE: XLSX - Export fails to comply with ECMA-376 paragraph 18.17.2 conce...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.2.3.3 release
Hardware: All All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: target:6.0.0 target:5.4.0.4 target:5.3.5
Keywords: filter:xlsx
Depends on:
Blocks: Calc-Function XLSX
  Show dependency treegraph
 
Reported: 2016-11-26 15:27 UTC by Victor V. Terber
Modified: 2017-07-28 21:49 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
Tescase, for bug 104186, Libreoffice (.ods) format (10.78 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-11-26 15:30 UTC, Victor V. Terber
Details
Testcase for LibreOffice bug 104186. XLSX file as saved by LibreOffice (4.39 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-11-26 15:31 UTC, Victor V. Terber
Details
Manually fixed tests case (4.39 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-11-27 23:46 UTC, Bartosz
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Victor V. Terber 2016-11-26 15:27:17 UTC
Description:
When saving as XLSX document the functions DAY(), MONTH() and YEAR() are handled differently with respect to localisation.

When the OO-created XSLX-codument is opened in Excel, DAY() and MONTH() seem to be ok, but YEAR() remains in English while it should be localised.

Steps to Reproduce:
1. Create a blank document with LO calc sheet with LO 5.2.3.3, using German localisation
2. Use in the document the functions TAG() (German for DAY), MONAT() (German for MONTH) and JAHR (German for YEAR).
3. Save as XLSX  'Microsoft Excel 2007-2013 XML (.xlsx)'
4. Open the XLSX document with a German version of Microsoft Excel 2016.
5. Observe the cells content: TAG() and MONAT() are correct, but instead of JAHR() you find YEAR()! Any calculations with this function is obviously broken.

Actual Results:  
Function YEAR() is visible in Excel

Expected Results:
Function JAHR() should be visible in Excel


Reproducible: Always

User Profile Reset: No

Additional Info:


User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:50.0) Gecko/20100101 Firefox/50.0
Comment 1 Victor V. Terber 2016-11-26 15:30:18 UTC
Created attachment 129036 [details]
Tescase, for bug 104186, Libreoffice (.ods) format

Testcase for LibreOffice bug 104186.

Save as XLSX and open in localised Excel version to observe the bug.
Comment 2 Victor V. Terber 2016-11-26 15:31:29 UTC
Created attachment 129037 [details]
Testcase for LibreOffice bug 104186. XLSX file as saved by LibreOffice

Testcase for LibreOffice bug 104186.

XLSX file as created by LibreOffice. Open in localised Excel version to observe the bug.
Comment 3 Victor V. Terber 2016-11-26 19:32:11 UTC
My original (implicit) analysis was wrong:

The real trigger is not any specific function, but the fact that a blank is found the LO formula between the function keyword and the following brace. The original finding (that a specific function was wrongly translated into XLSX format) was pure coincidence.

In fact, a cell with such a function is exported correctly and shown correctly in a localized Excel sheet:

=YEAR(A1)

This translates correctly in a German Excel sheet into '=JAHR(A1)'

But following cell is shown incorrectly after saved as an XLSX:

=YEAR (A1)

This translates wrongly in a German Excel sheet into '=YEAR (A1)'

The provided test cases are therefore still valid.
Comment 4 Victor V. Terber 2016-11-26 19:41:27 UTC
Updated and corrected description:

Description:
When saving as XLSX document LO functions are handled differently with respect to localisation if a blank is found between the function keyword and the following brace.

When the OO-created XSLX-codument is opened in localised Excel, cells formulas which use a function followed by a blank are not localised. Cells formulas where a brace follows directly the function keyword are handled and shown correctly.


Steps to Reproduce:
1. Create a blank calc sheet with LO 5.2.3.3 (German localisation)
2. Set a date in cell A1. Use in B1 the functions "=TAG(A1)", and in C1 "=TAG (A1)". "TAG" is the German word for "DAY"
3. Save as XLSX  'Microsoft Excel 2007-2013 XML (.xlsx)'
4. Open the XLSX document with a German version of Microsoft Excel 2016.
5. Observe the cells content: Cell B1 is ok, containing "=TAG(A1)". Cell C1 is faulty, containing "=DAY (A1)".

Actual Results:  
Cell C1() shows an error in Excel. The cell contains the formula "=DAY (A1)"

Expected Results:
Cell C1() should show the day of cell A1. The cell should contain the (localised) formula "=TAG (A1)" as found in B1.

Reproducible: Always

User Profile Reset: No

User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:50.0) Gecko/20100101 Firefox/50.0
Comment 5 Victor V. Terber 2016-11-27 12:24:46 UTC
In my understanding the root cause is a LO violation of the ECMA-376 standard.

Paragraph 18.17.2 (page 2034, header "Syntax), of ECMA-376, 4th Edition, states:

"An arbitrary number of space characters can separate two adjacent tokens, except that no space characters shall separate a function-name from the left parenthesis (() that follows it."

In my opinion the XLSX files saved by LO do not take any precaution to fulfil the except clause of this requirement. Would the requirement be fulfilled the observed compatibility problem with MS Excel would not occur (as can be proven trivially by manually changing the XLSX file accordingly).
Comment 6 Bartosz 2016-11-27 23:46:44 UTC
Created attachment 129057 [details]
Manually fixed tests case

I make sure that it is root cause of problem, I manually removed space in attached spreadsheet.
Could you please confirm if it is working for you ?
Comment 7 Victor V. Terber 2016-11-28 06:48:58 UTC
(In reply to Bartosz from comment #6)
> Created attachment 129057 [details]
> Manually fixed tests case
> 
> I make sure that it is root cause of problem, I manually removed space in
> attached spreadsheet.
> Could you please confirm if it is working for you ?

Confirmed: the attached XLSX file is working fine for me. Opening with German Excel 2016 shows the expected results.
Comment 8 Yousuf Philips (jay) (retired) 2017-07-16 18:15:11 UTC
@Eike: Can you or moggi give your input on this. Thanks.
Comment 9 Eike Rathke 2017-07-17 17:39:14 UTC
Well, yes, ECMA-376-1:2016 18.17.2 Syntax states what comment 5 mentioned.
So we'll have to strip those spaces when exporting.
Comment 10 Commit Notification 2017-07-17 18:14:38 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=7232980be6a4d67ed28a21b74ef3544cacb29d6a

Resolves: tdf#104186 spaces between function name and ( not allowed in OOXML

It will be available in 6.0.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 12 Commit Notification 2017-07-21 04:22:43 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-3":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=6ff7de9c93e280cfe54a0ed660595c98d052ab11&h=libreoffice-5-3

Resolves: tdf#104186 spaces between function name and ( not allowed in OOXML

It will be available in 5.3.6.

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 13 Commit Notification 2017-07-21 04:22:54 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-4":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=6f75cd76215045837697699e7dfcd7b0a40467f4&h=libreoffice-5-4

Resolves: tdf#104186 spaces between function name and ( not allowed in OOXML

It will be available in 5.4.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 14 Commit Notification 2017-07-21 09:28:02 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-4-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=461dcd1aa820ca933e322c4bcdf4adeb948c355b&h=libreoffice-5-4-0

Resolves: tdf#104186 spaces between function name and ( not allowed in OOXML

It will be available in 5.4.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 15 Commit Notification 2017-07-26 13:55:33 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-3-5":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=e76351c03a0d7f53f64ab1c8eb86f81295511a40&h=libreoffice-5-3-5

Resolves: tdf#104186 spaces between function name and ( not allowed in OOXML

It will be available in 5.3.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.