Bug 100011 - Cannot open sheet containg FLOOR/CEILING functions by MS Excel, after export to .xlsx
Summary: Cannot open sheet containg FLOOR/CEILING functions by MS Excel, after export ...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.1.3.2 release
Hardware: All All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: target:5.4.0 target:5.3.3 target:5.2.7
Keywords:
Depends on:
Blocks:
 
Reported: 2016-05-23 15:06 UTC by Winfried Donkers
Modified: 2017-04-07 07:51 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
Xlsx-document created with Calc 5.1.3 (4.81 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-05-23 15:06 UTC, Winfried Donkers
Details
Xlsx-document saved by Excel2016 after auto-repair (8.95 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-05-23 15:08 UTC, Winfried Donkers
Details
The 'original' ods document (10.57 KB, application/x-vnd.oasis.opendocument.spreadsheet)
2016-05-30 06:17 UTC, Winfried Donkers
Details
Xlsx-document created with Calc 5.1.3 and manually fixed. (4.77 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-08-04 15:11 UTC, Bartosz
Details
manually fixed document opened and saved by Excel2016 (8.94 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-08-05 05:49 UTC, Winfried Donkers
Details
Minimal ods file with bug reproduction (8.75 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-08-07 03:18 UTC, Bartosz
Details
Minimal .xlsx file created with LibreOffice 5.1 (4.45 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-08-07 03:26 UTC, Bartosz
Details
ods file (7.67 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-02-21 20:00 UTC, Tad Whiteside
Details
save-as xlsx floor file (4.34 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2017-02-21 20:00 UTC, Tad Whiteside
Details
save-as xlsx floor file after fix with MS Office 2016 (8.11 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2017-02-28 11:29 UTC, Bartosz
Details
save-as xlsx floor file after manual fix (4.30 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2017-03-11 22:21 UTC, Bartosz
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Winfried Donkers 2016-05-23 15:06:05 UTC
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.
Comment 1 Winfried Donkers 2016-05-23 15:08:39 UTC
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.
Comment 2 Buovjaga 2016-05-28 14:05:51 UTC
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)
Comment 3 Winfried Donkers 2016-05-30 06:17:27 UTC
Created attachment 125383 [details]
The 'original' ods document

Document created by version 5.1.3, as requested in comment#2
Comment 4 Buovjaga 2016-06-14 14:14:01 UTC
(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)
Comment 5 Bartosz 2016-08-04 14:35:44 UTC
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
Comment 6 Bartosz 2016-08-04 15:10:18 UTC
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&lt;=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&lt;=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&lt;=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&lt;=0,-0.5,0.5),1)</f>
                <v>#VALUE!</v>
            </c>
        </row>
Comment 7 Bartosz 2016-08-04 15:11:44 UTC
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?
Comment 8 Winfried Donkers 2016-08-05 05:49:05 UTC
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
Comment 9 Bartosz 2016-08-07 03:18:36 UTC
Created attachment 126634 [details]
Minimal ods file with bug reproduction
Comment 10 Bartosz 2016-08-07 03:26:09 UTC
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.
Comment 11 Winfried Donkers 2016-08-29 11:50:56 UTC
(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?
Comment 12 Bartosz 2016-08-29 15:11:24 UTC
@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.
Comment 13 Winfried Donkers 2016-08-31 10:43:11 UTC
(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.
Comment 14 Xisco Faulí 2016-09-19 15:29:44 UTC Comment hidden (obsolete)
Comment 15 Tad Whiteside 2017-02-21 20:00:00 UTC
Created attachment 131396 [details]
ods file
Comment 16 Tad Whiteside 2017-02-21 20:00:49 UTC
Created attachment 131397 [details]
save-as xlsx floor file
Comment 17 Tad Whiteside 2017-02-21 20:03:03 UTC
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
Comment 19 Bartosz 2017-02-28 11:29:44 UTC
Created attachment 131531 [details]
save-as xlsx floor file after fix with MS Office 2016
Comment 20 Bartosz 2017-03-11 22:21:28 UTC
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
Comment 21 Bartosz 2017-03-11 22:27:48 UTC
More information about DefinedName from official MS documentation:
https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.definedname.aspx
Comment 22 Bartosz 2017-03-26 20:51:10 UTC
Review which fix this issue is available at:
https://gerrit.libreoffice.org/#/c/35549/
Comment 23 Commit Notification 2017-04-06 19:25:34 UTC
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.
Comment 24 Commit Notification 2017-04-06 19:34:52 UTC
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.
Comment 25 Eike Rathke 2017-04-06 20:07:29 UTC
Pending review
https://gerrit.libreoffice.org/36233 for 5-3
https://gerrit.libreoffice.org/36234 for 5-2
Comment 26 Commit Notification 2017-04-06 20:59:44 UTC
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.
Comment 27 Commit Notification 2017-04-07 07:48:00 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=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.
Comment 28 Commit Notification 2017-04-07 07:51:01 UTC
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.