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

How to validate domain in angular or node javascript

 How to validate domain in angular or node javascript 

___________________________________in Angular___________________________________
first you need to install plugin using this command
npm install is-valid-domain

how to use or integrate plugin in component just need to follow this syntex and then use varible "isValidDomain" method that have serveral parameter

import * as isValidDomain from 'is-valid-domain';

and how to use validation
this.isvalidDomainName()

the defination of validator methord

  isvalidDomainName() {
    return isValidDomain(this.domainForm.get('DomainName').value, { subdomain: true })
  }

_____________________________________in node__________________________________________

const isValidDomain = require('is-valid-domain')
 
isValidDomain('example.com') // true
isValidDomain('foo.example.com') // true
isValidDomain('bar.foo.example.com') // true
isValidDomain('exa-mple.co.uk') // true
isValidDomain('xn--80ak6aa92e.com') // true
isValidDomain('_dnslink.ipfs.io') // true
isValidDomain('exa_mple.com') // false
isValidDomain('-example.co.uk') // false
isValidDomain('example') // false
isValidDomain('ex*mple.com') // false
isValidDomain('*.example.com') // false
isValidDomain('*.com') // false
isValidDomain(3434) // false
 
isValidDomain('foo.example.com', {subdomain: true}) // true
isValidDomain('foo.example.com', {subdomain: false}) // false
isValidDomain('*.example.com', {wildcard: false}) // false
isValidDomain('*.example.com', {wildcard: true}) // true
isValidDomain('*.example.com', {subdomain: false, wildcard: true})

For more information just go to its official site
https://www.npmjs.com/package/is-valid-domain





Featured Post

how to find n number of nodes have child or cildren

 how to find n number of nodes have child or cildren for that we use recursive function  const   data = {     'id' : '0' ,...

Popular Posts