You can create a line sparkline, column sparkline, or winloss sparkline by using the following functions in a formula: =LINESPARKLINE(data, dataOrientation, dateAxisData?, dateAxisOrientation?, setting?), =COLUMNSPARKLINE(data, dataOrientation, dateAxisData?, dateAxisOrientation?, setting?), =WINLOSSSPARKLINE(data, dataOrientation, dateAxisData?, dateAxisOrientation?, setting?).
The functions have the following parameters:
data: A range reference that represents sparkline data, such as "A1:C3".
dataOrientation: A number that represents the sparkline data orientation. One of the following:
vertical - 0
horizontal - 1
dateAxisData: (optional) A range reference that represents the sparkline date axis data, such as "D1:F3".
dateAxisOrientation: (optional) A number that represents the sparkline date axis orientation. One of the following:
vertical - 0
horizontal - 1
setting: (optional) A string with JSON format; each item keeps the same definition as GC.Spread.Sheets.Sparklines.SparklineSetting.
After you create sparklines, you can control which value points are shown (such as high, low, first, last, or any negative values), change the type of the sparkline (Line, Column, or WinLoss), apply styles, and control whether to show the horizontal axis.
You can highlight individual data markers (values) in a line sparkline by making some or all of the markers visible.
showFirst: whether the first data point is formatted differently for each sparkline in this sparkline group
showHigh: whether the data points with the highest value are formatted differently for each sparkline in this sparkline group
showLast: whether the last data point is formatted differently for each sparkline in this sparkline group
showLow: whether the data points with the lowest value are formatted differently for each sparkline in this sparkline group
showNegative: whether the negative data points are formatted differently for each sparkline in this sparkline group
showMarkers: whether data markers are displayed for each sparkline in this sparkline group
You can change the style and format of sparklines using the following methods:
axisColor: the color of the axis
firstMarkerColor: the color of the first data point for each sparkline in this sparkline group
highMarkerColor: the color of the highest data point for each sparkline in this sparkline group
lastMarkerColor: the color of the last data point for each sparkline in this sparkline group
lowMarkerColor: the color of the lowest data point for each sparkline in this sparkline group
markersColor: the color of the data markers for each sparkline in this sparkline group
negativeColor: the color of the negative data points for each sparkline in this sparkline group
seriesColor: the color for each sparkline in this sparkline group
Sparklines offer additional settings. For example, sometimes there are empty values in the data series in the chart. You can use the displayEmptyCellsAs option to control how to display the empty cells, as shown in the following example:
The following example illustrates how to apply these settings:
window.onload = function () {
var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 3 });
spread.options.allowDynamicArray = true;
initSheet(spread.sheets[0]);
initSheet1(spread.sheets[1]);
initSheet2(spread.sheets[2]);
};
function initSheet(sheet) {
sheet.name("Line Sparkline");
sheet.suspendPaint();
sheet.setArray(2, 1, data[0]);
for(var i=4;i<14;i++)
{
sheet.setFormula(i-1, 8, '=LINESPARKLINE(C'+i+':H'+i+',1,,,"{showMarkers:true}")');
sheet.setRowHeight(i-1,30);
}
//format sheet
sheet.addSpan(0, 1, 1, 8);
sheet.getCell(0, 1).value("Sales by Country").font("bold 15px Arial")
.backColor("#DDDDDD");
sheet.setRowHeight(0,45);
sheet.getRange(2, 1, 1, 8)
.font("bold 13px Arial")
.setBorder(new GC.Spread.Sheets.LineBorder("black", GC.Spread.Sheets.LineStyle.thin), { bottom: true });
sheet.getRange(3, 2, 10, 6)
.formatter("$#,##0");
for(var j=2;j<8;j++)
{
sheet.setColumnWidth(j,80);
}
sheet.setColumnWidth(1,120);
sheet.setColumnWidth(8,150);
sheet.getRange(0, 0, 15, 9).vAlign(GC.Spread.Sheets.VerticalAlign.center);
sheet.resumePaint();
};
function initSheet1(sheet) {
sheet.name("Column Sparkline");
sheet.suspendPaint();
sheet.setArray(2, 1, data[1]);
for(var i=4;i<14;i++)
{
sheet.setFormula(i-1, 8, '=COLUMNSPARKLINE(C'+i+':H'+i+',1)');
sheet.setRowHeight(i-1,30);
}
//format sheet
sheet.addSpan(0, 1, 1, 8);
sheet.getCell(0, 1).value("Sales Data").font("15px Arial")
.backColor("#DDDDDD");
sheet.setRowHeight(0,45);
sheet.getRange(2, 1, 1, 8)
.font("bold 13px Arial")
.setBorder(new GC.Spread.Sheets.LineBorder("black", GC.Spread.Sheets.LineStyle.thin), { bottom: true });
sheet.getRange(3, 2, 10, 6)
.formatter("$#,##0");
for(var j=2;j<8;j++)
{
sheet.setColumnWidth(j,80);
}
sheet.setColumnWidth(1,120);
sheet.setColumnWidth(8,150);
sheet.getRange(0, 0, 15, 9).vAlign(GC.Spread.Sheets.VerticalAlign.center);
sheet.resumePaint();
};
function initSheet2(sheet) {
sheet.name("Winloss Sparkline");
sheet.suspendPaint();
sheet.getCell(2, 1).text("Target (tasks per week):").font("14px Arial");
sheet.getCell(2, 2).value(5).font("bold 14px Arial");
sheet.setArray(4, 1, data[2]);
for(var i=6;i<16;i++)
{
sheet.setFormula(i-1, 8, '=WINLOSSSPARKLINE(C'+i+':H'+i+'-$C$3,1,,,"{showNegative:true}")');
sheet.setRowHeight(i-1,30);
}
//format sheet
sheet.addSpan(0, 1, 1, 8);
sheet.getCell(0, 1).value("Employee Performance").font("15px Arial")
.backColor("#DDDDDD");
sheet.setRowHeight(0,45);
sheet.getRange(4, 1, 1, 8)
.font("bold 13px Arial")
.setBorder(new GC.Spread.Sheets.LineBorder("black", GC.Spread.Sheets.LineStyle.thin), { bottom: true });
sheet.setColumnWidth(1,180);
sheet.setColumnWidth(8,120);
sheet.getRange(0, 0, 15, 9).vAlign(GC.Spread.Sheets.VerticalAlign.center);
sheet.resumePaint();
};
<!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$/spread/source/js/license.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/data/standard-sparklines.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" style="width:100%;height:100%"></div>
</div></body>
</html>
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
