Angular 7 and xlsx library how to export array with field name instead of index

Question

Following on this question on stack, and this stackblitz, it seems that the material table does not fully exported to excel if the pagination is not showing all data.

So instead, I will export the array as a whole, but the problem is that the main field names is showing the indexes instead of the names:

So instead of:

exportTable()
{
  //let data = Object.values(this.dataSource);
  const ws: xlsx.WorkSheet=xlsx.utils.table_to_sheet(data);
  const wb: xlsx.WorkBook = xlsx.utils.book_new();
  xlsx.utils.book_append_sheet(wb, ws, 'All Ind. Searched Data Export');

  /* save to file */
  xlsx.writeFile(wb, 'ExportAllData_Ind.xlsx');
}

I changed to:

exportTable()
{
  let data = Object.values(this.dataSource);
  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, 'All Ind. Searched Data Export');

  /* save to file */
  xlsx.writeFile(wb, 'ExportAllData_Ind.xlsx');
}

The problem is that the exported excel is setting the field names into indexes and then add at the end, the real field names:

I know it is something related to arrays, but how can I export the array with only the field name part.

score 1 · Accepted Answer · answered Dec 24 '18 at 07:07

Okay i found my own solution, until the problem of extracting an angular material data table is fixed as it's only extracting the active pagination sheet instead of all table. I just used the traditional method:

exportTable()
  {
    let newArray:any[]=[];
    let data = Object.values(this.allsearched);
      Object.keys(data).forEach((key, index)=>{
        newArray.push({
          'Ind. ID': data[key].individual_id,
          'HH ID': data[key].hh_id,
          'Name(en)': data[key].ind_first_name_en+' '+data[key].ind_last_name_en,
          'Name(ar)': data[key].ind_first_name_ar+' '+data[key].ind_last_name_ar,
          'UID': data[key].uId,
          'Gender': data[key].ind_gender,
          'D.O.B': data[key].dob,
          'Head Of HH': data[key].head_of_hh,
          'Phone Number': data[key].phone,
          'Status': data[key].ind_status,
          'User ID': data[key].user_id
        })
      })


    const ws: xlsx.WorkSheet=xlsx.utils.json_to_sheet(newArray);
    const wb: xlsx.WorkBook = xlsx.utils.book_new();
    xlsx.utils.book_append_sheet(wb, ws, 'All Ind. Searched Data Export');

    /* save to file */
    xlsx.writeFile(wb, 'ExportAllData_Ind.xlsx');
  }

score 0 · Answer 2 · answered Mar 09 '23 at 13:20

This is a modification of @alim1990's answer. #typeScript

  exportXlsxFromErray(erray: any, fileName: string, sheetName: string) {
    let newArray:any[]=[];
    let data: any[] = Object.values(erray);
    Object.keys(data).forEach((key: any)=>{
      var headers = Object.keys(data[key]);
      // if you want to exclude a column, replace the previous line with the following line:
      // var headers = Object.keys(data[key]).filter(x => x != 'nameOfColumnToBeExcluded');
      var obj = {} as any;
      headers.forEach((header: string) => {
        obj[header] = data[key][header];
      })
      newArray.push(obj)
    })
    const ws: XLSX.WorkSheet=XLSX.utils.json_to_sheet(newArray);
    const wb: XLSX.WorkBook = XLSX.utils.book_new();
    XLSX.utils.book_append_sheet(wb, ws, sheetName);

    fileName += '.xlsx';
    /* save to file */
    XLSX.writeFile(wb, fileName);
  }

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). — Community, Mar 14 '23 at 20:14

Angular 7 and xlsx library how to export array with field name instead of index

2 Answers2