I have a first draft (with Typescript) that is generally working but is a bit messy. I'd be happy for feedback on how to improve it from someone that understands xlsx-calc better.

In a separate file "xlsx-calc.d.ts":

declare module 'xlsx-calc' {
    export default XLSX_CALC;
}

I think this is all generic Typescript. Here is the part defining INDIRECT when initializing the component.

There is a Map of the retrieved values so we don't retrieve them repeatedly. I don't like that it is referencing a "workbook" object that's assumed to be in the scope where this is defined, but don't know how one is supposed to reference the workbook from in an XLSX_CALC function.

import * as formulajs from '@formulajs/formulajs';
import XLSX from 'xlsx';
import XLSX_CALC from 'xlsx-calc';

    XLSX_CALC.import_functions(formulajs);

    // Define our own implementation of INDIRECT() that handles the cases used in our hosted applications
    type ExcelValueType = string | number | boolean | Date | undefined;

    // NOTE - This is added to in INDIRECT() and cleared when the file blob is reset
    const indirectDataMap : Map<string, ExcelValueType[][]> = new Map();

    XLSX_CALC.set_fx('INDIRECT',
        function(ref_text: string, a1?: boolean | string | number){
            if (a1 === false || a1 === 'FALSE' || a1 === 0){
                console.log('INDIRECT() does not yet support R1C1 style references, ignoring FALSE argument to INDIRECT()');
            }

            // NOTE: Initially we plan to just support references to defined names
            //       and not computed names such as INDIRECT("B" & 2) resolving to B2.

            const ref : string | undefined = workbook?.Workbook?.Names?.find(
                    (dn: DefinedName) => dn.Name.toLowerCase() === ref_text.toLowerCase())?.Ref;

            if (ref === undefined){
                throw Error(`Defined name not found for "${ref_text}" when evaluating INDIRECT()`);
                //throw Error('#N/A');
            } else {
                if (indirectDataMap.has(ref)){
                    return indirectDataMap.get(ref);
                } else {
                    const refSplit : string[] = ref.split('!', 2);
                    const sheetName : string = refSplit[0];
                    const rangeStr : string = refSplit[1];
    
                    const sheet: XLSX.Sheet | undefined = workbook?.Sheets[sheetName];
    
                    if (sheet === undefined){
                        throw Error(`Sheet "${sheetName}" not found when evaluating INDIRECT()`);
                        //throw Error('#N/A');
                    } else {
                        const rangeIndexes : XLSX.Range = XLSX.utils.decode_range(rangeStr);
                        const valueMatrix : ExcelValueType[][] = [];
    
                        let curRow : ExcelValueType[] = [];
        
                        for (let ri = rangeIndexes.s.r; ri <= rangeIndexes.e.r; ri++) {
                            for (let ci = rangeIndexes.s.c; ci <= rangeIndexes.e.c; ci++) {
                                curRow.push(sheet[XLSX.utils.encode_cell({r: ri, c: ci})].v);
                            }
                            valueMatrix.push(curRow);
                            curRow = [];
                        }
                        indirectDataMap.set(ref, valueMatrix);
                        return valueMatrix;    
                    }
                }
            } 
        }
    )

Here is how the workbook is read from an ArrayBuffer and calculations triggered.

                        workbook = XLSX.read(buffer, { type: "array", cellStyles: true });

                        indirectDataMap.clear();
                        
                        // TODO - This is also doing calculations for hidden sheets.  Perhaps
                        //        create our own implementation based on the source code
                        //        that takes an option on which sheets to evaluate, and only
                        //        does this for the visible sheets.

                        XLSX_CALC(workbook, { continue_after_error: true}); // , log_error: true });

I know this code is a bit of a mess, posting it anyway for suggested improvements.

Uh oh!

Uh oh!

INDIRECT function #58

Description

Metadata

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

Issue actions