Creating excel file and writing to it with ExcelJS

miyagisan picture miyagisan · Mar 13, 2019 · Viewed 15.4k times · Source

I wrote a script that creates a new excel file with ExcelJS. Adds 3 headers and inserts 2 rows. Then saves that file to disk.

In the next step it should read that previously saved file, add 1 row and save it again under a new name. I can't find the reason why the second part where I try to add 3rd row never happens. Both files look the same and there are no errors in console.

const Excel = require('exceljs');

async function exTest(){
  const workbook = new Excel.Workbook();
  const worksheet = workbook.addWorksheet("My Sheet");

worksheet.columns = [
  {header: 'Id', key: 'id', width: 10},
  {header: 'Name', key: 'name', width: 32}, 
  {header: 'D.O.B.', key: 'dob', width: 15,}
];

worksheet.addRow({id: 1, name: 'John Doe', dob: new Date(1970, 1, 1)});
worksheet.addRow({id: 2, name: 'Jane Doe', dob: new Date(1965, 1, 7)});

// save under export.xlsx
await workbook.xlsx.writeFile('export.xlsx');

// load a copy of export.xlsx
const newWorkbook = new Excel.Workbook();
await newWorkbook.xlsx.readFile('export.xlsx');

const newworksheet = newWorkbook.getWorksheet('My Sheet');
newworksheet.addRow(
  {id: 3, name: 'New Guy', dob: new Date(2000, 1, 1)}
);

await newWorkbook.xlsx.writeFile('export2.xlsx');

console.log("File is written");
};

exTest();

Answer

miyagisan picture miyagisan · Mar 14, 2019

Solved it by adding array to describe columns in second part of the code. Then the 3rd row was added successfully. When passing an object with column names to addRow() I had to provide a description of columns that already existed in excel file.

const Excel = require('exceljs');

async function exTest(){
  const workbook = new Excel.Workbook();
  const worksheet = workbook.addWorksheet("My Sheet");

worksheet.columns = [
 {header: 'Id', key: 'id', width: 10},
 {header: 'Name', key: 'name', width: 32}, 
 {header: 'D.O.B.', key: 'dob', width: 15,}
];

worksheet.addRow({id: 1, name: 'John Doe', dob: new Date(1970, 1, 1)});
worksheet.addRow({id: 2, name: 'Jane Doe', dob: new Date(1965, 1, 7)});

// save under export.xlsx
await workbook.xlsx.writeFile('export.xlsx');

//load a copy of export.xlsx
const newWorkbook = new Excel.Workbook();
await newWorkbook.xlsx.readFile('export.xlsx');

const newworksheet = newWorkbook.getWorksheet('My Sheet');
newworksheet.columns = [
 {header: 'Id', key: 'id', width: 10},
 {header: 'Name', key: 'name', width: 32}, 
 {header: 'D.O.B.', key: 'dob', width: 15,}
];
await newworksheet.addRow({id: 3, name: 'New Guy', dob: new Date(2000, 1, 1)});

await newWorkbook.xlsx.writeFile('export2.xlsx');

console.log("File is written");

};

exTest();