while using header option with XLSX.utils.json_to_sheet , headers not overriding

Question

I'm trying to change header titles by passing an array of titles to options but it does not override the headers. Instead it inserts new headers before the original data. I am passing the same numbers of header titles.

Here is my code:

const ws: XLSX.WorkSheet = XLSX.utils.json_to_sheet(
  json,
  {header: headerColumns}
);

const wb: XLSX.WorkBook = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, ws, 'Transactions');
const excelBuffer: any = XLSX.write(wb, { bookType: 'xlsx', type: 'array' });

this.saveAsExcelFile(excelBuffer, excelFileName);

And output looks like below:

score 7 · Accepted Answer · edited Jan 25 '23 at 14:45

7

The basic job of the "header" option is not to override, rather just shift the starting option of the columns.

i.e. any value passed in the header option will be treated as the first column, provided the value should match with existing keys you have in the data.

XLSX.utils.json_to_sheet([{A:1,B:2}, {B:2,C:3}], {header:['C']});

Here column "C" will be the first column in the excel. For more look out for detailed description here: https://docs.sheetjs.com/#sheetjs-js-xlsx

edited Jan 25 '23 at 14:45

fredrivett

5,419
3
35
48

answered Sep 17 '19 at 09:12

Prabhanath

98
1
7

6

that is such weird behavior. – dvdmn Jun 04 '21 at 20:21

score 3 · Answer 2 · edited Jan 25 '23 at 14:40

This is how I have achieved similar behavior:

const XLSX = require('xlsx');
const wb = XLSX.utils.book_new();
    
const Heading = [
    ['Sr No', 'User Name', 'Department', 'Bank', 'Country', 'Region', 'Amount']
];
    
// creating sheet and adding data from 2nd row of column A.
// leaving first row to add Heading
const ws = XLSX.utils.json_to_sheet(data, { origin: 'A2', skipHeader: true });
    
// adding heading to the first row of the created sheet.
// sheet already have contents from above statement.
XLSX.utils.sheet_add_aoa(ws, Heading, { origin: 'A1' });
    
// appending sheet with a name
XLSX.utils.book_append_sheet(wb, ws, 'Records');
    
const fileContent = XLSX.write(wb, { bookType: 'xlsx', type: 'buffer' });

It's also worth remembering this: // eslint-disable-next-line @typescript-eslint/no-var-requires const XLSX = require('xlsx'); — dzhukov, May 15 '23 at 12:02

score 2 · Answer 3 · edited Jan 25 '23 at 14:42

Very traditional approach but working, please see complete code below:

const worksheet: XLSX.WorkSheet = XLSX.utils.json_to_sheet(
  this.releaseDateWiseCountList
);

worksheet.A1.v = "Pick Release Date";
worksheet.B1.v = "Task Type";
worksheet.C1.v = "First Shift";
worksheet.D1.v = "Second Shift";
worksheet.E1.v = "Total";
worksheet.F1.v = "Grand Total";
worksheet.G1.v = "Pick %";

const workbook: XLSX.WorkBook = {
  Sheets: { 'data': worksheet }, SheetNames: ['data']
};

const excelBuffer: any = XLSX.write(
  workbook, { bookType: 'xlsx', type: 'array' }
);

const data: Blob = new Blob([buffer], {type: EXCEL_TYPE});  

FileSaver.saveAs(data, 'Result_export_' + new  Date().getTime() + EXCEL_EXTENSION);

while using header option with XLSX.utils.json_to_sheet , headers not overriding

3 Answers3