angularsheetjsjs-xlsx

sheet_to_json producing bad array of array


Using Angular 9, I am building a step wizard and the first step is to read and parse an .xlsx file.

I have a file input defined as such:

<form [formGroup]="steps[currentStep].form">
  <label>Select File to Process</label>
  <input accept=".xlsx"
         autocomplete="off"
         floatPlaceholder="always"
         formControlName="input"
         placeholder="Input"
         type="file"
         (change)="fileChanged($event)">
</form>

I then have an interface that represents a one-to-one relationship to a data table, I would define the entire interface but there is like 100 columns (outside of my control and they're all named terribly). But it basically looks like:

export interface IRequestItem {
    ReqItemId: number;
    ReqNum?: string;
    _ReqItem?: number;
    CatNum?: number;
    // ... etc.
}

The idea is that the user would upload the .xlsx file using the input and then an array of IRequestItem would be created. To do this, here is how the fileChanged event handler is setup:

fileChanged(e): void {
    this.steps[this.currentStep].loading = true;
    const reader = new FileReader();
    const file = e.target.files[0];
    reader.onload = (event) => {
        const data = reader.result;
        const workBook = XLSX.read(data, { type: 'binary' });
        if (workBook.SheetNames.length === 0) {
            this.steps[this.currentStep].loading = false;
            return;
        }
        const worksheet = workBook.Sheets[workBook.SheetNames[0]];
        this.workSheet = XLSX.utils.sheet_to_json<IRequestItem>(worksheet, {
            blankrows: false,
            header: 1,
            raw: true,
            rawNumbers: true
        });

        this.steps[this.currentStep].loading = false;
    };
    reader.readAsBinaryString(file);
}

Basically it does the following:

  1. Set the loading property of the current step
  2. Get the file contents using a FileReader
    • Step 3 would be executed after the file contents are read
  3. Get the FileReader results and convert it to an XLSX.WorkBook
    • If the user uploads an empty workbook then unset the loading property and return
  4. Get the first worksheet of the workbook
  5. Try to convert the worksheet to an array of IRequestItem using the XLSX.utils.sheet_to_json<T> method

Everything appears to run fine up until step 5, but if setup a debugger on that line the output in the console is an array of array.

What I don't understand about this is that I'm creating the .xlsx file by querying the table in SSMS, exporting the results as a .CSV file, and then saving the file using the .xlsx extension (in Microsoft Excel). So I know for a fact that not only is my file properly setup but I also know that the data represents a legit IRequestItem.


Solution

  • After a long while of searching, I realized that I misread the documentation. Per https://docs.sheetjs.com/#json I was specifying the wrong header property in the Sheet2JSONOpts. Instead, it should have been:

    this.workSheet = XLSX.utils.sheet_to_json<IRequestItem>(worksheet, {
      blankrows: false,
      header: 'A',
      raw: true,
      rawNumbers: true
    });