Angular export specific columns of html table to excel file using XLSX

Question

I want to export specific columns of HTML table to excel file. The columns to be selected are based on some condition selected by user.

this.checkList = [
      { title: 'Accession Number', show: true, link: 'accessionNumber' },
      { title: 'Title', show: true, link: 'title' },
      { title: 'Sub Title', show: false, link: 'subTitle' },
      { title: 'Status', show: true, link: 'status' },
      { title: 'Authors', show: true, link: 'authors' },
      { title: 'ISBN', show: true, link: 'isbn' },
      { title: 'ISBN 10', show: false, link: 'isbn10' },
      { title: 'ISBN 13', show: false, link: 'isbn13' },
      { title: 'Subjects', show: true, link: 'subjects' },
      { title: 'Publishers', show: false, link: 'publishers' },
      { title: 'Vendors', show: false, link: 'vendors' },
      { title: 'Contributors', show: false, link: 'contributors' },
      { title: 'Collaborators', show: false, link: 'collaborators' },    
];

If show is true then we will display that column. Here is the HTML code to show table.

<table id="dataTable" #dataTable class="table table-striped">
            <thead>
                <tr>
                    <th class="my-text"
                        *ngFor="let item of checkList"
                        [ngClass]="{'d-none': !item.show}">{{item.title}}</th>
                </tr>
            </thead>
            <tbody class="ng-star-inserted">
                <tr>
                    <td *ngFor="let item of checkList"
                        [ngClass]="{'d-none': !item.show}">
                        <input nbInput
                            [(ngModel)]="checkSearch[item.link]"
                            (keyup)="filter()"
                            placeholder="{{item.title}}">
                </tr>
                <tr class="ng2-smart-row selected ng-star-inserted"
                    *ngFor="let book of booksResult | paginate: {itemsPerPage: 10, currentPage: page}">
                    <td *ngFor="let item of checkList"
                        class="my-text text-center"
                        [ngClass]="{'d-none': !item.show}">{{ book[item.link] }}</td>
                </tr>
            </tbody>
        </table>

I am using exportToExcel function as follows.

  export2Excel() {
    const ws: XLSX.WorkSheet = XLSX.utils.table_to_sheet(
      this.table.nativeElement,
    );
    const wb: XLSX.WorkBook = XLSX.utils.book_new();
    XLSX.utils.book_append_sheet(wb, ws, 'Sheet1');
    XLSX.writeFile(wb, 'books.xlsx');
  }

The table element is included as follows.

@ViewChild('dataTable') table: ElementRef;

However, When I export this I get d-none elements also, although no data is shown in those columns. Is there any way to include only elements that are being displayed?

Answer 1

You can create a custom data array depending on your business logic and pass the array to make the excel. Use XLSX.utils.json_to_sheet to make excel

 exportToExcel() {
     // implement your logic to make the data set from your original dataset.
     let data = [
      { title: "Accession Number", show: true, link: "accessionNumber" },
      { title: "Title", show: true, link: "title" },
      { title: "Sub Title", show: false, link: "subTitle" },
      { title: "Status", show: true, link: "status" },
      { title: "Authors", show: true, link: "authors" },
      { title: "ISBN", show: true, link: "isbn" },
      { title: "ISBN 10", show: false, link: "isbn10" },
      { title: "ISBN 13", show: false, link: "isbn13" },
      { title: "Subjects", show: true, link: "subjects" },
      { title: "Publishers", show: false, link: "publishers" },
      { title: "Vendors", show: false, link: "vendors" },
      { title: "Contributors", show: false, link: "contributors" },
      { title: "Collaborators", show: false, link: "collaborators" }
    ];
    const fileName = "test.xlsx";

    const ws: XLSX.WorkSheet = XLSX.utils.json_to_sheet(data);
    const wb: XLSX.WorkBook = XLSX.utils.book_new();
    XLSX.utils.book_append_sheet(wb, ws, "test");

    XLSX.writeFile(wb, fileName);
  }

Here Example