使用例:外部チャートの併用

外部チャートとスライサーを連携して以下のような処理を実装することができます。

<p>GeneralSlicerDataを継承したスライサーを作成します。</p> <pre><code class="hljs js language-js"> <span class="hljs-function"><span class="hljs-keyword">function</span> <span class="hljs-title">ChartSlicerData</span>(<span class="hljs-params">datas, columnNames</span>) </span>{ GC.Spread.Slicers.GeneralSlicerData.call(<span class="hljs-keyword">this</span>, datas, columnNames); <span class="hljs-keyword">this</span>.listeners = []; } ChartSlicerData.prototype = GC.Spread.Slicers.GeneralSlicerData.prototype; ChartSlicerData.prototype.constructor = ChartSlicerData; </code></pre> <p><strong>attachListener</strong>および、<strong>dettachListener</strong>をオーバーライドします。</p> <pre><code class="hljs js language-js"> ChartSlicerData.prototype.attachListener = <span class="hljs-function"><span class="hljs-keyword">function</span> (<span class="hljs-params">listener</span>) </span>{ <span class="hljs-keyword">this</span>.listeners.push(listener); }; ChartSlicerData.prototype.dettachListener = <span class="hljs-function"><span class="hljs-keyword">function</span> (<span class="hljs-params">listener</span>) </span>{ <span class="hljs-keyword">for</span> (<span class="hljs-keyword">var</span> i = <span class="hljs-number">0</span>; i &lt; <span class="hljs-keyword">this</span>.listeners.length; i++) { <span class="hljs-keyword">if</span> (<span class="hljs-keyword">this</span>.listeners[i] === listener) { <span class="hljs-keyword">this</span>.listeners.splice(i); <span class="hljs-keyword">break</span>; } } }; </code></pre> <p><strong>onFiltered</strong>をオーバーライドし、独自のフィルタ機能を実装します。</p> <pre><code class="hljs js language-js"> ChartSlicerData.prototype.onFiltered = <span class="hljs-function"><span class="hljs-keyword">function</span> (<span class="hljs-params"></span>) </span>{ <span class="hljs-keyword">for</span> (<span class="hljs-keyword">var</span> i = <span class="hljs-number">0</span>; i &lt; <span class="hljs-keyword">this</span>.listeners.length; i++) { <span class="hljs-keyword">this</span>.listeners[i].onFiltered({ <span class="hljs-attr">dataIndexes</span>: <span class="hljs-keyword">this</span>.getFilteredRowIndexes() }); <span class="hljs-comment">// refreshLishを呼び出してチャートUIをリフレッシュする。</span> <span class="hljs-keyword">this</span>.listeners[i].refreshList(); } }; </code></pre> <p>独自のスライサを作成します。</p> <pre><code class="hljs js language-js"> <span class="hljs-function"><span class="hljs-keyword">function</span> <span class="hljs-title">ChartSlicer</span>(<span class="hljs-params">container, columnName, slicerData, title, legend</span>) </span>{ <span class="hljs-keyword">this</span>.data = slicerData; <span class="hljs-keyword">this</span>.container = container; <span class="hljs-comment">// いくつかのコードはここでは省略されていますが、右側に表示されています。</span> ... this.slicerData.attachListener(<span class="hljs-keyword">this</span>); <span class="hljs-comment">// onDataLoaded関数を呼び出してUIを構築する。</span> <span class="hljs-keyword">this</span>.onDataLoaded(); } ChartSlicer.prototype.constructor = ChartSlicer; </code></pre> <p><strong>onDataLoaded</strong>でデータのロード処理を実装します。</p> <pre><code class="hljs js language-js"> ChartSlicer.prototype.onDataLoaded = <span class="hljs-function"><span class="hljs-keyword">function</span> (<span class="hljs-params"></span>) </span>{ <span class="hljs-keyword">var</span> self = <span class="hljs-keyword">this</span>; ... $(<span class="hljs-keyword">this</span>.container).append($chartDiv); $(<span class="hljs-keyword">this</span>.container).append($footerDiv); ... var indexes = []; ... <span class="hljs-comment">// slicerDataのdoFilterメソッドを呼び出す。</span> self.slicerData.doFilter(self.columnName, { <span class="hljs-attr">exclusiveRowIndexes</span>: indexes }); ... }; </code></pre> <p><strong>onFiltered</strong>イベントを実装します。</p> <pre><code class="hljs js language-js"> ChartSlicer.prototype.onFiltered = <span class="hljs-function"><span class="hljs-keyword">function</span> (<span class="hljs-params">data</span>) </span>{ ... var filteredRowIndexs = data.dataIndexes; <span class="hljs-keyword">for</span> (<span class="hljs-keyword">var</span> r = <span class="hljs-number">0</span>, len = sheet.getRowCount() ; r &lt; len; r++) { sheet.setRowVisible(r, <span class="hljs-literal">false</span>); } <span class="hljs-keyword">for</span> (<span class="hljs-keyword">var</span> i = <span class="hljs-number">0</span>, len = filteredRowIndexs.length; i &lt; len; i++) { sheet.setRowVisible(filteredRowIndexs[i], <span class="hljs-literal">true</span>); } ... }; </code></pre> <p>スライサー用のデータを作成し、アタッチします。</p> <pre><code class="hljs js language-js"> <span class="hljs-keyword">var</span> slicerData = <span class="hljs-keyword">new</span> ChartSlicerData(ret.arrayDatas, ret.columnNames); <span class="hljs-keyword">var</span> yearsOnListChart = <span class="hljs-keyword">new</span> ChartSlicer($(<span class="hljs-string">'#yearsOnList'</span>)[<span class="hljs-number">0</span>], <span class="hljs-string">'years on list'</span>, slicerData, <span class="hljs-string">'Years on the List'</span>, <span class="hljs-string">'Number of companies'</span>); <span class="hljs-keyword">var</span> workersChart = <span class="hljs-keyword">new</span> ChartSlicer($(<span class="hljs-string">'#workers'</span>)[<span class="hljs-number">0</span>], <span class="hljs-string">'workers'</span>, slicerData, <span class="hljs-string">'Workers'</span>, <span class="hljs-string">'Number of companies'</span>); </code></pre>
window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss")); spread.options.tabStripRatio = 0.6; initSpread(spread); }; function initSpread(spread) { var sd = data; if (sd.length > 0) { if (sd) { if (!spread) { return; } var sheet = spread.getActiveSheet(); initSlicer(sheet, sd); } } } function 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 }; } function 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; } function ChartSlicerData(datas, columnNames) { GC.Spread.Slicers.GeneralSlicerData.call(this, datas, columnNames); this.listeners = []; } ChartSlicerData.prototype = GC.Spread.Slicers.GeneralSlicerData.prototype; ChartSlicerData.prototype.constructor = ChartSlicerData; ChartSlicerData.prototype.onFiltered = function () { let self = this; self.listeners.forEach(function (listener) { listener.onFiltered({dataIndexes: self.getFilteredRowIndexes()}); listener.refreshList(); }) for (var i = 0; i < this.listeners.length; i++) { } }; ChartSlicerData.prototype.attachListener = function (listener) { this.listeners.push(listener); }; ChartSlicerData.prototype.dettachListener = function (listener) { for (var i = 0; i < this.listeners.length; i++) { if (this.listeners[i] === listener) { this.listeners.splice(i); break; } } }; function ChartSlicer(container, columnName, slicerData, title, legend) { 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(); } ChartSlicer.prototype.constructor = ChartSlicer; ChartSlicer.prototype.getXAxis = function () { var xAxis = []; var exclusiveData = this.slicerData.getExclusiveData(this.columnName); var maxValue = getMaxInArray(exclusiveData); if (this.columnName === "years on list") { var xAxisCount = 6, xTick = Math.floor(maxValue / xAxisCount); for (var i = 1; i <= xAxisCount; i++) { xAxis.push(xTick * i); } } else if (this.columnName === "workers") { if (typeof maxValue === "number") { var base = 1; while (maxValue > 1) { xAxis.push(Math.pow(10, base)); base++; maxValue = parseInt(maxValue / 10); } } } return xAxis; }; ChartSlicer.prototype.getSeriesByXAxisScope = function (xAxis, columnName, slicerData) { if (!xAxis || xAxis.length <= 0 || !slicerData) { return; } var series = [], data = slicerData.getData(columnName), filterdRowIndexes = slicerData.getFilteredRowIndexes(); for (var x = 0, len1 = xAxis.length; x < len1; x++) { var scopeStart = 0; if (x > 0) { scopeStart = xAxis[x - 1]; } var scopeEnd = xAxis[x]; var seriesItem = 0; for (var i = 0, len = filterdRowIndexes.length; i < len; i++) { var rowIndex = filterdRowIndexes[i], dataItem = data[rowIndex]; if (dataItem >= scopeStart && dataItem < scopeEnd) { seriesItem++; } } series.push(seriesItem); } return series; } ChartSlicer.prototype.onDataLoaded = function () { var self = this; var 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>'; textSpan = footerDiv.firstChild; 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); var 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) { var dataIndex = param.dataIndex; var startScope = 0, endScope = self.xAxis[dataIndex]; if (dataIndex > 0) { startScope = self.xAxis[dataIndex - 1]; } var _footerDiv = self.container.lastChild; _footerDiv.firstChild.innerText = self.title + ': ' + startScope + ' to ' + endScope; _footerDiv.style.display = 'block'; var indexes = []; var exclusiveData = self.slicerData.getExclusiveData(self.columnName); for (var 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); }; ChartSlicer.prototype.refreshList = function () { this.verticalChart.setSeries([{data: this.getSeriesByXAxisScope(this.verticalChart.getOption().xAxis[0].data, this.columnName, this.slicerData)}]); }; ChartSlicer.prototype.onFiltered = function (data) { var spread = GC.Spread.Sheets.findControl(document.getElementById('ss'));; var sheet = spread.getActiveSheet(); sheet.suspendPaint(); sheet.suspendEvent(); var filteredRowIndexs = data.dataIndexes; for (var r = 0, len = sheet.getRowCount(); r < len; r++) { sheet.setRowVisible(r, false); } for (var i = 0, len = filteredRowIndexs.length; i < len; i++) { sheet.setRowVisible(filteredRowIndexs[i], true); } sheet.resumeEvent(); sheet.resumePaint(); }; function initSlicer(sheet, datas) { sheet.suspendPaint(); sheet.name("The 2014 Inc. 5000."); sheet.setDataSource(datas); sheet.setColumnCount(20); sheet.resumePaint(); var ret = parseJSONToArray(datas); var slicerData = new ChartSlicerData(ret.arrayDatas, ret.columnNames); var yearsOnListChart = new ChartSlicer(document.getElementById('yearsOnList'), "years on list", slicerData, "Years on the List", "Number of companies"); var workersChart = new ChartSlicer(document.getElementById('workers'), "workers", slicerData, "Workers", "Number of companies"); }
<!doctype html> <html style="height:100%;font-size:14px;"> <head> <meta name="spreadjs culture" content="ja-jp" /> <meta charset="utf-8" /> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <link rel="stylesheet" type="text/css" href="$DEMOROOT$/ja/purejs/node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/ja/purejs/node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/spread/source/js/external/echart/echarts.js" type="text/javascript"></script> <script src="$DEMOROOT$/ja/purejs/node_modules/@grapecity/spread-sheets-resources-ja/dist/gc.spread.sheets.resources.ja.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script> <script src="$DEMOROOT$/spread/source/data/chartSlicer.js" type="text/javascript"></script> <script src="app.js" type="text/javascript"></script> <link rel="stylesheet" type="text/css" href="styles.css"> </head> <body> <div class="sample-tutorial"> <div id="ss" class="sample-spreadsheets"></div> <div class="options-container"> <div id="workers" class="sample-chart"></div> <div id="yearsOnList" class="sample-chart"></div> </div> </div> </body> </html>
.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; }