blob: 08649e17f0fb8dd3edaecafcb745ea025579387a [file] [view]
<!--
Licensed to the Apache Software Foundation (ASF) under one
or more contributor license agreements. See the NOTICE file
distributed with this work for additional information
regarding copyright ownership. The ASF licenses this file
to you under the Apache License, Version 2.0 (the
"License"); you may not use this file except in compliance
with the License. You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing,
software distributed under the License is distributed on an
"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
KIND, either express or implied. See the License for the
specific language governing permissions and limitations
under the License.
-->
OpenFormula Function Compatibility Gap Audit — Implementation Roadmap
=====================================================================
Branch: openformula
Starting commit: 6a2f742073
Audit type: Read-only source inspection (roadmap is a proposal, not committed work)
This document proposes an implementation sequence for the 15 audited
functions, grouping related work where appropriate. No production
code changes have been made.
Updated 2026-11-18 to match the code after the post-merge review: the branch
tip did not compile and several H.1-H.9 changes were wrong; the fixes are
noted per group. The H.x regression rows were replaced by 99 rows checked
against Excel 16 (end of test/odf-validate/calcval-cases14.tsv), which match
on macOS and Linux builds.
Group 1 — Byte-Oriented Text Functions (FINDB, REPLACEB, SEARCHB) — COMPLETED
--------------------------------------------------------------------------------
Status: Implemented in H.2.
Changes:
1. Added SC_OPCODE_FINDB=412, SC_OPCODE_SEARCHB=413, SC_OPCODE_REPLACEB=414
to compiler.hrc. Existing opcodes (BITAND=404 through IFERROR=411) were
NOT renumbered — new opcodes were added at the end of the function range.
(The earlier note that XLS files store 8192 + opcode was wrong:
TokenPool::nScTokenOff in tokstack.cxx is an in-memory offset.)
2. Added ocFindB, ocSearchB, ocReplaceB to opcode.hxx enum.
3. Registered function names in core_resource.src (ODF and Excel sections,
plus localized en-US text).
4. Added HID_FUNC_FINDB, HID_FUNC_SEARCHB, HID_FUNC_REPLACEB to helpids.h
and hidother.src.
5. Added dispatch cases in interpr4.cxx.
6. Implemented ScFindB(), ScSearchB(), ScReplaceB() in interpr1.cxx
using DBCS byte semantics (consistent with existing LENB/LEFTB/RIGHTB/
MIDB). Added lcl_BytePosToCharIndex() and lcl_CharIndexToBytePos()
helpers for byte↔character position conversion.
2026-11-18: ScFindB() and ScSearchB() popped the two text arguments in
the wrong order (FINDB("b";"abc") searched "b" for "abc"); fixed.
xls: now BIFF 205-207 both ways (xlformula.cxx saFuncTable_2), and
Excel 16 calculates FINDB from an AOO xls; xlsx: read
(formulabase.cxx, ODF names FINDB/SEARCHB/REPLACEB).
7. Regression rows in calcval-cases14.tsv (FINDB.*, SEARCHB.*,
REPLACEB.*; rewritten 2026-11-18).
8. Added Function Wizard documentation in main/sc/source/ui/src/scfuncs.src
(SC_OPCODE_FINDB, SC_OPCODE_SEARCHB, SC_OPCODE_REPLACEB resource
blocks with parameter names and descriptions).
Byte semantics: DBCS model (1 byte for ASCII/non-DBCS chars, 2 bytes for
DBCS chars per IsDBCS()). Consistent with existing LENB/LEFTB/RIGHTB/
MIDB implementations. Byte positions are 1-based. Partial DBCS character
boundaries are avoided in REPLACEB by scanning forward byte-by-byte.
Rationale: These three functions were entirely missing from the byte-
oriented text function family. Now implemented following the existing
B-function patterns.
Group 2 — Date/Time Compatibility (DATEDIF, ISOWEEKNUM) — DATEDIF COMPLETED
--------------------------------------------------------------------------------
DATEDIF Status: Implemented in H.3.
Changes:
1. Added SC_OPCODE_DATEDIF = 415 to compiler.hrc. Existing opcodes
unchanged. (STOP_2_PAR is now 420 and LAST_OPCODE_ID 419.)
2. Added ocDateDif to opcode.hxx enum.
3. Registered "DATEDIF" name in core_resource.src (ODF and Excel sections,
plus localized en-US text).
4. Added HID_FUNC_DATEDIF to helpids.h and hidother.src.
5. Added dispatch case in interpr4.cxx.
6. Implemented ScInterpreter::ScDateDif() in interpr2.cxx
implementing ODF 1.4 §6.10.3 with all six formats (y, m, d, md, ym, yd).
2026-11-18: rewritten to Excel's behavior where ODF is silent (Jim's
decision): start after end is an error, the format ignores case, "md"
and "yd" follow Excel; "yd" was wrong before. See
datedif-semantic-design.md.
7. Added Function Wizard entry in scfuncs.src (Start_date, End_date, Format
parameters, ID_FUNCTION_GRP_DATETIME group).
8. Regression rows DATEDIF.* in calcval-cases14.tsv (rewritten
2026-11-18).
9. Fixed BIFF5/BIFF7 import mapping in xlformula.cxx:
changed `ocNoName` → `ocDateDif` for function ID 351 (DATEDIF).
2026-11-18: the import-only flag was dropped, so DATEDIF is written
to xls too (Excel 16 calculates it); xlsx reads DATEDIF.
Semantic design: See datedif-semantic-design.md for detailed algorithm
specification including reverse-date policy, time fraction handling, and
month/year boundary semantics.
ISOWEEKNUM: COMPLETED in H.5 (commit on openformula branch).
Changes:
1. Added SC_OPCODE_ISOWEEKNUM = 416 (compiler.hrc, opcode.hxx).
2. Added ScIsoWeekNum() in interpr2.cxx using Date::GetWeekOfYear(MONDAY, 4).
3. Added dispatch case ocIsoWeekNum in interpr4.cxx.
4. Registered ISOWEEKNUM name in core_resource.src (ODF and Excel namespaces).
The ODF list also has ISOWEEKNUM for ocWeek, which is how AOO writes
WEEKNUM(d;mode); on reading, the first entry wins. 2026-11-18:
FormulaCompiler::Factor turns an ODF ISOWEEKNUM with one argument into
ocIsoWeekNum, so files from other applications load; two arguments
stay WEEKNUM.
5. Added function wizard resource in scfuncs.src (1 argument, no mode;
its ExtraData had one number too many, fixed 2026-11-18).
6. Added HID_FUNC_ISOWEEKNUM in helpids.h and hidother.src.
7. Added help topic func_isoweeknum.xhp, registered in makefile and 04060102.xhp.
8. Calcval rows ISOWEEKNUM.* (rewritten 2026-11-18; several SG.ISO
expectations were wrong, e.g. week 1 for Sunday 2024-12-29).
xls: written as `_xlfn.ISOWEEKNUM` (2026-11-18). xlsx `_xlfn.ISOWEEKNUM`
is read too (2026-11-18).
9. WEEKNUM (ScGetWeekOfYear) unchanged: retains 2-arg SUN/MON mode.
Rationale: ISOWEEKNUM needed separate opcode and evaluator for ISO 8601
compliance (1 arg, Monday start, week 1 = first Thursday). Now resolved.
Group 3 — Financial Odd-Period Functions (ODDFPRICE, ODDFYIELD)
---------------------------------------------------------------
Status: native opcodes DEFERRED — pending financial function registration
policy. The Analysis add-in functions were implemented on 2026-11-18 (Jim's
decision).
Rationale: H.6.1 opcode registration was reverted (f4f3de4f92).
A broader financial function registration policy must be established
before allocating native opcodes to ODDFPRICE/ODDFYIELD. This includes:
1. Inventory of native vs AddIn-only financial functions.
2. Consistent criteria for opcode allocation, import/export,
Function Wizard, and help registration.
3. Decision on whether ODDFPRICE/ODDFYIELD should receive native
implementations or remain AddIn-only.
Current state: Implemented as Analysis AddIn only
(financial.cxx getOddfprice/getOddfyield, analysishelper.cxx
GetOddfprice/GetOddfyield). On trunk both threw (THROW_RTE, #87380#);
8ce50c3485 brought in an unfinished version. The 2026-11-18 code follows
Excel's short and long odd first period formulas, requires the first coupon
to be on the maturity's coupon schedule (as Excel), and solves the yield
with a bracketed Illinois iteration. Against Excel 16 on 4524 random cases:
14 differences, all classified (4 where DFC equals E and Excel gives #NUM!,
5 annual 30/360 bonds with Feb-end coupons off by 2/360 of a coupon, 5
where Excel's yield is less exact or wrong). No native opcode; Function
Wizard and English help (04060118.xhp) come from the add-in.
PRIORITY: P2 (Excel compatibility gap). DEFERRED until policy defined.
Group 4 — RRI (Rate of Return)
------------------------------
Recommended sequence:
1. Review ScZGZ() (interpr2.cxx:1198) for missing validation:
zero/negative present value, zero periods, overflow/underflow.
2. Add input validation (PushIllegalArgument for invalid inputs).
3. English help exists (04060118.xhp, HID_FUNC_ZGZ).
4. Verify Excel import/export mapping (`_xlfnodf.RRI` may cause
Excel compatibility issues — Excel has RRI natively).
5. Add test cases.
Rationale: Implementation exists but lacks validation (ScZGZ() checks
nothing; ODF requires N > 0). Export naming may cause Excel compatibility
issues.
2026-11-18: the ODF and English names are RRI (ZGZ is still read, through
OpCodeMap::putOpCodeAlias). The RRI commit had also renamed ocZGZ,
SC_OPCODE_ZGZ, ScZGZ() and HID_FUNC_ZGZ and reworded the help; that was
undone, because translations are keyed on SC_OPCODE_ZGZ and the German
function name ZGZ would have been lost.
Group 5 — ERROR.TYPE
--------------------
Status: COMPLETED in H.4, redone 2026-11-18.
H.4 changed ScErrorType() itself, so ERRORTYPE (ODF name
ORG.OPENOFFICE.ERRORTYPE) stopped returning the internal error numbers its
help documents (Err:518 gives 518). Jim's decision: ERRORTYPE is back to
the trunk behavior and ERROR.TYPE is its own opcode.
Changes now:
1. SC_OPCODE_ERROR_TYPE_ODF = 419, ocErrorTypeODF, names "ERROR.TYPE"
(ODF, English, UI). ScErrorType( bool bODF ) with lcl_ErrorTypeODF()
in interpr1.cxx; the mapping is that of XclTools::GetXclErrorCode, so
Err:502 gives 3, anything not listed 7. Listed in CASE_OCERRFUNC.
2. xls: BIFF 261 reads as ERROR.TYPE; ERRORTYPE is still written as 261.
xlsx: ERROR.TYPE reads as ERROR.TYPE.
3. Help: func_errortype.xhp with its own help ID HID_FUNC_ERROR_TYPE_ODF
(it had reused ERRORTYPE's), embedded in 04060109.xhp next to
ERRORTYPE instead of the date functions page.
4. Calcval rows `ERROR.TYPE.*` and ERRORTYPE.legacy*.
Group 6 — NUMBERVALUE (Critical Gap) — COMPLETED
------------------------------------------------
Status: Implemented in H.1.1 (commit on openformula branch); rewritten
2026-11-18 to the ODF step order and moved to interpr1.cxx next to VALUE.
Changes:
1. Added case ocNumberValue : ScNumberValue(); to the interpr4.cxx
dispatch switch.
2. ScInterpreter::ScNumberValue() in interpr1.cxx, following ODF 1.2/1.4
§6.13.28: group separators removed only before the decimal separator,
the first decimal separator becomes '.', whitespace removed, trailing
percent signs divide by 100, and the rest must be a plain xsd:float
(lcl_IsPlainFloat()). LEN(DecimalSeparator) must be 1 and it must not
occur in GroupSeparator. Omitted separators come from the locale; the
locale group separator is dropped when it equals the given decimal
separator.
3. Regression rows NUMBERVALUE.* in calcval-cases14.tsv (rewritten
2026-11-18).
4. Function Wizard resource in scfuncs.src: the trunk strings kept,
unsuppressed, a third parameter added, parameters 2 and 3 optional
(the H.1 block had one flag too few).
Differences from Excel 16 (checked): empty text is an error (Excel 0); an
empty GroupSeparator is accepted (Excel error); a two-character
DecimalSeparator is an error (Excel uses the first).
Rationale: CRITICAL gap resolved — opcode (392) and name registered
but no evaluator existed. Function now compiles and evaluates
correctly. P1 priority resolved.
Group 7 — GETPIVOTDATA
----------------------
Recommended sequence:
1. Review ScGetPivotData() (interpr2.cxx:2972) for correctness.
2. XLSX import exists (formulabase.cxx GETPIVOTDATA row, import-only);
there is no xlsx export at all. Not run.
3. Verify DataPilot dependency handling (refresh, multiple tables).
4. Add test cases.
Rationale: Implementation present. DataPilot-dependent application-level
function.
Group 8 — FDIST, FINV (Legacy Statistical)
------------------------------------------
2026-11-18: H.8/H.8.1 turned ocFDist/ocFInv into the ODF left-tail
functions and added ocLegacyFDist/ocLegacyFInv, which changed the meaning of
FDIST/FINV typed in every UI language, in macros and in .sxc files; the H.6.1
revert also dropped the new opcode defines, so the branch did not build.
Jim's decision: FDIST and FINV stay as on trunk (right tail; ODF
LEGACY.FDIST/LEGACY.FINV; xls 281/282), and the ODF left-tail functions are
new opcodes F.DIST (SC_OPCODE_F_DIST_LEFT 417, ScFDistLeft()) and F.INV
(SC_OPCODE_F_INV_LEFT 418, ScFInvLeft()), with ODF names FDIST and FINV.
F.DIST takes an optional Cumulative (default TRUE), returns 0 for x < 0 as
ODF says, and gives the density through GetBetaDistPDF(). F.DIST matches
Excel 16 on 300 random cases; F.INV on 298 of 300, the other two within
3e-10 where the distribution is almost flat (p = 0.999, r2 = 1). xls: `_xlfn.F.DIST`/`_xlfn.F.INV`
(Excel calculates them; F.DIST needs four arguments there), and `_xlfnodf.FDIST`/`_xlfnodf.FINV` from older files import as FDIST/FINV. Help: F.DIST and F.INV sections in
04060182.xhp.
Original recommended sequence:
1. Review ScFDist() (interpr3.cxx:1588) — enforces exactly 3
params; ODF allows 3–4 with cumulative flag.
2. Review ScFInv() (interpr3.cxx:2154) — numerical inversion via
iteration.
3. Consider adding optional 4th cumulative parameter to FDIST.
4. Review export naming: `_xlfnodf.FDIST`/`_xlfnodf.FINV` — Excel has
these natively without _xlfn prefix; may cause compatibility
issues.
5. English help exists for both (04060182.xhp; FVERT is only the help ID
name).
6. Add test cases.
Rationale: Implementations present but export naming and parameter
count are gaps. P2 priority.
Group 9 — HARMEAN
-----------------
Recommended sequence:
1. Review ScHarMean() (interpr3.cxx:2712) — counts only x>0,
errIllegalArgument for x<=0. Verify against ODF definition.
2. English help exists (04060182.xhp; HARMITTEL is only the help ID name).
3. BIFF 320 both ways (xlformula.cxx saFuncTable_4) and xlsx 320
(formulabase.cxx).
4. Add test cases.
Rationale: Implementation present and appears correct. No gap left in
this audit's terms.
Group 10 — DDE (Deferred)
-------------------------
Recommended sequence:
1. Evaluate platform support (Linux, Windows, macOS).
2. Address security concerns (external application access,
user confirmation).
3. Consider whether DDE should be available in headless/server mode
(currently disabled).
4. If implemented: add Excel import/export mappings, test cases.
Rationale: DEFER — requires substantial platform/application-level
work; unresolved security and platform-appropriateness questions.
Not a pure formula function.
Group 11 — HYPGEOMDIST Cumulative (H.9, added 2026-11-18)
---------------------------------------------------------
H.9/H.9.1 added the optional fifth argument Cumulative (default FALSE). The
optional argument was popped after the others, one line of the trunk PMF
algorithm was changed (wrong in 12240 of 135750 exhaustive small cases), and
the function header was lost in a later merge. 2026-11-18: the trunk
algorithm itself gave wrong results for some large arguments
(HYPGEOMDIST(900;1828;2033;4167) gave 3.2E+181 instead of 0.0219), so
GetHypGeomDist() now multiplies the C(n,x) factors and the probability ratios
in an order that keeps the product near 1 (same size limit as before).
Cumulative sums the smaller tail with term ratios. Checked: all 635375 small
cases against exact values (worst relative error 9e-14), 400 large cases
against exact big-integer values, and Excel 16 HYPGEOM.DIST/HYPGEOMDIST on 895 cases.
xls cannot hold five arguments (written as NA()).
Prioritization Summary
----------------------
P1 (Critical — ODF-required behavior missing):
- ERROR.TYPE: RESOLVED in H.4
- NUMBERVALUE: RESOLVED in H.1
- FINDB/REPLACEB/SEARCHB: RESOLVED in H.2
- DATEDIF: RESOLVED in H.3/H.3.1
P1 (High — semantic gaps):
- ISOWEEKNUM: RESOLVED in H.5
P2 (Excel compatibility):
- ODDFPRICE/ODDFYIELD: add-in implemented 2026-11-18; native opcodes
DEFERRED — pending financial registration policy
- RRI: no validation of N > 0; xls export as `_xlfnodf.RRI`
- GETPIVOTDATA: xlsx import present, not run
- F.DIST: xls export with three arguments shows #N/A in Excel
P3 (Minor):
- HARMEAN: nothing open
DEFER (Platform/application-level):
- DDE: Requires DDE subsystem; security questions
Confidence Levels
-----------------
Run and checked against Excel 16 (2026-11-18): FINDB, REPLACEB, SEARCHB
(ASCII; CJK counts 2 bytes in AOO always), DATEDIF, ISOWEEKNUM, NUMBERVALUE,
ERROR.TYPE, F.DIST, F.INV, FDIST, FINV, HYPGEOMDIST, ODDFPRICE, ODDFYIELD,
RRI (one case). Source only: GETPIVOTDATA, HARMEAN, DDE.