Posted 22 November 2020, 10:28 pm EST
Hello,
I want export the excel report with formatting of numeric columns data with styling such as foreground color change. I did this and its working but it takes long time to download the report. for example its taking more than 15 minutes for 20000 records. Is there any way to make it faster?
OR is there any way to download the excel report similar to the screen display where formatting and styling is already done?
the code snippet is as given below.
private _exportReport(grid: wjcGrid.FlexGrid, records, headers): wjcXlsx.Workbook {
var book = new wjcXlsx.Workbook(); var dateFormat = wjcXlsx.Workbook.toXlsxDateFormat('d'), stdNumWidth = 85, simpleTextStyle = new wjcXlsx.WorkbookStyle(), simpleCaptionStyle = new wjcXlsx.WorkbookStyle(), negativeCaptionStyle = new wjcXlsx.WorkbookStyle(), positiveCaptionStyle = new wjcXlsx.WorkbookStyle(), valueStyle = new wjcXlsx.WorkbookStyle(), highlightedValueStyle = new wjcXlsx.WorkbookStyle(), tableHeaderStyle = new wjcXlsx.WorkbookStyle(), tableValueStyle = new wjcXlsx.WorkbookStyle(), tableIntegerStyle = new wjcXlsx.WorkbookStyle(); simpleCaptionStyle.hAlign = wjcXlsx.HAlign.Right; simpleTextStyle.hAlign = wjcXlsx.HAlign.Left; negativeCaptionStyle.basedOn = simpleCaptionStyle; negativeCaptionStyle.font = new wjcXlsx.WorkbookFont(); //totalCaptionStyle.font.bold = true; negativeCaptionStyle.hAlign = wjcXlsx.HAlign.Right; negativeCaptionStyle.font.color = '#FF0000'; positiveCaptionStyle.basedOn = simpleCaptionStyle; positiveCaptionStyle.font = new wjcXlsx.WorkbookFont(); positiveCaptionStyle.hAlign = wjcXlsx.HAlign.Right; positiveCaptionStyle.font.color = '#000000'; valueStyle.font = new wjcXlsx.WorkbookFont(); valueStyle.font.family = 'Arial'; highlightedValueStyle.basedOn = valueStyle; highlightedValueStyle.fill = new wjcXlsx.WorkbookFill(); highlightedValueStyle.fill.color = '#e1e1e1'; tableHeaderStyle.font = new wjcXlsx.WorkbookFont(); tableHeaderStyle.font.bold = true; tableHeaderStyle.fill = new wjcXlsx.WorkbookFill(); tableHeaderStyle.fill.color = '#00adf2'; tableValueStyle.fill = new wjcXlsx.WorkbookFill(); tableValueStyle.fill.color = '#f4b19b'; tableIntegerStyle.basedOn = tableValueStyle; tableIntegerStyle.format = wjcXlsx.Workbook.toXlsxNumberFormat('00'); var sheet = new wjcXlsx.WorkSheet(), rows = sheet.rows; book.sheets.push(sheet); rows[0] = new wjcXlsx.WorkbookRow(); rows[0].cells[8] = new wjcXlsx.WorkbookCell(); rows[0].cells[8].value = 'FX Exposure Report'; rows[0].cells[8].link ='I1:J1'; rows[0].height = 45; rows[0].cells[8].colSpan = 5; rows[0].cells[8].style = new wjcXlsx.WorkbookStyle(); rows[0].cells[8].style.font = new wjcXlsx.WorkbookFont(); rows[0].cells[8].style.font.size = 32; rows[0].cells[8].style.font.bold = true; rows[1] = new wjcXlsx.WorkbookRow(); rows[1].style = new wjcXlsx.WorkbookStyle(); rows[1].style.hAlign = wjcXlsx.HAlign.Center; var col; for (var i = 0; i < grid.columns.length; i++) { col = grid.columns[i]; //console.log("col name->" + col.name); if(col != undefined) { rows[1].cells[i] = new wjcXlsx.WorkbookCell(); rows[1].cells[i].value = col.name; rows[1].cells[i].style = tableHeaderStyle; } } var cell; var i=0; var j=0; records.forEach(element => { rows[i+2] = new wjcXlsx.WorkbookRow(); rows[i+2].style = new wjcXlsx.WorkbookStyle(); rows[i+2].style.hAlign = wjcXlsx.HAlign.Left; j=0; headers.forEach(col => { cell=element[col]; if(cell != undefined) { if(this.isNumber(cell)) { var value2Replace=''; if (parseInt(cell) != 0 && parseInt(cell) < 0) { value2Replace="(" + wjcCore.Globalize.format(Math.abs(cell), "n0") + ")"; rows[i+2].cells[j] = new wjcXlsx.WorkbookCell(); rows[i+2].cells[j].value = value2Replace; rows[i+2].cells[j].style = negativeCaptionStyle; } else { rows[i+2].cells[j] = new wjcXlsx.WorkbookCell(); rows[i+2].cells[j].value = wjcCore.Globalize.format(Math.abs(cell), "n0"); rows[i+2].cells[j].style = positiveCaptionStyle; } } else { rows[i+2].cells[j] = new wjcXlsx.WorkbookCell(); rows[i+2].cells[j].value = cell; } } j++; }); i++; }); return book;
}
Kindly advise.
Thanks
Deepak