'SheetJS json_to_sheet renaming headers

I have an array of objects. By default, sheet_to_json uses object keys as headers. However, I need to rename those headers without explicitly modifying my array of objects.

Is this possible?



Solution 1:[1]

I'm sharing the solution that I found

// Using same variables as the above answer
var Heading = [
  ["FirstName", "Last Name", "Email"],
];
var Data = [
  {firstName:"Jack", lastName: "Sparrow", email: "[email protected]"},
  {firstName:"Harry", lastName: "Potter", email: "[email protected]"},
];

//Had to create a new workbook and then add the header
const ws = XLSX.utils.book_new();
XLSX.utils.sheet_add_aoa(ws, Heading);

//Starting in the second row to avoid overriding and skipping headers
XLSX.utils.sheet_add_json(ws, Data, { origin: 'A2', skipHeader: true });

Solution 2:[2]

replace the last line with below.

XLSX.utils.sheet_add_json(ws, data, {skipHeader: true, origin: "A2"});

Solution 3:[3]

This code is based on Angel Alvarez's answer and added a working demo:

let arr = [
  { firstName: 'Jack', lastName: 'Sparrow', email: '[email protected]' },
  { firstName: 'Harry', lastName: 'Potter', email: '[email protected]' },
];

let Heading = [['FirstName', 'Last Name', 'Email']];

//Had to create a new workbook and then add the header
const wb = XLSX.utils.book_new();
const ws: XLSX.WorkSheet = XLSX.utils.json_to_sheet([]);
XLSX.utils.sheet_add_aoa(ws, Heading);

//Starting in the second row to avoid overriding and skipping headers
XLSX.utils.sheet_add_json(ws, arr, { origin: 'A2', skipHeader: true });

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

XLSX.writeFile(wb, 'filename.xlsx');

STACKBLITZ DEMO

Solution 4:[4]

i made this work around because both of the above approach were not working for me

let EXCEL_EXTENSION = '.xlsx';
     let worksheet: XLSX.WorkSheet;
     let customHeader = true;
      let sheetName = 'My Sheet 1';  
      
    if (customHeader) {  
     const headers: any = { Cust: 'Customer Name', Addr1: 'Address 1' };
    this.data.unshift(headers); // if custom header, then make sure first row of data is custom header 
      worksheet = XLSX.utils.json_to_sheet(json, { skipHeader: true });
    } else {
      worksheet = XLSX.utils.json_to_sheet(json);
    }
    const workbook = XLSX.utils.book_new();
    const fileName =  'myExcelFile_Export_' + moment().format('MM-DD-YYYY_hh:mm:ss').toString() + EXCEL_EXTENSION;
    XLSX.utils.book_append_sheet(workbook, worksheet, sheetName);
    XLSX.writeFile(workbook, fileName);

Solution 5:[5]

This works for me:

var wb = XLSX.utils.book_new();

        var ws = XLSX.utils.json_to_sheet(json_data, { origin: 'A2', skipHeader: true });
        XLSX.utils.sheet_add_aoa(ws, headings); //heading: array of arrays

        XLSX.utils.book_append_sheet(wb, ws);


        XLSX.writeFile(wb, filename + '.xlsx') 

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Gander
Solution 3 juan_carlos_yl
Solution 4 kumar chandraketu
Solution 5 Sebastián Ibarra