Created attachment 125243 [details] Xlsx-document created with Calc 5.1.3 Calc document created with version 5.1.3 saved as xlsx produces errors when opened with Excel. With Excel version 2010 or 2013, the document doesn't open. With Excel 2016, Excel reports errors and proposes to repair the document. After repair, the document behaves normally.
Created attachment 125244 [details] Xlsx-document saved by Excel2016 after auto-repair As I don't see differences in the way the functions are saved (in which case I would have assigned myself to the bug report), I hope that someone with OOXML-expertise sees what goes wrong and either fixes the problem or provides me with sufficient clues for me to fix it.
Repro, I re-saved it from LibO. Would be good, if there was original .ods to test with. Win 8.1 32-bit MSO 2013 LibO Version: 5.3.0.0.alpha0+ Build ID: 9d2f7be4e65595241db3cf5135b69bd9e4ce6a30 CPU Threads: 4; OS Version: Windows 6.29; UI Render: default; TinderBox: Win-x86@42, Branch:master, Time: 2016-05-27_22:55:26 Locale: fi-FI (fi_FI)
Created attachment 125383 [details] The 'original' ods document Document created by version 5.1.3, as requested in comment#2
(In reply to Winfried Donkers from comment #3) > Created attachment 125383 [details] > The 'original' ods document I also reproduced with this. Version: 5.3.0.0.alpha0+ Build ID: bb6acbd0c3e8240c976ed62e04275ec67fa5a61d CPU Threads: 4; OS Version: Windows 6.29; UI Render: default; TinderBox: Win-x86@42, Branch:master, Time: 2016-06-11_00:27:38 Locale: fi-FI (fi_FI)
After reproducing error on MS Excel I received following informations: Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded. Removed Records: Named range from /xl/workbook.xml part (Workbook) Removed Records: Formula from /xl/worksheets/sheet1.xml part
CEILING.MATH function is only supported with Excel 2016, Excel 2013. https://support.office.com/en-US/article/CEILING-MATH-function-80F95D2F-B499-4EEE-9F16-F795A8E306C8 The strings which are invalid for MS Excel are: In /xl/workbook.xml: <definedName function="true" hidden="false" name="AFRONDEN.BENEDEN" vbProcedure="true"></definedName> <definedName function="true" hidden="false" name="AFRONDEN.BOVEN" vbProcedure="true"></definedName> after adding names, it was starts working correctly: <definedName function="true" hidden="false" name="AFRONDEN.BENEDEN" vbProcedure="true">Blad1!$A$1</definedName> <definedName function="true" hidden="false" name="AFRONDEN.BOVEN" vbProcedure="true">Blad1!$A$1</definedName> The values which is causing error in /xl/worksheets/sheet1.xml is: <row r="9" customFormat="false" ht="12.8" hidden="false" customHeight="false" outlineLevel="0" collapsed="false"> <c r="A9" s="2" t="e"> <f aca="false">FLOOR(beta,IF(beta<=0,-0.5,0.5),1)</f> <v>#VALUE!</v> </c> <c r="B9" s="3" t="e"> <f aca="false">CEILING(beta,IF(beta<=0,-0.5,0.5),1)</f> <v>#VALUE!</v> </c> <c r="D9" s="2" t="e"> <f aca="false">FLOOR(-beta,IF(-beta<=0,-0.5,0.5),1)</f> <v>#VALUE!</v> </c> <c r="E9" s="3" t="e"> <f aca="false">CEILING(-beta,IF(-beta<=0,-0.5,0.5),1)</f> <v>#VALUE!</v> </c> </row>
Created attachment 126592 [details] Xlsx-document created with Calc 5.1.3 and manually fixed. Could you please check if following file is working with MS Excel 2016?
Created attachment 126605 [details] manually fixed document opened and saved by Excel2016 The document in attachment #126592 [details] opens in Excel 2016 without warnings or errors, except for one I didn't see before: Excel warned that the document was opened in safe mode (read only) and should only be set to edit mode when really necessary and trusted etc. I switched to edit mode and saved the document. It is much larger than the document I opened. HTH
Created attachment 126634 [details] Minimal ods file with bug reproduction
Created attachment 126635 [details] Minimal .xlsx file created with LibreOffice 5.1 The root cause of problem is wrong FLOOR() function invocation According to specification: https://support.office.com/en-us/article/FLOOR-function-14bb497c-24f2-4e04-b327-b0b4de5a8886 the FLOOR() is taken only two parameters. In example three parameters were provided: FLOOR(beta,1,2) In MS Excel 2010 it is not possible to add more parameters to functions. The error will be displayed and the value from formula will be deleted. That's why I don't know how to resolve this bug.
(In reply to Bartosz from comment #10) [...] > The root cause of problem is wrong FLOOR() function invocation [...] > That's why I don't know how to resolve this bug. I will investigate this as I worked on the FLOOR functions; do you want me to take over the bug fixing?
@Winfried Donkers Sure you could freely take this issue. The issue is more general, and it is not connected only with FLOOR, but with all functions. By design LibreOffice is allowing incorrect syntax of function, and in that case it's put "Err:504". On the other side the MS Office is not allowing adding functions with incorrect syntax. As the result, MS Office assume that all functions has correct syntax. When document with incorrect function syntax is exported into MS Excel, the error will be displayed during first opening of the document. What is the proposed solutions: 1. Change design of LibreOffice and do not allow add functions with incorrect syntax 2. Display error during exporting document into .xlsx/.xls with incorrect function syntax. I would vote for 2. but I need advice if that is preferred solution.
(In reply to Bartosz from comment #12) @Bartosz: There currently is a mechanism (in sc/source/core/tool/compiler.cxx) that checks and/or converts functions when exporting to OOXML. In case of FLOOR, the function name is changed to FLOOR.MATH on export to OOXML; see bug 69552 for this. When I create a Calc document with FLOOR(1,1,1) and save thas as xlsx, the xlsx document contains _xlfn.FLOOR.MATH(1,1,1), which IMHO is correct. However, Excel2016 complains on opening and proceeds to repair the document. So your proposed solutions are not needed, the mechanism already is there and seems to be working. It looks as if we need to look at the problem from both sides (you investigating the OOXML document shortcomings (and possibly the xport code itself) and me the pre-export and function code in sc/source/core/tool). If you know of other instances than FLOOR where the same problem occurs, I would be willing to investigate those too. The pre-export mechanism is supposed to deliver valid Excel function calls when exporting to OOXML.
'needsConfirmationAdvise' is only used for unconfirmed bugs. Removing it from this bug. [NinjaEdit]
Created attachment 131396 [details] ods file
Created attachment 131397 [details] save-as xlsx floor file
Bug still present in LO v5.2.5.1 Minimal example in previously attached files =floor(42.5) save as xlsx open in excel excel cannot read...want to fix...blah..blah..ok opens with =_xlfn.FLOOR.MATH(42.5) expected =floor(42.5,1) Perhaps LO should either: 1) require significance option to be set or 2) default to 1 Ceiling function has same issue
Comparison of different FLOOR functions: FLOOR: http://www.excelfunctions.net/Floor-Function.html FLOOR.MATH: http://www.excelfunctions.net/excel-floor-math-function.html FLOOR.PRECISE: http://www.excelfunctions.net/Excel-Floor-Precise-Function.html How FLOOR is working in LO https://help.libreoffice.org/Calc/Mathematical_Functions#FLOOR
Created attachment 131531 [details] save-as xlsx floor file after fix with MS Office 2016
Created attachment 131819 [details] save-as xlsx floor file after manual fix After investigation I found the root cause of importing problem by MS Excel. In file: xl/workbook.xml the following line are causing import failure by MS Excel: <definedNames> <definedName function="true" hidden="false" name="FLOOR" vbProcedure="true"></definedName> </definedNames> After deleting it Excel Sheet is importing perfectly
More information about DefinedName from official MS documentation: https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.definedname.aspx
Review which fix this issue is available at: https://gerrit.libreoffice.org/#/c/35549/
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=ea01a08763e56a7de66f0c24655a627669c8a7f7 Resolves: tdf#100011 (re-)add ocCeil and ocFloor .xls(x) export mappings 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 "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=4dd95cadcd330aee49b3c84d3e336b808217af46 these FUNCFLAG_EXPORTONLY need also FUNCFLAG_MACROCALL_NEW, tdf#100011 related 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.
Pending review https://gerrit.libreoffice.org/36233 for 5-3 https://gerrit.libreoffice.org/36234 for 5-2
Bartosz Kosiorek committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=d5215e0e58febf582b18ad38f5745f8ae924cc1f unit test for CEILING and FLOOR .xlsx export, tdf#100011 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": http://cgit.freedesktop.org/libreoffice/core/commit/?id=84c3729eb210ff255761c4613ba03c4fb5e949f8&h=libreoffice-5-3 Resolves: tdf#100011 (re-)add ocCeil and ocFloor .xls(x) export mappings It will be available in 5.3.3. 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-2": http://cgit.freedesktop.org/libreoffice/core/commit/?id=8da7b298272d04b19150d303292270f85b99858b&h=libreoffice-5-2 Resolves: tdf#100011 (re-)add ocCeil and ocFloor .xls(x) export mappings It will be available in 5.2.7. 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.