merged column only display as first row

Question

I have this excel format then will be import and display into html table.

The participants will always have same Trainer so i just merged the trainer column but the problem is when console.log it, it count as one row only.

Here's the code

    const Importssss = document.getElementById('importInput');
           var JSONExcel = function () {
               this.parseExcel = function (file) {
                   var reader = new FileReader();
               reader.onload = function (e) {
            var data = e.target.result;
            var excelBinary = XLSX.read(data, { type: 'binary' });
            excelBinary.SheetNames.forEach(function (sheetName) { // Reading each sheet data in excel
                var XL_row_object = XLSX.utils.sheet_to_row_object_array(excelBinary.Sheets[sheetName]);
                var json_object = JSON.stringify(XL_row_object); // json from excel
                var excelData = (JSON.parse(json_object)); // json parsed from excel
                console.log(excelData);
            })
        };
        reader.onerror = function (ex) {
            console.log(ex);
        };
        reader.readAsBinaryString(file);
    };
};

      Importssss.addEventListener('change', function (evt) {
    var files = evt.target.files;
    if (files.length !== 0) {
        var JSONConvert = new JSONExcel();
        JSONConvert.parseExcel(files[0]); // Pass to Excel Converter Function
    }
})

and this is the output

Expected output should not count as a first row but in all row.

Answer 1

You can create a new array that keeps track of the last-named trainer

You can also simplify your code in several ways. Most importantly, there is no need for the JSON.stringify followed by JSON.parse. They cancel each other out, so just skip those two lines.

Just call XLSX.utils.sheet_to_row_object_array, but perhaps name the resulting variable explicitly like this:

const arrayOfRowsSkippingTrainer = XLSX.utils.sheet_to_row_object_array(excelBinary.Sheets[sheetName]);

Then you can create an array with the missing Trainer filled in:

// const arrayOfRowsSkippingTrainer = XLSX.utils.sheet_to_row_object_array(excelBinary.Sheets[sheetName]);

const arrayOfRowsSkippingTrainer = [{
    Trainer: "T1",
    Participant: "P1",
    Subject: "S1",
    Score: "10"
  },
  {
    Participant: "P2",
    Score: "20",
    Subject: "S2",
  },
  {
    Participant: "P3",
    Score: "30"
  },
  {
    Trainer: "T2",
    Participant: "P4",
    Score: "40"
  },
  {
    Participant: "P5",
    Score: "50"
  },
  {
    Trainer: "T3",
    Participant: "P6",
    Score: "60"
  },
  {
    Trainer: "T4",
    Participant: "P7",
    Score: "70"
  },
]


let lastTrainer = "";
let lastSubject = "";
const arrayOfRows = arrayOfRowsSkippingTrainer.map(row => {
  if ("Trainer" in row) {
    lastTrainer = row.Trainer
  }
  if ("Subject" in row) {
    lastSubject = row.Subject
  }
  return { ...row,
    Trainer: lastTrainer,
    Subject: lastSubject,
  }
})

console.log(arrayOfRows)