Standard Sparklines

You can also use formulas to set an Excel-like sparkline, like Line sparkline, Column sparkline and WinLoss sparkline.

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; }