Overview

SpreadJS provides Excel-like chart functionality. The chart feature is provided as a plugin feature, which makes it easy and flexible to add charts to SpreadJS.

<p>To use the chart feature, add the js file link into the document's head section:</p> <pre><code class="hljs"><span class="hljs-tag">&lt;<span class="hljs-name">head</span>&gt;</span> ... <span class="hljs-tag">&lt;<span class="hljs-name">script</span> <span class="hljs-attr">src</span>=<span class="hljs-string">'.../spreadjs/gc.spread.sheets.all.x.x.x.min.js'</span> <span class="hljs-attr">type</span>=<span class="hljs-string">'text/javascript'</span>&gt;</span><span class="hljs-tag">&lt;/<span class="hljs-name">script</span>&gt;</span> <span class="hljs-tag">&lt;<span class="hljs-name">script</span> <span class="hljs-attr">src</span>=<span class="hljs-string">'.../spreadjs/plugins/gc.spread.sheets.chart.x.x.x.min.js'</span> <span class="hljs-attr">type</span>=<span class="hljs-string">'text/javascript'</span>&gt;</span><span class="hljs-tag">&lt;/<span class="hljs-name">script</span>&gt;</span> <span class="hljs-tag">&lt;/<span class="hljs-name">head</span>&gt;</span> </code></pre> <p>You can create a chart with the <strong>sheet.charts.add</strong> method, as shown below:</p> <pre><code class="hljs js language-js"> sheet.charts.add(<span class="hljs-string">'Chart1'</span>, GC.Spread.Sheets.Charts.ChartType.columnClustered, <span class="hljs-number">0</span>, <span class="hljs-number">100</span>, <span class="hljs-number">400</span>, <span class="hljs-number">300</span>, <span class="hljs-string">"A1:D4"</span>); </code></pre> <p>You can get a chart in the sheet using the <strong>sheet.charts.get</strong> method, remove a chart using the <strong>sheet.charts.remove</strong> method, and/or also clear all your charts using the <strong>sheet.charts.clear</strong> method.</p> <pre><code class="hljs js language-js"> <span class="hljs-keyword">var</span> chart = sheet.charts.all()[index ?]; <span class="hljs-comment">// get chart by index</span> <span class="hljs-keyword">var</span> chart1 = sheet.charts.get(<span class="hljs-string">'Chart1'</span>) <span class="hljs-comment">// get chart by name</span> sheet.charts.remove(<span class="hljs-string">"Chart1"</span>); sheet.charts.clear(); </code></pre> <p>A chart is built with many sub-elements, and you can customize each one using the chart API:</p> <p><strong>Chart Title</strong>: The title of the chart</p> <p>You can get or set the text, the background color, the foreground color, the font, and the font size of the chart title.</p> <pre><code class="hljs js language-js"> <span class="hljs-keyword">var</span> title = chart.title(); title.text = <span class="hljs-string">"Sample"</span>; title.fontFamily = <span class="hljs-string">"Calibri Light"</span>; chart.title(title); </code></pre> <p><strong>Series</strong>: The data points of a data series that are plotted on the chart</p> <p>You can get, add, or remove a series item from the series collection of a chart, as well as customize the name, border, width, border color, y value, x value, and fill color of each series item.</p> <pre><code class="hljs js language-js"> <span class="hljs-keyword">var</span> series = chart.series(); <span class="hljs-keyword">var</span> seriesItem = series.get(index ?); chart.series.remove(index ?); chart.series.add(seriesItem); seriesItem.name = <span class="hljs-string">"Sheet1!$G$1"</span>; seriesItem.yValues = <span class="hljs-string">"Sheet1!$G$2:$G$4"</span>; seriesItem.backColor = <span class="hljs-string">"#f15253"</span>; seriesItem.border.lineType = GC.Spread.Sheets.Charts.LineDashStyle.longDash; seriesItem.border.width = <span class="hljs-number">2</span>; series.set(index ?, seriesItem); </code></pre> <p><strong>Data Labels</strong>: You can use data labels to identify the details of a data point in a data series</p> <p>You can get or set the chart’s data labels style, change the position and color of data labels, and control whether to show data labels in the chart.</p> <pre><code class="hljs js language-js"> <span class="hljs-keyword">var</span> dataLabels = chart.dataLabels(); dataLabels.showValue = <span class="hljs-literal">true</span>; dataLabels.showSeriesName = <span class="hljs-literal">false</span>; dataLabels.showCategoryName = <span class="hljs-literal">false</span>; <span class="hljs-keyword">var</span> dataLabelPosition = GC.Spread.Sheets.Charts.DataLabelPosition; dataLabels.position = dataLabelPosition.outsideEnd; <span class="hljs-comment">// this position contains many options, different chart type applies different position value</span> chart.dataLabels(dataLabels); </code></pre> <p>SpreadJS provides 28 chart types. You can get or set the current chart type using the following code:</p> <pre><code class="hljs js language-js"> <span class="hljs-keyword">var</span> chartType = chart.chartType(); </code></pre> <p>SpreadJS support display blank cells as below ways and diaplay #N/A cells as blank in chart.</p> <ul> <li><strong>gaps: Null values are skipped.</strong></li> <li><strong>zero: Null values are treated as having a value of zero.</strong></li> <li><strong>connected: Null values are skipped and the line of the area chart is connected.</strong></li> </ul> <pre><code class="hljs js language-js"> chart.displayBlanksAs(GC.Spread.Sheets.Charts.DisplayBlanksAs.gaps); chart.displayNaAsBlank(<span class="hljs-literal">true</span>); </code></pre> <p>SpreadJS support show or ignore hidden row and columns in chart.</p> <pre><code class="hljs js language-js"> chart.ignoreHidden(<span class="hljs-literal">false</span>); </code></pre>
window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), {sheetCount: 2}); initSpread(spread); _getElementById("insertChart").addEventListener('click',function () { insertChart(spread) }); _getElementById("switchRowColumn").addEventListener('click',function () { switchRowColumn(spread) }); _getElementById("removeChart").addEventListener('click',function () { removeChart(spread) }); _getElementById("removeAllCharts").addEventListener('click',function () { removeAllChart(spread) }); _getElementById("groupSelect").addEventListener('change',function () { changeTypeSelect(); }); _getElementById("displayBlanksCells").addEventListener('change',function () { displayBlanksCells(spread, this.value); }); _getElementById("showNAAsBlanks").addEventListener('click',function () { showNAAsBlanks(spread, this.checked); }); _getElementById("ignoreHidden").addEventListener('click',function () { ignoreHiddenRowAndColumn(spread, this.checked); }); changeTypeSelect(); }; var chartType = [ [{ typeDesc: 'Clustered Column', type:GC.Spread.Sheets.Charts.ChartType.columnClustered },{ typeDesc: 'Stacked Column', type:GC.Spread.Sheets.Charts.ChartType.columnStacked },{ typeDesc: '100% Stacked Column', type:GC.Spread.Sheets.Charts.ChartType.columnStacked100 }], [{ typeDesc: 'Line', type:GC.Spread.Sheets.Charts.ChartType.line },{ typeDesc: 'Stacked Line', type:GC.Spread.Sheets.Charts.ChartType.lineStacked },{ typeDesc: '100% Stacked Line', type:GC.Spread.Sheets.Charts.ChartType.lineStacked100 },{ typeDesc: 'Line With Markers', type:GC.Spread.Sheets.Charts.ChartType.lineMarkers },{ typeDesc: 'Stacked Line With Markers', type:GC.Spread.Sheets.Charts.ChartType.lineMarkersStacked },{ typeDesc: '100% Stacked Line With Markers', type:GC.Spread.Sheets.Charts.ChartType.lineMarkersStacked100 }], [{ typeDesc: 'Pie', type:GC.Spread.Sheets.Charts.ChartType.pie },{ typeDesc: 'Doughnut', type:GC.Spread.Sheets.Charts.ChartType.doughnut }], [{ typeDesc: 'Clustered Bar', type:GC.Spread.Sheets.Charts.ChartType.barClustered },{ typeDesc: 'Stacked Bar', type:GC.Spread.Sheets.Charts.ChartType.barStacked },{ typeDesc: '100% Stacked Bar', type:GC.Spread.Sheets.Charts.ChartType.barStacked100 }], [{ typeDesc: 'Area', type:GC.Spread.Sheets.Charts.ChartType.area },{ typeDesc: 'Stacked Area', type:GC.Spread.Sheets.Charts.ChartType.areaStacked },{ typeDesc: '100% Stacked Area', type:GC.Spread.Sheets.Charts.ChartType.areaStacked100 }], [{ typeDesc: 'Scatter', type:GC.Spread.Sheets.Charts.ChartType.xyScatter },{ typeDesc: 'Scatter With Smooth Lines And Markers', type:GC.Spread.Sheets.Charts.ChartType.xyScatterSmooth },{ typeDesc: 'Scatter With Smooth Lines', type:GC.Spread.Sheets.Charts.ChartType.xyScatterSmoothNoMarkers },{ typeDesc: 'Scatter With Straight Lines And Markers', type:GC.Spread.Sheets.Charts.ChartType.xyScatterLines },{ typeDesc: 'Scatter With Straight Lines', type:GC.Spread.Sheets.Charts.ChartType.xyScatterLinesNoMarkers },{ typeDesc: 'Bubble', type:GC.Spread.Sheets.Charts.ChartType.bubble }], [{ typeDesc: 'High-Low-Close', type:GC.Spread.Sheets.Charts.ChartType.stockHLC },{ typeDesc: 'Open-High-Low-Close', type:GC.Spread.Sheets.Charts.ChartType.stockOHLC },{ typeDesc: 'Volume-High-Low-Close', type:GC.Spread.Sheets.Charts.ChartType.stockVHLC },{ typeDesc: 'Volume-Open-High-Low-Close', type:GC.Spread.Sheets.Charts.ChartType.stockVOHLC }] ]; function initSpread(spread) { var sheet1 = spread.sheets[0]; sheet1.name("Common Chart"); var sheet2 = spread.sheets[1]; sheet2.name("Custom Chart"); initSheet(sheet1); initSheet(sheet2); //add chart initChart(sheet1); initChart(sheet2); //custom chart customChartStyle(sheet2); } function initSheet(sheet){ sheet.suspendPaint(); //prepare data for chart var dataArray = [ ["", 'Chrome', 'FireFox', 'IE', 'Safari', 'Edge', 'Opera', 'Other'], ["2015", 0.5651, 0.1734, 0.1711, 0.427, 0, 0.184, 0.293], ["2016", 0.6230, 0.1531, 0.1073, 0.464, 0.311, 0.166, 0.225], ["2017", 0.6360, 0.1304, 0.834, 0.589, 0.443, 0.223, 0.246] ]; sheet.setArray(0, 0, dataArray); sheet.resumePaint(); } function initChart(sheet) { //add common chart sheet.charts.add('Chart1', GC.Spread.Sheets.Charts.ChartType.columnClustered, 0, 100, 800, 300, "A1:H4"); } function customChartStyle(sheet){ var changeChart = sheet.charts.all()[0]; changeChartStyle(changeChart); } function changeChartStyle(chart) { //change orientation switchOrientation(chart); //change legend changeChartLegend(chart); //change chartArea changeChartArea(chart); //change chartTitle changeChartTitle(chart); //change dataLabels changeChartDataLabels(chart); //change axisTitles changeChartAxisTitles(chart); //change axesLine changeChartAxesLine(chart); //change series changeSeries(chart); //change gridLine changeGridLine(chart); //change seriesBorder changeSeriesBorder(chart); } function switchOrientation(chart){ chart.switchDataOrientation(); } function ignoreHiddenRowAndColumn(spread, value){ var activeSheet = spread.getActiveSheet(); var activeChart = getActiveChart(activeSheet); activeChart && activeChart.ignoreHidden(value); } function displayBlanksCells(spread, value) { var activeSheet = spread.getActiveSheet(); var activeChart = getActiveChart(activeSheet); var index = parseInt(value); if (index !== null && index !== undefined) { activeChart && activeChart.displayBlanksAs(index); } } function showNAAsBlanks(spread, value){ var activeSheet = spread.getActiveSheet(); var activeChart = getActiveChart(activeSheet); activeChart && activeChart.displayNaAsBlank(value); } function changeChartLegend(chart) { var legend = chart.legend(); legend.visible = true; var legendPosition = GC.Spread.Sheets.Charts.LegendPosition; legend.position = legendPosition.top; chart.legend(legend); } function changeChartArea(chart) { var chartArea = chart.chartArea(); chartArea.backColor = "rgba(93,93,93,1)"; chartArea.color = "rgba(255,255,255,1)"; chartArea.fontSize = 14; chart.chartArea(chartArea); } function changeChartTitle(chart) { var title = chart.title(); title.text = "Browser Market Share"; title.fontSize = 18; chart.title(title); } function changeChartDataLabels(chart) { var dataLabels = chart.dataLabels(); dataLabels.showValue = true; dataLabels.showSeriesName = false; dataLabels.showCategoryName = false; dataLabels.format = "0.00%"; var dataLabelPosition = GC.Spread.Sheets.Charts.DataLabelPosition; dataLabels.position = dataLabelPosition.outsideEnd; chart.dataLabels(dataLabels); } function changeChartAxisTitles(chart) { var axes = chart.axes(); axes.primaryCategory.title.text = 'Year'; axes.primaryCategory.title.fontSize = 14; chart.axes(axes); } function changeChartAxesLine(chart) { var axes = chart.axes(); axes.primaryValue.format = "0%"; chart.axes(axes); } function changeSeries(chart) { var series = chart.series(); var seriesItem = series.get(6); seriesItem.backColor = "#a3cf62"; series.set(6, seriesItem); } function changeGridLine(chart) { var axes = chart.axes(); axes.primaryCategory.majorGridLine.visible = false; axes.primaryValue.majorGridLine.visible = false; chart.axes(axes); } function changeSeriesBorder(chart) { var series = chart.series().get(); for (var i = 0; i < series.length; i++) { var seriesItem = series[i]; seriesItem.border.color = 'rgb(255,255,255)'; seriesItem.border.width = 1; chart.series().set(i, seriesItem); } } function insertChart(spread) { var activeSheet = spread.getActiveSheet(); var dataRange = activeSheet.getSelections()[0]; if(!judgeIsEmptyOneCell(activeSheet,dataRange)){ var rangeToFormula = GC.Spread.Sheets.CalcEngine.rangeToFormula; var dataFormula = rangeToFormula(dataRange); var groupIndex = parseInt(_getElementById('groupSelect').value); var typeIndex = parseInt(_getElementById('typeSelect').value); if(groupIndex < chartType.length){ var typeArray = chartType[groupIndex]; if(typeIndex < typeArray.length){ var type = typeArray[typeIndex].type; try{ activeSheet.charts.add('', type, 30, 120, 500, 300,dataFormula , GC.Spread.Sheets.Charts.RowCol.rows); }catch(e){ alert(e.message); } } } } } function switchRowColumn(spread) { var activeSheet = spread.getActiveSheet(); var activeChart = getActiveChart(activeSheet); if(activeChart){ var isSwitched = activeChart.switchDataOrientation(); if (!isSwitched) { alert("'Can't switch row/column"); } } } function removeChart(spread) { var activeSheet = spread.getActiveSheet(); var activeChart = getActiveChart(activeSheet); if(activeChart){ activeSheet.charts.remove(activeChart.name()); } } function removeAllChart(spread) { var activeSheet = spread.getActiveSheet(); activeSheet.charts.clear(); } function getActiveChart(sheet) { var activeChart = null; sheet.charts.all().forEach(function (chart) { if (chart.isSelected()) { activeChart = chart; } }); return activeChart; } function judgeIsEmptyOneCell(sheet,range){ if(range.rowCount === 1 && range.colCount === 1){ var cell = sheet.getCell(range.row, range.col); if(!cell.text()){ return true; } } return false; } function changeTypeSelect(){ var index = parseInt(_getElementById('groupSelect').value); if(index!==null && index!==undefined && index < chartType.length){ _getElementById('typeSelect').innerHTML=''; var typeArray = chartType[index]; for(var i=0;i<typeArray.length;i++){ var item = typeArray[i]; var option = document.createElement('option'); var value = document.createAttribute('value'); value.nodeValue=i; option.setAttributeNode(value); option.innerHTML=item.typeDesc; _getElementById('typeSelect').appendChild(option); } } } function _getElementById(id){ return document.getElementById(id); }
<!doctype html> <html style="height:100%;font-size:14px;"> <head> <meta charset="utf-8" /> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <link rel="stylesheet" type="text/css" href="$DEMOROOT$/en/purejs/node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/en/purejs/node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/en/purejs/node_modules/@grapecity/spread-sheets-charts/dist/gc.spread.sheets.charts.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/spread/source/js/license.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"> <p>Highlight a range of cells, select a chart group and chart type then click “Add Chart” to add the chart to the sheet. Switch the axes with “Switch row/column” or remove the chart(s) with “Remove chart” or “Remove all charts”. </p> <div class="option-row"> <label>Group:</label> <select id="groupSelect" style="width: 160px"> <option value="0" selected="selected">Column</option> <option value="1">Line</option> <option value="2">Pie</option> <option value="3">Bar</option> <option value="4">Area</option> <option value="5">Scatter</option> <option value="6">Stock</option> </select> </div> <div class="option-row"> <label>Type:</label> <select id="typeSelect" style="width: 160px"></select> </div> <div class="option-row"> <input type="button" style="width: 150px;margin:5px ;margin-bottom: 15px;" value="Add Chart" id="insertChart" /> <input type="button" style="width: 150px;margin:5px" value="Switch row/column" id="switchRowColumn" /> <input type="button" style="width: 150px;margin:5px" value="Remove chart" id="removeChart" /> <input type="button" style="width: 150px;margin:5px" value="Remove all charts" id="removeAllCharts" /> </div> <div class="option-row"> <label>Display Blanks Cells As:</label> <select id="displayBlanksCells" style="width: 80px"> <option value="1" selected="selected">Gaps</option> <option value="2">Zero</option> <option value="0">Connect</option> </select> </div> <div class="option-row"> <input type="checkbox" id="showNAAsBlanks" checked="checked"></input> <label for="showNAAsBlanks">Display #N/A Cells As Blank Cells</label> </div> <div class="option-row"> <input type="checkbox" id="ignoreHidden" checked="checked"></input> <label for="ignoreHidden">Ingore Hidden Rows And Columns</label> </div> </div> </div> </body> </html>
.sample-tutorial { position: relative; height: 100%; overflow: hidden; } .sample-spreadsheets { width: calc(100% - 280px); height: 100%; overflow: hidden; float: left; } .options-container { float: right; width: 280px; padding: 12px; height: 100%; box-sizing: border-box; background: #fbfbfb; overflow: auto; } .option-row { font-size: 14px; padding: 2px; margin-top: 2px; } .sample-options { z-index: 1000; } label { margin-bottom: 6px; } p{ padding:2px 10px; background-color:lavender; } input { padding: 2px 6px; } input[type=button] { margin-top: 6px; display: block; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; }