How to group excel file rows in Node.js

you need to extract the data you need yourself.

You could add various indicators to know where you are, and when you need to add what. For example, add question indicator to detect start of the question and new data object, blank row check, skip next row and stats/total row, and finish data object when you reach new question, reset it etc.

Try this:

const filename = path.join(__dirname, `./tempXLSX/dump.xlsx`)

const readOpts = {
    cellText: false,
    cellDates: true
};

const jsonOpts = {
    header: 1,
    defval: '',
    blankrows: true,
    raw: false,
}


const workbook = XLSX.readFile(filename, readOpts);
const worksheet = workbook.Sheets['Sheet'];
const isXLSXFiletoJSON = XLSX.utils.sheet_to_json(worksheet, jsonOpts)


const data = [];

// question, indicates a new object
const questionIndicator = /^q\d\.\s/i;

// or whatever you'll do with group..
let group = 1;

// your data object
let obj = {
    "question": "",
    "style": "",
    "thead": "",
    "season": "",
    "group": group,
    "detail": []
};

// stats/totals row after question row, skip it or whatever..
let prevQ = false;

// detect blank, empty row to add empty array
let prevEmtpy = false;

// check manually if blank is at the beginning and add it
if (Object.keys(worksheet)[1] !== 'A1') {
    data.push([]);
}

isXLSXFiletoJSON.map((row, i) => {


    // blank row
    if (!row[0] && !row[1] && !row[2]) {

        // add empty
        if (!prevEmtpy) {

            // add question
            data.push(obj);

            group++;

            obj = {
                "question": "",
                "style": "",
                "thead": "",
                "season": "",
                "group": group,
                "detail": []
            };

            data.push([]);
        }
        prevEmtpy = true;

    // stats/totals row, skip
    } else if (!row[0] && row[1] && row[2]) {

        // do something with totals/stats

        prevEmtpy = false;

    // question
    } else if (questionIndicator.test(row[0])) {

        // add question if blank didn't, and if it's populated
        if (i>0 && !prevEmtpy) {

            data.push(obj);

            group++;

            obj = {
                "question": "",
                "style": "",
                "thead": "",
                "season": "",
                "group": group,
                "detail": []
            };
        }

        // question values
        obj.question = row[0].replace(questionIndicator, '');
        obj.style = row[1];
        obj.thead = row[2];

        prevQ = true;
        prevEmtpy = false;

    // add details
    } else {

        // skip next row after question - "Answer Choices"
        // add details if it's not
        if (!prevQ) {

            obj.detail.push({
                "answer": row[0],
                "percent": row[1],
                "response": row[2]
            });
        }

        prevQ = false;
        prevEmtpy = false;
    }

    // done, add last question
    if (i + 1 === isXLSXFiletoJSON.length) {
        data.push(obj);
    }

});


console.log(JSON.stringify(data, null, ' '));