Node exceljs reading file
And in case you are using file as ArrayBuffer (for example file was read on client with FileReader.readAsArrayBuffer()), then to make it load with this lib you have to do the next:
let workbook = new Excel.Workbook();
let stream = new Stream.Readable();
stream.push(file); // file is ArrayBuffer variable
stream.push(null);
workbook.xlsx.read(stream).then((workbook)=> {
// get worksheet, read rows, etc
});
var workbook = new Excel.Workbook();
workbook.xlsx.readFile(filename)
.then(function() {
var worksheet = workbook.getWorksheet(sheet);
worksheet.eachRow({ includeEmpty: true }, function(row, rowNumber) {
console.log("Row " + rowNumber + " = " + JSON.stringify(row.values));
});
});
The below code snippet will help you to read the file sample.xlsx, which contains two columns 1. username 2. password. and I'm trying to assert with Mocha(chai), to check the data type match.
var Excel = require('exceljs');
const assert = require("chai").assert;
var workbook = new Excel.Workbook();
workbook.creator ="Naveen";
workbook.modified ="Kumar";
workbook.xlsx.readFile("sample.xlsx").then(function(){
var workSheet = workbook.getWorksheet("one");
workSheet.eachRow({ includeEmpty: true }, function(row, rowNumber) {
currRow = workSheet.getRow(rowNumber);
console.log("User Name :" + currRow.getCell(1).value +", Password :" +currRow.getCell(2).value);
console.log("User Name :" + row.values[1] +", Password :" + row.values[2] );
assert.equal(currRow.getCell(2).type, Excel.ValueType.Number);
// console.log("Row " + rowNumber + " = " + JSON.stringify(row.values));
});
})
Hope this helps somebody
//Read a file
var workbook = new Excel.Workbook();
workbook.xlsx.readFile("data/Sample.xlsx").then(function () {
//Get sheet by Name
var worksheet=workbook.getWorksheet('Sheet1');
//Get Lastrow
var row = worksheet.lastRow
//Update a cell
row.getCell(1).value = 5;
row.commit();
//Save the workbook
return workbook.xlsx.writeFile("data/Sample.xlsx");
});