javascriptjsonexcelangularjssheetjs

SheetJS specify header order with json_to_sheet

I am using SheetJS in angular to export json as .xlsx file. For reference the json could be as follows:

[{
   "ID": "E111",
   "Name": "John",
   "LastLogin": "2022-02-12"
},
{
   "ID": "E112",
   "Name": "Jake",
   "Score": 22
   "LastLogin": "2022-02-12"
}]

Note: The keys to the object are unknown, and can vary. The only known keys are ID and LastLogin.

I am using the following function to export

public exportAsExcelFile(json: any[], excelFileName: string): void {
   const worksheet: XLSX.WorkSheet = XLSX.utils.json_to_sheet(json);
   console.log('worksheet',worksheet);
   const workbook: XLSX.WorkBook = { Sheets: { 'data': worksheet }, SheetNames: ['data'] };
   const excelBuffer: any = XLSX.write(workbook, { bookType: 'xlsx', type: 'array' });
   this.saveAsExcelFile(excelBuffer, excelFileName);

}
private saveAsExcelFile(buffer: any, fileName: string): void {
   const data: Blob = new Blob([buffer], {
       type: EXCEL_TYPE
   });
   FileSaver.saveAs(data, fileName + '_export_' + new Date().getTime() + EXCEL_EXTENSION);
}

The resulting excel looks like this

enter image description here

I want LastLogin to be the last column no matter the object. Is there a way to achieve this? I am pretty new to this, so any help is appreciated.

Solution

  • The behaviour of SheetJS here is to take the order of column headers for the Excel data from the first row, and then as new object keys are encountered then for the matching row header to be added at the end.

    To control this behaviour to get the output formatted the way you want, you can process the input json before calling XLSX.utils.json_to_sheet.

    Define this function:

    function restructureObjectForSheet(obj) {
  // for each object in the array put the keys in a new array
  // flatten that array 
  // there will be duplicate names which can be removed with Set
  // turn it back into an array
  const uniqKeys = Array.from(new Set(obj.map(o => Object.keys(o)).flat()));

  // remove LastLogin from this array
  // then put LastLogin at the end of the array
  const endKey = "LastLogin";
  const rowHeaders = uniqKeys.filter(k => k !== endKey).concat(endKey);

  // process the original data into a new array
  // first entry will define row headers in Excel sheet
  const newData = obj.map(o => {
    return rowHeaders.reduce((a, c) => {a[c] = o[c] ; return a}, {});
  });

  return newData;
}

    I've commented the code, but the essential features are:

    Now, in your exportAsExcelFile method, just make this adjustment before the 1st line:

    const newJson = restructureObjectForSheet(json);
const worksheet: XLSX.WorkSheet = XLSX.utils.json_to_sheet(newJson );