To use the chart feature, add the SpreadJS Chart JS file link into the document's head section:
You can create a chart with the sheet.charts.add method, as shown below:
In addition, you can also specify that the data source of the chart is multiple selections
When the set data source is equal to the table structure reference, chart will automatically update the series area
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 supports displaying blank cells in the following ways and displaying #N/A cells as blank in the 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 supports showing or ignoring hidden rows and columns in chart.
SpreadJS supports preserving or ignoring unsupported chart types when importing.
window.onload = function () {
var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), {
sheetCount: 3
});
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");
var sheet3 = spread.sheets[2];
sheet3.name("Table Binding Chart");
initSheet(sheet1);
initSheet(sheet2);
initBindingTable(sheet3);
//add chart
initChart(sheet1);
initChart(sheet2);
initBindingChart(sheet3);
//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 initBindingTable(sheet) {
sheet.suspendPaint();
var data = {
name: 'Jones',
region: 'East',
sales: [{
orderDate: '1/6/2013',
item: 'Pencil',
units: 95,
cost: 1.99
},
{
orderDate: '4/1/2013',
item: 'Binder',
units: 60,
cost: 4.99
},
{
orderDate: '6/8/2013',
item: 'Pen Set',
units: 16,
cost: 17.99
},
{
orderDate: '7/8/2013',
item: 'ball-point pen',
units: 16,
cost: 8.99
},
{
orderDate: '8/8/2013',
item: 'writing brush',
units: 16,
cost: 18.99
},
{
orderDate: '9/8/2013',
item: 'painting brush',
units: 16,
cost: 10.99
}
]
};
var tableColumns = [],
names = ['orderDate', 'item', 'units', 'cost'],
labels = ['Order Date', 'Item', 'Units', 'Cost'];
var table = sheet.tables.add('tableRecords', 0, 0, 4, 4);
table.autoGenerateColumns(false);
names.forEach(function (name, index) {
var tableColumn = new GC.Spread.Sheets.Tables.TableColumn();
tableColumn.name(labels[index]);
tableColumn.dataField(name);
tableColumns.push(tableColumn);
});
table.bindColumns(tableColumns);
table.bindingPath('sales');
var source = new GC.Spread.Sheets.Bindings.CellBindingSource(data);
sheet.setDataSource(source);
var button = sheet.shapes.addFormControl("button", GC.Spread.Sheets.Shapes.FormControlType.button, 500, 10, 150, 50);
button.text("Set Table Data Source");
var style = button.style();
style.textEffect.color = "rgb(0, 0, 0)";
style.textEffect.font = "bold 15px Calibri";
style.textFrame.vAlign = GC.Spread.Sheets.VerticalAlign.center;
style.textFrame.hAlign = GC.Spread.Sheets.HorizontalAlign.center;
button.style(style);
sheet.bind(GC.Spread.Sheets.Events.FormControlButtonClicked, function (s, args) {
var length = Math.floor(Math.random() * 20) + 1
data.sales = generateTestData(length, 10, 20);
var source = new GC.Spread.Sheets.Bindings.CellBindingSource(data);
sheet.setDataSource(source);
});
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 initBindingChart(sheet) {
//add table binding chart
sheet.charts.add('Chart1', GC.Spread.Sheets.Charts.ChartType.columnClustered, 250, 80, 800, 300, "tableRecords[[#Headers], [#Data], [Item]],tableRecords[[#Headers], [#Data], [Units]],tableRecords[[#Headers], [#Data], [Cost]]");
}
function customChartStyle(sheet) {
var changeChart = sheet.charts.all()[0];
changeChartStyle(changeChart);
}
function changeChartStyle(chart) {
//change orientation
switchOrientation(chart);
//change legend
changeChartLegend(chart);
//change chartAreasourcevar
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 dataRanges = activeSheet.getSelections();
var dataFormulas = [];
if (dataRanges) {
dataRanges.forEach((dataRange) => {
if (!judgeIsEmptyOneCell(activeSheet, dataRange)) {
var rangeToFormula = GC.Spread.Sheets.CalcEngine.rangeToFormula;
dataFormulas.push(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, dataFormulas.join(','), 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 generateTestData(length, maxUnits, maxCost) {
const testData = [];
const items = [
'apple', 'banana', 'orange', 'strawberry', 'grape', 'mango', 'watermelon',
'pineapple', 'kiwi', 'pear', 'peach', 'blueberry', 'cherry', 'lemon', 'lime',
'plum', 'raspberry', 'blackberry', 'pomegranate', 'coconut'
];
for (var i = 0; i < length; i++) {
const orderDate = new Date().toISOString().slice(0, 10);
const item = items[Math.floor(Math.random() * items.length)];
const units = Math.floor(Math.random() * maxUnits) + 1;
const cost = Math.floor(Math.random() * maxCost) + 1;
const order = {
orderDate: orderDate,
item: item,
units: units,
cost: cost
};
testData.push(order);
}
return testData;
}
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/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets-shapes/dist/gc.spread.sheets.shapes.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/en/purejs/node_modules/@mescius/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:#F4F8EB;
}
input {
padding: 2px 6px;
}
input[type=button] {
margin-top: 6px;
display: block;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
