Node exceljs reading file

Question

So according to the offical documentation i should be able to read an excel document using:

    // read from a file 
var workbook = new Excel.Workbook();
workbook.xlsx.readFile(filename)
    .then(function() {
        // use workbook 
    });

// pipe from stream 
var workbook = new Excel.Workbook();
stream.pipe(workbook.xlsx.createInputStream());

I have the following document:

enter image description here

What i need to do is basicly to load each row into an object:

var excelObject = {competence1: '', competence2: ''}

And then save it into an array.

However the documentation doesnt give me more on how i might read from this file. It uses a variable called stream however this variable is not explained anywhere.

Does anyone know the plugin and know how i might achieve my goal?

I've never used the module but in the `.then()` callback, I assume you can access the data from the worksheet e.g. `workbook.getWorksheet("My Sheet").getRow(4).getCell(1).value` as per the docs. https://github.com/guyonroche/exceljs/blob/master/README.md — Matt Harrison, Mar 04 '15 at 20:58
It looks like the stream example is for piping data into a workbook. — Matt Harrison, Mar 04 '15 at 20:59

score 32 · Accepted Answer · answered Nov 06 '15 at 10:18

32

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));
        });
    });

answered Nov 06 '15 at 10:18

Diogo Cardoso

21,637
26
100
138

14

filename is file path or what ? my file path is public/uploads/test.xlsx – Pranay Soni Apr 18 '16 at 19:42
1

`row.values` returning an array, is there any built in method to get it as a object (Key value pair)? – Dipak Mar 28 '17 at 06:40
2

`sheet$.eachRow is not a function` function was deprecated or doesn't exist – Pian0_M4n Dec 12 '19 at 17:08

score 8 · Answer 2 · answered Apr 09 '16 at 00:37

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
    });

score 6 · Answer 3 · answered Apr 16 '18 at 12:23

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

score 4 · Answer 4 · edited Jul 03 '18 at 03:05

4

//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");

});

edited Jul 03 '18 at 03:05

Stephen Rauch

47,830
31
106
135

answered Jul 03 '18 at 02:46

Qin Luo

41
1

Won't it be better to add some description as well? – Failed Scientist Jul 03 '18 at 03:05

score 1 · Answer 5 · answered Nov 06 '19 at 08:15

Step1: Download exceljs module using npm

Step2: Use the following piece of code to read data from excel

import { Workbook, Worksheet } from 'exceljs';

public static async getExcelD(filename: string, column1Item: string) {
        let wb:Workbook = new Workbook();
        let datafile = path.join(__dirname, "../testData", filename);
        await wb.xlsx.readFile(datafile).then(async ()=>{
            let sh:Worksheet = wb.getWorksheet("Sheet1");
            for(let i=1;i<=sh.actualRowCount;i++){
                if(sh.getRow(i).getCell(1).value==column1Item){
                    data1 = sh.getRow(i).getCell(2).value;
                }
            }
        })
        return await data1;
}

score 1 · Answer 6 · edited Dec 27 '19 at 15:00

I was getting an error for .xls file. After trying all the permutation and combination, I converted the .xls file into .xlsx and it worked.

const Excel = require('exceljs/dist/es5');


const readExcel = async () => {

  //You can use the absolute path also
  let filepath = './genovaFile.xlsx';

  let workbook = new Excel.Workbook();

  await workbook.xlsx.readFile(filepath);

//You can use the index number also eg. 0 for selecting the sheet
  let worksheet = workbook.getWorksheet("sheet1");

   worksheet.eachRow({ includeEmpty: true }, async (row, rowNumber) => {

    console.log("row " + row.values);

   })

}

Node exceljs reading file

6 Answers6

Linked