Download it now!
Bug 67536 - Functions that have a *_ADD equivalent are not exported correctly to .xls
Summary: Functions that have a *_ADD equivalent are not exported correctly to .xls
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.0.4.2 release
Hardware: Other All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: target:4.2.0 target:4.1.5
Keywords:
Depends on:
Blocks:
 
Reported: 2013-07-30 13:12 UTC by
Modified: 2013-12-01 00:47 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
file saved as ODS (12.17 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-07-30 13:13 UTC,
Details
file saved as XLSX (5.37 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2013-07-30 13:14 UTC,
Details
file saved as XLS (7.50 KB, application/vnd.ms-excel)
2013-07-30 13:14 UTC,
Details

Note You need to log in before you can comment on or make changes to this bug.
Description 2013-07-30 13:12:55 UTC
When using some functions with *_ADD within the name, the table don't work correctly when saving in different formats. This concerns:
- CUMIPMT_ADD
- CUMPRINC_ADD
- EFFECT_ADD
- GCD_ADD
- ISEVEN_ADD
- ISODD_ADD
- LCM_ADD
- NOMINAL_ADD

Not concerned are:
- CONVERT_ADD
- DURATION_ADD
- WEEKNUM_ADD

1) When saving as ODS and opening again, these functions are renamed to its variant without _ADD.

E.g.: Type into A1 "=GCD_ADD(24;18)" and saving the spreadsheet as ODS.

Expected behavior: When opening A1 contains =GCD_ADD(24;18).
Shown behavior: When opening A1 contains =GCD(24;18).

2) When saving as XLSX and opening again, functions without _ADD are converted to subtractions.

E.g.: Type into A1 "=GCD(24;18)" and saving the spreadsheet as XLSX.

Expected behavior: When opening A1 contains =GCD(24;18).
Shown behavior: When opening A1 contains =(24-18).

3) When saving as XLS and opening again, functions without _ADD show #MACRO? as output value.

E.g.: Type into A1 "=GCD(24;18)" and saving the spreadsheet as XLS.

Expected behavior: When opening A1 shows 6.
Shown behavior: When opening A1 shows #MACRO?.
Comment 1 2013-07-30 13:13:38 UTC
Created attachment 83295 [details]
file saved as ODS
Comment 2 2013-07-30 13:14:08 UTC
Created attachment 83296 [details]
file saved as XLSX
Comment 3 2013-07-30 13:14:36 UTC
Created attachment 83297 [details]
file saved as XLS
Comment 4 Thomas Hackert 2013-07-30 14:17:54 UTC
Hello Christian, *,
I can confirm this bug with LO Version: 4.1.1.0.0+
Build ID: 8c1e1d99f10894718975300a052358de7e3fef2
TinderBox: Linux-x86_64@31-Release-Configuration-RHEL5-Baseline, Branch:libreoffice-4-1, Time: 2013-07-28_13:00:24 and installed Germanophone lang- as well as helppack under Debian Testing AMD64 ... :( Furthermore, all this new functions seem not anywhere in LO's OLH ... :(
HTH
Thomas.
Comment 5 2013-07-31 04:28:13 UTC
I can't confirm missing the OLH's content of these functions, the built in help as well as the online help contain these functions.

E.g. the GCD_ADD function you can find here in the OLH:
https://help.libreoffice.org/Calc/Mathematical_Functions#GCD_ADD
Comment 6 Björn Michaelsen 2013-07-31 10:08:29 UTC
I can confirm behaviour 1 and 3 on LibreOffice 4.0.4 already:

 Shown behavior: When opening A1 contains =GCD(24;18).
 Shown behavior: When opening A1 shows #MACRO?.

so not a release regression in 4.1 there at least, but likely older. As I have not confirmed it to have been working in any previous version, not marking it as a regression yet. Requesting bibisect though.

Splitting out behaviour 2 to bug 67572 as is does _not_ show in 4.0.4. In the future, please file one report for each bug. Thanks.
Comment 7 Noel Power 2013-08-07 11:46:44 UTC
since I took bug# 67572 probably these have the same root cause
Comment 8 Noel Power 2013-08-08 10:29:02 UTC
ok, didn't check yet whether the 1 & 3 actually worked previously or not

bug # 67572 is marked as a regression but I guess in my notes here I cover some it as well ( suspect bug #67572 is related to http://cgit.freedesktop.org/libreoffice/core/commit/?id=1162738c6fbd8505ffa27b28118318cc522a5368 but it didn't unapply for me cleanly to check... also I think Eike knows what he is doing so I guess dealing with how it is now is the way to go )

here's how it seems to go..

scenario a) functions saved to ods
==================================

for those function that additionally have builtin variants.....

