Using the Spread.Sheets Custom Slicer to Create a Filter Panel

  • 0 Comments

Slicers provide an easy and efficient way for users to filter table data quickly. SpreadJS slicers allow the developer to customize the types of filters they want to provide to the users and these slicers can be used with different types of UI and JavaScript libraries. A very common use of slicers are the filter panels you see on common product websites such as Amazon.com. When you narrow your list down by price and brand, for example, you are using a slicer. In this article, we use a SpreadJS custom slicer to create a filter panel that helps us narrow a list of classic cars. We used an ASP.NET Web project in Visual Studio, but you can use the development environment of your choice. The final result will look like this: The final result of our project will be a table with a filter panel. The final result of our project will be a table with a filter panel. To download the sample used in this blog, click here: SpreadJS Custom Slicer To read more about using slicers in SpreadJS, click here: http://spread.grapecity.com/Demos/JS/TutorialSample/#/samples/generalSlicerData

Set Up the Project

Create a new empty ASP.NET Web project, and add a new html file in Visual Studio 2015. In this file, add references to the SpreadJS script and css files, as well as the noUiSlider and jQuery script files:


<!DOCTYPE html>  
<html xmlns="http://www.w3.org/1999/xhtml">  
<head>  
    <meta charset="utf-8" />  
    <title>SpreadJS Custom Slicer</title>  

    <link href="http://cdn.grapecity.com/spreadjs/hosted/css/gcspread.sheets.excel2013white.9.40.20153.0.css" rel="stylesheet" type="text/css" />  
    <script type="text/javascript" src="http://cdn.grapecity.com/spreadjs/hosted/scripts/gcspread.sheets.all.9.40.20153.0.min.js"></script>  

    <script src="https://code.jquery.com/jquery-2.1.1.min.js" type="text/javascript"></script>  

    <link type="text/css" href="https://cdnjs.cloudflare.com/ajax/libs/noUiSlider/8.1.0/nouislider.min.css" rel="stylesheet" />  
    <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/noUiSlider/8.1.0/nouislider.min.js"></script>  

</head>  
<body>  
</body>  
</html>

Once this is done, add a script to the page to contain the slicer code as well as a div element that represents the Spread component:


