How to get cell by name with exceljs?

Question

For now, I have to hardcode like this

var cell = worksheet.getCell('A1');

But I want to define my cell by name like data and access the cell by:

var cell = worksheet.getCell('data');

How can I do that with exceljs ? Thank you very much!

Note: The only solution seems to solve my problem is to add column/row headers and define key, but I don't want to do that in my code:

worksheet.columns = [
  { header: 'Id', key: 'id', width: 10 }
];

Answer 1

Just like to suggest another way. I used console.table() to check what column-name and key in a worksheet. I didn't have any key assign to column-name. So we iterate first row and assign cell.text to key.

console.table(worksheet.columns);

worksheet.getRow(1).eachCell((cell, colNumber) => {
  worksheet.getColumn(colNumber).key = cell.text;
});

console.table(worksheet.columns);


worksheet.getRow(2).getCell("UserName").value;

Answer 2

There doesn't seem to be a built-in method, as far as I can tell. Here's a function that should do the trick:

function getCellByName(worksheet, name) {
    var match;
    worksheet.eachRow(function (row) {
        row.eachCell(function (cell) {
            for (var i = 0; i < cell.names.length; i++) {
                if (cell.names[i] === name) {
                    match = cell;
                    break;
                }
            }
        });
    });
    return match;
}

With ES6:

const getCellByName = (worksheet, name) => {
    let match
    worksheet.eachRow(row => row.eachCell(cell => {
        if (cell.names.find(n => n === name)) {
            match = cell
        }
    }))
    return match
}

That may end up being infeasible with very large worksheets, as you have to iterate through every non-null cell in the sheet.

If you're looking for something with that functionality built-in, I haven't tried it yet but xlsx-populate looks promising.