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