<script>  
    $(document).ready(function () {  
    }  
</script>  
</head>  
<body>  
    <div id="spreadSheet" style="width: 100%; height: 550px; border: 1px solid gray"></div>  
</body>

Load Data

Define a JavaScript function that loads a JSON file via an XMLHttpRequest:


function loadJSON(file, callback) {  
    var xobj = new XMLHttpRequest();  
    xobj.overrideMimeType("application/json");  
    xobj.open('GET', file, true);  
    xobj.onreadystatechange = function () {  
        if (xobj.readyState == 4 && xobj.status == "200") {  
            // Required use of an anonymous callback as .open will NOT return a value but simply returns undefined in asynchronous mode  
            callback(xobj.responseText);  
        }  
    };  
    xobj.send(null);  
}

After that, call that function and create a new function to pass in as the callback. In this callback function, define the variables that will be passed in as column names and data:


loadJSON("ClassicCars.json", function (response) {  

    var actual_JSON = JSON.parse(response);  

    columnNames = ["Name", "Miles/Gallon", "Cylinders", "Displacement", "Horsepower", "Weight (lbs)", "Acceleration", "Year", "Origin", "Image"];  
    var json\_data = actual\_JSON["Classic Cars"];  
    var datas = new Array(json_data.length);  
    for (var i in json_data) {  
        datas[i] = [];  
        for (var j in json_data[i]) {  
            datas[i].push(json_data[i][j]);  
        }  
    }  
});

Programmatically create a table that will be appended to the spreadsheet div in order to contain and format the data:


// add data source  
function init(datas, columnNames) {  
   var tableStr = "<table id='spreadTable' border='1' cellpadding='0' cellspacing='0' style='width:100%;'><tr>";  
    for (var i = 0; i < columnNames.length -1; i++) {  
        tableStr += "<th style='height: 30px; width:";  
        if (i === 0)  
             tableStr += "150";  
        else if (i === 1)  
              tableStr += "110";  
        else if (i === 2)  
              tableStr += "100";  
        else if (i === 3)  
              tableStr += "140";  
        else if (i === 4)  
              tableStr += "120";  
        else if (i === 5)  
              tableStr += "110";  
        else if (i === 6)  
              tableStr += "140";  
        else if (i === 7)  
              tableStr += "80";  
        else if (i === 8)  
              tableStr += "80";  
        else if (i === 9)  
              tableStr += "330";  
        tableStr += "px'>" + columnNames[i] + "</th>";  
    }  
    tableStr += "";  

    for (var i = 0; i < datas.length; i++) {  
        tableStr += "<tr>";  
        for (var j = 0; j < datas[i].length - 1; j++) {  
             tableStr += "<td>" + datas[i][j] + "</td>";  
        }  
        tableStr += "</tr>";  
    }  
    tableStr += "</table>";  
    var $listTable = $(tableStr);  
    $("#spreadsheet").append($listTable);  

    formatTable();  
}  
function formatTable() {  
    var spreadTable = document.getElementById("spreadTable");  

    for (var i = 0; i < spreadTable.rows[0].cells.length; i++) {  
         spreadTable.rows[0].cells[i].style.backgroundColor = "Green";  
         spreadTable.rows[0].cells[i].style.color = "White";  
    }  
}

The table without slicers. The table without slicers.

Create Slicers

In this sample, there are two custom slicers that are used for four different properties. The first slicer is for selecting a set number of variables to filter by using check boxes:


// create custom slicer  
function CheckBoxSlicer(container) {  
    this.container = container;  
}  
CheckBoxSlicer.prototype = new GcSpread.Slicer.SlicerBase();  
CheckBoxSlicer.prototype.constructor = CheckBoxSlicer;  
CheckBoxSlicer.prototype.onDataLoaded = function () {  
    var self = this;  
    $(self.container).append($("<span>" + self.columnName + ":</span>"));  
    // get column data and build slicer UI.  
    var datas = self.exclusiveDatas, domString = "";  
    for (var i = 0; i < datas.length; i++) {  
        domString += '<input type="checkbox" name="' + self.columnName + '" value="' + datas[i] + '" >' + '<span>' + datas[i] + '</span>';  
    }  
    $(self.container).append($(domString));  
    $("[name='" + self.columnName + "']").change(function () {  
        var slicer = self,  
            exclusiveData = slicer.slicerData.getExclusiveData(slicer.columnName),  
            parent = $(this).parent(),  
            children = parent.children(),  
            indexes = [];  
        for (var i = 0, length = children.length; i < length; i++) {  
            if (children[i].checked) {  
                var value = children[i].value;  
                if (!isNaN(parseInt(value))) {  
                    value = parseInt(value);  
                }  
                indexes.push(exclusiveData.indexOf(value));  
            }  
        }  
        // respond UI event and invoke doFilter function and doUnfilter function.  
        if (indexes.length === 0) {  
            slicer.slicerData.doUnfilter(slicer.columnName);  
        } else {  
            slicer.slicerData.doFilter(slicer.columnName, { exclusiveRowIndexes: indexes });  
        }  
    });  
};  
// get the filtered result and update slicer UI.  
CheckBoxSlicer.prototype.onFiltered = function () {  
    var slicerdata = this.slicerData;  
    var filteredRowIndexs = slicerdata.getFilteredRowIndexes();  
    var trs = $("#spreadSheet").find("tr");  
    for (var i = 0; i < slicerdata.data.length; i++) {  
        if (filteredRowIndexs.indexOf(i) !== -1) {  
            $(trs[i + 1]).show();  
        } else {  
            $(trs[i + 1]).hide();  
        }  
    }  
};

The second slicer is for selecting a range of data with sliders:


function NumberSlicer(container) {  
    this.container = container;  
}  
NumberSlicer.prototype = new GcSpread.Slicer.SlicerBase();  
NumberSlicer.prototype.constructor = CheckBoxSlicer;  
NumberSlicer.prototype.onDataLoaded = function () {  
    var self = this;  
    var connectSlider = $("<div style='width:150px; margin-top: 16px; margin-left: 10px;'></div>")[0];  
    var min = 46;  
    var max = 198;  
    if (self.columnName === "Year") {  
        max = 1982;  
        min = 1970;  
    } else if (self.columnName === "Miles/Gallon") {  
        max = 47;  
        min = 11;  
    }  
    noUiSlider.create(connectSlider, {  
        start: [min, max],  
        connect: false,  
        range: {  
            'min': min,  
            'max': max  
        }  
    });  
    var connectBar = document.createElement('div'),  
        connectBase = connectSlider.getElementsByClassName('noUi-base')[0];  

    // Give the bar a class for styling and add it to the slider.  
    connectBar.className += 'connect';  
    connectBase.appendChild(connectBar);  
    $(self.container).append($("<span>" + self.columnName + ":</span>"));  
    var $textSpan = $("<span></span>");  
    $(self.container).append($textSpan);  
    $(self.container).append(connectSlider);  
    var connectHandles = connectSlider.getElementsByClassName('noUi-origin');  

    connectSlider.noUiSlider.on('update', function (values, handle) {  
        // Pick left for the first handle, right for the second.  
        var side = handle ? 'right' : 'left',  
        // Get the handle position and trim the '%' sign.  
            offset = (connectHandles[handle].style.left).slice(0, -1);  
        // Right offset is 100% - left offset  
        if (handle === 1) {  
            offset = 100 - offset;  
        }  
        connectBar.style[side] = offset + '%';  

        var min = parseInt(values[0]), max = parseInt(values[1]);  
        $textSpan.text(min + "-" + max);  
        var slicer = self, exclusiveData = self.slicerData.getExclusiveData(slicer.columnName), indexes = [];  

        for (var i = 0, length = exclusiveData.length; i < length; i++) {  
            if (exclusiveData[i] >= min && exclusiveData[i] <= max) {  
                indexes.push(i)  
            }  
        }  
        slicer.slicerData.doFilter(slicer.columnName, { exclusiveRowIndexes: indexes })  
    });  
};  
NumberSlicer.prototype.onFiltered = function () {  
    var slicerdata = this.slicerData;  
    var filteredRowIndexs = slicerdata.getFilteredRowIndexes();  
    var trs = $("#spreadsheet").find("tr");  
    for (var i = 0; i < slicerdata.data.length; i++) {  
        if (filteredRowIndexs.indexOf(i) !== -1) {  
            $(trs[i + 1]).show();  
        } else {  
            $(trs[i + 1]).hide();  
        }  
    }  
};

Place Slicers

In the body of the HTML page, add a div for each slicer:


<body>  
    <h1>Classic Cars 1970 - 1982</h1>  
    <div id="sample">  
        <div id="slicerContainer" style="height: 120px; width: 500px; column-count: 2; border: 1px solid gray">  
            <div id="slicer_Cylinders" style="margin-left: 20px; margin-top: 10px; height: 50px;"></div>  
            <div id="slicer_Horsepower" style="margin-left: 20px; height: 50px;"></div>  
            <div id="slicer_Year" style="margin-top: 10px; margin-left: 20px; height: 50px;"></div>  
            <div id="slicer_MPG" style="margin-top: 10px; margin-left: 20px; height: 50px;"></div>  
        </div>  
        <hr />  
        <div id="spreadsheet" style="width: 100%; height: 550px; border: 1px solid gray"></div>  
    </div>  
</body>

Then, in the loadJSON callback, attach the slicers to the div elements after the call to init():


// Create GeneralSlicerData  
var dataSource = new GcSpread.Slicer.GeneralSlicerData(datas, columnNames);  

// Attach slicers to GeneralSlicerData  
var cylindersSlicer = new CheckBoxSlicer($("#slicer_Cylinders")[0]);  
cylindersSlicer.setData(dataSource, "Cylinders");  
var horsepowerSlicer = new NumberSlicer($("#slicer_Horsepower")[0]);  
horsepowerSlicer.setData(dataSource, "Horsepower");  
var yearSlicer = new NumberSlicer($("#slicer_Year")[0]);  
yearSlicer.setData(dataSource, "Year");  
var mpgSlicer = new NumberSlicer($("#slicer_MPG")[0]);  
mpgSlicer.setData(dataSource, "Miles/Gallon");

SpreadJS Custom Slicer Figure 1 In this tutorial, SpreadJS slicers were used to customize how data is filtered by the user. The data was loaded from a JSON file, and then that data was used to populate a table which could be filtered using the SpreadJS slicers. Slicers allow users to filter data quickly without needing a drop-down menu. To learn more about SpreadJS and to download a trial, click here: http://spread.grapecity.com/spreadJS/

MESCIUS inc.