How To Generate Excel in angular 6

How To Generate Excel in angular 6



_________________________________________________________________________________
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';
const EXCEL_TYPE = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8';
const EXCEL_EXTENSION = '.xlsx';

declare const ExcelJS: any;

@Injectable({
  providedIn: 'root'
})
export class ExcelService {
  workbook: ExcelJS.Workbook;
  worksheet: any;

  constructor() {
  }

  public generateExcel(paramData: any, _ReferenceNo) {
    // 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(paramData, true); // For Home
    this.generateRows(paramData, false); // For Visitor


    // Generate Excel File
    this.workbook.xlsx.writeBuffer().then((data) => {
      const blob = new Blob([data], { type: EXCEL_TYPE });
      // Given name
      FileSaver.saveAs(blob, _ReferenceNo + EXCEL_EXTENSION);
    });
  }

  generateRows(paramData, isHome) {
    const tapValue = paramData.newTAPInstructionForm;
    const taxTreatmentList = paramData.taxTreatmentList;
    const chargingIntervalEnumList = paramData.chargingIntervalEnumList;
    const perEnumList = paramData.perEnumList;

    const _entity = isHome ? tapValue.HomeEntity : tapValue.VisitorEntity;
    const _worksheetText = isHome ? ' HPMN' : ' VPMN';
    const clmIndex = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25];

