title: Convert XLS(x) data to Angular Material Data Table
body: <p>I am working on a project where I need to import data from an Excel spreadsheet (eventually, multiple sheets), and I want to display the data in an Angular Material datatable.</p>
  <p>I can load the Excel data into my component as csv, json, html, or text using npm - xlsx module.<br />
  My HTML page is working correctly, but any attempts to pass data gives me a blank table.  The data does not line up correctly to the way mat-table reads the data.  I do have a standard HTML table that does work and shows the data properly, but I really need the functionality that comes from Angular Material.</p>
  <p>This gives me data in this format:</p>
  <pre><code>Block1: 
  Data:    0: (5) [&quot;SYMBOL&quot;, &quot;QTY&quot;, &quot;MFG&quot;, &quot;PART NUMBER&quot;, &quot;DESC&quot;]
           1: (5) [&quot;TB2&quot;, 1, &quot;AB&quot;, &quot;1492-EBJ3&quot;, &quot;PLASTIC END BARRIER, GRAY FOR AB 1492-J*&quot;]
           2: (5) [&quot;TB2&quot;, 11, &quot;AB&quot;, &quot;1492-J4&quot;, &quot;SINGLE CIRCUIT SCREW TERMINAL BLOCK, 22-10 AWG, GRAY, 35A, 4MM&quot;]
           3: (5) [&quot;ECR1&quot;, 1, &quot;AB&quot;, &quot;700S-CF620DC&quot;, &quot;CF TYPE SAFETY CONTROL RELAY,  120VAC COIL, 8 POLE, 6 NO 2 NC STANDARD CONTACTS&quot;]
           4: (5) [&quot;SPARE&quot;, 5, &quot;BUSSMANN&quot;, &quot;AGC-1/2&quot;, &quot;FAST ACTING, 1/2AMP, 250V, 1/4 X 1-1/4 INCH, GLASS TUBE FUSE&quot;]
  </code></pre>
  <p>I need to convert this to this format:</p>
  <pre><code> Block2:
       0: {partId: 1, partNumber: &quot;*M-XB-12248&quot;, description: &quot;Killark XB-12248 mod. w/holes&quot;, supplierId: 31, supplierName: &quot;Kendall Electrical Supply&quot;, …}
       1: {partId: 4, partNumber: &quot;E9UVCRN&quot;, description: &quot;1 Gang Vertical Box, Weatherproof&quot;, supplierId: 31, supplierName: &quot;Kendall Electrical Supply&quot;, …}
       2: {partId: 79, partNumber: &quot;F66G36&quot;, description: &quot;36&quot; Straight Wireway Section&quot;, supplierId: 31, supplierName: &quot;Kendall Electrical Supply&quot;, …}
       3: {partId: 21, partNumber: &quot;EMT1-1/4 ELB&quot;, description: &quot;1 1/4&quot; EMT Sweep&quot;, supplierId: 14, supplierName: &quot;Graybar&quot;, …}
  </code></pre>
  <p>I am looking at the format of the two blocks, not the specific data in the blocks (block 2 is from another section of app that is reading a JSON file).</p>
  <p>Here is my angular component to read/parse the Excel data, and where I also want to convert the data to the type in Block2 above.</p>
  <pre><code>import { Component, OnInit, Input, ViewChild, ElementRef } from '@angular/core';
  import { MatTableDataSource } from '@angular/material/table';
  import * as XLSX from 'xlsx';
  
  export class BoMItem {
    item: string;
    symbol: string;
    qty: number;
    mfg: string;
    partNumber: string;
    desc: string;
  }
  
  @Component({
    selector: 'app-bom-list',
    templateUrl: './bom-list.component.html',
    styleUrls: ['./bom-list.component.css']
  })
  export class BomListComponent implements OnInit {
  
    data: [][];
  
    @ViewChild('bomTable', { static: false }) bomTable: ElementRef;
  
    // dataSource = new MatTableDataSource&lt;BoMItem&gt;();
  
    dataSource = new MatTableDataSource&lt;BoMItem&gt;();
    // displayedColumns: string[] = ['item', 'symbol', 'qty', 'mfg', 'partNumber', 'desc'];
    displayedColumns: string[] = ['partNumber', 'mfg', 'desc', 'qty'];
  
    importFile: File;
    storeData: any;
    csvData: any;
    jsonData: any;
    textData: any;
    worksheet: any;
    arrData: any;
  
    constructor() {
    }
  
    ngOnInit() {
    }
  
    uploadedFile(evt: any) {
      const target: DataTransfer = (evt.target) as DataTransfer;
  
      const reader: FileReader = new FileReader();
  
      reader.onload = (e: any) =&gt; {
        let bstr: string = e.target.result;
  
        let wb: XLSX.WorkBook = XLSX.read(bstr, { type: 'binary' });
  
        let wsName: string = wb.SheetNames[0];
  
        let ws: XLSX.WorkSheet = wb.Sheets[wsName];
  
        this.data = (XLSX.utils.sheet_to_json(ws, { header: 1 }));
        console.log('Data: ', this.data);
        this.jsonData = JSON.stringify(this.data);
  
        console.log('JsonData: ', this.jsonData);
  
      };
  
      reader.readAsBinaryString(target.files[0]);
    }
  
  }
  </code></pre>
  <p>HTML :</p>
  <pre><code>  &lt;table&gt;
      &lt;tbody&gt;
        &lt;tr *ngFor=&quot;let row of data&quot;&gt;
          &lt;td *ngFor=&quot;let cell of row&quot;&gt;
            {{cell}}
          &lt;/td&gt;
        &lt;/tr&gt;
      &lt;/tbody&gt;
    &lt;/table&gt;
  
    &lt;div class=&quot;class=&quot; mat-elevation-z8&gt;
      &lt;table mat-table [dataSource]=&quot;dataSource&quot; id=&quot;bomTable&quot; #bomTable&gt;
  
        &lt;ng-container matColumnDef=&quot;partNumber&quot;&gt;
          &lt;th mat-header-cell *matHeaderCellDef&gt; Part Number &lt;/th&gt;
          &lt;td mat-cell *matCellDef=&quot;let element&quot;&gt;{{ element.partNumber }}&lt;/td&gt;
        &lt;/ng-container&gt;
  
        &lt;ng-container matColumnDef=&quot;mfg&quot;&gt;
          &lt;th mat-header-cell *matHeaderCellDef&gt; MFG &lt;/th&gt;
          &lt;td mat-cell *matCellDef=&quot;let element&quot;&gt;{{ element.mfg }}&lt;/td&gt;
        &lt;/ng-container&gt;
  
        &lt;ng-container matColumnDef=&quot;desc&quot;&gt;
          &lt;th mat-header-cell *matHeaderCellDef&gt; Desc &lt;/th&gt;
          &lt;td mat-cell *matCellDef=&quot;let element&quot;&gt;{{ element.desc }}&lt;/td&gt;
        &lt;/ng-container&gt;
  
        &lt;ng-container matColumnDef=&quot;qty&quot;&gt;
          &lt;th mat-header-cell *matHeaderCellDef&gt; QTY &lt;/th&gt;
          &lt;td mat-cell *matCellDef=&quot;let element&quot;&gt;{{ element.qty }}&lt;/td&gt;
        &lt;/ng-container&gt;
  
        &lt;tr mat-header-row *matHeaderRowDef=&quot;displayedColumns&quot;&gt;&lt;/tr&gt;
        &lt;tr mat-row *matRowDef=&quot;let row; columns: displayedColumns;&quot;&gt;&lt;/tr&gt;
  
      &lt;/table&gt;
    &lt;/div&gt;
  </code></pre>