ExcelJS node: is there a method/ module for converting xlsx to json in exceljs

Question

https://github.com/guyonroche/exceljs

I m new to exceljs and just see the description of exceljs at github i

.e : "Read, manipulate and write spreadsheet data and styles to XLSX and JSON."

I need to convert workbook into JSON object and did not find any method / module like for csv in exceljs.

let me know if there is one.

Answer 1

I know this question is old but I am posting an answer for others who may be in the same position I am today.

Yes, there is.

I was looking for the same answer as you, but without success. After a bit of search, I found a way to do it.

import Exceljs from 'exceljs';

const workbook = new Exceljs.Workbook();
await workbook.xlsx.load(data);
const json = JSON.stringify(workbook.model);
console.log(json); // the json object

I combined the response to this issue and Exceljs' read me to figure it out. I hope this helps.

Answer 2

It's very simple to get values from excel file using exceljs.

const book = [];
workbook.eachSheet( sheet => {
  const sheet = [];
  worksheet.eachRow(row => {
    sheet.push(row.values);
  });
book.push(sheet);
});

But take a look on array indexes. Cells usually starts from 1.

Answer 3

    // data
    let excelTitles = [];
    let excelData = [];

    // excel to json converter (only the first sheet)
    workbook.worksheets[0].eachRow((row, rowNumber) => {
        // rowNumber 0 is empty
        if (rowNumber > 0) {
            // get values from row
            let rowValues = row.values;
            // remove first element (extra without reason)
            rowValues.shift();
            // titles row
            if (rowNumber === 1) excelTitles = rowValues;
            // table data
            else {
                // create object with the titles and the row values (if any)
                let rowObject = {}
                for (let i = 0; i < excelTitles.length; i++) {
                    let title = excelTitles[i];
                    let value = rowValues[i] ? rowValues[i] : '';
                    rowObject[title] = value;
                }
                excelData.push(rowObject);
            }
        }
    })
    console.log(excelData);
    return;