How Generate excel of any datatable with dynamic column with custom code in angular

 How Generate excel of any datatable with dynamic column with custom code in angular



Suppose you have json like and you need to excel file of this json use 

[
    {
      "ID": 17,
      "DomainName": "xyz.com",
      "Mode": 0,
      "UserID": "646d29b6-e309-4566-983f-da9b6c5b669b",
      "UserName": "abc abc",
      "CreateDate": "26/03/2021 05:18:06",
      "commonResponse": {
        "Message": null,
        "IsSuccess": false
      }
    },
    {
      "ID": 1,
      "DomainName": "abc.com",
      "Mode": 0,
      "UserID": "646d29b6-e309-4566-983f-da9b6c5b669b",
      "UserName": "abc abc",
      "CreateDate": "25/03/2021 05:27:52",
      "commonResponse": {
        "Message": null,
        "IsSuccess": false
      }
    },
    {
      "ID": 11,
      "DomainName": "Test.com",
      "Mode": 0,
      "UserID": "646d29b6-e309-4566-983f-da9b6c5b669b",
      "UserName": "abc abc",
      "CreateDate": "23/03/2021 06:08:31",
      "commonResponse": {
        "Message": null,
        "IsSuccess": false
      }
    },
    {
      "ID": 9,
      "DomainName": "MNO.com",
      "Mode": 0,
      "UserID": "646d29b6-e309-4566-983f-da9b6c5b669b",
      "UserName": "abc abc",
      "CreateDate": "22/03/2021 12:30:12",
      "commonResponse": {
        "Message": null,
        "IsSuccess": false
      }
    }
  ]

service code of json for generate excel and download 

import { Injectable } from '@angular/core';
import * as Excel from 'exceljs/dist/exceljs';
import * as ExcelJS from 'exceljs/dist/exceljs';
import * as FileSaver from 'file-saver';
import * as logoFile from './tritexlogo.js';
import * as lodash from 'lodash';
import * as moment from 'moment';
import { HostService } from '../host/host.service.js';
import { UtilityProvider } from '../../utility/utility.js';
import { BrowserStorageService } from '../../utility/browser-storage.service.js';
const EXCEL_TYPE = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8';
const EXCEL_EXTENSION = '.xlsx';
declare const ExcelJSany;
@Injectable({
  providedIn: 'root'
})
export class ListExcelService {
  workbookExcelJS.Workbook;
  worksheetany;
  row = 10;
  row1 = this.row + 1;
  constructor(private browserStorageServiceBrowserStorageService) {
  }

