Donate. I desperately need donations to survive due to my health

Get paid by answering surveys Click here

Click here to donate

Remote/Work from Home jobs

How to create excel for large amount record set using exceljs in angular 6

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