Modifying an xlsx file with ExcelJS and NodeJS

Question

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?

Do you actually have a worksheet "Sheet 1" in the first book? In my Excel the default sheet name is "Sheet1" (no space) — Michael Tallino
– Michael Tallino, Commented Feb 17, 2017 at 4:58
I'll add that basic operations in exceljs seem OK - but take a look through the issues tracker on GitHub before you get too far down your road and make sure that any features you need work adequately. — Michael Tallino
– Michael Tallino, Commented Feb 17, 2017 at 4:59
Actually, I changed "Sheet 1" from the Excel document that I'm working with from something else because the name of the sheet is someone's name and I don't want to post that online... Suffice to say I've double checked, and the name is correct. — Display name
– Display name, Commented Feb 17, 2017 at 5:01

Antonio Narkevich · Accepted Answer · 2018-02-27 16:08:39Z

9

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.

edited Feb 27, 2018 at 16:08

user8280225

answered Feb 17, 2017 at 6:45

Antonio Narkevich

4,34623 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Alexander Over a year ago

Using the current version of exceljs (1.0.0) I am trying to modify a simple sheet of a workbook that contains another sheet with pivot. The action corrupts the workbook and if I recover it with Excel - it loses the pivot :(

Antonio Narkevich Over a year ago

@Alexander would you mind providing the source code of what you are doing? Ideally please create a separate question and send me the link.

Alexander Over a year ago

Thank you @Antonio Narkevich. It was done using your code (just read, add row to an empty sheet and save). And it corrupts the Excel (just try to perform it on Workbook with a pivot). Then I tried several NodeJS packages and didn't find one that works fine with pivot. Finally I wrote my code in Python, since it has a library that doesn't corrupt Excel with a pivot (openpyxl).

Collectives™ on Stack Overflow

Modifying an xlsx file with ExcelJS and NodeJS

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related