How Generate excel of any datatable with dynamic column with custom code in angular
[
{
"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 ExcelJS: any;
@Injectable({
providedIn: 'root'
})
export class ListExcelService {
workbook: ExcelJS.Workbook;
worksheet: any;
row = 10;
row1 = this.row + 1;
constructor(private browserStorageService: BrowserStorageService) {
}
public generateExcel(paramData: any, Name): Promise<any> {
//
return new Promise((resolve, reject) => {
// 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, Name);
// 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(paramData, Name) {
this.worksheet = this.workbook.addWorksheet(Name,
{ views: [{ showGridLines: false }] });
const commonInfo = [
['Date', new Date()],
];
if (commonInfo) {
commonInfo.forEach((eleCommonInfo, index) => {
const rowIndex = index + 2;
// CommonInfo Header
this.worksheet.mergeCells('C' + rowIndex + ':D' + rowIndex);
this.worksheet.getCell('C' + rowIndex).value = eleCommonInfo[0] as string;
// CommonInfo Content
this.worksheet.mergeCells('E' + rowIndex + ':F' + rowIndex);
this.worksheet.getCell('E' + rowIndex).value = eleCommonInfo[1] as 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(x, y) {
const logoBase64 = this.browserStorageService.getLocalStorageItem('logoBase64');
const logo = this.workbook.addImage({
base64: logoBase64,
extension: 'png',
});
this.worksheet.addImage(logo, x + ':' + y);
}
getCellNo(alphabets, totalitem): any {
if (totalitem <= 26) {
const item = ((90) - (26 - totalitem));
for (let i = 65; i <= item; i++) {
alphabets.push(String.fromCharCode(i));
}
}
if (totalitem > 26) {
for (let i = 65; i <= 90; i++) {
alphabets.push(String.fromCharCode(i));
}
for (let j = 0; j < totalitem - 26; j++) {
for (let i = 65; i <= 90; i++) {
alphabets.push(alphabets[j] + String.fromCharCode(i));
if (alphabets.length === totalitem) {
return alphabets;
}
}
}
}
return alphabets;
}
getDomain(paramData: any) {
const clmIndex = lodash.range(1, (5 * 2));
let Cell = [];
Cell = this.getCellNo(Cell, clmIndex.length);
const startIndex = 2;
const celllength = 2;
const HeaderColumn = ['Domain Name', 'User Name', 'Create Date'];
const BodyColumn = ['DomainName', 'UserName', 'CreateDate'];
this.bindRow(null, Cell, HeaderColumn, startIndex, this.row, celllength, true);
let header1Border = this.bindFormat(Cell, startIndex, celllength, this.row, 3);
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(el, Cell, BodyColumn, startIndex, this.row, celllength);
let headerBorder = this.bindFormat(Cell, startIndex, celllength, this.row, 3);
this.BindBodyFormat(headerBorder, el);
});
}
getformateDate(date, format) {
if (date instanceof moment) {
const _date: any = date;
date = moment(moment.utc(_date._i, format).toDate()).format(format);
} else {
date = moment(moment.utc(date, format).toDate()).format(format);
}
return date;
}
BindHeaderFormat(headerFormat: any[]) {
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(Cell, startIndex, celllength, row, NoOfItems, isChild = false): any[] {
let items = [];
for (let i = 0; i < NoOfItems; i++) {
if (i === 0) {
if (!isChild) {
items.push(Cell[startIndex] + row);
}
} else {
items.push(Cell[startIndex + (celllength * i)] + row);
}
}
return items;
}
BindBodyFormat(BOdyFormat: any[], element: any, isDealOverView = false) {
BOdyFormat.forEach((eleheader2, k) => {
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(element, k) ? 'ffffff' : 'CCFFE5' //'d8cfcf'
}
};
}
});
}
bindHeader(Data, Cell, startIndex, row: any, celllength): any[] {
let header1Border = [];
Data.Headings.forEach((element, i) => {
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(el: any, Cell: any[], Columns: string[], startIndex: number, row: any, celllength: number, IsHeader = false) {
if (Columns && Columns.length > 0) {
Columns.forEach((element, i) => {
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 listexcelService: ListExcelService
calling method on button click event
generateListExcel(datasource: any) {
if (datasource.filteredData) {
this.userService.saveUserActivityLog(Constants.GEN_SETUP.NAME, Constants.GEN_SETUP.SUBMODULE.DOMAIN,
Constants.USER_ACTIVITIES.Domain.EXPORT_EXCEL, Constants.NO_COMMENTS, '');
this.listexcelService.generateExcel(lodash.cloneDeep(datasource.filteredData), 'Domain');
}
}
No comments:
Post a Comment
Thank You For Your Great Contribution