how can we save and applied filter in spreadJS

Posted by: jekin.desai on 7 September 2020, 5:16 pm EST

  • Posted 7 September 2020, 5:16 pm EST

    I am using spreadJS licence version in my Angular app v10, I want to save filters that i have applied while leaving the page,and when i come back again to same page i want the saved filter to be applied to spreadsheet? how can i achieve this?
    For storing filters i can use localStorage(but if you have an better idea do let me know)
  • Replied 8 September 2020, 11:50 pm EST

    Hi,

    To preserve the filter you may use the filter.toJSON() method to serialize it to JSON and then use the filter.fromJSON() method to restore filter from the JSON. Please refer to the following sample which demonstrates the same:
    https://codesandbox.io/s/spread-js-starter-izuwl?file=/src/index.js
    In the above sample, filter is saved in the localstorage but you may save the filter JSON string anywhere according to the requirements of your application.

    Regards
    Sharad
  • Replied 9 September 2020, 3:59 pm EST

    Hi,

    The above code work fine if i apply filter to
    country 
    or
    id 
    or both, but if i try to apply filter to column
    sales 
    or
    expenses 
    or together with
    country 
    or
    id 
    and do refresh it becomes blank.
  • Marked as Answer

    Replied 11 September 2020, 1:28 am EST

    The issue in the previous sample was observed because the data in the sample was generating random values for sales and expenses columns.
    I have updated the sample to fix this, please refer to the following updated sample and let me know if you face any issues:
    https://codesandbox.io/s/spread-js-starter-forked-fyxh0?file=/src/index.js
  • Replied 16 September 2020, 9:36 pm EST

    Thank you!

    Is there any way to automatically set the width of individual column base on its col header size or its value size which ever is maximum?
  • Replied 17 September 2020, 4:11 pm EST

    You may use the autoFitColumn() method to set the width automatically. Please refer to the following code snippet:
    spread.options.autoFitType = GC.Spread.Sheets.AutoFitType.cellWithHeader;
    spread.getActiveSheet().autoFitColumn(index);


    API reference:
    https://www.grapecity.com/spreadjs/docs/v13/online/SpreadJS~GC.Spread.Sheets.Worksheet~autoFitColumn.html
  • Replied 17 September 2020, 7:14 pm EST

    Ok. Thank you!
  • Replied 18 September 2020, 1:43 am EST

    Last question:

    i want to save the edited row in DB:

    I have a 1000 rows and i'm editing let say any 10 rows (irrespective of cells that is edited in that row),is there any way i can find out which row was edited?
    and how can i get the data of the particular row(s) only.

    note editing can done via drag drop,drag fill,keyboard F2,mouse double click,copy paste or keyboard delete,moving the block of cells to another cells(means all possible way).
  • Replied 21 September 2020, 1:12 am EST

    Hi Jekin,

    You may use getDirtyRows method for the required functionality. Please refer to the following code snippet and sample for demonstration.


    sheet.setDataSource(getData(100));

    document.getElementById("button1").addEventListener("click", function () {
    var dirtyRows = sheet.getDirtyRows();
    var len = dirtyRows.length;
    if (len > 0) {
    for (var i = 0; i < len; i++) {
    var dr = dirtyRows[i];
    console.log("row:" + dr.row);
    }
    }
    });


    sample: https://codesandbox.io/s/affectionate-lake-jo119?file=/src/index.js

    API References:

    getDirtyRows: https://www.grapecity.com/spreadjs/docs/v13/online/SpreadJS~GC.Spread.Sheets.Worksheet~getDirtyRows.html

    Regards
    Avinash
  • Replied 29 September 2020, 7:50 pm EST

    Hi, I got the row number which were modified, by how can i get the data of that row which corresponding column info.
    From your example if i have modified the first and second row then i should get:
    [
    { ID:0,Name:C,Info:0,Roll:0,Marks:80,HasBack:TRUE},
    { ID:1,Name:D,Info:1,Roll:37,Marks:90,HasBack:TRUE}
    ]

  • Replied 6 October 2020, 3:17 am EST

    Hi, any update?
  • Replied 7 October 2020, 1:32 am EST

    Hi Jekin,

    Sorry for the late reply. If I understand correctly you want to get the row and column index of changed Cells. for this, you may use getDirtyCells method. Please refer to the following code snippet and the attached sample demonstrating the same.

    document.getElementById("button2").addEventListener("click", function () {
    var dirtyCells = sheet.getDirtyCells();
    var len = dirtyCells.length;
    if (len > 0) {
    for (var i = 0; i < len; i++) {
    var cell = dirtyCells[i];
    console.log(
    "row:" +
    cell.row +
    " " +
    "col:" +
    cell.col +
    " " +
    "oldValue:" +
    cell.oldValue +
    " " +
    "newValue:" +
    cell.newValue
    );
    }
    }
    });

    sample: https://codesandbox.io/s/lively-shadow-q0n97?file=/src/index.js:562-1110


    API references:

    getDirtyCells: https://www.grapecity.com/spreadjs/docs/v13/online/SpreadJS~GC.Spread.Sheets.Worksheet~getDirtyCells.html


    Regards
    Avinash
  • Replied 8 October 2020, 11:44 pm EST

    No, i don't want the cells info that were modified.

    I want list of arrays for which rows were modified.
    Considering your sample example, suppose first and second row are modified then i need each cell of that row as value and there corresponding column header as
    key

    like this:

    [
    { ID:0,Name:C,Info1:0,Roll:0,Marks:80,HasBack:TRUE},
    { ID:1,Name:D,Info1:1,Roll:37,Marks:90,HasBack:TRUE}
    ]


  • Replied 11 October 2020, 11:31 pm EST

    Hi Jekin,

    You may use a sheet.getDataItem method for the required functionality. Please refer to the following code snippet and updated sample and let us know if you any issues.


    var dirtyRows = sheet.getDirtyRows();
    var len = dirtyRows.length;
    var rowsItem = [];
    if (len > 0) {
    for (var i = 0; i < len; i++) {
    var dr = dirtyRows[i];
    rowsItem.push(sheet.getDataItem(dr.row));
    }
    console.log(rowsItem);
    }


    Sample: https://codesandbox.io/s/affectionate-lake-jo119?file=/src/index.js:344-617


    API references:

    getDataItem: https://www.grapecity.com/spreadjs/docs/v13/online/SpreadJS~GC.Spread.Sheets.Worksheet~getDataItem.html
  • Replied 13 October 2020, 7:18 pm EST

    Ok, Thank you !!!
  • Replied 25 October 2020, 5:25 pm EST

    Hi,

    Is there any way to refresh spreadSheet to original data after editing some data?
    refresh() and repaint() method is not working as expected.
    Scenario:
    i have original data which is shown in spreadSheet, now if someone edit data and now i want to undo all changes or to show original data(reset to original value).

    Is it possible?
  • Replied 25 October 2020, 7:27 pm EST

    Hi Jekin,

    You may store the original JSON string by using the toJSON method and whenever you want to restore the Spread, you just need to call the spread.fromJSON method. Please refer to the following and code snippet and let us know if you face any issues.

    var wbJSON=spread.toJSON();
    //restore JSON
    spread.fromJSON(workbookObj);

    Further, for undo the individual steps SJS support undo manager. Please refer to the following references for more information.

    fromJSON: https://www.grapecity.com/spreadjs/docs/v13/online/SpreadJS~GC.Spread.Sheets.Workbook~fromJSON.html

    toJSON: https://www.grapecity.com/spreadjs/docs/v13/online/SpreadJS~GC.Spread.Sheets.Workbook~toJSON.html
    undo method: https://www.grapecity.com/spreadjs/docs/v13/online/SpreadJS~GC.Spread.Commands.UndoManager~undo.html


    undo/Redo: https://www.grapecity.com/spreadjs/docs/v13/online/undo.html


    Regards
    Avinash
Need extra support?

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

Learn More

Forum Channels