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
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.
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.
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.
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
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).
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 ?
(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.
@Eike: Can you or moggi give your input on this. Thanks.
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.
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.
Pending review https://gerrit.libreoffice.org/40085 for 5-4 https://gerrit.libreoffice.org/40086 for 5-3 https://gerrit.libreoffice.org/#/c/40087/1 for 5-4-0 https://gerrit.libreoffice.org/40088 for 5-3-5
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.
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.
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.
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.