Loop Through Table And Save Its Content

I'm reading an Excel file in javascript. One of the cell has a VLOOKUP formula: VLOOKUP(C40,D107:F114,3,FALSE) //(searched value, table range, returned value column, exact match)

Solution 1:

The first "minimization" (and most important one) is to check if your sheet instance allows to extract a slice (idem the submatrix or table you try to extract).

On a more generic way you can:

1. skip some lines about extracting the formula (or at least make it clearer)

let formula = 'D107:F114'let [colStart, rowStart, colEnd, rowEnd] = formula.match(/([A-Z]+)(\d+):([A-Z]+)(\d+)/).slice(1);
//[ colStart='D', rowStart='107', colEnd='F', rowEnd='114' ]
rowStart = parseInt(rowStart);
rowEnd   = parseInt(rowEnd);
colStart = colStart.charCodeAt()-65;
colEnd   = colEnd.charCodeAt()-65;

note that here, you may have columns like AA or ZZ so you may want to adapt the transformation of colStart and colEnd accordingly

2. then use map instead of for loop

let table = Array(colEnd-colStart+1).fill(0).map((_, j)=>{
    returnArray(rowEnd-rowStart+1).fill(0).map((_, i)=>{
        let col = colStart + j;
        let row = rowStart + i;
        return sheet.cell(String.fromCharCode(col+65)+row).value();
    })
})

Regarding AA or ZZ, below an algorithm to convert them back and forth to int, but once again probably rely on your library since it would have to parse your string anyway..

let sheet = {
    cell(x){return {value(){return x}}}
}

//not used, but enough to handle single letters...functiontoColIdx(s){
    return s.charCodeAt()-'A'.charCodeAt();
}

functionidxToCol(idx){
    returnString.fromCharCode(idx+'A'.charCodeAt())
}

let base = (function(){
    let idxToChar = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'.split('');
    let charToIdx   = idxToChar.reduce((acc, f,i)=>(acc[f] = i+1, acc),{})
    //I have checked the identity of idxToCol == idxToCol(toColIdx(idxToCol)) up to 16384return {
        toColIdx(s){
            return s.split('')
                .reverse()
                .reduce((acc,c,i)=>acc+charToIdx[c]*Math.pow(26,i),0);
        },
        idxToCol(idx){
            if(idx==1)return'A';
            let n = Math.ceil(Math.log(idx)/Math.log(26));
            s = '';
            for(let i = 0; i<n; ++i){
                let x = idx % 26;
                if(x != 0){
                    s = idxToChar[x-1] + s;
                    idx-=x;
                    idx /= 26;
                }else{
                    s = 'Z' + s;
                    idx-=26;
                    idx /= 26;
                    if(idx==0) return s;
                }
            }
            return s;
        }
    }
})();
functionextract(sheet, formula){
    let [colStart, rowStart, colEnd, rowEnd] = formula.match(/([A-Z]+)(\d+):([A-Z]+)(\d+)/).slice(1);
    //[ colStart='D', rowStart='107', colEnd='F', rowEnd='114' ]
    rowStart = parseInt(rowStart);
    rowEnd   = parseInt(rowEnd);
    colStart = base.toColIdx(colStart)
    colEnd   = base.toColIdx(colEnd)
    returnArray(colEnd-colStart+1).fill(0).map((_, j)=>{
        returnArray(rowEnd-rowStart+1).fill(0).map((_, i)=>{
            let col = colStart + j;
            let row = rowStart + i;
            return sheet.cell(base.idxToCol(col)+row).value();
        })
    })   
}
console.log(JSON.stringify(extract(sheet, 'A107:D114'),null,2))
console.log(JSON.stringify(extract(sheet, 'BZ107:CA114'),null,2))