Bug 151166 - [FILEOPEN] NUMBERS codec missing functions
Summary: [FILEOPEN] NUMBERS codec missing functions
Status: NEW
Alias: None
Product: Document Liberation Project
Classification: Unclassified
Component: libetonyek (show other bugs)
Version:
(earliest affected)
unspecified
Hardware: All All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: difficultyInteresting, easyHack, skillCpp, skillDebug
Depends on:
Blocks: Apple-iWork
  Show dependency treegraph
 
Reported: 2022-09-24 23:09 UTC by SheetJS
Modified: 2024-06-04 13:10 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
specimen (120.00 KB, application/zip)
2022-09-24 23:09 UTC, SheetJS
Details
Numbers 12.1 formulae missing from 9c3a8cb8 (157.86 KB, application/zip)
2022-09-27 18:17 UTC, SheetJS
Details
Numbers address documentation (233.46 KB, image/png)
2022-09-28 08:36 UTC, osnola
Details

Note You need to log in before you can comment on or make changes to this bug.
Description SheetJS 2022-09-24 23:09:04 UTC
Description:
Some functions like `ISDATE` are missing, instead rendered as `funct306` or some other number based on the function ID.

Steps to Reproduce:
Open attachment

Actual Results:
Formula for cell C2 is `=funct306($A2)`

Expected Results:
Formula for cell C2 is `ISDATE($A2)`


Reproducible: Always


User Profile Reset: No



Additional Info:
The last few releases of Numbers introduced new functions (12.1 was released in late June 2022), so libetonyek may need a refresh.
Comment 1 SheetJS 2022-09-24 23:09:25 UTC
Created attachment 182665 [details]
specimen
Comment 2 Julien Nabet 2022-09-25 08:05:29 UTC
Laurent/David: one for you since it concerns libetonyek?
Comment 3 Eike Rathke 2022-09-25 11:16:34 UTC
Appears to be this map
https://opengrok.libreoffice.org/xref/libetonyek/src/lib/IWAParser.cpp?r=9c3a8cb8&mo=116710&fi=3603#3537

Tagging this as EasyHack, with some knowledge and/or debugging with a completed Numbers document the mapping could be enhanced.
Comment 4 SheetJS 2022-09-27 18:16:18 UTC
list in 9c3a8cb8 extends to 303 "CurrencyCode".

starting from 304, based on Numbers 12.1 :

code | name in Numbers
-----+----------------
 304 | ISNUMBER
 305 | ISTEXT
 306 | ISDATE
 309 | MAXIFS
 310 | MINIFS
 311 | XIRR
 312 | XNPV
 313 | IFS
 314 | XLOOKUP
 315 | XMATCH
 316 | SUBTOTAL
 317 | COUNTMATCHES
 318 | TEXTBEFORE
 319 | TEXTBETWEEN
 320 | TEXTAFTER
 321 | REGEX
 322 | REFERENCE.NAME
 323 | FORMULATEXT
 324 | REGEX.EXTRACT
 325 | GETPIVOTDATA

Laurent: There is a semi-related issue https://bugs.documentfoundation.org/show_bug.cgi?id=150782 .  Seeing some of the names like `IWORKFormula::Address`, is this the right place / time to change 194 to "DURATION" and 212 to something different?
Comment 5 SheetJS 2022-09-27 18:17:39 UTC
Created attachment 182700 [details]
Numbers 12.1 formulae missing from 9c3a8cb8
Comment 6 osnola 2022-09-28 08:36:35 UTC
Created attachment 182703 [details]
Numbers address documentation

(In reply to SheetJS from comment #5)
>
> Laurent: There is a semi-related issue
> https://bugs.documentfoundation.org/show_bug.cgi?id=150782 .  Seeing some of
> the names like `IWORKFormula::Address`, is this the right place / time to
> change 194 to "DURATION" and 212 to something different?

I'm not sure why I wrote `IWORKFormula::Address`; maybe because I thought this function is different from the Address function of odf, but I'm not sure if this is true ( see attachment ). 

Otherwise yes, this function creates the result that will be returned to librevenge, so we can change the name of the functions or make special cases: for instance, if the function is "toto" and it has two parameters, return instead of toto(stack_1,stack_2): toto(tuto(stack_2), stack_1, 1) with some work.


Note:
- the format of iWorks files has changed over time, at first the files were compressed xml files ( and the formulas were strings, in this case IworkFormula::parse is called to convert such string into the structure wanted by librevenge). Now it uses a binary format, we just have to switch from a suffix notation to the usual notation.
Comment 7 SheetJS 2022-09-28 21:11:36 UTC
> I thought this function is different from the Address function of odf,

You probably tested it :) They give different results with cross-sheet/table references.

=ADDRESS(2,2,2,FALSE,"Sheet2") is the string value "Sheet2!R2C[2]" in LibreOffice and Excel.  In Numbers it is the string value "Sheet2::R2C[2]" using two colons.

Overall the casing in the map is a bit inconsistent.  A number of functions like CUMPRINC are all upper-case letters, while others like AverageIfs use proper case
Comment 8 osnola 2022-09-29 07:46:18 UTC
(In reply to SheetJS from comment #7)

> Overall the casing in the map is a bit inconsistent.  A number of functions
> like CUMPRINC are all upper-case letters, while others like AverageIfs use
> proper case

For the casing, I think I was trying to make the functions more human readable, and since I didn't know CUMPRINC, I left it that way. I would have looked at the documentation, I probably would have written it as CumPrinc to indicate that it's short for "Cumulative Principal".

But feel free to change it, it doesn't really matter (as long as LibreOffice accepts to use CumPrinc or CUMPRINC in a formula).
Comment 9 SheetJS 2022-10-27 22:10:54 UTC
Numbers 12.2 new functions:

code | name in Numbers
-----+----------------
 328 | TEXTJOIN
 329 | CONCAT
 330 | BITAND
 331 | BITOR
 332 | BITXOR
 333 | BITLSHIFT
 334 | BITRSHIFT
 335 | ISOWEEKNUM
 336 | SWITCH