How can I improve this function so that it overwrites the Excel cell?

Question

I'm using this code to try and overwrite a cell in Excel:

function readExcelFile(workbook, row, cell, newData) {
    workbook.xlsx.readFile('H://filename.xlsm')
        .then(function () {
            cell.value = newData;
            row.commit();
            return workbook.xlsx.writeFile('H://newFile.xlsx');
        });
}

I can get the data logged to the console, but the Excel file stays the same.

Any help would be great, thanks.

Answer 1

Presumably, the function parameters row and cell are objects related to workbook? However, they'll lose this connection within the scope of the anonymous function where you're writing the file. (Technically speaking, JavaScript passes variables to functions by value and not by reference.)

Instead pass the info needed to find the cell, and get it locally within the function:

function readExcelFile(workbook, worksheetName, rowIndex, columnIndex, newData) {
    workbook.xlsx.readFile('H://filename.xlsm')
        .then(function () {
            worksheet = workbook.getWorksheet(worksheetName);
            row = worksheet.getRow(rowIndex);
            cell = row.getCell(columnIndex);
            cell.value = newData;
            row.commit();
            return workbook.xlsx.writeFile('H://newFile.xlsx');
        });
}

readExcelFile(my_workbook, "My Worksheet", 3, 3, 34232);