Overview

Visualize your data using the most popular spreadsheet charts. Supported charts include Column, Line, Pie, Area, Bar, XYScatter, Stock, Combo, Radar, Sunburst, Treemap with additional support for trendlines, error bars, and other customizations. The charts can also be imported or export to Excel files.

To use the chart feature, add the js file link into the document's head section: You can create a chart with the sheet.charts.add method, as shown below: You can get a chart in the sheet using the sheet.charts.get method, remove a chart using the sheet.charts.remove method, and/or also clear all your charts using the sheet.charts.clear method. A chart is built with many sub-elements, and you can customize each one using the chart API: Chart Title: The title of the chart You can get or set the text, the background color, the foreground color, the font, and the font size of the chart title. Series: The data points of a data series that are plotted on the chart 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. Data Labels: You can use data labels to identify the details of a data point in a data series You can customize the chart’s series data labels style between below options showValue showSeriesName showCategoryName showPercentage separator position format color transparency backColor backColorTransparency borderColor borderWidth borderColorTransparency SpreadJS provides 28 chart types. You can get or set the current chart type using the following code: SpreadJS support display blank cells as below ways and diaplay #N/A cells as blank in chart. gaps: Null values are skipped. zero: Null values are treated as having a value of zero. connected: Null values are skipped and the line of the area chart is connected. SpreadJS support show or ignore hidden row and columns in chart. SpreadJS support preserve or ignore unsupported chart type when import.
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); var series0 = chart.series().get(0); series0.dataLabels= { showSeriesName: true, showCategoryName: true, separator: ";", position: GC.Spread.Sheets.Charts.DataLabelPosition.Center, color: "red", backColor: "white", borderColor: "blue", borderWidth: 2 }; chart.series().set(0, series0); var series2 = chart.series().get(2); series2.dataLabels= { showSeriesName: true, separator: "/", position: GC.Spread.Sheets.Charts.DataLabelPosition.insideEnd, color: "yellow", backColor: "white", borderColor: "green", borderWidth: 1 }; chart.series().set(2, series2); var series4 = chart.series().get(4); series4.dataLabels= { showCategoryName: true, separator: ":", position: GC.Spread.Sheets.Charts.DataLabelPosition.above, color: "blue", backColor: "white", borderColor: "red", borderWidth: 2.5 }; chart.series().set(4, series4); } 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" ></input> <label for="showNAAsBlanks">Display #N/A Cells As Blank Cells</label> </div> <div class="option-row"> <input type="checkbox" id="ignoreHidden" 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; }