Hide/Delete column when exporting html table with XLSX (table to sheet)

Question

I want to be able to export in excel files format my tables of my DOM.

My function (Typescript 3.5 / Angular 8)

ExportTOExcel() {
    const ws: XLSX.WorkSheet=XLSX.utils.table_to_sheet(document.getElementById('serversTable'));
    const wb: XLSX.WorkBook = XLSX.utils.book_new();

    XLSX.utils.book_append_sheet(wb, ws, 'Servers');

    /* save to file */
    XLSX.writeFile(wb, 'myproject.xls');
}

My table :

<table id="serversTable" mat-table [dataSource]="serverArray" multiTemplateDataRows>
   <ng-container matColumnDef="number">
      <th mat-header-cell *matHeaderCellDef>Qty</th>
      <td mat-cell *matCellDef="let resourceGroup">
         ...
      </td>
   </ng-container>

   <ng-container matColumnDef="name">
      <th mat-header-cell *matHeaderCellDef>name</th>
      <td mat-cell *matCellDef="let  resourceGroup">
         ...
      </td>
   </ng-container>

   <!-- Other columns -->

   <ng-container matColumnDef="expandedDetail">
      ...
   </ng-container>

   <tr mat-header-row *matHeaderRowDef="displayedColumns"></tr>
   <tr mat-row *matRowDef="let resourceGroup; columns: displayedColumns;" class="example- 
      element-row" [class.example-expanded-row]="expandedElement === resourceGroup 
      (click)="clickOnRowDisk(resourceGroup)">
   </tr>
   <tr mat-row *matRowDef="let row; columns: ['expandedDetail']" class="example-detail-row"></tr>
</table>

The expandedDetail is the column that I don't want it to be in the file written by the function ExportTOExcel. This column is the last column of my table.

I tried

ws['!cols'] = [];
ws['!cols'][0] = { hidden: true };

but doesn't work.

Straight

Answer 1

To Hide Column

const ws: 
 XLSX.WorkSheet=XLSX.utils.table_to_sheet(document.getElementById('serversTable'));

ws['!cols'] = [];
ws['!cols'][12] = { hidden: true };
/* here 12 is your column number (n-1) */
const wb: XLSX.WorkBook = XLSX.utils.book_new();

XLSX.utils.book_append_sheet(wb, ws, 'Servers');
/* save to file */
XLSX.writeFile(wb, 'myproject.xls');

Tobitobiah

Answer 2

To Remove Column

const ws: XLSX.WorkSheet = XLSX.utils.table_to_sheet(this.table.nativeElement);
delete (ws['O1'])
/* O1 is your Column in Excel*/

const wb: XLSX.WorkBook = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, ws, 'Sheet1');
/* save file */
XLSX.writeFile(wb, 'SheetTest.xlsx');

Tobitobiah

O1 is cell of Column "O" and row "1" – Bioscopy

This will clear the data but the column will be generated as empty. – Bioscopy

Also, to be clear, this answer is in TypeScript, not Javascript. Please label it as such to not confuse newer coders. – Andro

Answer 3

I couldn't do it with the delete command (ws['O1'])

Html Export

XLSX.utils.sheet_add_aoa(ws, [["Name", ""]], { origin: "A1" });

In the Action column I managed to put a value of zero, so it would not be displayed.

Result enter image description here

Underlie