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