How to add report title before header row in SheetJs in angular

Question

I'm trying to export JSON to excel by using SheetJS, but I'm not able to add the title to the table. Now, I want to add a title in the middle of a row before the header row.

In export service :

import { Injectable } from '@angular/core';

import * as FileSaver from 'file-saver';

import * as XLSX from 'xlsx';

@Injectable({

  providedIn: 'root'

})

export class ExportService {

  mimeTypes: any = {

    xlsx: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8',

    xls: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8',

    txt: 'text/plain;charset=utf-16',

    csv: 'text/csv;charset=utf-16',

    xml: 'application/xml',

    zip: 'application/zip',

    jpg: 'image/jpeg',

    jpeg: 'image/jpeg',

    png: 'image/png',

    mp4: 'video/mp4',

    avi: 'video/avi',

    bmp: 'image/bmp',

    doc: 'application/msword',

    docx: 'application/vnd.openxmlformats-officedocument.wordprocessingml.document',

    ppt: 'application/vnd.ms-powerpoint',

    pps: 'application/vnd.ms-powerpoint',

    pptx: 'application/vnd.openxmlformats-officedocument.presentationml.presentation',

    pdf: 'application/pdf',

    rar: 'application/vnd.rar',

    webp: 'image/webp',

    '7z': 'application/x-7z-compressed',

    gif: 'image/gif',

    gz: 'application/gzip',

    json: 'application/json'

  };

public exportExcel(jsonData: any[], fileName: string): void {

    if (jsonData.length > 0) {

      const header = Object.keys(jsonData[0]); // columns name



      const wscols = [];

      for (const head of header) {

        wscols.push({ wch: head.length + 5 });

      }



      const ws: XLSX.WorkSheet = XLSX.utils.json_to_sheet(jsonData);

      ws['!cols'] = wscols;



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

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

      this.saveFile(excelBuffer, fileName + '.xlsx', 'xlsx');

    }

  }

I tried the below options and went through some stack overflow answers as well but none of that worked for me :

public exportExcel(jsonData: any[], fileName: string,reportName: any, fromDate: any,toDate:any): void {
    if (jsonData.length > 0) {
      const header = Object.keys(jsonData[0]); // columns name               const arr = Object.values(jsonData);   
      const wscols = [];
      for (const head of header) {
        wscols.push({ wch: head.length + 5 });
      }

      const ws: XLSX.WorkSheet = XLSX.utils.json_to_sheet(jsonData);
      ws['!cols'] = wscols;
      //console.log(ws);

 

      const merge = [
        { s: { r: 0, c: 0 }, e: { r: 0, c: 4 } },
        { s: { r: 1, c: 0 }, e: { r: 1, c: 4 } },
      ];
      ws["!merges"] = merge;
     // XLSX.utils.sheet_add_aoa(ws, header , {origin: -1});
      const wb: XLSX.WorkBook = { Sheets: { data: ws }, SheetNames: ['data'] };
      XLSX.utils.sheet_add_json(ws, arr, { origin: 'A5', skipHeader: true });
      const excelBuffer: any = XLSX.write(wb, { bookType: 'xlsx', type: 'array' });
      this.saveFile(excelBuffer, fileName + '.xlsx', 'xlsx');
    }
  }