Slicers are a newly added feature to Spread.Sheets that gives users the ability to filter their table data in a new way. With SpreadJS V11, we have also added chart support, which can be combined with slicers to give a better visual representation of filtered data.
Download the sample
Setup
Create an HTML file and add the Spread.Sheets script and style references. For this page, we will need to reference the main Spread.Sheet script as well as the chart script:
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta charset="utf-8" />
<title>Spread HTML Page</title>
<link href="http://cdn.grapecity.com/spreadjs/hosted/css/gc.spread.sheets.excel2013white.11.0.0.css" rel="stylesheet" />
<script src="http://cdn.grapecity.com/spreadjs/hosted/scripts/gc.spread.sheets.all.11.0.0.min.js" type="text/javascript"></script>
<script src="http://cdn.grapecity.com/spreadjs/hosted/scripts/plugins/gc.spread.sheets.charts.11.0.0.min.js" type="text/javascript"></script>
</head>
<body>
</body>
</html>
Then add a DIV element to host the Spread.Sheets instance:
<body>
<div id="ss" style="position: absolute; width: 98%; height: 80%; border: 1px solid gray;"></div>
</body>
Now we can initialize the Spread component. For this, we will separate the process into 4 functions: the window’s onload function, initSpread, initSheet, and initChart. The onload function will be the starting function:
window.onload = function () {
var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 1 });
var activeSheet = spread.getActiveSheet();
spread.options.tabStripRatio = 0.6;
initSpread(spread);
}
That function calls the initSpread function, which we will use to get the active sheet and then initialize the sheet and the chart:
function initSpread(spread) {
var sheet = spread.getActiveSheet();
spread.suspendPaint();
initSheet(sheet);
initChart(sheet);
spread.resumePaint();
}
When initiating the sheet, we will want to add a data-bound table. In this sample, the data is in a separate JavaScript file, so reference that in the page:
<script src="./data/data.js" type="text/javascript"></script>
Now we can create a table and we can use that function to load the data and bind it to a table in the initSheet function:
function initSheet(sheet) {
sheet.suspendPaint();
sheet.name("State Resident Population 2016");
sheet.setRowCount(69);
sheet.setColumnCount(17);
// Create the table and set the array as the data source
sheet.tables.add("table1", 16, 0, data.length, data[0].length);
sheet.setArray(16, 0, data);
// Set decimal places to 0
for (var r = 16; r <= 67; r++) {
for (var c = 1; c <= 3; c++) {
sheet.getCell(r, c).formatter('#');
}
}
sheet.resumePaint();
}
Add Slicer
The data in this sample is census data of the age of the population of the United States, organized by State. Since there is a lot of data to look at, having a slicer to filter out the specific data that you want can be really helpful. We can add a slicer with just a few lines of code in the initSheet function:
// Add a slicer for the table to filter by "State"
var slicer1 = sheet.slicers.add("slicer1", "table1", "State");
slicer1.position(new GC.Spread.Sheets.Point(830, 25));
// Show the data items in their original positions
slicer1.showNoDataItemsInLast(false);
Add Chart
Now we can add a chart to Spread. If the chart is bound to the table, then it will automatically be updated whenever the slicer is changed. In the initChart function we will add the table-bound chart, which will add the data to the chart. In order to ensure the series names are added, be sure to use “table1[#all]” as the table name to bind to.
function initChart(sheet) {
// Add the chart
var chart = sheet.charts.add('Chart1', GC.Spread.Sheets.Charts.ChartType.columnClustered, 0, 5, 800, 300, "table1[#all]");
chart.title({ text: "State Resident Population 2016", color: "darkblue", fontSize: "17pt" });
}
Finally, we can add some code to get rid of gridlines and headers to make it look more presentable (add this code to the initSheet function):
sheet.options.gridline = { showVerticalGridline: false, showHorizontalGridline: false };
sheet.options.colHeaderVisible = false;
sheet.options.rowHeaderVisible = false;
By combining SJS built-in charts and slicers, developers can provide their users with powerful data analysis tools with just a small amount of code.
Download SpreadJS 11
