SheetJS json_to_sheet renaming headers

Question

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?

score 11 · Answer 1 · edited Feb 24 '20 at 15:04

11

replace the last line with below.

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

edited Feb 24 '20 at 15:04

Gander

1,428
1
19
26

answered Feb 24 '20 at 14:18

Peilin Zhai

111
1
3

Angel Alvarez Pérez · Answer 2 · 2020-10-06T16:03:51.003

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: "abc@example.com"},
  {firstName:"Harry", lastName: "Potter", email: "abc@example.com"},
];

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

score 2 · Answer 3 · answered Jul 24 '20 at 01:25

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

Help would be appreciated if you can explain what this data here imply in the code --> 'this.data.unshift(headers)' — Suresh, Apr 09 '21 at 08:02
Array.prototype.unshift() javascript function will append the header at the front of the array of object. Therefore first row in array of object will be your header and remaining will be data row. — kumar chandraketu, Apr 09 '21 at 16:02

score 0 · Answer 4 · answered Mar 03 '21 at 22:15

0

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')

answered Mar 03 '21 at 22:15

Sebastián Ibarra

1

While this code may solve the question, [including an explanation](https://meta.stackexchange.com/q/114762) of how and why this solves the problem would really help to improve the quality of your post, and probably result in more up-votes. Remember that you are answering the question for readers in the future, not just the person asking now. Please edit your answer to add explanations and give an indication of what limitations and assumptions apply. – John Conde Mar 03 '21 at 23:43

SheetJS json_to_sheet renaming headers

4 Answers4