Skip starting blank rows in Excel with XLSX in JavaScript Sheet-JS

Question

I have the following code that works great when the header row is row 1

  readerData(rawFile) {         
      return new Promise((resolve, reject) => {
        const reader = new FileReader();
        reader.onload = e => {
          const data = e.target.result;
          const workbook = XLSX.read(data, { type: "array" });
          const firstSheetName = workbook.SheetNames[0];
          const worksheet = workbook.Sheets[firstSheetName];
          const header = this.getHeaderRow(worksheet);
          const results = XLSX.utils.sheet_to_json(worksheet,{ header: 0, range: 0, defval: ""});
          this.generateData({ header, results });
          this.loading = false;
          resolve();
        };
        reader.readAsArrayBuffer(rawFile);
      });
    },
    generateData({ header, results }) {
      this.excelData.header = header;
      this.excelData.results = results;
      this.excelData.original_results = [...results];
      this.onSuccess && this.onSuccess(this.excelData);

         var grid = this.$refs.membersGrid.ej2Instances;                         
        grid.dataSource = this.excelData.results;
          grid.refresh(); 
    },
      getHeaderRow(sheet) {
      const headers = [];
      const range = XLSX.utils.decode_range(sheet["!ref"]);
      let C;
      const R = range.s.r;
      /* start in the first row */
      for (C = range.s.c; C <= range.e.c; ++C) {
        /* walk every column in the range */
        const cell = sheet[XLSX.utils.encode_cell({ c: C, r: R })];
        /* find the cell in the first row */
        let hdr = "UNKNOWN " + C; // <-- replace with your desired default
        if (cell && cell.t) hdr = XLSX.utils.format_cell(cell);
        headers.push(hdr);
      }
      return headers;
    },

It works great and put all of the Header values into the excelData.header and it put all of the named array data into the excelData.results. My problem is it all goes to a mess when the first row or first two rows are blank or I need to skip them. I've tried

  https://github.com/SheetJS/sheetjs/issues/463

but I'm using "xlsx": "^0.17.1" . When I used

    range.s.r = 1;

I was able to change my range to A2 but I could not get my named array of data. Any help is appreciated .

Answer 1

After a few days of digging and a lot of trial and error code I got my solution that I will post for others to use if they have the same problem. I used the same code up to this point.

  readerData(rawFile) {
  let fileName = rawFile.name;
  this.showLoading();
  return new Promise((resolve, reject) => {
    const reader = new FileReader();
    reader.onload = e => {
      var data = e.target.result;
      var workbook = XLSX.read(data, { type: "array" });
      var firstSheetName = workbook.SheetNames[0];
      var worksheet = workbook.Sheets[firstSheetName];
      var range = XLSX.utils.decode_range(worksheet['!ref']);
      var header = this.getHeaderRow(worksheet,range);

The reason for this is that it gives me the file name, the first sheets name and the value in the first row. From there I can determine where my header row is and where the data row starts. The data row does not have to be the row after the header so my next line of code could be:

       range.s.r = 1; // <-- zero-indexed, so setting to 1 will skip row 0
       worksheet['!ref'] = XLSX.utils.encode_range(range);
       header = this.getHeaderRow(worksheet,range);
       range.s.r = 2; 
      var results = XLSX.utils.sheet_to_json(worksheet,{ header: header, range: range, defval: ""});

The results are based upon the values header and range that have been adjusted using the:

      range.s.r = 1 or 2 or whatever number you need

I can now read all of the Excel sheets and make a clean named array for data processing. Hope this helps someone. rather than my original 0's