Creating excel file and writing to it with ExcelJS
Solution 1
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();
Solution 2
Working link attached
const Excel = require('exceljs');
// Create workbook & add worksheet
const workbook = new Excel.Workbook();
const worksheet = workbook.addWorksheet('ExampleSheet');
// add column headers
worksheet.columns = [
{ header: 'Package', key: 'package_name' },
{ header: 'Author', key: 'author_name' }
];
// Add row using key mapping to columns
worksheet.addRow(
{ package_name: "ABC", author_name: "Author 1" },
{ package_name: "XYZ", author_name: "Author 2" }
);
// Add rows as Array values
worksheet
.addRow(["BCD", "Author Name 3"]);
// Add rows using both the above of rows
const rows = [
["FGH", "Author Name 4"],
{ package_name: "PQR", author_name: "Author 5" }
];
worksheet
.addRows(rows);
// save workbook to disk
workbook
.xlsx
.writeFile('sample.xlsx')
.then(() => {
console.log("saved");
})
.catch((err) => {
console.log("err", err);
});
https://repl.it/@vishwasc/ExcelJs-Example#index.js
image attachemnts
// add image to workbook by filename
const imageId1 = workbook.addImage({
filename: 'path/to/image.jpg',
extension: 'jpeg',
});
// add image to workbook by buffer
const imageId2 = workbook.addImage({
buffer: fs.readFileSync('path/to.image.png'),
extension: 'png',
});
// add image to workbook by base64
const myBase64Image = "data:image/png;base64,iVBORw0KG...";
const imageId2 = workbook.addImage({
base64: myBase64Image,
extension: 'png',
});
Note that in both cases, the extension must be specified. Valid extension values include 'jpeg', 'png', 'gif'.
miyagisan
Updated on July 05, 2022Comments
-
miyagisan almost 2 years
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();
-
Kunal Mukherjee over 3 yearsNice solution!!