Use SheetJS to customize header order using json_to_sheet

Question

Use SheetJS to customize header order using json_to_sheet

I am currently using SheetJS within the Angular framework to export JSON data as an .xlsx file. An example of the JSON structure I am working with is shown below:

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

Please note that the keys in the objects are not fixed and may vary. The only consistent keys are ID and LastLogin.

The function I am utilizing for exporting the data is as follows:

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);
}

Upon export, the resulting Excel sheet appears like this:

https://i.sstatic.net/TlYZ4.png

My objective is to ensure that the LastLogin column is always displayed as the final column irrespective of the object structure. Is there a method to achieve this? As I am relatively new to this concept, any guidance would be greatly appreciated.

javascript json excel angularjs sheetjs

Answer 1

Answer №1

Improve organization and labeling with this code snippet:

To easily reorder and relabel headers, utilize the map function to transform a TypeScript object into an array with customizable headers-

const transformedArray = this.myTsObjArray.map(value=>({
'New Header 1': value.prop2,
'New Header 2': value.prop1
}));

Next, incorporate the modified object into XLSX format-

const ws:XLSX.workSheet = XLSX.utils.json_to_sheet(transformedArray);

Answer 2

Improve organization and labeling with this code snippet:

To easily reorder and relabel headers, utilize the map function to transform a TypeScript object into an array with customizable headers-

const transformedArray = this.myTsObjArray.map(value=>({
'New Header 1': value.prop2,
'New Header 2': value.prop1
}));

Next, incorporate the modified object into XLSX format-

const ws:XLSX.workSheet = XLSX.utils.json_to_sheet(transformedArray);

Answer 3

Answer №2

SheetJS operates by extracting the order of column headers for Excel data from the initial row, then adding new object keys as they are encountered to match the row header at the end.

To customize the output format according to your preferences, preprocess the input JSON before invoking XLSX.utils.json_to_sheet.

You can define a function like this:

function adjustObjectForWorksheet(obj) {
  // extract all unique keys from objects in the array
  // flatten the array and remove duplicate names with Set
  const uniqueKeys = Array.from(new Set(obj.map(o => Object.keys(o)).flat()));

  // exclude LastLogin key from the array and place it at the end
  const lastKey = "LastLogin";
  const headers = uniqueKeys.filter(k => k !== lastKey).concat(lastKey);

  // transform original data into a new array
  // first entry will set row headers in Excel sheet
  const modifiedData = obj.map(o => {
    return headers.reduce((acc, curr) => {acc[curr] = o[curr]; return acc;}, {});
  });

  return modifiedData;
}

The code is commented, but the main functionalities include:

obtaining an array of unique keys from all objects in the input array (referenced as your json variable)
ensuring that LastLogin appears as the last element in the array
creating a new object per input object where missing properties (e.g., Score) have a value of undefined

In your exportAsExcelFile method, simply apply this adjustment before the first line:

const updatedJson = adjustObjectForWorksheet(json);
const worksheet: XLSX.WorkSheet = XLSX.utils.json_to_sheet(updatedJson );

Answer 4

SheetJS operates by extracting the order of column headers for Excel data from the initial row, then adding new object keys as they are encountered to match the row header at the end.

To customize the output format according to your preferences, preprocess the input JSON before invoking XLSX.utils.json_to_sheet.

You can define a function like this:

function adjustObjectForWorksheet(obj) {
  // extract all unique keys from objects in the array
  // flatten the array and remove duplicate names with Set
  const uniqueKeys = Array.from(new Set(obj.map(o => Object.keys(o)).flat()));

  // exclude LastLogin key from the array and place it at the end
  const lastKey = "LastLogin";
  const headers = uniqueKeys.filter(k => k !== lastKey).concat(lastKey);

  // transform original data into a new array
  // first entry will set row headers in Excel sheet
  const modifiedData = obj.map(o => {
    return headers.reduce((acc, curr) => {acc[curr] = o[curr]; return acc;}, {});
  });

  return modifiedData;
}

The code is commented, but the main functionalities include:

obtaining an array of unique keys from all objects in the input array (referenced as your json variable)
ensuring that LastLogin appears as the last element in the array
creating a new object per input object where missing properties (e.g., Score) have a value of undefined

In your exportAsExcelFile method, simply apply this adjustment before the first line:

const updatedJson = adjustObjectForWorksheet(json);
const worksheet: XLSX.WorkSheet = XLSX.utils.json_to_sheet(updatedJson );

Use SheetJS to customize header order using json_to_sheet

Answer №1

Answer №2

Similar questions

Tips for expanding the content of a blogger page to fill the entire frame of the page

Two entities positioned on opposite extremes

Exploring Python's requests module: encoding JSON data in URLs

Ubuntu is experiencing a DNS problem. While the URL request works perfectly on MacOSX, it is unsuccessful on Ubuntu

The $http Service encounters a failure with an unknown status code

Combining Java for the back-end and JavaScript for the front-end: a comprehensive guide

Avoid injecting JavaScript code into an element with AJAX to prevent unnecessary loading times

Incorporate JSON data into a Flutter list view in a dynamic way without the need to first load it into

Creating an interactive animation of bouncing balls within an HTML5 canvas using JavaScript

NodeJs:- Retrieve data from the initial dropdown menu and display it dynamically in the secondary dropdown

How do I locate the specific page in angular.js where I need to make changes?

EJS functionality is operational, however, the HTML content is not displaying

"Discovering hidden features of Django when using the pyexcel library for creating Excel files on a

Using Regular Expressions for validation restricts the functionality of home, end, and arrow keys

Detecting collisions between multiple moving objects in Three.js

Combining and consolidating JSON attributes

Untangling Internet JSON data

I would like to implement a delay on this button so that when it is clicked, there is a pause before navigating

Is it possible to reset the existing localStorage value if we access the same URL on a separate window?

Model data is missing from the form submission