Angular 5 export Excel in the customized format

Question

I am using Excel service provided by Angular. I would like to get the output like this as shown in the image. Excel import is required in this format.

My Component calls the service to get JSON data, then it calls the Excel service to export the output. How do I customize my function to get this output format?

JSON format:

[
    {
        "applicationName": "Application1", 
        "migration": "Rehost", 
        "hostname": "DemoVM5", 
        "ipAddress": "10.0.1.7", 
        "operatingSystem": "Microsoft(R) Windows(R) Server 2003, Standard Edition", 
        "migrationStatus": "Failed", 
        "error": null, 
        "runDetails": {
            "rehostCompletedCount": 0, 
            "rehostFailedCount": 2, 
            "refactorCompletedCount": 0, 
            "refactorFailedCount": 0, 
            "runId": 41, 
            "rehostCount": 2, 
            "refactorCount": 0, 
            "status": null, 
            "dateTime": null
        }
    }, 
    {
        "applicationName": "Application1", 
        "migration": "Rehost", 
        "hostname": "DemoVM2", 
        "ipAddress": "10.0.1.6", 
        "operatingSystem": "Microsoft(R) Windows(R) Server 2003, Standard Edition", 
        "migrationStatus": "Failed", 
        "error": null, 
        "runDetails": {
            "rehostCompletedCount": 0, 
            "rehostFailedCount": 2, 
            "refactorCompletedCount": 0, 
            "refactorFailedCount": 0, 
            "runId": 41, 
            "rehostCount": 2, 
            "refactorCount": 0, 
            "status": null, 
            "dateTime": null
        }
    }
]

Excel service code

import {
  Injectable
} from '@angular/core';
import * as FileSaver from 'file-saver';
import * as XLSX from 'xlsx';
import * as _ from 'underscore';

const EXCEL_TYPE = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8';
const EXCEL_EXTENSION = '.xlsx';

@Injectable()
export class ExcelService {
  public data: any;
  public sheetName: string = "Sheet1";
  public workbook: XLSX.WorkBook = {
    Sheets: {},
    SheetNames: [],
    Props: {}
  }
  public ws: any;
  public wbout: any;

  constructor() {}

  public exportAsExcelFile(json: any[], excelFileName: string): void {
    this.data = json;
    this.downloadExcel(excelFileName);
  }

  public transformData(data: any) {
    let dataNew: any = [];
    let keys_arr = [];
    _.each(data, function(json) {
      let key: any = json;
      let arr = _.filter(key, function(val, i) {
        let value: any = val;
        let index: any = i;
        keys_arr.push(index);
        if (value == 0) {
          return '0';
        } else {
          return value;
        }
      });
      dataNew.push(arr);
    });
    dataNew.unshift(_.uniq(keys_arr));
    return dataNew;
  }

  public sheet_from_array_of_arrays(data) {
    let ws = {};
    let endCell = {
      c: 10000000,
      r: 10000000
    };
    let startCell = {
      c: 0,
      r: 0
    };
    let range = {
      s: endCell,
      e: startCell
    };

    let wscols = [];

    for (let R = 0; R != data.length; ++R) {
      for (let C = 0; C != data[R].length; ++C) {
        wscols.push({
          wch: 20
        });
        if (range.s.r > R) range.s.r = R;
        if (range.s.c > C) range.s.c = C;
        if (range.e.r < R) range.e.r = R;
        if (range.e.c < C) range.e.c = C;
        let cell = {
          v: data[R][C],
          t: 's',
          s: {}
        };

        if (R === 0) {
          cell.s = {
            "font": {
              "bold": true,
              "sz": 13,
              "alignment": {
                "horizontal": "center",
                "vertical": "center"
              }
            }
          };
        }

        if (cell.v == null) continue;
        let cell_ref = XLSX.utils.encode_cell({
          c: C,
          r: R
        });
        if (typeof cell.v === 'number')
          cell.t = 'n';
        else if (typeof cell.v === 'boolean')
          cell.t = 'b';
        else
          cell.t = 's';
        ws[cell_ref] = cell;
      }
    }
    ws['!cols'] = wscols;

    if (range.s.c < 10000000)
      ws['!ref'] = XLSX.utils.encode_range(endCell, startCell);

    return ws;
  }

  public generateExcelFile(): any {
    this.ws = this.sheet_from_array_of_arrays(this.transformData(this.data));
    this.workbook.SheetNames = [];
    this.workbook.SheetNames.push(this.sheetName);
    this.workbook.Sheets[this.sheetName] = this.ws;
    this.wbout = XLSX.write(this.workbook, {
      bookType: 'xlsx',
      type: 'binary'
    });
    return this.wbout;
  }

  public createView(s: any): ArrayBuffer {
    let buf = new ArrayBuffer(s.length);
    let view = new Uint8Array(buf);
    for (let i = 0; i != s.length; ++i)
      view[i] = s.charCodeAt(i) & 0xFF;
    return buf;
  }

  public downloadExcel(fileName: string): void {
    this.sheetName = fileName + '_export_' + new Date().getTime();
    FileSaver.saveAs(new Blob([this.createView(this.generateExcelFile())], {
      type: "application/octet-stream"
    }), fileName + '_export_' + new Date().getTime() + EXCEL_EXTENSION);
  }
}

Answer 1

You can use exceljs. It is a pure open source package while XLSX package is not.

The customization of an excel sheet using XLSX is a pro feature.

sheetjs pro doc

Exceljs is the better option for xlsx generation

exceljs doc

Sample code :

  sheet1.getCell('A1').fill = {
    type: "pattern",
    pattern: "solid",
    fgColor: { argb: "ff1573f4" },
  };
  sheet1.getCell('A1').border = {
    top: { style: "thin" },
    left: { style: "thin" },
    bottom: { style: "thin" },
    right: { style: "thin" },
  };
  sheet1.getCell('A1').font = {
    name: "",
    family: 4,
    size: 11,
    color: { argb: "ffffffff" },
    underline: false,
    bold: true,
  };

Answer 2

For XLSX library, If you want to provide any styling in excel you need to use it with xlsx-style package.

XLSX-Style here you will find documentation of how to apply cell styling.

ExcelJS is also another popular and easy to use client side excel generation library. This library also provide more styling features then xlsx & xlsx-style. you can refer export to excel in Angular using ExcelJS article for your reference.