The chart series supports the following customization:
chartType
axisGroup
backColor
invertColor
backColorTransparency
border
name
xValues
yValues
errorBars
trendlines
dataLabels
bubbleSizes
doughnutHoleSize
gapWidth
overlap
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.
You can set different colors for positive and negative numbers in a series
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
showDataLabelsRange
dataLabelsRange
separator
position
format
numberFormatLinked
color
transparency
backColor
backColorTransparency
borderColor
borderWidth
borderColorTransparency
bubbleSizes: The bubble sizes formula of the series. This is used for bubble chart.
doughnutHoleSize: The hole size of the doughnut chart. This is used for doughnut chart. The maximum value is 0.9, the minimum value is 0.
gapWidth: The gap width of the bar and column chart group and funnel type. The maximum value is 5, the minimum value is 0.
overlap: The overlap of the bar and column chart group and funnel type. The maximum value is 1, the minimum value is -1.
error bars: Error bars allow you to quickly display margins of error and standard deviations in your charts.
For more information, check out the Error Bars Demo.
trendlines: Trendline is an additional line that indicates the slope (or trend) in a particular data series.
For more information, check out the Trendlines Demo.
var spread;
window.onload = function () {
spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), {
sheetCount: 2
});
initSpread(spread);
initEvent(spread);
//readSetting(new GC.Spread.Sheets.CellTypes.CheckBoxList());
}
var ColumnGroup = [
GC.Spread.Sheets.Charts.ChartType.columnClustered,
GC.Spread.Sheets.Charts.ChartType.columnStacked,
GC.Spread.Sheets.Charts.ChartType.columnStacked100,
GC.Spread.Sheets.Charts.ChartType.clusteredColumn,
GC.Spread.Sheets.Charts.ChartType.waterfall
];
var BarGroup = [
GC.Spread.Sheets.Charts.ChartType.barClustered,
GC.Spread.Sheets.Charts.ChartType.barStacked,
GC.Spread.Sheets.Charts.ChartType.barStacked100
];
var positions = [
GC.Spread.Sheets.Charts.DataLabelPosition.center,
GC.Spread.Sheets.Charts.DataLabelPosition.insideEnd,
GC.Spread.Sheets.Charts.DataLabelPosition.outsideEnd,
GC.Spread.Sheets.Charts.DataLabelPosition.bestFit,
GC.Spread.Sheets.Charts.DataLabelPosition.below
];
function initSpread(spread) {
var sheet = spread.getSheet(0);
sheet.suspendPaint();
var dataArray = [
["", 'Chrome', 'Firefox', 'IE', 'Safari', 'Edge', 'Opera', 'Other'],
["2014", 0.4966, 0.1801, 0.2455, 0.0470, 0.0, 0.0150, 0.0158],
["2015", 0.5689, 0.1560, 0.1652, 0.0529, 0.0158, 0.0220, 0.0192],
["2016", -0.6230, 0.1531, 0.1073, 0.0464, 0.0311, 0.0166, 0.0225],
["2017", 0.6360, 0.1304, 0.0834, 0.0589, 0.0443, 0.0223, 0.0246]
];
sheet.setArray(0, 0, dataArray);
sheet.getCell(1,1).formatter("0%");
sheet.getCell(2,1).formatter("##0.0E+0");
sheet.getCell(3,1).formatter("0.00");
sheet.getCell(4,1).formatter("$#,##0_);($#,##0)");
var columnChart = sheet.charts.add('Chart1', GC.Spread.Sheets.Charts.ChartType.column, 30, 120, 480, 270, "A1:D5");
columnChart.title({
text: "Column Chart"
});
var bubbleChart = sheet.charts.add('Chart2', GC.Spread.Sheets.Charts.ChartType.bubble, 520, 120, 480, 270, "A1:D5");
bubbleChart.title({
text: "Bubble Chart"
});
var DoughnutChart = sheet.charts.add('Chart3', GC.Spread.Sheets.Charts.ChartType.doughnut, 30, 400, 480, 270, "A1:D5");
DoughnutChart.title({
text: "Doughnut Chart"
});
setDataLabel(columnChart);
setDataLabel(bubbleChart);
setDataLabel(DoughnutChart);
sheet.resumePaint();
}
function setDataLabel(chart) {
var series = chart.series().get(0);
var dataLabels = series.dataLabels;
dataLabels.showValue = true;
dataLabels.showSeriesName = false;
dataLabels.showCategoryName = false
var dataLabelPosition = GC.Spread.Sheets.Charts.DataLabelPosition;
dataLabels.position = dataLabelPosition.outsideEnd;
chart.series().set(0, series);
}
function initEvent(spread) {
spread.bind(GC.Spread.Sheets.Events.FloatingElementSelected, function () {
var sheet = spread.getActiveSheet();
var chart = getActiveChart(sheet);
if (chart) {
readSetting(chart);
}
});
_getElementById("apply").addEventListener('click', function () {
applySetting();
});
_getElementById("invertIfNegative").addEventListener('click', function (e) {
let value = e.target.checked;
if (value) {
_getElementById("invertColorDiv").style.display = "block";
} else {
_getElementById("invertColorDiv").style.display = "none";
}
});
}
function readSetting(chart) {
var series = chart.series().get(0);
if (series.backColor && series.backColor.startsWith('#')) {
_getElementById("backColor").value = rgbaToHex(series.backColor);
} else {
_getElementById("backColor").value = '#5b9bd5';
}
if (chart.chartType() === GC.Spread.Sheets.Charts.ChartType.columnClustered) {
_getElementById("invertIfNegativeDiv").style.display = "block";
_getElementById("invertColorDiv").style.display = "block";
_getElementById("invertIfNegative").checked = !!series.invertIfNegative;
if (!!series.invertIfNegative) {
_getElementById("invertColorDiv").style.display = "block";
if (series.invertColor) {
_getElementById("invertColor").value = rgbaToHex(series.invertColor);
} else {
_getElementById("invertColor").value = '#FFFFFF';
}
} else {
_getElementById("invertColorDiv").style.display = "none";
}
} else {
_getElementById("invertIfNegativeDiv").style.display = "none";
_getElementById("invertColorDiv").style.display = "none";
}
if (series.border && series.border.borderColor && series.border.borderColor.startsWith('#')) {
_getElementById("borderColor").value = series.border.borderColor;
} else {
_getElementById("borderColor").value = '#FFFFFF';
}
_getElementById("backColorTransparency").value = series.backColorTransparency || 0;
_getElementById("borderLineType").value = (series.border && series.border.lineType);
_getElementById("borderWidth").value = (series.border && series.border.width);
_getElementById("borderColorTransparency").value = (series.border && series.border.transparency);
if (series.dataLabels) {
_getElementById("dataLabelShowValue").checked = series.dataLabels.showValue;
_getElementById("dataLabelShowSeriesName").checked = series.dataLabels.showSeriesName;
_getElementById("dataLabelShowCategoryName").checked = series.dataLabels.showCategoryName;
_getElementById("dataLabelShowPercentage").checked = series.dataLabels.showPercentage;
_getElementById("dataLabelShowDataLabelsRange").checked = series.dataLabels.showDataLabelsRange;
_getElementById("dataLabelsRange").value = series.dataLabels.dataLabelsRange;
_getElementById("separator").value = series.dataLabels.separator;
_getElementById("position").value = positions.indexOf(series.dataLabels.position);
}
_getElementById("name").value = series.name;
_getElementById("xValues").value = series.xValues;
_getElementById("yValues").value = series.yValues;
if (chart.chartType() === GC.Spread.Sheets.Charts.ChartType.bubble) {
_getElementById("bubbleSizes").disabled = '';
_getElementById("bubbleSizes").value = series.bubbleSizes;
} else {
_getElementById("bubbleSizes").disabled = 'disabled';
_getElementById("bubbleSizes").value = '';
}
if (ColumnGroup.indexOf(chart.chartType()) !== -1 || BarGroup.indexOf(chart.chartType()) !== -1) {
_getElementById("gapWidth").disabled = '';
_getElementById("overlap").disabled = '';
_getElementById("gapWidth").value = series.gapWidth;
_getElementById("overlap").value = series.overlap;
} else {
_getElementById("gapWidth").disabled = 'disabled';
_getElementById("overlap").disabled = 'disabled';
_getElementById("gapWidth").value = '';
_getElementById("overlap").value = '';
}
if (chart.chartType() === GC.Spread.Sheets.Charts.ChartType.doughnut) {
_getElementById("doughnutHoleSize").disabled = '';
_getElementById("doughnutHoleSize").value = series.doughnutHoleSize;
} else {
_getElementById("doughnutHoleSize").disabled = 'disabled';
_getElementById("doughnutHoleSize").value = '';
}
}
function applySetting() {
var sheet = spread.getActiveSheet();
var chart = getActiveChart(sheet);
if (!chart) {
return;
}
var border = {
lineType: _getValue("borderLineType"),
width: _getFloat("borderWidth"),
color: _getText("borderColor"),
transparency: _getFloat("borderColorTransparency")
};
var series = {
backColor: _getText("backColor"),
backColorTransparency: _getText("backColorTransparency") || 0,
name: _getText("name"),
xValues: _getText("xValues"),
yValues: _getText("yValues"),
dataLabels: {
showValue: _getBoolean("dataLabelShowValue"),
showSeriesName: _getBoolean("dataLabelShowSeriesName"),
showCategoryName: _getBoolean("dataLabelShowCategoryName"),
showPercentage: _getBoolean("dataLabelShowPercentage"),
showDataLabelsRange: _getBoolean("dataLabelShowDataLabelsRange"),
dataLabelsRange: _getText("dataLabelsRange"),
separator: _getText("separator"),
position: positions[_getValue("position")]
}
}
if (border) {
series.border = border;
}
if (chart.chartType() === GC.Spread.Sheets.Charts.ChartType.bubble) {
series.bubbleSizes = _getFloat("bubbleSizes")
} else if (ColumnGroup.indexOf(chart.chartType()) !== -1 || BarGroup.indexOf(chart.chartType()) !== -1) {
series.gapWidth = _getFloat("gapWidth")
series.overlap = _getFloat("overlap")
} else if (chart.chartType() === GC.Spread.Sheets.Charts.ChartType.doughnut) {
series.doughnutHoleSize = _getFloat("doughnutHoleSize")
}
if (chart.chartType() === GC.Spread.Sheets.Charts.ChartType.columnClustered) {
series.invertIfNegative = _getBoolean("invertIfNegative");
series.invertColor = _getText("invertColor");
}
chart.series().set(0, series);
}
function getActiveChart(sheet) {
var activeChart = null;
sheet.charts.all().forEach(function (chart) {
if (chart.isSelected()) {
activeChart = chart;
}
});
return activeChart;
}
function _getElementById(id) {
return document.getElementById(id);
}
function rgbaToHex(rgbaColor) {
let regex = /^rgba?\((\d+),\s*(\d+),\s*(\d+)(?:,\s*([\d.]+))?\)$/;
let match = rgbaColor.match(regex);
if (!match) {
return rgbaColor;
}
let r = parseInt(match[1]);
let g = parseInt(match[2]);
let b = parseInt(match[3]);
return "#" + toHex(r) + toHex(g) + toHex(b);
}
function toHex(n) {
let hex = n.toString(16);
return hex.length == 1 ? "0" + hex : hex;
}
function _getBoolean(id) {
return _getElementById(id).checked;
}
function _getFloat(id) {
return isNaN(parseFloat(_getElementById(id).value)) ? undefined : parseFloat(_getElementById(id).value);
}
function _getValue(id) {
return _getElementById(id).value ? parseInt(_getElementById(id).value) : undefined;
}
function _getText(id) {
return _getElementById(id).value ? _getElementById(id).value : undefined;
}
<!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 class="sample-tutorial">
<div id="ss" class="sample-spreadsheets"></div>
<div class="options-container">
<p>Change the properties below then press the Set button to apply.</p>
<div class="option-row">
<label for="backColor">backColor</label>
<input id="backColor" type="color"/>
</div>
<div class="option-row" id="invertIfNegativeDiv">
<label for="invertIfNegative">invert if negative</label>
<input type="checkbox" id="invertIfNegative" />
</div>
<div class="option-row" id = "invertColorDiv">
<label for="invertColor">invertColor</label>
<input id="invertColor" type="color"/>
</div>
<div class="option-row">
<label for="backColorTransparency">backColorTransparency</label>
<input id="backColorTransparency" type="text"/>
</div>
<div class="option-row">
<label for="borderLineType">borderLineType</label>
<select id="borderLineType">
<option value="0">solid</option>
<option value="1">squareDot</option>
<option value="2">dash</option>
<option value="3">longDash</option>
<option value="4">dashDot</option>
<option value="5">longDashDot</option>
<option value="6">longDashDotDot</option>
<option value="7">sysDash</option>
<option value="8">sysDot</option>
<option value="9">sysDashDot</option>
<option value="10">dashDotDot</option>
<option value="11">roundDot</option>
</select>
</div>
<div class="option-row">
<label for="borderWidth">borderWidth</label>
<input id="borderWidth" type="number" min="0" max="100"/>
</div>
<div class="option-row">
<label for="borderColor">borderColor</label>
<input id="borderColor" type="color"/>
</div>
<div class="option-row">
<label for="borderColorTransparency">borderColorTransparency</label>
<input id="borderColorTransparency" type="number" min="0" max="1"/>
</div>
<div class="option-row">
<label for="dataLabelShowValue">dataLabelShowValue</label>
<input type="checkbox" id="dataLabelShowValue" />
</div>
<div class="option-row">
<label for="dataLabelShowSeriesName">dataLabelShowSeriesName</label>
<input type="checkbox" id="dataLabelShowSeriesName" />
</div>
<div class="option-row">
<label for="dataLabelShowCategoryName">dataLabelShowCategoryName</label>
<input type="checkbox" id="dataLabelShowCategoryName" />
</div>
<div class="option-row">
<label for="dataLabelShowPercentage">dataLabelShowPercentage</label>
<input type="checkbox" id="dataLabelShowPercentage" />
</div>
<div class="option-row">
<label for="dataLabelShowDataLabelsRange">dataLabelShowDataLabelsRange</label>
<input type="checkbox" id="dataLabelShowDataLabelsRange" />
</div>
<div class="option-row">
<label for="dataLabelsRange">dataLabelsRange</label>
<input id="dataLabelsRange"/>
</div>
<div class="option-row">
<label for="separator">separator</label>
<select type="text" id="separator">
<option value=",">,(comma)</option>
<option value=";">;(semicolon)</option>
<option value=".">.(period)</option>
<option value="
">(new line)</option>
<option value=" ">(space)</option>
</select>
</div>
<div class="option-row">
<label for="position">position</label>
<select type="text" id="position">
<option value="0">Center</option>
<option value="1">Inside End</option>
<option value="2">Outside End</option>
<option value="3">Best Fit</option>
<option value="4">Below</option>
</select>
</div>
<div class="option-row">
<label for="name">name</label>
<input id="name"/>
</div>
<div class="option-row">
<label for="xValues">xValues</label>
<input id="xValues"/>
</div>
<div class="option-row">
<label for="yValues">yValues</label>
<input id="yValues"/>
</div>
<div class="option-row">
<label for="bubbleSizes">bubbleSizes</label>
<input id="bubbleSizes" type="text" readonly/>
</div>
<div class="option-row">
<label for="doughnutHoleSize">doughnutHoleSize</label>
<input id="doughnutHoleSize" type="number" step="0.1" min="0" max="0.9"/>
</div>
<div class="option-row">
<label for="gapWidth">gapWidth</label>
<input id="gapWidth" type="number" step="0.1" min="0" max="5"/>
</div>
<div class="option-row">
<label for="overlap">overlap</label>
<input id="overlap" type="number" step="0.1" min="-1" max="1"/>
</div>
<div class="option-row">
<input type="button" id="apply" value="Set" />
</div>
</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: 5px;
}
.option-row {
padding-bottom: 5px;
}
label {
display:inline-block;
}
input{
padding: 1px 8px;
box-sizing: border-box;
width: 100%;
}
select{
width: 100%;
}
input[type=checkbox] {
display: inline-block;
width: auto;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