    _entity.forEach(element => {
      // Add a Worksheet
      this.worksheet = this.workbook.addWorksheet(element.OperatorCode + _worksheetText,
        { views: [{ showGridLines: false }] });
      // Logo
      const logo = this.workbook.addImage({
        base64: logoFile.logoBase64,
        extension: 'png',
      });
      this.worksheet.addImage(logo, 'H2:J6');
      const CurrencyID = element.OperatorCurrencyID;
      // Message information
      const msgTextRow = this.worksheet.addRow(['*TAP Instruction sheet created and approved on the Tritex IOT Management platform']);
      msgTextRow.font = { color: { argb: '949494' }, };

      let commonInfo = [];
      if (isHome) {
        commonInfo = [
          ['Contract Effective Date', element.EffectiveDate],
          ['Party A', tapValue.TradingEntity],
          ['Party A Opco', element.OperatorName],
          ['Party A Opco TCID', element.OperatorCode],
          ['Party B', tapValue.CounterParty],
        ];
      } else {
        commonInfo = [
          ['Contract Effective Date', element.EffectiveDate],
          ['Party A', tapValue.TradingEntity],
          ['Party B', tapValue.CounterParty],
          ['Party B Opco', element.OperatorName],
          ['Party A Opco TCID', element.OperatorCode],
        ];
      }

      if (commonInfo) {
        commonInfo.forEach((eleCommonInfo, index) => {
          const rowIndex = index + 2;

          // CommonInfo Header
          this.worksheet.mergeCells('A' + rowIndex + ':C' + rowIndex);
          this.worksheet.getCell('A' + rowIndex).value = eleCommonInfo[0] as string;
          // CommonInfo Content
          this.worksheet.mergeCells('D' + rowIndex + ':F' + rowIndex);
          this.worksheet.getCell('D' + rowIndex).value = eleCommonInfo[1] as string;
          this.worksheet.getRow(rowIndex).font = { bold: true };

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

      // Blank Row
      this.worksheet.addRow([]);

      // Header 1
      this.worksheet.mergeCells('F8:M8');
      this.worksheet.getCell('F8').value = 'Voice';
      this.worksheet.mergeCells('N8:Q8');
      this.worksheet.getCell('N8').value = 'SMS';
      this.worksheet.mergeCells('R8:T8');
      this.worksheet.getCell('R8').value = 'Data';
      this.worksheet.mergeCells('U8:W8');
      this.worksheet.getCell('U8').value = 'VoLTE';

      this.worksheet.getRow(8).font = { bold: true };
      const header1Border = ['F8', 'N8', 'R8', 'U8'];
      header1Border.forEach(eleheader2 => {
        this.worksheet.getCell(eleheader2).border = {
          top: { style: 'thin' },
          left: { style: 'thin' },
          bottom: { style: 'thin' },
          right: { style: 'thin' }
        };
      });

      // Header 2
      this.worksheet.mergeCells('F9:G9');
      this.worksheet.getCell('F9').value = 'MOC Local';
      this.worksheet.mergeCells('H9:I9');
      this.worksheet.getCell('H9').value = 'MOC Home';
      this.worksheet.mergeCells('J9:K9');
      this.worksheet.getCell('J9').value = 'MOC RoW';
      this.worksheet.mergeCells('L9:M9');
      this.worksheet.getCell('L9').value = 'MTC';
      this.worksheet.mergeCells('N9:O9');
      this.worksheet.getCell('N9').value = 'SMS - MO';
      this.worksheet.mergeCells('P9:Q9');
      this.worksheet.getCell('P9').value = 'SMS - MT';
      this.worksheet.mergeCells('R9:T9');
      // this.worksheet.getCell('Q9').value = 'Data';
      this.worksheet.mergeCells('U9:W9');
      // this.worksheet.getCell('T9').value = 'VoLTE';
      this.worksheet.getRow(9).font = { bold: true };

      const header2Border = ['F9', 'H9', 'J9', 'L9', 'N9', 'P9', 'R9', 'U9'];
      header2Border.forEach(eleheader2 => {
        this.worksheet.getCell(eleheader2).border = {
          top: { style: 'thin' },
          left: { style: 'thin' },
          bottom: { style: 'thin' },
          right: { style: 'thin' }
        };
      });

      // Header 3
      const header3 = ['Party A', 'Party B', 'Start Date', 'End Date', 'Curruncy',
        'Rate per min', 'Interval', 'Rate per min', 'Interval', 'Rate per min', 'Interval', 'Rate per min', 'Interval'
        , 'Rate', 'Interval', 'Rate', 'Interval', 'Rate', 'per:', 'Interval', 'Rate', 'per:', 'Interval', 'Exclusions', 'Tax'];
      const header3Row = this.worksheet.addRow(header3);
      header3Row.font = { bold: true };
      clmIndex.forEach(eleClmIndex => {
        header3Row.getCell(eleClmIndex).border = {
          top: { style: 'thin' },
          left: { style: 'thin' },
          bottom: { style: 'double' },
          right: { style: 'thin' }
        };
      });

      element.EntityDetails[0].TapService.forEach((eleTab, periodIndex) => {

        // Period Header
        const titleRow = this.worksheet.addRow(['Period ' + (periodIndex + 1) as string]);
        titleRow.font = { family: 4, size: 16, underline: 'double', bold: true };

        element.EntityDetails.forEach((eleEntity, entityIndex) => {
          const eleTabDetails = eleEntity.TapService[periodIndex].TapServicePeriodWise;
          const _temp = [eleEntity.PartyATAPCode
            , eleEntity.PartyBTAPCode
            , (eleEntity.TapService[periodIndex].StartDate) ? eleEntity.TapService[periodIndex].StartDate as string : 'n/a'
            , (eleEntity.TapService[periodIndex].EndDate) ? eleEntity.TapService[periodIndex].EndDate as string : 'n/a'
            , (CurrencyID) ? CurrencyID as string : 'n/a'
            , (eleTabDetails[0].Rate) ? eleTabDetails[0].Rate as string : 'n/a'
            , chargingIntervalEnumList.filter(x => x.EnumID === eleTabDetails[0].IntervalEnumID).map(x => x.DisplayName).toString()
            , (eleTabDetails[1].Rate) ? eleTabDetails[1].Rate as string : 'n/a'
            , chargingIntervalEnumList.filter(x => x.EnumID === eleTabDetails[1].IntervalEnumID).map(x => x.DisplayName).toString()
            , (eleTabDetails[2].Rate) ? eleTabDetails[2].Rate as string : 'n/a'
            , chargingIntervalEnumList.filter(x => x.EnumID === eleTabDetails[2].IntervalEnumID).map(x => x.DisplayName).toString()
            , (eleTabDetails[3].Rate) ? eleTabDetails[3].Rate as string : 'n/a'
            , chargingIntervalEnumList.filter(x => x.EnumID === eleTabDetails[3].IntervalEnumID).map(x => x.DisplayName).toString()
            , (eleTabDetails[4].Rate) ? eleTabDetails[4].Rate as string : 'n/a'
            , chargingIntervalEnumList.filter(x => x.EnumID === eleTabDetails[4].IntervalEnumID).map(x => x.DisplayName).toString()
            , (eleTabDetails[5].Rate) ? eleTabDetails[5].Rate as string : 'n/a'
            , chargingIntervalEnumList.filter(x => x.EnumID === eleTabDetails[5].IntervalEnumID).map(x => x.DisplayName).toString()
            , (eleTabDetails[6].Rate) ? eleTabDetails[6].Rate as string : 'n/a'
            , perEnumList.filter(x => x.EnumID === eleTabDetails[6].PerEnumID).map(x => x.DisplayName).toString()
            , chargingIntervalEnumList.filter(x => x.EnumID === eleTabDetails[6].IntervalEnumID).map(x => x.DisplayName).toString()
            , (eleTabDetails[7].Rate) ? eleTabDetails[7].Rate as string : 'n/a'
            , perEnumList.filter(x => x.EnumID === eleTabDetails[7].PerEnumID).map(x => x.DisplayName).toString()
            , chargingIntervalEnumList.filter(x => x.EnumID === eleTabDetails[7].IntervalEnumID).map(x => x.DisplayName).toString()
            , (element.Exclusions) ? element.Exclusions : 'n/a'
            , taxTreatmentList.filter(x => x.EnumID === eleEntity.TaxTreatmentEnumID).map(x => x.DisplayName).toString()
          ];
          const rowEntity = this.worksheet.addRow(_temp);
          clmIndex.forEach(eleClmIndex => {
            rowEntity.getCell(eleClmIndex).border = {
              top: { style: 'thin' },
              left: { style: 'thin' },
              bottom: { style: 'thin' },
              right: { style: 'thin' }
            };
          });
        });
      });
    });
  }
}

No comments:

Post a comment

Thank You For Your Great Contribution

Featured Post

how to find duplicate item from array using multiple object keys in angular or javascript

 how to find duplicate item from array using multiple object keys There is a way to find based of multiple key find duplicate records in ang...

Popular Posts