Read and write a xlsm file using exceljs package npm

Question

I have a xlsm file with few data already in it and need to write some data and create a new xlsm file during automation. With below code the file gets created , but it becomes corrupt and unable to open. File size reduces, for ex from 8kb to 7kb. Not sure what is getting missed while writing the file.

var Excel = require('exceljs');
var workbook = new Excel.Workbook();

workbook.xlsx.readFile('Book.xlsm')
.then(function () {
var worksheet = workbook.getWorksheet(1);
var row = worksheet.getRow(1);
console.log(row.getCell(1).value + " - First value"); // Get A1 value
row.getCell(3).value = "c"; //Set value to A3
row.commit();
return workbook.xlsx.writeFile('new.xlsm');
})

Note : Just created Book.xlsm with some value columns a,b and values 1,2. Trying to set A3 with 'c' and save as new.xlsm

If there are any other npm package which doesn't have this problem also would be great.

Answer 1

I think that currently exeljs is not suitable package for working with xlsm files. More robust package for this case is xlsx.

Here is a small code snippet that will demonstrate reading existing xlsm file, adding additional data to it and writing new xlsm file. You can test it in NodeJS environment.

Note that when working with xlsm files you pass bookVBA: true option to readFile method, which by default is false. See parsing options for details.

const XLSX = require('xlsx');

try {

    const workbook = XLSX.readFile('./test.xlsm', { bookVBA: true });
    let worksheet = workbook.Sheets['Sheet1'];
    XLSX.utils.sheet_add_aoa(worksheet, [
        ['text in A1', 'text in B1', 5000],
        ['text in A2', 'text in B2', 'text in C2'],
        [null, new Date(), 'text in C3', 'text in D3']
    ]);
    XLSX.writeFile(workbook, './output.xlsm');
    console.log('Completed ...');

} catch (error) {
    console.log(error.message);
    console.log(error.stack);
}

See the supported output formats for details.