Exceljs: How to get the alphanumeric reference for a cell? For use in a formula.

[lukelaws]

I'm creating a worksheet from a datasource with an arbitrary number of columns and rows. I want to put a sum at the bottom of each column but I'm having trouble generating the formula. I think I would have to provide the alphanumeric cell ranges to the formula to do this. I understand cell names are an option but those won't work with Apple Numbers and also the make the formulas harder to edit. I want my users to be able to edit the formulas easily.

I also found the function encode lib/utils/col-cache and that could work but I get the impression that was never intended for public use and I think there is an elegant way of doing this i'm' completely missing. Thanks in advance for the help!

[jayflo]

I'm not sure if this is all you're asking for, but the function for converting from (row, col) to [A-Z]+\d+ (excel) coordinates is very simple:

Note: these assume 1-based row/col values (consistent with exceljs).

var CAPITAL_A = 65;

function excelCoords(row, col) {
    var colStr = '';

    while(col > 0) {
        colStr = toChar((col - 1) % 26) + colStr;
        col = Math.floor((col - 1) / 26);
    }

    return colStr + row;
}

function toChar(n) {
    return String.fromCharCode(CAPITAL_A + n);
}

// The inverse is also quite simple:

function cartesianCoords(excelCoords) {
    var row = parseInt(excelCoords.replace(/^[A-Z]+/, ''));
    var colChars = excelCoords.replace(/\d+$/, '').split('').reverse();
    var col = 0;
    var multiplier = 1;

    while(colChars.length) {
        col += toBase26Ish(colChars.shift()) * multiplier;
        multiplier *= 26;
    }

    return [row, col];
}

function toBase26Ish(c) {
    return c.charCodeAt(0) - CAPITAL_A + 1;
}

[offg777]

There really should be some built in functionality to get a range address from row & col and vise versa

[sossargsyan]

If I understood correctly it can be easily done by using _address property:
worksheet.getRow(5).getCell(1)._address // A5