Loop rows activesheet to find visible rows

[Morten]

I have a kendospreadsheet with a column filter on every column.

After the user has set filters, I want only to get visible rows.

How is this possible?

Found resources:

https://docs.telerik.com/kendo-ui/knowledge-base/spreadsheet-hidden-rows-count-on-filtering

[Morten]

Not a good solution, but...

By adding a comment property to the cells I want to the value from and querying the DOM, I can get the values I need as an array:

cells.push({ value: locationId, enable: false, comment: "locationId" });

var visibleValues = $("#locations div.k-spreadsheet-data .k-spreadsheet-cell.k-spreadsheet-has-comment div:visible")
    .map(function () {
        return $(this).text().trim();
    })
    .get();

The ":visible" appears not necessary.
Also, rows must be visible.

I'm hoping for a more solid approach :-)

[Morten]

I'm having another issue with being unable to get the row visibility.

If I filter rows (by any columns) and paste a value into multiple consecutive rows, the value is also copied into the hidden rows

[Nikolay]

Hi Morten,

The issue arises because Kendo Spreadsheet does not have a built-in method to get only visible rows after filtering. However, you can work around this by checking each row's visibility using the filter applied in the data source.

Since the Kendo Spreadsheet uses a data source internally, you can get only the visible rows by applying the current filter to the data source manually.

Steps:
1. Get the spreadsheet widget.
2. Access its dataSource.
3. Apply the filters manually to get the visible rows.

function getVisibleRows() {
    var sheet = $("#spreadsheet").data("kendoSpreadsheet").activeSheet();
    var dataSource = sheet.dataSource; 

    if (dataSource) {
        var filteredData = dataSource.view(); // Gets only visible (filtered) data
        console.log(filteredData); // Check the result
        return filteredData;
    }

    return [];
}

// Usage:
var visibleRows = getVisibleRows();
console.log(visibleRows);

Regarding the pasting over hidden rows, if you paste data into multiple rows, Kendo Spreadsheet will also paste it into hidden rows.
Workaround: Temporarily unhide all rows before pasting, then restore their visibility.

var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");
var sheet = spreadsheet.activeSheet();
var totalRows = sheet._rows._count;
var hiddenRows = [];

// Store hidden rows and make them visible
for (var row = 0; row < totalRows; row++) {
    if (!sheet.rowVisibility(row)) {
        hiddenRows.push(row);
        sheet.showRow(row);
    }
}

// Perform paste operation (manually trigger it if needed)
// Example: sheet.range("A1").value("Pasted Value");

// Restore hidden rows
hiddenRows.forEach(function (row) {
    sheet.hideRow(row);
});

Regards,
Nikolay
Progress Telerik

[Morten]

Hi Nikolay.
Thanks for your answer.

In my case I add data to the spreadsheet by way of the sheets property:

// build sheets
let sheets = [
    {
        name: "Locations",
        frozenRows: 1,
        rows: rows,
        filter: filter,
        columns: columns
    }
];

// initialize survey-category-targets spreadsheet
let $locationsSheet = $("#locations").kendoSpreadsheet({
    toolbar: false,
    sheets: sheets,
    sheetsbar: false,
    change: function (e) {

 

I'm getting "TypeError: sheet.rowVisibility is not a function" when calling .rowVisibility in the change event:

change: function (e) {

let comments = e.range.value(); // only once cell is enabled for edit

var spreadsheet = $("#locations").data("kendoSpreadsheet");
var sheet = spreadsheet.activeSheet();
var totalRows = sheet._rows._count;
var hiddenRows = [];
var visibleRows = [];
for (var row = 0; row < totalRows; row++) {
    if (sheet.rowVisibility(row)) {
        visibleRows.push(row);
    } else {
        hiddenRows.push(row);
    }
}

 

I have a sheet with locations. Only one column (comments) is editable.

I want to enter comments with a row id into a browser datastore for later export to Excel.

All this works fine in the change event using when the user does not copy-paste values into multiple filtered rows.

    for (let i = rowNoTopLeft; i <= rowNoBottomRight; i++) {
        const selectedLocationId = $activeSheet.range("A" + i).value();
        if (selectedLocationId == null) continue;
        // remove item if locationId already exists in basket
        basket = basket.filter(function (item) {
            return item.locationId !== selectedLocationId;
        });
        // remove item with selectedLocationId when comments is empty
        if (comments !== null) {
            var item = {
                locationId: selectedLocationId,
                comments: comments
            };
            basket.push(item);
        }
    }

    window.localforage.setItem(viewModel.basketName, basket).then(function () {
    ...
    ...

I would love to have a row visibility property I could easily access in the range object

 

[Nikolay]

Hi Morten,

A rowVisibility() method is unavailable in Kendo Spreadsheet, as the Spreadsheet does not maintain a direct concept of row visibility like the Grid. However, you can determine visible rows by checking for hidden row properties in the sheet's JSON data.

To get only visible rows:

1. Retrieve the sheet's JSON data.
2. Check for hidden rows using rows[i].hidden.
3. Return only rows that are not hidden.

function getVisibleRows() {
    var spreadsheet = $("#locations").data("kendoSpreadsheet");
    var sheet = spreadsheet.activeSheet();
    var sheetData = sheet.toJSON(); // Get sheet's JSON representation
    var visibleRows = [];

    if (sheetData && sheetData.rows) {
        sheetData.rows.forEach((row, index) => {
            if (!row.hidden) { // If the row is NOT hidden, add to visibleRows
                var rowData = [];
                if (row.cells) {
                    row.cells.forEach(cell => {
                        rowData.push(cell.value || ""); // Extract cell values
                    });
                }
                visibleRows.push(rowData);
            }
        });
    }

    console.log("Visible Rows:", visibleRows);
    return visibleRows;
}

// Usage
var visibleRows = getVisibleRows();
console.log(visibleRows);

Regards,

Nikolay

[Morten]

Thanks Nikolay. I miss-read your first response. I got it working now.

Working with filters in spreadsheet, I wish there was:

- a filter change event
- a built in way to "get visible rows"

/Morten