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.
<template>
<div class="sample-tutorial">
<gc-spread-sheets class="sample-spreadsheets" @workbookInitialized="initSpread">
<gc-worksheet />
</gc-spread-sheets>
<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" v-model="groupType" @change="changeTypeSelect">
<option value="0">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" v-model="chartType"></select>
</div>
<div class="option-row">
<input type="button" style="width: 150px;margin:5px ;margin-bottom: 15px;" value="Add Chart" id="insertChart" @click="insertChart" />
<input type="button" style="width: 150px;margin:5px" value="Switch row/column" id="switchRowColumn" @click="switchRowColumn" />
<input type="button" style="width: 150px;margin:5px" value="Remove chart" id="removeChart" @click="removeChart" />
<input type="button" style="width: 150px;margin:5px" value="Remove all charts" id="removeAllCharts" @click="removeAllChart" />
</div>
<div class="option-row">
<label>Display Blanks Cells As:</label>
<select id="displayBlanksCells" style="width: 80px" v-model="displayBlanksCells" @change="displayBlanksCell">
<option value="1">Gaps</option>
<option value="2">Zero</option>
<option value="0">Connect</option>
</select>
</div>
<div class="option-row">
<input type="checkbox" id="showNAAsBlanks" v-model="displayNaNAsBlank" @click="showNAAsBlanks" />
<label for="showNAAsBlanks">Display #N/A Cells As Blank Cells</label>
</div>
<div class="option-row">
<input type="checkbox" id="ignoreHidden" v-model="ignoreHidden" @change="ignoreHiddenRowAndColumn" />
<label for="ignoreHidden">Ingore Hidden Rows And Columns</label>
</div>
</div>
</div>
</template>
<script setup>
import GC from "@mescius/spread-sheets";
import {
onMounted
} from 'vue';
import {
ref
} from "vue";
import "@mescius/spread-sheets-vue";
import "@mescius/spread-sheets-charts";
const chartTypes = [
[{
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
}
]
];
let spreadRef;
const groupType = ref("0");
const chartType = ref("0");
const displayBlanksCells = ref("1");
const displayNaNAsBlank = ref(false);
const ignoreHidden = ref(true);
function initSpread(spread) {
spreadRef = spread;
spread.suspendPaint();
spread.setSheetCount(3);
let sheet1 = spread.getSheet(0);
sheet1.name("Common Chart");
let sheet2 = spread.getSheet(1);
sheet2.name("Custom Chart");
let 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);
spread.resumePaint();
changeTypeSelect();
}
function _getElementById(id) {
return document.getElementById(id);
}
function initSheet(sheet) {
sheet.suspendPaint();
//prepare data for chart
let dataArray = [
["", "Chrome", "FireFox", "IE", "Safari", "Edge", "Opera", "Other"],
["2015", 0.5651, 0.1734, 0.1711, 0.427, 0, 0.184, 0.293],
["2016", 0.623, 0.1531, 0.1073, 0.464, 0.311, 0.166, 0.225],
["2017", 0.636, 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 initBindingTable(sheet) {
sheet.suspendPaint();
let 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
}
]
};
let tableColumns = [],
names = ['orderDate', 'item', 'units', 'cost'],
labels = ['Order Date', 'Item', 'Units', 'Cost'];
let table = sheet.tables.add('tableRecords', 0, 0, 4, 4);
table.autoGenerateColumns(false);
names.forEach(function (name, index) {
let tableColumn = new GC.Spread.Sheets.Tables.TableColumn();
tableColumn.name(labels[index]);
tableColumn.dataField(name);
tableColumns.push(tableColumn);
});
table.bindColumns(tableColumns);
table.bindingPath('sales');
let source = new GC.Spread.Sheets.Bindings.CellBindingSource(data);
sheet.setDataSource(source);
let button = sheet.shapes.addFormControl("button", GC.Spread.Sheets.Shapes.FormControlType.button, 500, 10, 150, 50);
button.text("Set Table Data Source");
let 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 () {
let length = Math.floor(Math.random() * 20) + 1
data.sales = generateTestData(length, 10, 20);
let source = new GC.Spread.Sheets.Bindings.CellBindingSource(data);
sheet.setDataSource(source);
});
sheet.resumePaint();
}
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) {
let 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 changeChartLegend(chart) {
let legend = chart.legend();
legend.visible = true;
let legendPosition = GC.Spread.Sheets.Charts.LegendPosition;
legend.position = legendPosition.top;
chart.legend(legend);
}
function changeChartArea(chart) {
let 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) {
let title = chart.title();
title.text = "Browser Market Share";
title.fontSize = 18;
chart.title(title);
}
function changeChartDataLabels(chart) {
let dataLabels = chart.dataLabels();
dataLabels.showValue = true;
dataLabels.showSeriesName = false;
dataLabels.showCategoryName = false;
dataLabels.format = "0.00%";
let dataLabelPosition = GC.Spread.Sheets.Charts.DataLabelPosition;
dataLabels.position = dataLabelPosition.outsideEnd;
chart.dataLabels(dataLabels);
let 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);
let 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);
let 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) {
let axes = chart.axes();
axes.primaryCategory.title.text = "Year";
axes.primaryCategory.title.fontSize = 14;
chart.axes(axes);
}
function changeChartAxesLine(chart) {
let axes = chart.axes();
axes.primaryValue.format = "0%";
chart.axes(axes);
}
function changeSeries(chart) {
let series = chart.series();
let seriesItem = series.get(6);
seriesItem.backColor = "#a3cf62";
series.set(6, seriesItem);
}
function changeGridLine(chart) {
let axes = chart.axes();
axes.primaryCategory.majorGridLine.visible = false;
axes.primaryValue.majorGridLine.visible = false;
chart.axes(axes);
}
function changeSeriesBorder(chart) {
let series = chart.series().get();
for (let i = 0; i < series.length; i++) {
let seriesItem = series[i];
seriesItem.border.color = "rgb(255,255,255)";
seriesItem.border.width = 1;
chart.series().set(i, seriesItem);
}
}
function getActiveChart(sheet) {
let 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) {
let cell = sheet.getCell(range.row, range.col);
if (!cell.text()) {
return true;
}
}
return false;
}
function changeTypeSelect(e) {
let index = parseInt(groupType.value || 0);
if (index !== null && index !== undefined && index < chartTypes.length) {
_getElementById("typeSelect").innerHTML = "";
let typeArray = chartTypes[index];
for (let i = 0; i < typeArray.length; i++) {
let item = typeArray[i];
let option = document.createElement("option");
let value = document.createAttribute("value");
value.nodeValue = i;
option.setAttributeNode(value);
option.innerHTML = item.typeDesc;
_getElementById("typeSelect").appendChild(option);
}
}
}
function insertChart() {
let spread = spreadRef;
let activeSheet = spread.getActiveSheet();
let dataRanges = activeSheet.getSelections();
let dataFormulas = [];
if (dataRanges) {
dataRanges.forEach((dataRange) => {
if (!judgeIsEmptyOneCell(activeSheet, dataRange)) {
let rangeToFormula = GC.Spread.Sheets.CalcEngine.rangeToFormula;
dataFormulas.push(rangeToFormula(dataRange));
}
})
}
let groupIndex = parseInt(groupType.value);
let typeIndex = parseInt(chartType.value);
if (groupIndex < chartTypes.length) {
let typeArray = chartTypes[groupIndex];
if (typeIndex < typeArray.length) {
let 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() {
let spread = spreadRef;
let activeSheet = spread.getActiveSheet();
let activeChart = getActiveChart(activeSheet);
if (activeChart) {
let isSwitched = activeChart.switchDataOrientation();
if (!isSwitched) {
alert("'Can't switch row/column");
}
}
}
function removeChart() {
let spread = spreadRef;
let activeSheet = spread.getActiveSheet();
let activeChart = getActiveChart(activeSheet);
if (activeChart) {
activeSheet.charts.remove(activeChart.name());
}
}
function removeAllChart() {
let spread = spreadRef;
let activeSheet = spread.getActiveSheet();
activeSheet.charts.clear();
}
function ignoreHiddenRowAndColumn(e) {
let spread = spreadRef;
let activeSheet = spread.getActiveSheet();
let activeChart = getActiveChart(activeSheet);
activeChart && activeChart.ignoreHidden(ignoreHidden.value);
}
function displayBlanksCell(e) {
let spread = spreadRef;
let activeSheet = spread.getActiveSheet();
let activeChart = getActiveChart(activeSheet);
let index = parseInt(displayBlanksCells.value);
if (index !== null && index !== undefined) {
activeChart && activeChart.displayBlanksAs(index);
}
}
function showNAAsBlanks(e) {
let spread = spreadRef;
let activeSheet = spread.getActiveSheet();
let activeChart = getActiveChart(activeSheet);
activeChart && activeChart.displayNaNAsBlank(!displayNaNAsBlank.value);
}
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 (let 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;
}
</script>
<style scoped>
#app {
height: 100%;
}
.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;
}
</style>
<!DOCTYPE html>
<html 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="./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: {
'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",
'@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js',
'@mescius/spread-sheets-resources-en': 'npm:@mescius/spread-sheets-resources-en/index.js',
'@mescius/spread-sheets-vue': 'npm:@mescius/spread-sheets-vue/index.js',
'@mescius/spread-sheets-shapes': 'npm:@mescius/spread-sheets-shapes/index.js',
'@mescius/spread-sheets-charts': 'npm:@mescius/spread-sheets-charts/index.js',
},
meta: {
'*.css': { loader: 'systemjs-plugin-css' },
'*.vue': { loader: "../plugin-vue/index.js" }
}
});
})(this);