a) WEEKNUM - actually it seems that the builtin variant for this actually has the text "ISOWEEKNUM" associated with it ( ok makes sense as we need to disambiguate between '_ADD' and without )
b) WEEKNUM_ADD -> this one actually gets saved as table:formula="of:=WEEKNUM(X,Y)" but on import gets mapped to WEEKNUM_ADD ( because ISOWEEKNUM->WEEKNUM )
c) GCD - builtin - text representing the formula  is "GCD" ( or I guess a localized name ) - there is opcode -> rsid type relationship ( think I saw that somewhere ) and we write -> table:formula="of:=GCD(34;1048)"
d) GCD_ADD ( addin )  - the text representing the formula is also "GCD" ( and same table:formula="of:=GCD(34;1048)") sent to ods.  It seems the Symbol -> Addin name is created from the entries in ScCompiler::maAddInMap and it is the ODF name that is used as the Symbol ( e.g. in the case of GCD_ADD ( the english name ) it is "GCD" (the odf name) that is returned

Question? why is the odf name GCD the same for the builtin and addin functions ?
how can we distinguish them? Or is this a mapping error ( e.g. mistake in the maAddInMap entries ) Shouldn't we be outputting the English ( I didn't see how a localised name could be got for those ScCompiler::maAddInMap entries ) Or is it possible to have a 'ISOGSD" type arrangement like for WEEKNUM ( didn't look yet at where the WEEKNUM->ISOWEEKNUM relationship is established )


scenario b) functions saved as xlsx
===================================
afaict all the "_add" functions behave the same, basically if the "_add" name is used then the correct version is exported to xlsx

however if say you have GCD(x,y) then it is "(x,y)" that is exported, basicially when attempting to create a string from the formula tokens GCD is 'correctly' seen as a builtin opCode, however it has no entry when FormulaCompiler::getSymbol( builitn opcode for GCD ) is queried. Well odf builtin GCD doesn't exist in XLSX ( Compiler set CONV_XL_A1 ) with so I guess the code is doing what it is supposed to do ( but not what we expect... or is that right ) I mean I would expect that it would sortof do the the mapping from GCD->GCD ( etc. ) perhaps in FormulaCompiler::CreateStringFromToken if the builtin token doesn't resolve to a string we could fallback to the native string? would that be a viable solution ?
Comment 9 Noel Power 2013-08-08 13:30:53 UTC
Eike, I could do with some advice how to proceed ( was going to cc you but see you are already cc'd )
Comment 10 Eike Rathke 2013-08-21 16:49:58 UTC
1) save/load ODF .ods
   AddIn *_ADD functions that have a functional internal equivalent without
   _ADD that is defined in ODFF are saved without _ADD suffix and when loaded
   map to the internal one. This is on purpose and doesn't harm at all.

2) save/load OOXML .xlsx or BIFF .xls
   The internal functions without _ADD seem not to be known to the Excel
   mappings, likely because the LO AddIn ones actually were implemented as an
   equivalent to the Excel Analysis Pack AddIn ones, those AddIn functions
   have to follow specific conventions when saved to Excel files, and such
   definitions do not exist for the LO internal functions. The AddIn functions
   are exported correctly automatically. For the BIFF case apparently the
   internal function is stored but along with some #MACRO? error because the
   function is not known to Excel and thus stored as macro. For the OOXML case
   an explicit mapping exists but resolves to writing the AddIn function. Not
   sure yet why when saving the internal function the name is omitted, which
   when loading (a,b) correctly resolves to the Union (not minus) operator
   (a~b)
Comment 11 Eike Rathke 2013-08-21 16:51:05 UTC
Maybe best that I take this over, also bug 67572.
Comment 12 Joel Madero 2013-10-29 03:12:22 UTC
I don't know how useful a bibisect is here. 

Bjoern - you mind trying? 

For #1 - bibisect is useless
For #2 - can't reproduce
For #3 - I don't get the #macro but it does add _ADD in bibisect, vs. in 4.1 it does change it to something weird with a ~. So basically I can't confirm it was ever working correctly so bibisecting is difficult
Comment 13 Commit Notification 2013-11-02 15:39:58 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=06a5b1ecc39bb50dadc0bcb7378455500943ece3

resolved fdo#67536 export internal equivalents as add-in to .xls



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 Eike Rathke 2013-11-02 15:43:02 UTC
Note that the change fixes the .xls export only, for .xlsx exists the split-off bug 67572
Comment 15 Eike Rathke 2013-11-02 15:55:55 UTC
Pending review for 4-1 at https://gerrit.libreoffice.org/6525
Comment 16 Commit Notification 2013-12-01 00:47:30 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-4-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=db0d51e647e01d852ef95e46e12f1dba2d09be2b&h=libreoffice-4-1

resolved fdo#67536 export internal equivalents as add-in to .xls


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