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

Asp.net And HTML Css And Web Development: How to convert Dot net sql date / Date string to L...

Asp.net And HTML Css And Web Development: How to convert Dot net sql date / Date string to L... : How to convert Dot net sql date / Date str...

Popular Posts