Spread.Sheets Documentation
Creating a Custom Slicer
Spread.Sheets Documentation > Developer's Guide > Managing the User Interface > Using a Slicer > Creating a Custom Slicer

You can create a custom slicer with the GeneralSlicerData class or the TableSlicerData class.

The GeneralSlicerData class is used to process data for a two-dimensional array. The class provides properties and methods that you can use to filter the data and get the filtered result. You can attach multiple slicers to a custom slicer. Each slicer can filter one column of data. After the filter is complete, the other slicers are notified by the GeneralSlicerData class. The slicers then can get the filtered result from GeneralSlicerData and update their UI.

The slicer works with the GeneralSlicerData class based on the following steps:

  1. Create a new GeneralSlicerData with your data.
  2. Create a new custom slicer and attach it to GeneralSlicerData.
  3. Get the column data from GeneralSlicerData and build the slicer UI.
  4. Respond to the UI events and invoke the GeneralSlicerData doFilter method.
  5. Get the filtered result from GeneralSlicerData and update the slicer UI.

You can get all the data for a specific column with the getData method. You can get exclusive (non-repeating data) with the getExclusiveData method. You can filter a column with the doFilter method or clear the filter information with the doUnfilter method. For a complete list of methods, refer to the GeneralSlicerData class.

You can attach a listener to GeneralSlicerData with the attachListener function and detach it with the detachListener function.

The following image displays a custom slicer.

Using Code

This example creates a custom slicer.

JavaScript
Copy Code

<!DOCTYPE html>
 <html>
 <head>
     <title>Spread.Sheets Sample</title>
     <link href="./css/gc.spread.sheets.excel2013white.11.0.0.css" rel="stylesheet" type="text/css" />
     <script src="http://code.jquery.com/jquery-2.0.2.js" type="text/javascript"></script>
     <script src="./scripts/gc.spread.sheets.all.11.0.0.min.js"></script>

    <script type="text/javascript">
         //Define data source.
         columnNames = ["Name", "City", "Birthday"];
         data = [
             ["Bob", "NewYork", "1968/6/8"],
             ["Betty", "Washington", "1972/7/3"],
             ["Alice", "NewYork", "1964/3/2"]];

        //Define custom slicer.
         function MySlicer(container) {
             this.container = container;
             this.slicerData = null;
             this.columnName = null;
         }
         MySlicer.prototype.setData = function (slicerData, columnName) {
             this.slicerData = slicerData;
             this.columnName = columnName;
             this.slicerData.attachListener(this);
             this.onDataLoaded();
         }
         MySlicer.prototype.onDataLoaded = function () {
             //create slicer dom tree.
             var columnName = this.columnName,
             exclusiveData = this.slicerData.getExclusiveData(columnName);
             $(this.container).append($('<span>' + this.columnName + ':</span>' + '<br />'));
             var domString = "";
             for (var i = 0; i < exclusiveData.length; i++) {
                 domString += '<input type="checkbox" name="' + columnName + '" value="' + exclusiveData[i] + '">';
                 domString += '<span>' + exclusiveData[i] + '</span>';
                 domString += '<br />';
             }
             $(this.container).append($(domString));
             //attach events to dom.
             var self = this;
             $("[name='" + self.columnName + "']").change(function () {
                 var slicer = self,
                 exclusiveData = slicer.slicerData.getExclusiveData(slicer.columnName),
                 parent = $(this).parent(),
                 items = parent.children(),
                 indexes = [];
                 for (var i = 0, length = items.length; i < length; i++) {
                     if (items[i].checked) {
                         var value = items[i].value;
                         if (!isNaN(parseInt(value))) {
                             value = parseInt(value);
                         }
                         indexes.push(exclusiveData.indexOf(value))
                     }
                 }
                 if (indexes.length === 0) {
                     slicer.slicerData.doUnfilter(slicer.columnName);
                 } else {
                     slicer.slicerData.doFilter(slicer.columnName, { exclusiveRowIndexes: indexes });
                 }
             });
         };
         MySlicer.prototype.onFiltered = function () {
             //The following is an example of showing the filtered result.
             var slicerdata = this.slicerData;
             var filteredRowIndexs = slicerdata.getFilteredRowIndexes();
             var trs = $listTable.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();
                 }
             }
         }

        //Define the show filtered result method.
         function initFilteredResultList() {
             var tableStr = "<table border='1' cellpadding='0' cellspacing='0'><tr>";
             for (var i = 0; i < columnNames.length; i++) {
                 tableStr += "<th>" + columnNames[i] + "</th>";
             }
             tableStr += "</tr>";
             for (var i = 0; i < data.length; i++) {
                 tableStr += "<tr>";
                 for (var j = 0; j < data[i].length; j++) {
                     tableStr += "<td>" + data[i][j] + "</td>";
                 }
                 tableStr += "</tr>";
             }
             tableStr += "</table>";
             $listTable = $(tableStr);
             $("#list").append($listTable);
         }

        $(document).ready(function () {
             //init filtered result list.
             initFilteredResultList();
             //create a custom slicer and add this slicer to the "slicerContainer" div.
             var slicer = new MySlicer($("#slicerContainer")[0]);
             var slicerData = new GC.Spread.Slicers.GeneralSlicerData(data, columnNames);
             slicer.setData(slicerData, "Name");
         });
     </script>

