Forums Home / Spread / SpreadJS
Posted by: jekin.desai on 8 September 2020, 2:16 am EST
jekin.desai
Posted 8 September 2020, 2:16 am 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)
sharad.tomer
Posted 9 September 2020, 8:50 am 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
jekin.desai
Posted 10 September 2020, 12:59 am EST
Hi,
The above code work fine if i apply filter to
country id sales expenses country sharad.tomer
Posted 11 September 2020, 10: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
jekin.desai
Posted 17 September 2020, 6:36 am 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?
sharad.tomer
Posted 18 September 2020, 1:11 am 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
jekin.desai
Posted 18 September 2020, 4:14 am EST
Ok. Thank you!
jekin.desai
Posted 18 September 2020, 10: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).
avinash.pathak
Posted 21 September 2020, 10: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
jekin.desai
Posted 30 September 2020, 4:50 am 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}
]
jekin.desai
Posted 6 October 2020, 12:17 pm EST
Hi, any update?
avinash.pathak
Posted 7 October 2020, 10: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
jekin.desai
Posted 9 October 2020, 8:44 am 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}
]
avinash.pathak
Posted 12 October 2020, 8:31 am 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
jekin.desai
Posted 14 October 2020, 4:18 am EST
Ok, Thank you !!!
jekin.desai
Posted 26 October 2020, 2:25 am 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?
avinash.pathak
Posted 26 October 2020, 4:27 am 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
jekin.desai
Posted 28 October 2020, 3:44 am 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()
avinash.pathak
Posted 28 October 2020, 7:22 am 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
jekin.desai
Posted 30 October 2020, 8:48 am 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?
jekin.desai
Posted 2 November 2020, 12:41 am EST
Hi, Any update?
avinash.pathak
Posted 2 November 2020, 9: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
jekin.desai
Posted 3 November 2020, 7:15 am EST
Filters are working as expected, but after i load the data using ```
spread.fromJSON(jsonData)
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));
});
avinash.pathak
Posted 5 November 2020, 10: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
jekin.desai
Posted 5 November 2020, 10:16 am EST
ok, Thank you!
