Converting an excel sheet to json using ExcelJS

gistfile1.js

import Exceljs from "exceljs";
import { groupBy, compact, isEmpty } from "lodash";


const workbookFile = '/path/to/your/excelSheet/xlsx';
const nameOfSheet = 'name of sheet in workbook';

let workbook = new Exceljs.Workbook();
await workbook.xlsx.readFile(workbookFile);
const sheet = workbook.getWorksheet(nameOfSheet);

// headerRowNumber is the number of the row with the titles counting from 1
let json = sheetToJson(sheet, { headerRowNumber: 2 })

function sheetToJson({ sheet, headerRowNumber = 1 }) {
    let headerRow = sheet.getRow(headerRowNumber);
    headerRow = headerRow._cells.map((cell) => {
        let header = cell.value;
        return {
            column: cell._column._number,
            address: cell.address,
            value: header,
        };
    });

    let headers = {};
    headerRow.forEach((row) => {
        headers[row.column] = row.value;
    });

    let rows = sheet._rows.map((row) => {
        return row._cells.map((cell) => {
            return {
                column: cell._column._number,
                cell: cell.address,
                value: cell.value,
            };
        });
    });
     rows = rows.slice(headerRowNumber).map((row) => {
        let data = [];
        if (isEmpty(compact(row.map((cell) => cell.value)))) return null;

        row.forEach((cell) => {
            let key = headers[cell.column];
            let value = cell.value;
            data.push({ key, value });
        });
        data = groupBy(data, "key");
        for (let key of Object.keys(data)) {
            data[key] = data[key].map((k) => k.value);
        }
        return data;
    });
    rows = compact(rows);
    // console.log(JSON.stringify(rows, null, 2));
    return rows;
}