Create a slicer data which inherits from GeneralSlicerData.
Overwrite attachListener and detachListener functions.
Overwrite the onFiltered function to implement your filter logic.
Create your own slicer.
Create your onDataLoaded function.
Create your onFiltered function.
Create your slicer data and attach it to your slicer.
<template>
<div class="sample-tutorial">
<gc-spread-sheets class="sample-spreadsheets" @workbookInitialized="initSpread">
</gc-spread-sheets>
<div class="options-container">
<div id="workers" class="sample-chart"></div>
<div id="yearsOnList" class="sample-chart"></div>
</div>
</div>
</template>
<script setup>
import '@mescius/spread-sheets-vue'
import { ref, nextTick } from "vue";
import GC from "@mescius/spread-sheets";
const spreadRef = ref(null);
let initSpread = function (spread) {
spreadRef.value = spread;
let sd = data;
if (sd.length > 0) {
if (sd) {
if (!spread) {
return;
}
let sheet = spread.getActiveSheet();
initSlicer(sheet, sd);
}
}
}
class ChartSlicerData extends GC.Spread.Slicers.GeneralSlicerData {
constructor(datas, columnNames) {
super(datas, columnNames);
this.listeners = [];
}
onFiltered() {
let self = this;
self.listeners.forEach(function (listener) {
listener.onFiltered({
dataIndexes: self.getFilteredRowIndexes()
});
listener.refreshList();
})
}
attachListener(listener) {
this.listeners.push(listener);
}
detachListener(listener) {
for (var i = 0; i < this.listeners.length; i++) {
if (this.listeners[i] === listener) {
this.listeners.splice(i);
break;
}
}
}
}
class ChartSlicer {
constructor(container, columnName, sheet, slicerData, title, legend) {
this.sheet = sheet;
this.data = slicerData;
this.container = container;
this.columnName = columnName;
this.slicerData = slicerData;
this.title = title;
this.legend = legend;
this.xAxis = [];
this.series = [];
this.verticalChart = null;
this.slicerData.attachListener(this);
this.onDataLoaded();
}
getXAxis = function () {
let xAxis = [];
let exclusiveData = this.slicerData.getExclusiveData(this.columnName);
let maxValue = getMaxInArray(exclusiveData);
if (this.columnName === "years on list") {
let xAxisCount = 6,
xTick = Math.floor(maxValue / xAxisCount);
for (let i = 1; i <= xAxisCount; i++) {
xAxis.push(xTick * i);
}
} else if (this.columnName === "workers") {
if (typeof maxValue === "number") {
let base = 1;
while (maxValue > 1) {
xAxis.push(Math.pow(10, base));
base++;
maxValue = parseInt(maxValue / 10);
}
}
}
return xAxis;
};
getSeriesByXAxisScope(xAxis, columnName, slicerData) {
if (!xAxis || xAxis.length <= 0 || !slicerData) {
return;
}
let series = [],
data = slicerData.getData(columnName),
filterdRowIndexes = slicerData.getFilteredRowIndexes();
for (let x = 0, len1 = xAxis.length; x < len1; x++) {
let scopeStart = 0;
if (x > 0) {
scopeStart = xAxis[x - 1];
}
let scopeEnd = xAxis[x];
let seriesItem = 0;
for (let i = 0, len = filterdRowIndexes.length; i < len; i++) {
let rowIndex = filterdRowIndexes[i],
dataItem = data[rowIndex];
if (dataItem >= scopeStart && dataItem < scopeEnd) {
seriesItem++;
}
}
series.push(seriesItem);
}
return series;
}
onDataLoaded() {
let self = this;
let chartDiv = document.createElement('div'),
footerDiv = document.createElement('div');
chartDiv.id = 'chart_div';
chartDiv.style.width = '100%';
chartDiv.style.height = '90%';
footerDiv.innerHTML = '<span id="text_span"></span>' +
'<button>Remove Filter</button>';
let textSpan = footerDiv.firstChild;
let removeFilter = footerDiv.lastChild;
removeFilter.onclick = function (e) {
self.slicerData.doUnfilter(self.columnName);
footerDiv.style.display = 'none';
};
footerDiv.style.width = '100%';
footerDiv.style.height = '100%';
footerDiv.style.display = 'none';
self.container.appendChild(chartDiv);
self.container.appendChild(footerDiv);
this.xAxis = this.getXAxis();
this.series = this.getSeriesByXAxisScope(this.xAxis, this.columnName, this.slicerData);
this.verticalChart = echarts.init(chartDiv);
let option = {
title: {
subtext: this.title,
},
legend: {
data: [this.legend]
},
grid: {
x: "15%",
width: "80%",
x2: "5%",
y: "30%",
height: "59%",
y2: "15%"
},
xAxis: [{
type: 'category',
data: this.xAxis,
axisTick: {
show: true,
length: 2,
lineStyle: {
color: "#333",
width: 1
}
}
}],
yAxis: [{
type: 'value'
}],
series: [{
name: this.legend,
type: "bar",
data: this.series,
itemStyle: {
normal: {
color: "#9fd5b7",
label: {
show: true
}
},
emphasis: {
color: "#ababab"
}
}
}]
};
this.verticalChart.setOption(option);
function clickHandler(param) {
let dataIndex = param.dataIndex;
let startScope = 0,
endScope = self.xAxis[dataIndex];
if (dataIndex > 0) {
startScope = self.xAxis[dataIndex - 1];
}
let _footerDiv = self.container.lastChild;
_footerDiv.firstChild.innerText = self.title + ': ' + startScope + ' to ' + endScope;
_footerDiv.style.display = 'block';
let indexes = [];
let exclusiveData = self.slicerData.getExclusiveData(self.columnName);
for (let i = 0, len = exclusiveData.length; i < len; i++) {
if (exclusiveData[i] >= startScope && exclusiveData[i] < endScope) {
indexes.push(i);
}
}
self.slicerData.doFilter(self.columnName, {
exclusiveRowIndexes: indexes
});
}
this.verticalChart.on("click", clickHandler);
}
refreshList() {
this.verticalChart.setSeries([{
data: this.getSeriesByXAxisScope(this.verticalChart.getOption().xAxis[0].data, this.columnName, this.slicerData)
}]);
}
onFiltered(data) {
let sheet = this.sheet;
sheet.suspendPaint();
sheet.suspendEvent();
let filteredRowIndexs = data.dataIndexes;
for (let r = 0, len = sheet.getRowCount(); r < len; r++) {
sheet.setRowVisible(r, false);
}
for (let i = 0, len = filteredRowIndexs.length; i < len; i++) {
sheet.setRowVisible(filteredRowIndexs[i], true);
}
sheet.resumeEvent();
sheet.resumePaint();
}
}
const initSlicer = (sheet, datas) => {
sheet.suspendPaint();
sheet.name("The 2014 Inc. 5000.");
sheet.setDataSource(datas);
sheet.setColumnCount(20);
sheet.resumePaint();
let ret = parseJSONToArray(datas);
let slicerData = new ChartSlicerData(ret.arrayDatas, ret.columnNames);
let yearsOnListChart = new ChartSlicer(document.getElementById('yearsOnList'), "years on list", sheet, slicerData, "Years on the List", "Number of companies");
let workersChart = new ChartSlicer(document.getElementById('workers'), "workers", sheet, slicerData, "Workers", "Number of companies");
}
const getMaxInArray = (array) => {
if (!array || array.length <= 0) {
return;
}
var max = array[0];
for (var i = 1, len = array.length; i < len; i++) {
if (array[i] > max) {
max = array[i];
}
}
return max;
}
const parseJSONToArray = (datas) => {
if (!datas || datas.length <= 0) {
return;
}
var columnNames = Object.keys(datas[0]),
keyCount = columnNames.length,
arrayDatas = [];
for (var i = 0, len = datas.length; i < len; i++) {
var data = datas[i];
if (data) {
var dataItem = [];
arrayDatas.push(dataItem);
for (var j = 0; j < keyCount; j++) {
dataItem.push(data[columnNames[j]]);
}
}
}
return {
columnNames: columnNames,
arrayDatas: arrayDatas
};
}
</script>
<style scoped>
#app {
height: 100%;
}
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: calc(100% - 300px);
height: 100%;
overflow: auto;
float: left;
}
.options-container {
float: right;
width: 290px;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
overflow: auto;
}
.sample-chart {
width: 100%;
height: 48%;
box-sizing: border-box;
}
button {
background: #9fd5b7;
border: none;
border-radius: 0;
margin-left: 3px;
}
button:hover {
background: #ababab;
border: none;
border-radius: 0;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
</style>
<!DOCTYPE html>
<html lang="en" style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<title>SpreadJS VUE</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css"
href="$DEMOROOT$/en/vue3/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/en/vue3/node_modules/systemjs/dist/system.src.js"></script>
<script src="$DEMOROOT$/spread/source/js/external/echart/echarts.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/data/chartSlicer.js" type="text/javascript"></script>
<script src="./systemjs.config.js"></script>
<script src="./compiler.js" type="module"></script>
<script>
var System = SystemJS;
System.import("./src/app.js");
System.import('$DEMOROOT$/en/lib/vue3/license.js');
</script>
</head>
<body>
<div id="app"></div>
</body>
</html>
(function (global) {
SystemJS.config({
transpiler: 'plugin-babel',
babelOptions: {
es2015: true
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
packageConfigPaths: [
'./node_modules/*/package.json',
"./node_modules/@mescius/*/package.json",
"./node_modules/@babel/*/package.json",
"./node_modules/@vue/*/package.json"
],
map: {
'@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js',
'@mescius/spread-sheets-shapes': 'npm:@mescius/spread-sheets-shapes/index.js',
'@mescius/spread-sheets-slicers': 'npm:@mescius/spread-sheets-slicers/index.js',
'@mescius/spread-sheets-vue': 'npm:@mescius/spread-sheets-vue/index.js',
'vue': "npm:vue/dist/vue.esm-browser.js",
'tiny-emitter': 'npm:tiny-emitter/index.js',
'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js',
"systemjs-babel-build": "npm:systemjs-plugin-babel/systemjs-babel-browser.js",
},
meta: {
'*.css': { loader: 'systemjs-plugin-css' },
'*.vue': { loader: "../plugin-vue/index.js" }
}
});
})(this);
