I am converting JSON data to an excel file format. So far I have been able to create a file with the data. I am looking forward to add a custom message to be displayed (image below) in the first row and thereafter data should be displayed in the file with column headers.

I have taken reference from this stackblitz link

How can I achieve this ?

New Issue

Missing headers firstName, lastName, email, phone

I am converting JSON data to an excel file format. So far I have been able to create a file with the data. I am looking forward to add a custom message to be displayed (image below) in the first row and thereafter data should be displayed in the file with column headers.

I have taken reference from this stackblitz link

How can I achieve this ?

New Issue

Missing headers firstName, lastName, email, phone

• javascript
• json
• angular
• typescript
• angular-material

• what library are you using to create the file? – Maher Fattouh Commented Apr 13, 2020 at 12:19
• from what I can see you need to 1- add a row 2- merge its cells 3- set the value of the first cell to your desired message/content 4- set its format to fit your need. – Maher Fattouh Commented Apr 13, 2020 at 12:21
• @MaherFattouh, I guess it's XLSX library. I have tried searching for probable solution to it though, couldn't find one. – Mridul Commented Apr 13, 2020 at 12:30
• Sample example: medium./codeptivesolutions/… – shasi kanth Commented Sep 25, 2023 at 8:23

2 Answers 2

I assume when you say JSON, you mean a Javascript object that have been parsed from a JSON file. in my example it's myObject.

1. We create a worksheet using XLSX.utils.json_to_sheet(myObject);
2. We add a row to the start of the worksheet using: XLSX.utils.sheet_add_aoa(myWorkSheet, [["Your Mesage Goes Here"]], { origin: 0 }); this will insert an aoa (array of arrays) to a new row at the position defined by origin.
   • { origin: 0 } means first row
   • { origin: 1 } means 2nd row
   • { origin: -1 } means last row

in our case we add just one cell (A1) with the content: "Your Mesage Goes Here"

3. we merge the cells in range A1:D1 (4 cells) using myWorkSheet['!merges'] = [{ s: 'A1', e: 'D1' }];

   The rest is self explanatory I think

Here's a working example

myObject = [
  { name: "Moran", role: "back" },
  { name: "Alain", role: "front" },
  { name: "Tony", role: "back" },
  { name: "Mike", role: "back" },
  { name: "Abo", role: "back" },
  { name: "Toni", role: "back" }
];

function exportWS() {
  var myFile = "myFile.xlsx";
  var myWorkSheet = XLSX.utils.json_to_sheet(myObject);
  var myWorkBook = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(myWorkBook, myWorkSheet, "myWorkSheet");
  XLSX.writeFile(myWorkBook, myFile);
}
function exportWSPlus() {
  var myFile = "myFilePlus.xlsx";
  var myWorkSheet = XLSX.utils.json_to_sheet(myObject);
  XLSX.utils.sheet_add_aoa(myWorkSheet, [["Your Mesage Goes Here"]], { origin: 0 });
  var merges = myWorkSheet['!merges'] = [{ s: 'A1', e: 'D1' }];
  var myWorkBook = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(myWorkBook, myWorkSheet, "myWorkSheet");
  XLSX.writeFile(myWorkBook, myFile);
}

<script src="https://cdnjs.cloudflare./ajax/libs/xlsx/0.14.3/xlsx.full.min.js"></script>
<button type="button" onclick="exportWS()">Export Worksheet</button>
<button type="button" onclick="exportWSPlus()">Export Worksheet+</button>

feel free to ask any questions you may have.

I researched about this a lot and finally I could e up with a solution to this.

public exportAsExcelFile(json: Array<object>, excelFileName: string): void {
var worksheet: XLSX.WorkSheet = XLSX.utils.aoa_to_sheet([
  [`${excelFileName}`]]); // message to display
worksheet['!merges'] = [{ s: { r: 0, c: 0 }, e: { r: 0, c: 3 } }]; //for merging columns. s : start, e: end, c: column, r: row
XLSX.utils.sheet_add_json(worksheet, json, { origin: "A2" }); //origin for json data
const workbook: XLSX.WorkBook = { Sheets: { 'data': worksheet }, SheetNames: ['data'] };
const excelBuffer: any = XLSX.write(workbook, { bookType: 'xlsx', type: 'array' });
var range = XLSX.utils.decode_range(worksheet['!ref']);
for (var C = range.s.r; C <= range.e.r; ++C) {
  var address = XLSX.utils.encode_col(C) + "1"; 
  if (!worksheet[address]) continue;
  worksheet[address].v = worksheet[address].v.charAt(0).toUpperCase() + worksheet[address].v.substr(1).toLowerCase();
}
}