</head>
 <body>
     <div id="slicerContainer" style="border:1px solid gray;width:190px"></div>
     <hr/>
     <div id="list" style="width:300px;float:left"></div>
 </body>
 </html>

This example creates a custom slicer and uses the TableSlicerData class for data.

JavaScript
Copy Code

<!DOCTYPE html>
 <html>
 <head>
     <title>Spread.Sheets Development Sample</title>
     <link href="./css/gc.spread.sheets.excel2013white.11.0.0.css" rel="stylesheet" type="text/css" />
    <script src="http://code.jquery.com/jquery-2.0.2.js" type="text/javascript"></script>
     <script src="./scripts/gc.spread.sheets.all.11.0.0.min.js"></script>
     <script type="text/javascript">
         //Define data source.
         data = [
             { "Name": "Bob", "City": "NewYork", "Birthday": "1968/6/8" },
             { "Name": "Bob", "City": "NewYork", "Birthday": "1968/6/8" },
             { "Name": "Bob", "City": "NewYork", "Birthday": "1968/6/8" },
             { "Name": "Bob", "City": "NewYork", "Birthday": "1968/6/8" },
             { "Name": "Betty", "City": "Washington", "Birthday": "1972/7/3" },
             { "Name": "Betty", "City": "Washington", "Birthday": "1972/7/3" },
             { "Name": "Betty", "City": "Washington", "Birthday": "1972/7/3" },
             { "Name": "Alice", "City": "NewYork", "Birthday": "1964/3/2" },
             { "Name": "Alice", "City": "NewYork", "Birthday": "1964/3/2" },
             { "Name": "Alice", "City": "NewYork", "Birthday": "1964/3/2" }];

        //Define custom slicer.
         function MySlicer(container) {
             this.container = container;
             this.slicerData = null;
             this.columnName = null;
         }
         MySlicer.prototype.setData = function (slicerData, columnName) {
             this.slicerData = slicerData;
             this.columnName = columnName;
             this.slicerData.attachListener(this);
             this.onDataLoaded();
         }
         MySlicer.prototype.onDataLoaded = function () {
             //create slicer dom tree.
             var columnName = this.columnName,
             exclusiveData = this.slicerData.getExclusiveData(columnName);
             $(this.container).append($('<span>' + this.columnName + ':</span>' + '<br />'));
             var domString = "";
             for (var i = 0; i < exclusiveData.length; i++) {
                 domString += '<input type="checkbox" name="' + columnName + '" value="' + exclusiveData[i] + '">';
                 domString += '<span>' + exclusiveData[i] + '</span>';
                 domString += '<br />';
             }
             $(this.container).append($(domString));
             //attach events to dom.
             var self = this;
             $("[name='" + self.columnName + "']").change(function () {
                 var slicer = self,
                 exclusiveData = slicer.slicerData.getExclusiveData(slicer.columnName),
                 parent = $(this).parent(),
                 items = parent.children(),
                 indexes = [];
                 for (var i = 0, length = items.length; i < length; i++) {
                     if (items[i].checked) {
                         var value = items[i].value;
                         if (!isNaN(parseInt(value))) {
                             value = parseInt(value);
                         }
                         indexes.push(exclusiveData.indexOf(value))
                     }
                 }
                 if (indexes.length === 0) {
                     slicer.slicerData.doUnfilter(slicer.columnName);
                 } else {
                     slicer.slicerData.doFilter(slicer.columnName, { exclusiveRowIndexes: indexes });
                 }
             });
         };
         MySlicer.prototype.onFiltered = function () {
             //Sync the status if the data has been filtered by the Spread.Sheets table.
             var slicerdata = this.slicerData;
             var exclusiveIndexes = slicerdata.getFilteredIndexes(this.columnName);
             $.each($("#slicerContainer").children("input"), function (i,input) {
            });
         }

        $(document).ready(function () {            
             var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"),{sheetCount:1});
             var sheet = spread.getActiveSheet();

            //create a custom slicer and add this slicer to the "slicerContainer" div.
             var slicer = new MySlicer($("#slicerContainer")[0]);
             var table = sheet.tables.addFromDataSource("table1", 1, 1, data);
             var slicerData = table.getSlicerData();
             slicer.setData(slicerData, "Name");
        });
     </script>
</head>
 <body>
     <div id="slicerContainer" style="border:1px solid gray;width:190px"></div>
     <hr />
     <div id="ss" style="width:500px;height:500px;border:1px solid gray"></div>
 </body>
 </html>