excel export with formatting and styling for large data sets

Posted by: bhati.bhati on 22 November 2020, 10:28 pm EST

  • 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
  • Replied 23 November 2020, 11:52 pm EST

    Hi,

    We are able to replicate the issue at our end and hence we have forwarded it to the dev team for further investigation of the issue(Internal tracking ID: 469421). We will let you know about updates regarding the same.

    Regards
    Sharad
  • Replied 25 November 2020, 12:18 am EST

    Thanks Sharad,
    I am eagerly waiting for its resolution.

  • Replied 30 November 2020, 5:30 pm EST

    Hello Sharad,

    Is there any further update on this thread? Kindly let me know.

    Thanks
    Deepak
  • Replied 30 November 2020, 5:31 pm EST

    Hello Sharad,

    Is there any further update on this thread? Kindly let me know.

    Thanks
    Deepak
  • Replied 1 December 2020, 4:21 pm EST

    Hi Deepak,

    We are sorry for the delayed response. The issue is still with the dev team and they are currently investigating this. We will update you once we will have more information.

    Regards,
    Ashwin
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels