Get filtered row data in React Spreadsheet
29 Jun 202624 minutes to read
Filtering in the Spreadsheet allows you to display only the rows that match your criteria, hiding all others. You can enable filtering by setting the allowFiltering property, and apply filters either through the UI or programmatically using the applyFilter method. To identify which rows are filtered, iterate through the sheet’s row collection and check the isFiltered property of each row object. This flag indicates whether a row is currently hidden due to filtering.
The following example shows how to get the filtered rows from the Spreadsheet:
import * as React from 'react';
import { createRoot } from 'react-dom/client';
import { SpreadsheetComponent, SheetsDirective, SheetDirective, RangesDirective } from '@syncfusion/ej2-react-spreadsheet';
import { RangeDirective, ColumnsDirective, ColumnDirective } from '@syncfusion/ej2-react-spreadsheet';
import { defaultData } from './datasource';
function App() {
const spreadsheetRef = React.useRef(null);
const created = () => {
spreadsheetRef.current.cellFormat({ fontWeight: 'bold', textAlign: 'center', verticalAlign: 'middle' }, 'A1:F1');
// Construct the predicate model to be updated to the data.
let predicates = [{
field: 'C',
operator: 'equal',
value: 'Pink',
matchCase: false
}];
// Apply filter to the specified range.
spreadsheetRef.current.applyFilter(predicates, 'A1:C7');
};
const onClick = () => {
let spreadsheet = spreadsheetRef.current;
let activeSheet = spreadsheet.getActiveSheet();
let usedRange = activeSheet.usedRange;
for (let i = 0; i <= usedRange.rowIndex; i++) {
// Get the filtered row using isFiltered property.
let filteredRow = (activeSheet.rows[i]).isFiltered;
if (!filteredRow) {
let rowData = spreadsheet.getRowData(i);
console.log("Row:", i + 1, "Cells", rowData);
}
}
}
return (
<div className='control-section spreadsheet-control'>
<button className="e-btn custom-btn" onClick={onClick}> Get Filtered Data </button>
<SpreadsheetComponent ref={spreadsheetRef} created={created}>
<SheetsDirective>
<SheetDirective>
<RangesDirective>
<RangeDirective dataSource={defaultData}></RangeDirective>
</RangesDirective>
<ColumnsDirective>
<ColumnDirective width={180}></ColumnDirective>
<ColumnDirective width={130}></ColumnDirective>
<ColumnDirective width={130}></ColumnDirective>
<ColumnDirective width={180}></ColumnDirective>
<ColumnDirective width={130}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
</ColumnsDirective>
</SheetDirective>
</SheetsDirective>
</SpreadsheetComponent>
</div>
);
};
export default App;
const root = createRoot(document.getElementById('root'));
root.render(<App />);import * as React from 'react';
import { createRoot } from 'react-dom/client';
import { SpreadsheetComponent, SheetsDirective, SheetDirective, RangesDirective, ExtendedRowModel, UsedRangeModel, SheetModel, RangeDirective, ColumnsDirective, ColumnDirective } from '@syncfusion/ej2-react-spreadsheet';
import { PredicateModel } from '@syncfusion/ej2-react-grids';
import { defaultData } from './datasource';
function App() {
const spreadsheetRef = React.useRef<SpreadsheetComponent>(null);
const created = (): void => {
spreadsheetRef.current.cellFormat({ fontWeight: 'bold', textAlign: 'center', verticalAlign: 'middle' }, 'A1:F1');
// Construct the predicate model to be updated to the data.
let predicates: PredicateModel[] = [{
field: 'C',
operator: 'equal',
value: 'Pink',
matchCase: false
}];
// Apply filter to the specified range.
spreadsheetRef.current.applyFilter(predicates, 'A1:C7');
};
const onClick = () => {
let spreadsheet = spreadsheetRef.current;
let activeSheet: SheetModel = spreadsheet.getActiveSheet();
let usedRange: UsedRangeModel = activeSheet.usedRange;
for (let i: number = 0; i <= usedRange.rowIndex; i++) {
// Get the filtered row using isFiltered property.
let filteredRow: Object = (activeSheet.rows[i] as ExtendedRowModel).isFiltered;
if (!filteredRow) {
let rowData: Object = spreadsheet.getRowData(i);
console.log("Row:", i + 1, "Cells", rowData);
}
}
}
return (
<div className='control-section spreadsheet-control'>
<button className="e-btn custom-btn" onClick={onClick}> Get Filtered Data </button>
<SpreadsheetComponent ref={spreadsheetRef} created={created}>
<SheetsDirective>
<SheetDirective>
<RangesDirective>
<RangeDirective dataSource={defaultData}></RangeDirective>
</RangesDirective>
<ColumnsDirective>
<ColumnDirective width={180}></ColumnDirective>
<ColumnDirective width={130}></ColumnDirective>
<ColumnDirective width={130}></ColumnDirective>
<ColumnDirective width={180}></ColumnDirective>
<ColumnDirective width={130}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
</ColumnsDirective>
</SheetDirective>
</SheetsDirective>
</SpreadsheetComponent>
</div>
);
};
export default App;
const root = createRoot(document.getElementById('root')!);
root.render(<App />);/**
* Default data source
*/
export let defaultData = [
{
"Customer Name": "Romona Heaslip",
"Model": "Taurus",
"Color": "Aquamarine",
"Payment Mode": "Debit Card",
"Delivery Date": "07/11/2015",
"Amount": "8529.22"
},
{
"Customer Name": "Clare Batterton",
"Model": "Sparrow",
"Color": "Pink",
"Payment Mode": "Cash On Delivery",
"Delivery Date": "7/13/2016",
"Amount": "17866.19"
},
{
"Customer Name": "Eamon Traise",
"Model": "Grand Cherokee",
"Color": "Blue",
"Payment Mode": "Net Banking",
"Delivery Date": "09/04/2015",
"Amount": "13853.09"
},
{
"Customer Name": "Julius Gorner",
"Model": "GTO",
"Color": "Aquamarine",
"Payment Mode": "Credit Card",
"Delivery Date": "12/15/2017",
"Amount": "2338.74"
},
{
"Customer Name": "Jenna Schoolfield",
"Model": "LX",
"Color": "Yellow",
"Payment Mode": "Credit Card",
"Delivery Date": "10/08/2014",
"Amount": "9578.45"
},
{
"Customer Name": "Marylynne Harring",
"Model": "Catera",
"Color": "Green",
"Payment Mode": "Cash On Delivery",
"Delivery Date": "7/01/2017",
"Amount": "19141.62"
},
{
"Customer Name": "Vilhelmina Leipelt",
"Model": "7 Series",
"Color": "Goldenrod",
"Payment Mode": "Credit Card",
"Delivery Date": "12/20/2015",
"Amount": "6543.30"
},
{
"Customer Name": "Barby Heisler",
"Model": "Corvette",
"Color": "Red",
"Payment Mode": "Credit Card",
"Delivery Date": "11/24/2014",
"Amount": "13035.06"
},
{
"Customer Name": "Karyn Boik",
"Model": "Regal",
"Color": "Indigo",
"Payment Mode": "Debit Card",
"Delivery Date": "05/12/2014",
"Amount": "18488.80"
},
{
"Customer Name": "Jeanette Pamplin",
"Model": "S4",
"Color": "Fuscia",
"Payment Mode": "Net Banking",
"Delivery Date": "12/30/2014",
"Amount": "12317.04"
},
{
"Customer Name": "Cristi Espinos",
"Model": "TL",
"Color": "Aquamarine",
"Payment Mode": "Credit Card",
"Delivery Date": "12/18/2013",
"Amount": "6230.13"
},
{
"Customer Name": "Issy Humm",
"Model": "Club Wagon",
"Color": "Pink",
"Payment Mode": "Cash On Delivery",
"Delivery Date": "02/02/2015",
"Amount": "9709.49"
},
{
"Customer Name": "Tuesday Fautly",
"Model": "V8 Vantage",
"Color": "Crimson",
"Payment Mode": "Debit Card",
"Delivery Date": "11/19/2014",
"Amount": "9766.10"
},
{
"Customer Name": "Rosemaria Thomann",
"Model": "Caravan",
"Color": "Violet",
"Payment Mode": "Net Banking",
"Delivery Date": "02/08/2014",
"Amount": "7685.49"
},
{
"Customer Name": "Lyell Fuentez",
"Model": "Bravada",
"Color": "Violet",
"Payment Mode": "Debit Card",
"Delivery Date": "08/05/2016",
"Amount": "18012.45"
},
{
"Customer Name": "Raynell Layne",
"Model": "Colorado",
"Color": "Pink",
"Payment Mode": "Credit Card",
"Delivery Date": "05/30/2016",
"Amount": "2785.49"
},
{
"Customer Name": "Raye Whines",
"Model": "4Runner",
"Color": "Red",
"Payment Mode": "Debit Card",
"Delivery Date": "12/10/2016",
"Amount": "9967.74"
},
{
"Customer Name": "Virgina Aharoni",
"Model": "TSX",
"Color": "Pink",
"Payment Mode": "Cash On Delivery",
"Delivery Date": "10/23/2014",
"Amount": "5584.33"
},
{
"Customer Name": "Peta Cheshir",
"Model": "Pathfinder",
"Color": "Red",
"Payment Mode": "Net Banking",
"Delivery Date": "12/24/2015",
"Amount": "5286.53"
},
{
"Customer Name": "Jule Urion",
"Model": "Charger",
"Color": "Violet",
"Payment Mode": "Debit Card",
"Delivery Date": "11/20/2013",
"Amount": "13511.91"
},
{
"Customer Name": "Lew Gilyatt",
"Model": "Bonneville",
"Color": "Crimson",
"Payment Mode": "Credit Card",
"Delivery Date": "11/19/2013",
"Amount": "6498.19"
},
{
"Customer Name": "Jobey Fortun",
"Model": "B-Series",
"Color": "Blue",
"Payment Mode": "Net Banking",
"Delivery Date": "10/30/2014",
"Amount": "10359.67"
},
{
"Customer Name": "Blondie Crump",
"Model": "Voyager",
"Color": "Turquoise",
"Payment Mode": "Credit Card",
"Delivery Date": "04/06/2018",
"Amount": "8118.39"
},
{
"Customer Name": "Florentia Binns",
"Model": "Grand Prix",
"Color": "Orange",
"Payment Mode": "Cash On Delivery",
"Delivery Date": "10/13/2016",
"Amount": "10204.37"
},
{
"Customer Name": "Jaquelin Galtone",
"Model": "Sunbird",
"Color": "Red",
"Payment Mode": "Net Banking",
"Delivery Date": "10/22/2013",
"Amount": "6528.06"
},
{
"Customer Name": "Hakeem Easseby",
"Model": "Mirage",
"Color": "Crimson",
"Payment Mode": "Debit Card",
"Delivery Date": "9/12/2014",
"Amount": "5619.25"
},
{
"Customer Name": "Nickolaus Gidman",
"Model": "XK",
"Color": "Orange",
"Payment Mode": "Debit Card",
"Delivery Date": "05/12/2016",
"Amount": "5091.43"
},
{
"Customer Name": "Jenine Iglesia",
"Model": "Accord",
"Color": "Orange",
"Payment Mode": "Debit Card",
"Delivery Date": "09/03/2018",
"Amount": "14566.08"
},
{
"Customer Name": "Fax Witherspoon",
"Model": "Range Rover Sport",
"Color": "Orange",
"Payment Mode": "Credit Card",
"Delivery Date": "2/22/2018",
"Amount": "5284.87"
}
];/**
* Default data source
*/
export let defaultData: Object[] = [
{
"Customer Name": "Romona Heaslip",
"Model": "Taurus",
"Color": "Aquamarine",
"Payment Mode": "Debit Card",
"Delivery Date": "07/11/2015",
"Amount": "8529.22"
},
{
"Customer Name": "Clare Batterton",
"Model": "Sparrow",
"Color": "Pink",
"Payment Mode": "Cash On Delivery",
"Delivery Date": "7/13/2016",
"Amount": "17866.19"
},
{
"Customer Name": "Eamon Traise",
"Model": "Grand Cherokee",
"Color": "Blue",
"Payment Mode": "Net Banking",
"Delivery Date": "09/04/2015",
"Amount": "13853.09"
},
{
"Customer Name": "Julius Gorner",
"Model": "GTO",
"Color": "Aquamarine",
"Payment Mode": "Credit Card",
"Delivery Date": "12/15/2017",
"Amount": "2338.74"
},
{
"Customer Name": "Jenna Schoolfield",
"Model": "LX",
"Color": "Yellow",
"Payment Mode": "Credit Card",
"Delivery Date": "10/08/2014",
"Amount": "9578.45"
},
{
"Customer Name": "Marylynne Harring",
"Model": "Catera",
"Color": "Green",
"Payment Mode": "Cash On Delivery",
"Delivery Date": "7/01/2017",
"Amount": "19141.62"
},
{
"Customer Name": "Vilhelmina Leipelt",
"Model": "7 Series",
"Color": "Goldenrod",
"Payment Mode": "Credit Card",
"Delivery Date": "12/20/2015",
"Amount": "6543.30"
},
{
"Customer Name": "Barby Heisler",
"Model": "Corvette",
"Color": "Red",
"Payment Mode": "Credit Card",
"Delivery Date": "11/24/2014",
"Amount": "13035.06"
},
{
"Customer Name": "Karyn Boik",
"Model": "Regal",
"Color": "Indigo",
"Payment Mode": "Debit Card",
"Delivery Date": "05/12/2014",
"Amount": "18488.80"
},
{
"Customer Name": "Jeanette Pamplin",
"Model": "S4",
"Color": "Fuscia",
"Payment Mode": "Net Banking",
"Delivery Date": "12/30/2014",
"Amount": "12317.04"
},
{
"Customer Name": "Cristi Espinos",
"Model": "TL",
"Color": "Aquamarine",
"Payment Mode": "Credit Card",
"Delivery Date": "12/18/2013",
"Amount": "6230.13"
},
{
"Customer Name": "Issy Humm",
"Model": "Club Wagon",
"Color": "Pink",
"Payment Mode": "Cash On Delivery",
"Delivery Date": "02/02/2015",
"Amount": "9709.49"
},
{
"Customer Name": "Tuesday Fautly",
"Model": "V8 Vantage",
"Color": "Crimson",
"Payment Mode": "Debit Card",
"Delivery Date": "11/19/2014",
"Amount": "9766.10"
},
{
"Customer Name": "Rosemaria Thomann",
"Model": "Caravan",
"Color": "Violet",
"Payment Mode": "Net Banking",
"Delivery Date": "02/08/2014",
"Amount": "7685.49"
},
{
"Customer Name": "Lyell Fuentez",
"Model": "Bravada",
"Color": "Violet",
"Payment Mode": "Debit Card",
"Delivery Date": "08/05/2016",
"Amount": "18012.45"
},
{
"Customer Name": "Raynell Layne",
"Model": "Colorado",
"Color": "Pink",
"Payment Mode": "Credit Card",
"Delivery Date": "05/30/2016",
"Amount": "2785.49"
},
{
"Customer Name": "Raye Whines",
"Model": "4Runner",
"Color": "Red",
"Payment Mode": "Debit Card",
"Delivery Date": "12/10/2016",
"Amount": "9967.74"
},
{
"Customer Name": "Virgina Aharoni",
"Model": "TSX",
"Color": "Pink",
"Payment Mode": "Cash On Delivery",
"Delivery Date": "10/23/2014",
"Amount": "5584.33"
},
{
"Customer Name": "Peta Cheshir",
"Model": "Pathfinder",
"Color": "Red",
"Payment Mode": "Net Banking",
"Delivery Date": "12/24/2015",
"Amount": "5286.53"
},
{
"Customer Name": "Jule Urion",
"Model": "Charger",
"Color": "Violet",
"Payment Mode": "Debit Card",
"Delivery Date": "11/20/2013",
"Amount": "13511.91"
},
{
"Customer Name": "Lew Gilyatt",
"Model": "Bonneville",
"Color": "Crimson",
"Payment Mode": "Credit Card",
"Delivery Date": "11/19/2013",
"Amount": "6498.19"
},
{
"Customer Name": "Jobey Fortun",
"Model": "B-Series",
"Color": "Blue",
"Payment Mode": "Net Banking",
"Delivery Date": "10/30/2014",
"Amount": "10359.67"
},
{
"Customer Name": "Blondie Crump",
"Model": "Voyager",
"Color": "Turquoise",
"Payment Mode": "Credit Card",
"Delivery Date": "04/06/2018",
"Amount": "8118.39"
},
{
"Customer Name": "Florentia Binns",
"Model": "Grand Prix",
"Color": "Orange",
"Payment Mode": "Cash On Delivery",
"Delivery Date": "10/13/2016",
"Amount": "10204.37"
},
{
"Customer Name": "Jaquelin Galtone",
"Model": "Sunbird",
"Color": "Red",
"Payment Mode": "Net Banking",
"Delivery Date": "10/22/2013",
"Amount": "6528.06"
},
{
"Customer Name": "Hakeem Easseby",
"Model": "Mirage",
"Color": "Crimson",
"Payment Mode": "Debit Card",
"Delivery Date": "9/12/2014",
"Amount": "5619.25"
},
{
"Customer Name": "Nickolaus Gidman",
"Model": "XK",
"Color": "Orange",
"Payment Mode": "Debit Card",
"Delivery Date": "05/12/2016",
"Amount": "5091.43"
},
{
"Customer Name": "Jenine Iglesia",
"Model": "Accord",
"Color": "Orange",
"Payment Mode": "Debit Card",
"Delivery Date": "09/03/2018",
"Amount": "14566.08"
},
{
"Customer Name": "Fax Witherspoon",
"Model": "Range Rover Sport",
"Color": "Orange",
"Payment Mode": "Credit Card",
"Delivery Date": "2/22/2018",
"Amount": "5284.87"
}
];