Modifying an xlsx file with ExcelJS and NodeJS

Display name picture Display name · Feb 17, 2017 · Viewed 8.7k times · Source

My goal here is to read an xlsx file in, add a row, and output it. Simple enough right?

This is the code I have so far:

var filename1="input.xlsx";
var filename2="output.xlsx";
var Excel = require('exceljs');
var workbook = new Excel.Workbook();
workbook.xlsx.readFile(filename1);
workbook.getWorksheet("Sheet 1").addRow([1,2,3]);
workbook.xlsx.writeFile(filename2);

I believe this should read that data from "input.xlsx," write a row in addition to the data already on the sheet, and output it. Instead of copying the file, it creates an empty "output.xlsx."

I know I'm doing something stupid, also I'm totally new to nodeJS. Any thoughts?

Answer

Antonio Narkevich picture Antonio Narkevich · Feb 17, 2017

The problem you are experiencing is connected with Node.js asynchronous nature. When you call readFile(filename1) it starts reading file. But it's an async (non-blocking) function so the code after this line gets executed before the reading is done.

There are multiple ways to handle this: callbacks (called when the async call is done), promises (.then will be called when the call is executed), ES6 generators and ES7 async/await keywords.

exceljs works with promises (as per docs) so you can do the following:

'use strict';

const Excel = require('exceljs');
let filename1 = 'input.xlsx';
let filename2 = 'output.xlsx';
let workbook = new Excel.Workbook();
workbook.xlsx.readFile(filename1)
    .then(() => {
        workbook.getWorksheet('Sheet 1').addRow([1, 2, 3]);
        return workbook.xlsx.writeFile(filename2);
    }).then(() => {
        console.log('File is written');
    }).catch(err => console.error(err));

Also please make sure that 'Sheet 1' actually exists because for me the default name was 'Sheet1'.

There are many articles like this on this topic on the internet.