  public generateExcel(paramDataanyName): Promise<any> {
    //
    return new Promise((resolvereject=> {
      // Create workbook and worksheet
      this.workbook = new Excel.Workbook();

      // Set Workbook Properties
      this.workbook.creator = 'Tritex Solutions';
      this.workbook.lastModifiedBy = 'Tritex Solutions';
      this.workbook.created = new Date();
      this.workbook.modified = new Date();
      this.workbook.lastPrinted = new Date();
      this.generateRows(paramDataName);
      // Generate Excel File
      this.workbook.xlsx.writeBuffer().then((data=> {
        const blob = new Blob([data], { type: EXCEL_TYPE });
        // Given name
        FileSaver.saveAs(blob, (Name ? Name : 'temporary') + EXCEL_EXTENSION);

      });
      return resolve(true);
    });
  }

  generateRows(paramDataName) {

    this.worksheet = this.workbook.addWorksheet(Name,
      { views: [{ showGridLines: false }] });
    const commonInfo = [
      ['Date'new Date()],
    ];

    if (commonInfo) {
      commonInfo.forEach((eleCommonInfoindex=> {
        const rowIndex = index + 2;
        // CommonInfo Header
        this.worksheet.mergeCells('C' + rowIndex + ':D' + rowIndex);
        this.worksheet.getCell('C' + rowIndex).value = eleCommonInfo[0as string;
        // CommonInfo Content
        this.worksheet.mergeCells('E' + rowIndex + ':F' + rowIndex);
        this.worksheet.getCell('E' + rowIndex).value = eleCommonInfo[1as string;
        this.worksheet.getRow(rowIndex).font = { bold: true };

        const commonInfoBorder = ['C' + rowIndex'E' + rowIndex];
        commonInfoBorder.forEach(eleheader2 => {
          this.worksheet.getCell(eleheader2).border = {
            top: { style: 'thin' },
            left: { style: 'thin' },
            bottom: { style: 'thin' },
            right: { style: 'thin' }
          };

          this.worksheet.getCell(eleheader2).fill = {
            type: 'pattern',
            pattern: 'solid',
            fgColor: {
              argb: 'd8cfcf'
            }
          };
        });
      });
    }

    // Logo

    this.row = 7;
    this.row1 = this.row + 1;
   if (Name === "Domain") {
      this.bindlogo('G2''H6');
      this.getDomain(paramData);
    } else {
      this.bindlogo('P2''R6');
      this.getContract(paramData);
    }
  }


  bindlogo(xy) {
    const logoBase64 = this.browserStorageService.getLocalStorageItem('logoBase64');
    const logo = this.workbook.addImage({
      base64: logoBase64,
      extension: 'png',
    });
    this.worksheet.addImage(logox + ':' + y);
  }

  getCellNo(alphabetstotalitem): any {
    if (totalitem <= 26) {
      const item = ((90) - (26 - totalitem));
      for (let i = 65i <= itemi++) {
        alphabets.push(String.fromCharCode(i));
      }
    }
    if (totalitem > 26) {
      for (let i = 65i <= 90i++) {
        alphabets.push(String.fromCharCode(i));
      }
      for (let j = 0j < totalitem - 26j++) {
        for (let i = 65i <= 90i++) {
          alphabets.push(alphabets[j] + String.fromCharCode(i));
          if (alphabets.length === totalitem) {
            return alphabets;
          }
        }
      }
    }
    return alphabets;
  }


  getDomain(paramDataany) {
    const clmIndex = lodash.range(1, (5 * 2));
    let Cell = [];
    Cell = this.getCellNo(CellclmIndex.length);
    const startIndex = 2;
    const celllength = 2;
    const HeaderColumn = ['Domain Name''User Name''Create Date'];
    const BodyColumn = ['DomainName''UserName''CreateDate'];
    this.bindRow(nullCellHeaderColumnstartIndexthis.rowcelllengthtrue);
    let header1Border = this.bindFormat(CellstartIndexcelllengththis.row3);
    this.BindHeaderFormat(header1Border);
    paramData.forEach(el => {
      this.row++;
      this.row1 = this.row + 1;
      el['CreateDate'] = el.CreateDate ? this.getformateDate(el.CreateDate'DD/MM/YYYY HH:mm:ss') : el.CreateDate;
      this.bindRow(elCellBodyColumnstartIndexthis.rowcelllength);
      let headerBorder = this.bindFormat(CellstartIndexcelllengththis.row3);
      this.BindBodyFormat(headerBorderel);
    });
  }

  getformateDate(dateformat) {
    if (date instanceof moment) {
      const _dateany = date;
      date = moment(moment.utc(_date._iformat).toDate()).format(format);
    } else {
      date = moment(moment.utc(dateformat).toDate()).format(format);
    }
    return date;
  }

 

 

  BindHeaderFormat(headerFormatany[]) {
    headerFormat.forEach(eleheader2 => {
      this.worksheet.getCell(eleheader2).border = {
        top: { style: 'thin' },
        left: { style: 'thin' },
        bottom: { style: 'thin' },
        right: { style: 'thin' }
      };

      this.worksheet.getCell(eleheader2).fill = {
        type: 'pattern',
        pattern: 'solid',
        fgColor: {
          argb: 'd8cfcf'
        }
      };
    });
  }

  bindFormat(CellstartIndexcelllengthrowNoOfItemsisChild = false): any[] {
    let items = [];
    for (let i = 0i < NoOfItemsi++) {
      if (i === 0) {
        if (!isChild) {
          items.push(Cell[startIndex] + row);
        }
      } else {
        items.push(Cell[startIndex + (celllength * i)] + row);
      }
    }
    return items;
  }

  BindBodyFormat(BOdyFormatany[], elementanyisDealOverView = false) {
    BOdyFormat.forEach((eleheader2k=> {
      this.worksheet.getCell(eleheader2).border = {
        top: { style: 'thin' },
        left: { style: 'thin' },
        bottom: { style: 'thin' },
        right: { style: 'thin' }
      };
      if (!isDealOverView) {
        this.worksheet.getCell(eleheader2).fill = {
          type: 'pattern',
          pattern: 'solid',
          fgColor: {
            argb: (k === 0) ? 'CCFFE5' : this.getStatus(elementk) ? 'ffffff' : 'CCFFE5'  //'d8cfcf'
          }
        };
      }
    });
  }

  bindHeader(DataCellstartIndexrowanycelllength): any[] {
    let header1Border = [];
    Data.Headings.forEach((elementi=> {
      if (i == 0) {
        this.worksheet.mergeCells(Cell[startIndex] + this.row + ':' + Cell[startIndex + celllength - 1] + this.row);
        this.worksheet.getCell(Cell[startIndex] + this.row).value = element.Name;
        header1Border.push(Cell[startIndex] + this.row);
      } else {
        this.worksheet.mergeCells(Cell[startIndex + celllength * i] + this.row + ':' + Cell[startIndex + (celllength * (i + 1) - 1)] + this.row);
        this.worksheet.getCell(Cell[startIndex * (i + 1)] + this.row).value = element.Name;
        header1Border.push(Cell[startIndex + (celllength * i)] + this.row);
      }
    });
    return header1Border;
  }
  bindRow(elanyCellany[], Columnsstring[], startIndexnumberrowanycelllengthnumberIsHeader = false) {
    if (Columns && Columns.length > 0) {
      Columns.forEach((elementi=> {
        if (i === 0) {
          this.worksheet.mergeCells(Cell[startIndex] + row + ':' + Cell[startIndex + celllength - 1] + row);
          this.worksheet.getCell(Cell[startIndex] + row).value = IsHeader ? element : el[element];
        } else {
          this.worksheet.mergeCells(Cell[startIndex + startIndex * i] + row + ':' + Cell[startIndex + (celllength * i + 1)] + row);
          this.worksheet.getCell(Cell[startIndex * (i + 1)] + row).value = IsHeader ? element : el[element];
        }
      });

    }
  }
}


and then you need to call this generate excel method on any button the calling method syntex is like

set path of serverice
import { ListExcelService } from '../../../../../services/export-file/list_excel.service';


register service in construction
private listexcelServiceListExcelService

calling method on button click event
  generateListExcel(datasourceany) {
    if (datasource.filteredData) {
      this.userService.saveUserActivityLog(Constants.GEN_SETUP.NAMEConstants.GEN_SETUP.SUBMODULE.DOMAIN,
        Constants.USER_ACTIVITIES.Domain.EXPORT_EXCELConstants.NO_COMMENTS'');
      this.listexcelService.generateExcel(lodash.cloneDeep(datasource.filteredData), 'Domain');
    }
  }

No comments:

Post a comment

Thank You For Your Great Contribution

Featured Post

How to Create Lowercase convert autometically in angular using directive (for textbox , text control)

 How to Create Lowercase convert autometically in angular using directive  1).Directive code  // Usage example: <input lowercasedirective...

Popular Posts