CODEWITHSUNDEEP
node.jsexceljs
Marc Rasmussen
Marc Rasmussen

Reputation: 20545

Node exceljs reading file

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?

Upvotes: 26

Views: 86678

Answers (6)

Sumit Bopche
Sumit Bopche

Reputation: 748

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

   })

}

Upvotes: 3

Mayukh
Mayukh

Reputation: 11

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

Upvotes: 1

Qin Luo
Qin Luo

Reputation: 41

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

});

Upvotes: 4

Naveen
Naveen

Reputation: 500

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

Upvotes: 6

Statyan
Statyan

Reputation: 136

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

Upvotes: 8

Diogo Cardoso
Diogo Cardoso

Reputation: 22257

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

Upvotes: 34

Related Questions