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.
  • 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
  • Replied 27 October 2020, 6:44 pm EST

    Hi,

    fromJson() and toJson() is not working as expected.

    Code:
    store original data:
    this.sheetOriginalValues = this.spread.toJSON();


    restore original data:
      this.spread.fromJSON(this.sheetOriginalValues);


    After restore code is executed, excel become empty having column has (A,B,C ... and so on).

    Note: I only want the spreadsheet data to reset to original data(not the filter and sorting)

    Could you provide working example that would be great!

    Also after it is reset to original data, whether excel will be dirty
    if we execute code

    getDirtyRows()
  • Replied 27 October 2020, 10:22 pm EST

    Hi Jekin,

    You may use includeBindingSource serialization option for saving the JSON with the data source. Please refer to the following code snippet and the attached sample that demonstrates the same.

    document.getElementById("save").addEventListener("click", () => {
    jsonData = spread.toJSON({
    includeBindingSource: true
    });
    });
    document.getElementById("load").addEventListener("click", () => {
    spread.fromJSON(jsonData);
    });


    sample: https://codesandbox.io/s/quirky-mountain-f9ur5?file=/src/index.js:354-602

    >>Also after it is reset to original data, whether excel will be dirty if we execute code

    If we restore the data then the state of the data will also be restored and all the data will be at a valid state so getDirtyRows will return those rows that have been changed after we use fromJSON.

    Regards
    Avinash
  • Replied 29 October 2020, 11:48 pm EST

    Thanks, it is working as expected. but the issue is it clear out the filter and sorting. Also the Events like ClipboardPasted,DragDropBlockCompleted and so on... are not working after that. sheet.rowFilter().reset() also doesn't work.
    Only data need to be reset to original, rest all other things should work as it was working earlier.

    Angular v10: I have multiple tabs showing one sheet per tab.now when i modify the rows in tab 1 and change to tab 2,then again come to tab 1,edited data is still there, is there a way i can save(update) those edited value in excel or remove it when ever i want?
  • Replied 1 November 2020, 3:41 pm EST

    Hi, Any update?
  • Replied 2 November 2020, 12:39 am EST

    >>Restoring the Filter:

    For restoring the filter. you may use a filter.fromJSON method. After the data has loaded you just need to filter the data again. Please refer to the following code snippet and the attached sample that demonstrates the same.

    document.getElementById("save").addEventListener("click", () => {
    jsonData = spread.toJSON({
    includeBindingSource: true
    });
    });
    document.getElementById("load").addEventListener("click", () => {
    spread.fromJSON(jsonData);
    var filter = spread.getActiveSheet().rowFilter();
    filter.fromJSON(filterJSON);
    filter.filter();
    });


    sample: https://codesandbox.io/s/gifted-wave-lllv1?file=/src/index.js:737-1122

    Regarding Tab Switching:

    If I understand correctly you want to reset all the data on the sheet and export the sheet in excel when you come back on tab 1. if it is the case then you may use excel import/export feature please refer to the attached references.

    Further, if it is not the case, Kindly provide more information about the use case so that we could have a better understanding of the scenario and assist you accordingly.

    References:

    exporting the sheet in excel Demo: https://www.grapecity.com/spreadjs/demos/features/workbook/excel-import-export#demo_source_name

    Reset the Sheet: https://www.grapecity.com/spreadjs/docs/v13/online/SpreadJS~GC.Spread.Sheets.Worksheet~reset.html?highlight=reset%2C
  • Replied 2 November 2020, 10:15 pm EST

    Filters are working as expected, but after i load the data using
    spread.fromJSON(jsonData)
    ; then if i edit cell and get datasource() or getDataItem(rowId) it is giving null.

    Any solution to get dataItems value after
    spread.fromJSON(jsonData);


    even in you sample codesandbox, if you add getDataItem(rowId) --> it is giving null.

    document.getElementById("load").addEventListener("click", () => {
    spread.fromJSON(jsonData);
    var filter = spread.getActiveSheet().rowFilter();
    filter.fromJSON(filterJSON);
    filter.filter();
    console.log(spread.getActiveSheet().getDataItem(1));
    });
  • Marked as Answer

    Replied 5 November 2020, 1:10 am EST

    Hi Jekin,

    When we use fromJSON then the Datasource information is getting lost that is why the getDataItem is returning null. For this, you may use getDataSorce for storing the data. Please refer to the following code snippet and update the sample for demonstration.


    document.getElementById("save").addEventListener("click", () => {
    jsonData = JSON.stringify(spread.getActiveSheet().getDataSource());
    });

    document.getElementById("load").addEventListener("click", () => {
    spread.getActiveSheet().setDataSource(JSON.parse(jsonData));
    console.log(spread.getActiveSheet().getDataItem(0));
    });


    sample: https://codesandbox.io/s/tender-chatterjee-nth7r?file=/src/index.js:666-1006

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

    Regards
    Avinash
  • Replied 5 November 2020, 1:16 am EST

    ok, Thank you!
Need extra support?

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

Learn More

Forum Channels