CODEWITHSUNDEEP
javascriptangulartypescriptsheetjsqjsonobject
Raveen
Raveen

Reputation: 13

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

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

Upvotes: 1

Views: 645

Answers (1)

arz
arz

Reputation: 1

try this one:

var ws = xlsx.utils.aoa_to_sheet([])

xlsx.utils.sheet_add_json(ws, [["TITLE HERE"]], {skipHeader: true})

xlsx.utils.sheet_add_json(ws, [{a: 1, b: 1}, {a: 2, b: 2}], {origin: 2})

const workbook = xlsx.utils.book_new()

xlsx.utils.book_append_sheet(workbook, ws, "LAPORAN PEMBELIAN")

let fileName = "purchase-invoice.xlsx"

xlsx.writeFile(workbook, fileName)

Upvotes: 0

Related Questions