Active RowCount and Active Column Count

Posted by: dave.welch on 2 September 2020, 2:31 am EST

  • 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
  • Replied 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
  • Replied 3 September 2020, 2:58 am EST

    Hi Avinash,
    Thanks for your reply.

    In your example, you are counting number of rows/cols which have data.
    I need the last row/col number even if there are empty values in between

    In the example image, I should get 10 rows, 10 cols as the answer.

    Thanks.
  • Replied 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
Need extra support?

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

Learn More

Forum Channels