I am using exceljs for creating excel file in angular 6, it works fine with 100k records having around 30 columns in each row, but browser crashes if there are more than 100k records. This is what I am doing in client side
generateExcel(headerColumns, Model, sheetno, isInProcess) {
this.InitializeWorkbook();
const fileName = this.GenerateFileName();
const header = headerColumns;
const data = this.ConvertDataForExcel(Model, headerColumns);
this.AddHeaderAndColumns(sheetno, header);
this.AddDataToWorkbook(data, headerColumns);
this.workbook.xlsx.writeBuffer().then((data1) => {
const blob = new Blob([data1], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
fs.saveAs(blob, fileName);
this.InitializeWorkbook();
});
}
These are the methods for creating worksheets
Add header and columns
AddHeaderAndColumns(sheetno, header) {
this.Sheetno = sheetno;
this.worksheet = this.workbook.addWorksheet('Sheet' + sheetno);
const headerRow = this.worksheet.addRow(header);
headerRow.eachCell((cell, number) => {
cell.fill = {
type: 'pattern',
pattern: 'solid',
fgColor: { argb: 'ffffff' }
};
cell.border = { top: { style: 'thin' }, left: { style: 'thin' }, bottom: { style: 'thin' }, right: { style: 'thin' } };
});
}
adding data
AddDataToWorkbook(data, headerColumns) {
data.forEach(d => {
const row = this.worksheet.addRow(d).commit();
}
);
for (let k = 1; k <= headerColumns.length; k++) {
this.worksheet.getColumn(k).width = 20;
}
}
other methods
InitializeWorkbook() {
const Excels = require('exceljs');
this.workbook = new Excels.Workbook();
}
GenerateFileName() {
const displayDate = new Date().toLocaleDateString();
const fileName = 'ExportExcel' + displayDate + '.xlsx';
return fileName;
}
ConvertDataForExcel(data, headerColumns: Array<string>) {
const test: any = [];
if (data) {
const myarray = new Array(data.length);
for (let i = 0; i < data.length; i++) {
myarray[i] = new Array(headerColumns.length);
for (let j = 0; j < headerColumns.length; j++) {
myarray[i][j] = data[i][headerColumns[j]];
}
}
return myarray;
}
}
Comments
Post a Comment