Category / Section
How to get filtered rows collection in Spreadsheet.
1 min read
Solution
This Knowledge Base explains the way to get the filtered rows collection in the Spreadsheet.
- We can customize and get the filtered rows by using ‘actionComplete’ client side event.
JS
<div id="Spreadsheet"></div> $(function() {// Document is ready. $("#Spreadsheet").ejSpreadsheet({ sheets: [{ // window.defaultData from http://js.syncfusion.com/demos/web/scripts/xljsondata.min.js file. rangeSettings: [{ dataSource: window.defaultData }] }], actionComplete: "onActionComplete" }); });
MVC
[View] @(Html.EJ().Spreadsheet<ItemDetail>("Spreadsheet") .Sheets(sheet => { sheet.RangeSettings(range => { range.Datasource((IEnumerable<object>)ViewBag.Datasource).Add(); }).Add(); }) .ClientSideEvents(events => events.ActionComplete("onActionComplete")) )
[Controller] public class HomeController : Controller { public ActionResult Index() { // OrderItemsDataContext is a class which have list collection. var DataSource = new OrderItemsDataContext().GetAllItemDetails.ToList(); ViewBag.Datasource = DataSource; return View(); } }
ASP
[ASPX] <ej:Spreadsheet ID="Spreadsheet" runat="server"> <ClientSideEvents ActionComplete="onActionComplete"/> </ej:Spreadsheet>
[CS] protected void Page_Load(object sender, EventArgs e) { // OrderItemsDataContext is a class which have list collection. var dataSource = new OrderItemsDataContext().GetAllItemDetails.ToList(); this.Spreadsheet.Sheets.Add(new Syncfusion.JavaScript.Models.Sheet() { Datasource = dataSource }); }
JS
function onActionComplete(args) { if (args.action == "filtering") { // getFilteredRowsCollection(sheetIndex, tableId). // Need to give id of the table which has filter applied. // tableId default value is -1. var filteredRowsCollection = this.getFilteredRowsCollection(); // get filtered rows collection here. } }
- Here, the getFilteredRowsCollection() method has been extended from Spreadsheet.
- You can use the below code as a separate script file and load this script file after loading ej.web.all.min.js file.
SpreadsheetFilterPlugin.js
// The sheetIndex and tableId both are optional. Pass the tableId when you want to get the filtered rows collection for particular table. ej.Spreadsheet.prototype.getFilteredRowsCollection = function (sheetIndex, tableId) { var k = 0, filterCollection = [], sheetIndex = sheetIndex || this.getActiveSheetIndex(), tableId = tableId || -1, tableRange = this.getSheet(sheetIndex).filterSettings.tableRange, filteredRowsIndexes, tableRows; for (var t = 0, tableRangeLength = tableRange.length; t < tableRangeLength; t++) { filteredRowsIndexes = tableRange[t].fltrdIdxes; tableRows = tableRange[t].filteredRange; if (tableRange[t].tableID == tableId) for (var i = 0, filteredRowsIndexesLength = filteredRowsIndexes.length; i < filteredRowsIndexesLength; i++) { for (var j = k, tableRowsLength = tableRows.length; j < tableRowsLength; j++) { if (tableRows[j].idx === filteredRowsIndexes[i]) { filterCollection.push(tableRows[j]); k = j; break; } } } } return filterCollection; };