FlexGridXlsxConverter exports data only which is visible

Posted by: viplav.setia on 6 October 2020, 5:19 pm EST

  • Posted 6 October 2020, 5:19 pm EST

    Hello

    Is there a property to disable the excel export to the visible datagrid. By default, it is exporting data which is visible in the data grid. I want to export the whole datagrid.
  • Replied 6 October 2020, 5:26 pm EST

    viplav.setia said:
    Hello

    Is there a property to disable the excel export to the visible datagrid. By default, it is exporting data which is visible in the data grid. I want to export the whole datagrid.


    Specifically, the rows which are not visible do not export all data.
  • Replied 7 October 2020, 6:46 pm EST

    Hi Viplav,

    All of the rows are exported to the excel but they are also hidden in the exported excel file. You just need to display them again before exporting to excel. In the saveAsync method, the workbook instance is provided that can be used to display all the rows in the exported excel file. Please refer to the sample link below for reference:

    https://stackblitz.com/edit/js-vg3kna

    Regards,
    Ashwin
  • Replied 8 October 2020, 1:16 am EST

    Hi Ashwin

    Thank you for the reply. I tried this, it does not save the file anymore.

    My Code :


    //dg = ... ;
    var book = wijmo.grid.xlsx.FlexGridXlsxConverter.saveAsync(dg, {
    includeColumnHeaders: true,
    includeRowHeaders: true, formatItem : ....
    },
    null, // pName + 'xlsx';
    (base64,wb) =>
    {
    wb.sheets[0].rows.forEach(r => r.visible = true);
    wb.saveAync(pName + '.xlsx'); //pName - function parameter name
    },
    function (reason)
    { // onError
    jMsgAlert("Convert to XLSX", reason);
    }
    );

  • Replied 8 October 2020, 3:01 pm EST

    Hi Viplav,

    There could be some changes in the method due to different versions. Can you let me know which version are you using so that I could update the method accordingly?

    ~regards
  • Replied 8 October 2020, 6:17 pm EST

    Hi Ashwin

    I looked into wijmo.min.js file.
    It says Wijmo Library 5.20182.500.
    I don't have wijmo installed locally but i am referencing these js files from our server. But all the datagrids work.

  • Replied 11 October 2020, 7:09 pm EST

    I have updated the sample to use the provided version:

    https://stackblitz.com/edit/js-cctzhm

    In this version, the Workbook instance was not passed to the onSaved callback. So, you will need to use the instance returned by the saveAsync method.

    ~regards
  • Replied 12 October 2020, 7:17 pm EST

    Hi Ashwin

    Thank you for your reply.
    I could save the workbook but the problem is still there.
    All rows and columns were there. Look at the image attached. I marked the visible datagrid in view in a red box. The data here is exported, but the data in rows and columns not visible is not exported.


    Kind Regards
  • Replied 13 October 2020, 4:43 pm EST

    Hi Viplav,

    I think I am not understanding your requirements properly. Can you please explain them again? As per my understanding, you need to export all the data in FlexGrid even if it is not displayed on the grid. Is my understanding correct?
    In the sample that I provided, it only shows how to export all the rows including the hidden rows. But, you can also update the sample to export all the columns.

    https://stackblitz.com/edit/js-cctzhm

    ~regards
  • Replied 13 October 2020, 6:17 pm EST

    Hi Ashwin

    When I open my Datagrid in browser, i can see a certain portion of the datagrid. The rest is scrollable. When I export this datagrid to excel, the data of the cells which are visible in this portion are exported. The data in the cells which are not visible in the browser but can be seen on scrolling are not exported. All rows and columns are exported but the data inside the cells of the columns and rows which are not visible but can be viewed by scrolling are not exported.
    This is the problem.

    Kind regards
  • Replied 14 October 2020, 7:35 pm EST

    Hello Viplav,

    Please refer to the sample link below:

    https://stackblitz.com/edit/js-cctzhm

    In this sample, some cells are hidden (but can be viewed by scrolling). But, all the cells are exported to the excel file without any workaround. Can you let me know whether this is your scenario? If not, can you modify the sample which replicates your issue?

    ~regards
  • Replied 14 October 2020, 8:17 pm EST

    Hi Ashwin

    Let us start from the datagrid.
    Can the problem be with the datagrid when I call it?
      var dg = wijmo.Control.getControl(pDivHost);

    Does this take the datagrid which is in the view (scrollable) and not the complete grid?

  • Replied 14 October 2020, 8:45 pm EST

    The getControl method will return the reference of the FlexGrid and the CollectionView of the FlexGrid will always contain the complete data even if it is not visible.
  • Replied 15 October 2020, 12:48 am EST

    I found the issue. This is format item inside saveAsync.

     wert = dg.cells.getCellElement(j, i); 


    wert is null when the cell is not in view.
  • Replied 15 October 2020, 8:24 pm EST

    It seems that you are formatting the cell's element in the formatItem callback. If you need the cell, then you may use the getFormattedCell method provided in the formatItem event args to get the cell reference.

    https://www.grapecity.com/wijmo/api/classes/wijmo_grid_xlsx.xlsxformatitemeventargs.html#getformattedcell

    Let me know if this solves your issue.

    ~regards
  • Replied 19 October 2020, 6:49 pm EST

    Hi

    I don't fully understand the correct usage of this method here.
    What we are doing here is formatting data according to data types.
    My code:
     
    formatItem: function (args)
    {
    var i = args.col;
    var j = args.row;
    var wert = "";

    if (args.panel.cellType == wijmo.grid.CellType.Cell)
    {
    if (j != rowId)
    {
    rowId = j;

    jRow = a00.gDataGrid.jRowLoading(pName, j, j, j);
    }

    if (jRow != null &&
    jRow.cells != null &&
    i < jRow.cells.length &&
    jRow.cells[i] != null)
    {
    if (jRow.cells[i].ctrlType == 17)
    {

    wert = dg.cells.getCellElement(j, i);
    if (wert != null)
    args.xlsxCell.value = wert.innerText;
    else
    args.xlsxCell.value = jRow.cells[i].value + "" + args.xlsxCell.value;
    }
    }
    }



    Do you mean that I should do this :


    wert = args.getFormattedCell();


    But how do I specify the row and col number here because we are checking each cell.
  • Replied 21 October 2020, 12:42 am EST

    Hi Viplav,

    The getFormattedCell method will always return the cell which is currently being formatted. So, you do not need to provide the row and column index. If the formatItem is running for cell (0, 0), then this method will return the cell element for the (0, 0) cell and so on.

    ~regards
Need extra support?

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

Learn More

Forum Channels