xref: /trunk/audit/openformula-function-gaps/biff-function-mapping-audit.md (revision 195598447694ec8542914a9b51ae9942e8ebacd9)
1<!--
2 Licensed to the Apache Software Foundation (ASF) under one
3 or more contributor license agreements.  See the NOTICE file
4 distributed with this work for additional information
5 regarding copyright ownership.  The ASF licenses this file
6 to you under the Apache License, Version 2.0 (the
7 "License"); you may not use this file except in compliance
8 with the License.  You may obtain a copy of the License at
9
10   http://www.apache.org/licenses/LICENSE-2.0
11
12 Unless required by applicable law or agreed to in writing,
13 software distributed under the License is distributed on an
14 "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
15 KIND, either express or implied.  See the License for the
16 specific language governing permissions and limitations
17 under the License.
18-->
19
20BIFF Formula Function Mapping Audit
21====================================
22
23Branch: openformula
24HEAD: a330cbd51b (DATEDIF BIFF import mapping fix)
25Audit type: Read-only source inspection (no build, no runtime execution)
26
27Updated 2026-11-18 to match the code after the post-merge review. Rows marked
28"run" were written to xls by AOO, read back by AOO and opened in Excel 16.
29
30Methodology
31-----------
32
33Inspected `main/sc/source/filter/excel/xlformula.cxx` which contains all
34BIFF formula function mapping tables:
35
36- saFuncTable_2 (BIFF2, IDs 0-211)
37- saFuncTable_3 (BIFF3, IDs 219-244)
38- saFuncTable_4 (BIFF4, IDs 214-332)
39- saFuncTable_5 (BIFF5/7, IDs 70-354)
40- saFuncTable_8 (BIFF8, IDs 358-367)
41- saFuncTable_Oox (OOXML future functions, `_xlfn.` prefix, NOID/255)
42- saFuncTable_Odf (OpenFormula namespace, `_xlfnodf.` prefix, NOID/255)
43
44Import path: `FillXclFuncMap()` skips EXC_FUNCFLAG_EXPORTONLY entries,
45adds entries with non-NOID BIFF function IDs to maXclFuncMap.
46Export path: `FillScFuncMap()` skips EXC_FUNCFLAG_IMPORTONLY entries,
47adds all others to maScFuncMap keyed by opcode.
48
49BIFF function IDs and Calc opcode IDs are distinct namespaces. A BIFF
50function ID is the number Excel uses in its binary file format; a Calc
51opcode is AOO's internal representation. The BIFF tables map between them.
52
53Inventory
54---------
55
5632 rows. The functions of the OpenFormula compatibility work (H.1-H.9), the
57other function-gap entries and the B-function family (LENB/RIGHTB/MIDB/LEFTB).
58
59| # | Function | Opcode | Native Evaluator | BIFF ID | Import | Export | Classification |
60|---|----------|--------|-----------------|---------|--------|--------|----------------|
61| 1 | NUMBERVALUE | ocNumberValue (392) | Yes | None (_xlfnodf) | NOID import-only | 255 export-only | Correct; Excel shows #NAME? (run) |
62| 2 | FINDB | ocFindB (412) | Yes | BIFF2 ID 205 | both | both | Correct (added 2026-11-18; run) |
63| 3 | SEARCHB | ocSearchB (413) | Yes | BIFF2 ID 206 | both | both | Correct (added 2026-11-18) |
64| 4 | REPLACEB | ocReplaceB (414) | Yes | BIFF2 ID 207 | both | both | Correct (added 2026-11-18) |
65| 5 | DATEDIF | ocDateDif (415) | Yes | BIFF5 ID 351 | both | both | Correct (export added 2026-11-18; run) |
66| 6 | IFNA | ocIfna | Yes | None (_xlfn) | NOID import-only | 255 export-only | Correct |
67| 7 | SKEWP | ocSkewP | Yes | None (_xlfn) | NOID import-only | 255 export-only | Correct |
68| 8 | IFERROR | ocIfError | Yes | None (_xlfn) | NOID import-only | 255 export-only | Correct |
69| 9 | BITAND | ocBitAnd | Yes | None (_xlfn) | NOID import-only | 255 export-only | Correct |
70| 10 | BITOR | ocBitOr | Yes | None (_xlfn) | NOID import-only | 255 export-only | Correct |
71| 11 | BITXOR | ocBitXor | Yes | None (_xlfn) | NOID import-only | 255 export-only | Correct |
72| 12 | BITLSHIFT | ocBitLShift | Yes | None (_xlfn) | NOID import-only | 255 export-only | Correct |
73| 13 | BITRSHIFT | ocBitRShift | Yes | None (_xlfn) | NOID import-only | 255 export-only | Correct |
74| 14 | ISOWEEKNUM | ocIsoWeekNum (416) | Yes | None (_xlfn) | NOID import-only | 255 export-only | Correct (added 2026-11-18; run) |
75| 15 | WEEKNUM as ODF ISOWEEKNUM | ocWeek (365) | Yes | None (_xlfnodf) | NOID import-only | 255 export-only | Correct |
76| 16 | DDE | ocDde (369) | Yes | None | none | none | Intentional limitation |
77| 17 | ODDFPRICE | None (AddIn) | No | None | none | none | Intentional limitation |
78| 18 | ODDFYIELD | None (AddIn) | No | None | none | none | Intentional limitation |
79| 19 | RRI | ocZGZ (256) | Yes | None (_xlfnodf) | NOID import-only | 255 export-only | Correct; Excel shows #NAME? (run) |
80| 20 | ERROR.TYPE | ocErrorTypeODF (419) | Yes | BIFF4 ID 261 | both | both | Correct (run) |
81| 21 | ERRORTYPE | ocErrorType (151) | Yes | BIFF4 ID 261 | none | export-only | Correct; comes back as ERROR.TYPE (run) |
82| 22 | GETPIVOTDATA | ocGetPivotData (390) | Yes | BIFF8 ID 358 | both | both | Correct |
83| 23 | FDIST | ocFDist (315) | Yes | BIFF4 ID 281 | both | both | Correct (run) |
84| 24 | FINV | ocFInv (359) | Yes | BIFF4 ID 282 | both | both | Correct (run) |
85| 25 | F.DIST | ocFDistLeft (417) | Yes | None (`_xlfn.F.DIST`) | NOID import-only | 255 export-only | Correct; Excel calculates 4 arguments, #N/A for 3 (run) |
86| 26 | F.INV | ocFInvLeft (418) | Yes | None (`_xlfn.F.INV`) | NOID import-only | 255 export-only | Correct; Excel calculates it (run) |
87| 27 | HYPGEOMDIST | ocHypGeomDist (312) | Yes | BIFF4 ID 289 | both, 4 args | both, 4 args | Limitation: 5 args written as NA() (run) |
88| 28 | HARMEAN | ocHarMean (321) | Yes | BIFF4 ID 320 | both | both | Correct |
89| 29 | LEFTB | ocLeftB (402) | Yes | BIFF2 ID 208 | both | both | Correct |
90| 30 | RIGHTB | ocRightB (401) | Yes | BIFF2 ID 209 | both | both | Correct |
91| 31 | MIDB | ocMidB (403) | Yes | BIFF2 ID 210 | both | both | Correct |
92| 32 | LENB | ocLenB (400) | Yes | BIFF2 ID 211 | both | both | Correct |
93
94Import Findings
95---------------
96
97DATEDIF (BIFF5 ID 351): Was mapped to ocNoName → #NAME! on import. Fixed
98in commit a330cbd51b to map to ocDateDif.
99BIFF8 table (saFuncTable_8) does not include DATEDIF.
100
101ERROR.TYPE (BIFF4 ID 261): reads as ocErrorTypeODF since 2026-11-18. Before
102that it read as ocErrorType, so Excel files got AOO's internal error numbers.
103
104All functions in saFuncTable_Oox (`_xlfn.` prefix) have NOID as BIFF
105function ID for import, meaning they cannot be imported via BIFF ID
106lookup. These are Excel future functions (Excel 2007/2010/2013+) that
107don't exist in older BIFF versions. This is intentional.
108
109All functions in saFuncTable_Odf (`_xlfnodf.` prefix) have NOID as BIFF
110function ID for import, meaning they cannot be imported via BIFF ID
111lookup. These are ODF namespace functions not defined in BIFF. This is
112intentional.
113
114DDE has a native evaluator but no BIFF function ID in any AOO table.
115
116Functions ODDFPRICE, ODDFYIELD are Analysis AddIn functions with no native
117opcode. Classification: intentional limitation.
118
119Function DDE has a native opcode and evaluator but is not a BIFF function.
120Classification: intentional limitation.
121
122Export Findings
123---------------
124
125The tables are filled in the order 2, 3, 4, 5, 8, Oox, Odf; for export a
126later entry for the same opcode replaces an earlier one.
127
128All functions in saFuncTable_Oox have EXC_FUNCFLAG_EXPORTONLY entries with
129BIFF ID 255 (future function marker). These are exported to BIFF as future
130functions using the `_xlfn.` name mechanism. Correct for OOXML future
131functions.
132
133All functions in saFuncTable_Odf have EXC_FUNCFLAG_EXPORTONLY entries with
134BIFF ID 255 (future function marker). These are exported to BIFF using the
135`_xlfnodf.` name mechanism. AOO reads them back; Excel shows #NAME? for them.
136Until 2026-11-18 the ODF table listed ocFDist for "FDIST" and ocFInv for
137"FINV"; it now lists the left-tail ocFDistLeft/ocFInvLeft, which is what ODF
138FDIST/FINV mean.
139
140DATEDIF has been written to BIFF ID 351 since 2026-11-18 (it was
141import-only), and Excel 16 calculates it. ERRORTYPE has an export-only 261
142entry, so it is written as ERROR.TYPE, as before; on reading it becomes
143ERROR.TYPE.
144
145HYPGEOMDIST is written as BIFF4 ID 289, which takes four arguments; a call
146with the fifth (Cumulative) argument is written as NA().
147
148Functions in saFuncTable_2 (BIFF2 IDs 205-211 for B-functions) and
149saFuncTable_4 (BIFF4 IDs 261-320) and saFuncTable_8 (BIFF8 ID 358) have
150both import and export mappings. Correct.
151
152Remaining ocNoName entries in BIFF tables:
153
154- DATESTRING (BIFF5 ID 352): No native opcode. Intentional.
155- NUMBERSTRING (BIFF5 ID 353): No native opcode. Intentional.
156- PHONETIC (BIFF8 ID 360): No native opcode. Intentional.
157
158Adjacent Compatibility Layers
159-----------------------------
160
161OOXML/XLSX mappings (saFuncTable_Oox) are separate from BIFF mappings and
162use `_xlfn.` prefix names with NOID/255 BIFF IDs. These are not confused with
163BIFF support - NOID means no BIFF function ID exists.
164
165ODF namespace mappings (saFuncTable_Odf) use `_xlfnodf.` prefix names with
166NOID/255 BIFF IDs. These are distinct from native BIFF function IDs.
167
168BIFF function IDs and Calc opcode IDs are distinct namespaces. BIFF ID 255
169is used as a sentinel for "future function" in OOXML/OXDF namespace
170mappings, not as a real BIFF function ID for import purposes.
171
172The xlsx import is a separate table in oox/source/xls/formulabase.cxx. Since
1732026-11-18 it reads FINDB, SEARCHB, REPLACEB, DATEDIF and ERROR.TYPE (run
174with a hand-made xlsx); `_xlfn.F.DIST`, `_xlfn.F.INV`, `_xlfn.NUMBERVALUE`, `_xlfn.RRI` and
175`_xlfn.ISOWEEKNUM` are read too (run, hand-made xlsx).
176
177Defects Found
178-------------
179
1801. DATEDIF: BIFF5 ID 351 mapped to ocNoName → #NAME! on import. FIXED
181   in commit a330cbd51b (ocNoName → ocDateDif).
1822. FINDB, SEARCHB, REPLACEB had no BIFF mapping; FINDB came back from xls as
183   #MACRO? in AOO and #NAME? in Excel (run). FIXED 2026-11-18 (205-207).
1843. ISOWEEKNUM (ocIsoWeekNum) had no mapping; same symptom. FIXED 2026-11-18
185   (`_xlfn.ISOWEEKNUM`).
1864. ERROR.TYPE from Excel read as ERRORTYPE. FIXED 2026-11-18.
1875. Legacy FDIST and FINV have been written to xls as `_xlfnodf.FDIST` and
188   `_xlfnodf.FINV` since the 2011 OOo import (the ODF table is filled last and
189   overrode BIFF 281/282). Those names now import as FDIST and FINV, not as
190   F.DIST and F.INV (an xls written by the 11-16 build, run). FDIST and FINV
191   are now written as BIFF 281/282.
192
193Functions Requiring Follow-up
194-----------------------------
195
196- HYPGEOMDIST with five arguments: no xls form (Excel 2010's HYPGEOM.DIST
197  would need its own export entry; one opcode has one export entry).
198- NUMBERVALUE, RRI: written under `_xlfnodf.` names, which Excel does not
199  know; Excel 2013 has both.
200- F.DIST with three arguments: written as `_xlfn.F.DIST`, which Excel shows as
201  #N/A because it needs four.
202
203Statistics
204----------
205
206- Total rows: 32
207- Correct mappings: 28
208- Limitations: 4 (DDE, ODDFPRICE, ODDFYIELD intentional; HYPGEOMDIST with 5 args)
209- Functions with native evaluators but no BIFF mapping: 1 (DDE)
210- Functions implemented via AddIn (no native opcode): 2 (ODDFPRICE, ODDFYIELD)
211
212Source References
213-----------------
214
215- BIFF function tables: main/sc/source/filter/excel/xlformula.cxx
216  - saFuncTable_2: lines 74-221
217  - saFuncTable_3: lines 224-249
218  - saFuncTable_4: lines 252-326
219  - saFuncTable_5: lines 329-350
220  - saFuncTable_8: lines 353-368
221  - saFuncTable_Oox: lines 371-403
222  - saFuncTable_Odf: lines 410-439
223- Import path: FillXclFuncMap(), line 492
224- Export path: FillScFuncMap(), line 506
225- Opcode definitions: main/formula/inc/formula/compiler.hrc
226- Opcode enum: main/formula/inc/formula/opcode.hxx
227- Function name registration: main/formula/source/core/resource/core_resource.src
228- Evaluator dispatch: main/sc/source/core/tool/interpr4.cxx
229- xlsx import table: main/oox/source/xls/formulabase.cxx
230