Skip to main content

DHTMLX Spreadsheet. Filtering via API example

This demo shows how to apply data filtering programmatically in DHTMLX Spreadsheet using the setFilter() method with condition factors and exclusion rules.

Live example​

const spreadsheet = new dhx.Spreadsheet("spreadsheet", {
    
});

spreadsheet.parse(dataset);

spreadsheet.setFilter("C1:D20", [{condition: {factor: "ib", value: [5,8]}}, {exclude: [740]}]);
<!-- component container -->
<div style="height: 100%; max-width:100%" id="spreadsheet"></div>

<!-- dataset -->

<script>
const dataset = {
        styles: {
            bold: {
                "font-weight": "bold",
            },
            right: {
                "justify-content": "flex-end",
                "text-align": "right",
            },
        },
        data: [
            { cell: "a1", value: "Country", css:"bold" },
            { cell: "b1", value: "Product", css:"bold" },
            { cell: "c1", value: "Price", css:"right bold" },
            { cell: "d1", value: "Amount", css:"right bold" },
            { cell: "e1", value: "Total Price", css:"right bold" },

            { cell: "a2", value: "Ecuador" },
            { cell: "b2", value: "Banana" },
            { cell: "c2", value: 6.68, format: "currency" },
            { cell: "d2", value: 430 },
            { cell: "e2", value: 2872.4, format: "currency" },

            { cell: "a3", value: "Belarus" },
            { cell: "b3", value: "Apple" },
            { cell: "c3", value: 3.75, format: "currency" },
            { cell: "d3", value: 600 },
            { cell: "e3", value: 2250, format: "currency" },

            { cell: "a4", value: "Peru" },
            { cell: "b4", value: "Grapes" },
            { cell: "c4", value: 7.69, format: "currency" },
            { cell: "d4", value: 740 },
            { cell: "e4", value: 5690.6, format: "currency" },

            { cell: "a5", value: "Egypt" },
            { cell: "b5", value: "Orange" },
            { cell: "c5", value: 5.86, format: "currency" },
            { cell: "d5", value: 560 },
            { cell: "e5", value: 3281.6, format: "currency" },

            { cell: "a6", value: "South Africa" },
            { cell: "b6", value: "Grapefruit" },
            { cell: "c6", value: 8.58, format: "currency" },
            { cell: "d6", value: 800 },
            { cell: "e6", value: 6864, format: "currency" },

            { cell: "a7", value: "Spain" },
            { cell: "b7", value: "Lemon" },
            { cell: "c7", value: 9.12, format: "currency" },
            { cell: "d7", value: 650 },
            { cell: "e7", value: 5928, format: "currency" },

            { cell: "a8", value: "Iran" },
            { cell: "b8", value: "Pomegranate" },
            { cell: "c8", value: 9.67, format: "currency" },
            { cell: "d8", value: 300 },
            { cell: "e8", value: 2901, format: "currency" }
        ],
    };
</script>

Interactive dashboards and data exploration tools need to filter spreadsheet data without requiring users to navigate menus. Programmatic filtering lets developers apply filters based on external inputs such as search boxes, dropdowns, or URL parameters, and update the visible data instantly.

This example calls spreadsheet.setFilter("C1:D20", [{condition: {factor: "ib", value: [5,8]}}, {exclude: [740]}]) after loading the dataset. The first rule filters values in the range by the "ib" (is between) factor, while the second excludes a specific value from the filtered results.

Solution overview​

  1. Create the Spreadsheet with new dhx.Spreadsheet("spreadsheet", config) and load data
  2. Call spreadsheet.setFilter("C1:D20", [{condition: {factor: "ib", value: [5,8]}}, {exclude: [740]}])
  3. Provide one rule object per filtered column in the selected range

Key points​

  • Factor types: Available factors include "tc" (text contains), "ts" (text starts with), "te" (text ends with), "gt" (greater than), "lt" (less than), "eq" (equal), "ib" (is between), "d" (date is), "db" (date before), and more
  • Rules alignment: The rules array must align with the columns in the specified range. Use empty objects {} for columns without a filter rule

API reference​

  • setFilter(): Applies filtering rules to specified columns.
  • getFilter(): Returns current filter criteria for a sheet.

Additional resources​