Parse excel file and create JSON format in exceljs ON nodejs

Question

I have this excel file

I need to convert the data excel from file to JSON like below

[
   {
   "no_pemohonan": "PNL-202109200826210023105",
   "sumber_data": "HOSTS",
   "tgl_permohonan": "2021-09-20",
   },
   {
   "no_pemohonan": "PNL-202109200845131363376",
   "sumber_data": "HOSTS",
   "tgl_permohonan": "2021-09-20",
   },
   ...
]

I could make the data with this comment but i have to set the object again like below syntax

            const excel = require('exceljs');
            const workbook = new excel.Workbook();


            await workbook.xlsx.load(objDescExcel.buffer);
            let json = workbook.model;
            let worksheetsArr = json.worksheets.length;
            const arrRow = [];
            const arrIdPembatalan = [];

            // looping per worksheet
            for (let index = 0; index < worksheetsArr; index++) {
                let worksheet = workbook.worksheets[index];
                // console.log("worksheet " + worksheet);
                const rowlast = worksheet.lastRow;
                // looping semua row untuk
                worksheet.eachRow(async (row, rowNumber) => {
                    let new_row = row.values
                    // console.log(new_row);
                        let no_permohonan= new_row[2]
                        let sumber_data = new_row[3]
                        let tgl_permohonan = new_row[4]
                        
                        let header = {
                            no_permohonan: no_permohonan,
                            sumber_data : sumber_data ,
                            tgl_permohonan : tgl_permohonan ,
                        };
                        arrIdPembatalan.push(header)
                    }
                })
        }

I want to set the header automatically without have to set the header again like above syntax. I have seen this solution but it was written in xlsx library, while i am using exceljs.

Answer 1

Here is a nodejs implement.

(async function() {
    const excel = require('exceljs');
    const workbook = new excel.Workbook();
    // use readFile for testing purpose
    // await workbook.xlsx.load(objDescExcel.buffer);
    await workbook.xlsx.readFile(process.argv[2]);
    let jsonData = [];
    workbook.worksheets.forEach(function(sheet) {
        // read first row as data keys
        let firstRow = sheet.getRow(1);
        if (!firstRow.cellCount) return;
        let keys = firstRow.values;
        sheet.eachRow((row, rowNumber) => {
            if (rowNumber == 1) return;
            let values = row.values
            let obj = {};
            for (let i = 1; i < keys.length; i ++) {
                obj[keys[i]] = values[i];
            }
            jsonData.push(obj);
        })

    });
    console.log(jsonData);
})();

test result

$ node ./test.js ~/Documents/Book1.xlsx
[
  {
    no_pemohonan: 'PNL-202109200826210023105',
    sumber_data: 'HOSTS',
    tgl_permohonan: 2021-09-20T00:00:00.000Z
  },
  {
    no_pemohonan: 'PNL-202109200845131363376',
    sumber_data: 'HOSTS',
    tgl_permohonan: 2021-09-20T00:00:00.000Z
  }
]