Forums Home / Spread / SpreadJS
Posted by: dave.welch on 2 September 2020, 2:31 am EST
dave.welch
Posted 2 September 2020, 2:31 am EST
Hi,
By default, the sheet shows 20 columns and 200 rows.
So, getRowCount and getColumnCount functions give me 200 and 20.
Suppose I have data in only 10 rows and 5 columns, how do I get these “active” row and column count i.e 10 and 5?
In the toJSON() has somthing like “rowCount”:200,“columnCount”:20,“activeRow”:0,“activeCol”:0
How do i get the activeRow and activeCol using functions?
Thanks
avinash.pathak
Posted 3 September 2020, 12:58 am EST
Hi Dave,
If I understand correctly you want to get the non-empty row/column count. We are sorry, there is no direct method to achieve this functionality, but you may use toJSON which provides the data table containing all nonempty rows and columns. Please refer to the following code snippet and attached sample which demonstrates the same.
function _getNonEmptyRowCount(sheet) {
return Object.keys(sheet.toJSON().data.dataTable).length;
}
function _getNonEmptyColCount(sheet) {
let json = sheet.toJSON();
let dataTable = Object.keys(json.data.dataTable);
let nonEmptyColumns = [];
dataTable.forEach((row) => {
let rowArray = Object.keys(json.data.dataTable[row]);
rowArray.forEach((col) => {
if (!nonEmptyColumns.includes(col)) {
nonEmptyColumns.push(col);
}
});
});
return nonEmptyColumns.length;
}
sample: https://codesandbox.io/s/spreadjs-custom-formula-reference-forked-c2sus?file=/src/index.js:636-1141
If you had a different requirement then please explain more about your requirement so that we could assist you accordingly.
API references:
toJSON: https://www.grapecity.com/spreadjs/docs/v13/online/SpreadJS~GC.Spread.Sheets.Worksheet~toJSON.html
Regards
Avinash
dave.welch
Posted 3 September 2020, 2:58 am EST - Updated 3 October 2022, 1:04 am EST
avinash.pathak
Posted 3 September 2020, 11:45 pm EST
Hi Dave,
For getting the last non-empty row/column you may traverse the data table provided by the toJSON method and return the maximum column or row index among all of the rows and columns. Please refer to the following code snippet and attached sample which demonstrates the same.
function _getLastNonEmptyRow(sheet) {
let rows = Object.keys(sheet.toJSON().data.dataTable);
//the largest Row index is available at the last of the array
return 1 + +rows[rows.length - 1];
}
function _getLastNonEmptyCol(sheet) {
let json = sheet.toJSON();
let dataTable = Object.keys(json.data.dataTable);
let nonEmptyColIndex = -1;
dataTable.forEach((row) => {
let rowArray = Object.keys(json.data.dataTable[row]);
rowArray.forEach((col) => {
nonEmptyColIndex = Math.max(nonEmptyColIndex, col);
});
});
//return the index plus one
return 1 + nonEmptyColIndex;
}
sample: https://codesandbox.io/s/spreadjs-custom-formula-reference-forked-c2sus?file=/src/index.js:634-1276
Regards
Avinash
dave.welch
Posted 18 November 2020, 3:16 am EST
Hi Avinash,
I have a small issue in this.
I have 2 columns as your example. If I use you code above this gives me correct activeRows and activeColumns.
If now I “CUT” column 2 and insert the cut-cells before column 1 - i.e interchange column1 and column2 - then the active rows will be 200 and not previous activeRows.
The same thing happens for activeColumns as well if I cut and paste Rows
Can you please help?
Thanks,
Veerendra.
avinash.pathak
Posted 18 November 2020, 11:35 pm EST
Hi Dave,
What happening here is when we insert any row the style information is shifted below and SJS by design stores that information in the JSON data table so after insertion, every row has some style information in their JSON representation that is why the last non-empty row is coming as Row count of the sheet.
Further, we have escalated this to the concerned team for deeper investigation. We will update you regarding this as soon as we get any information. The internal tracking ID for this issue will be SJS-6574.
Regards
Avinash
avinash.pathak
Posted 6 December 2020, 3:47 pm EST
Hi Deve,
The issue is fixed in our latest V14.0.3 build. Please update to the latest build and let us know if the issue still persists for you. You may also downloade the latest build from here: http://cdn.grapecity.com/spreadjs/14.0.3/Files/SpreadJS.Release.14.0.3.zip
Further please refer to the following sample that demonstrates the issue fix. Please let us know if that helps you.
https://codesandbox.io/s/spreadjs-custom-formula-reference-forked-q1ms9?file=/src/index.js:634-1237
Regards,
Avinash
apagan
Posted 5 June 2023, 8:41 am EST
There was a problem with the code in the demo above this is the fixed fork
https://codesandbox.io/s/spreadjs-custom-formula-reference-forked-bygpp5
ankit.kumar
Posted 5 June 2023, 5:59 pm EST
Hi Alexander,
Thanks for sharing the sample with us. Do you find any particular scenario for which the sample shared by Avinash is being failed?
I tested with the Avinash’s sample (https://codesandbox.io/s/spreadjs-custom-formula-reference-forked-q1ms9?file=%2Fsrc%2Findex.js:634-1237) and it seems to be working fine.
Could you kindly let me know if we were missing any particular scenario/ use case?
Regards,
Ankit
