How can I read data from an Excel sheet using ExcelJS library?

Question

It keeps on giving file not found exception. Below is the function which i am calling to read data from the excel sheet:

import excel from 'exceljs'

getDataFromExcel(sheetName,rowNum,colNum){

    //const excel = require('exceljs')
    let wb  = new excel.Workbook()
    wb.xlsx.readFile('../testData/Full-TestData.xlsx').then(()=>{

        let sheet = wb.getWorksheet(sheetName)
        let data = sheet.getRow(rowNum).getCell(cellNum).value().toString()
        return data

    })
}

I am trying to read data from an excel sheet to use it in my script instead of hard coding however i am getting no file found exception

enter image description here

Answer 1

This demo will work

Save as read.js name under src directory.

function columnName (index) {
    var cname = String.fromCharCode(65 + ((index - 1) % 26))
    if (index > 26)
        cname = String.fromCharCode(64 + (index - 1) / 26) + cname
    return cname
}

function getDataFromExcel (sheet, rowNum, colNum) {
    cell = columnName(colNum) + String(rowNum)
    text = sheet.getCell(cell).text
    return text
}

module.exports = { getDataFromExcel, columnName }

Save as client.js name under src directory.

const ExcelJS = require('exceljs')
const excel = require("./read");

const fileName = '../testData/Full-TestData.xlsx';
const wb = new ExcelJS.Workbook()
const sheetName = 'contact angle'


wb.xlsx.readFile(fileName).then(() => {
    sheet = wb.getWorksheet(sheetName)

    // A1
    rowNum = 1
    colNum = 1
    cell = excel.columnName(colNum) + String(rowNum)
    text =  excel.getDataFromExcel(sheet, rowNum, colNum)
    console.log(cell + ": " + text)

    // B3
    rowNum = 3
    colNum = 2
    cell = excel.columnName(colNum) + String(rowNum)
    text =  excel.getDataFromExcel(sheet, rowNum, colNum)
    console.log(cell + ": " + text)

}).catch(err => {
    console.log(err.message)
})

Result

$ node client.js
A1: SDM
B3: 75.8

Install dependency

At src directory.

npm install exceljs

Using this excel file Save as Full-TestData.xlsx name under testData directory.

https://figshare.com/articles/dataset/Test_data_xlsx/22040333

File structure

├── src
│   └── read.js
│   └── client.js
└── testData
    └── Full-TestData.xlsx

Run it

at src directory.

node client.js

Result