Compatible

You can also use formula to set an excel like sparkline, like line sparkline, column sparkline and winloss sparkline.

<p>You can create a line sparkline, column sparkline, or winloss sparkline by using the following functions in a formula: <strong>=LINESPARKLINE(data, dataOrientation, dateAxisData?, dateAxisOrientation?, setting?), =COLUMNSPARKLINE(data, dataOrientation, dateAxisData?, dateAxisOrientation?, setting?), =WINLOSSSPARKLINE(data, dataOrientation, dateAxisData?, dateAxisOrientation?, setting?)</strong>.</p> <p>The functions have the following parameters:</p> <p><strong>data</strong>: A range reference that represents sparkline data, such as "A1:C3".</p> <p><strong>dataOrientation</strong>: A number that represents the sparkline data orientation. One of the following:</p> <ul> <li><strong>vertical - 0</strong></li> <li><strong>horizontal - 1</strong></li> </ul> <p><strong>dateAxisData</strong>: (optional) A range reference that represents the sparkline date axis data, such as "D1:F3".</p> <p><strong>dateAxisOrientation</strong>: (optional) A number that represents the sparkline date axis orientation. One of the following:</p> <ul> <li><strong>vertical - 0</strong></li> <li><strong>horizontal - 1</strong></li> </ul> <p><strong>setting</strong>: (optional) A string with JSON format; each item keeps the same definition as GC.Spread.Sheets.Sparklines.<strong>SparklineSetting</strong>.</p> <p>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.</p> <p>You can highlight individual data markers (values) in a line sparkline by making some or all of the markers visible.</p> <ul> <li><strong>showFirst: whether the first data point is formatted differently for each sparkline in this sparkline group</strong></li> <li><strong>showHigh: whether the data points with the highest value are formatted differently for each sparkline in this sparkline group</strong></li> <li><strong>showLast: whether the last data point is formatted differently for each sparkline in this sparkline group</strong></li> <li><strong>showLow: whether the data points with the lowest value are formatted differently for each sparkline in this sparkline group</strong></li> <li><strong>showNegative: whether the negative data points are formatted differently for each sparkline in this sparkline group</strong></li> <li><strong>showMarkers: whether data markers are displayed for each sparkline in this sparkline group</strong></li> </ul> <p>You can change the style and format of sparklines using the following methods:</p> <ul> <li><strong>axisColor: the color of the axis</strong></li> <li><strong>firstMarkerColor: the color of the first data point for each sparkline in this sparkline group</strong></li> <li><strong>highMarkerColor: the color of the highest data point for each sparkline in this sparkline group</strong></li> <li><strong>lastMarkerColor: the color of the last data point for each sparkline in this sparkline group</strong></li> <li><strong>lowMarkerColor: the color of the lowest data point for each sparkline in this sparkline group</strong></li> <li><strong>markersColor: the color of the data markers for each sparkline in this sparkline group</strong></li> <li><strong>negativeColor: the color of the negative data points for each sparkline in this sparkline group</strong></li> <li><strong>seriesColor: the color for each sparkline in this sparkline group</strong></li> </ul> <p>Sparklines offer additional settings. For example, sometimes there are empty values in the data series in the chart. You can use the <strong>displayEmptyCellsAs</strong> option to control how to display the empty cells, as shown in the following example:</p> <pre><code class="hljs js language-js"> <span class="hljs-keyword">var</span> setting = <span class="hljs-keyword">new</span> GC.Spread.Sheets.Sparklines.SparklineSetting(); setting.options.displayEmptyCellsAs = GC.Spread.Sheets.Sparklines.EmptyValueStyle.gaps; setting.options.rightToLeft = <span class="hljs-literal">true</span>; setting.options.displayHidden = <span class="hljs-literal">false</span>; setting.options.displayXAxis = <span class="hljs-literal">false</span> setting.options.lineWeight = <span class="hljs-number">2</span>; setting.options.manualMax = <span class="hljs-number">3</span>; setting.options.manualMin = <span class="hljs-number">1</span>; setting.options.markersColor = <span class="hljs-string">'Magenta'</span>; setting.options.maxAxisType = GC.Spread.Sheets.Sparklines.SparklineAxisMinMax.custom; setting.options.minAxisType = GC.Spread.Sheets.Sparklines.SparklineAxisMinMax.individual; </code></pre> <p>The following example illustrates how to apply these settings:</p> <pre><code class="hljs js language-js"> <span class="hljs-keyword">var</span> setting = <span class="hljs-keyword">new</span> GC.Spread.Sheets.Sparklines.SparklineSetting(); setting.options.showFirst = <span class="hljs-literal">true</span>; setting.options.showHigh = <span class="hljs-literal">true</span>; setting.options.displayXAxis = <span class="hljs-literal">true</span>; setting.options.axisColor = <span class="hljs-string">'Cyan'</span>; <span class="hljs-keyword">var</span> sparkline = sheet.getSparkline(<span class="hljs-number">11</span>, <span class="hljs-number">0</span>); sparkline.setting(setting); </code></pre>
window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss")); initSpread(spread); }; function initSpread(spread) { var spreadNS = GC.Spread.Sheets; var sheet = spread.sheets[0]; sheet.suspendPaint(); sheet.addSpan(0, 0, 1, 5); sheet.getCell(0, 0).value("The company revenue in 2014").font("20px Arial").hAlign(spreadNS.HorizontalAlign.center).vAlign(spreadNS.VerticalAlign.center); var table1 = sheet.tables.add("table1", 1, 0, 13, 5, spreadNS.Tables.TableThemes.medium4); table1.rowFilter().filterButtonVisible(false); sheet.setValue(1, 0, "Month"); sheet.setValue(1, 1, "Revenue"); sheet.setValue(1, 2, "Diagram 1"); sheet.setValue(1, 3, "Diagram 2"); sheet.setValue(1, 4, "Diagram 3"); for (var i = 2; i < 14; i++) { var today = new Date(); sheet.setValue(i, 0, new Date(today.getFullYear() - 1, i - 1, 1)); } sheet.setFormatter(-1, 0, "mm/dd/yyyy"); sheet.setValue(2, 1, 10); sheet.setValue(3, 1, 20); sheet.setValue(4, 1, 50); sheet.setValue(5, 1, 100); sheet.setValue(6, 1, 30); sheet.setValue(7, 1, - 10); sheet.setValue(8, 1, -25); sheet.setValue(9, 1, 60); sheet.setValue(10, 1, 50); sheet.setValue(11, 1, 30); sheet.setValue(12, 1, 80); sheet.setValue(13, 1, 88); sheet.addSpan(2, 2, 12, 1); sheet.setFormula(2, 2, '=LINESPARKLINE(B3:B14,0,A3:A14,0,"{axisColor:rgb(255,255,0),firstMarkerColor:brown,highMarkerColor:red,lastMarkerColor:blue,lowMarkerColor:green,markersColor:purple,negativeColor:yellowgreen,seriesColor:pink,displayXAxis:true,showFirst:true,showHigh:true,showLast:true,showLow:true,showNegative:true,showMarkers:true,lineWeight:3,displayHidden:false,displayEmptyCellsAs:1,rightToLeft:false,minAxisType:1,maxAxisType:1,manualMax:5,manualMin:-3}")'); sheet.addSpan(2, 3, 12, 1); sheet.setFormula(2, 3, '=COLUMNSPARKLINE(B3:B14,0,A3:A14,0,"{axisColor:rgb(255,255,0),firstMarkerColor:brown,highMarkerColor:red,lastMarkerColor:blue,lowMarkerColor:green,markersColor:purple,negativeColor:yellowgreen,seriesColor:pink,displayXAxis:true,showFirst:true,showHigh:true,showLast:true,showLow:true,showNegative:true,showMarkers:true,lineWeight:3,displayHidden:false,displayEmptyCellsAs:1,rightToLeft:false,minAxisType:1,maxAxisType:1,manualMax:5,manualMin:-3}")'); sheet.addSpan(2, 4, 12, 1); sheet.setFormula(2, 4, '=WINLOSSSPARKLINE(B3:B14,0,A3:A14,0)'); sheet.setRowHeight(0, 50); for (var i = 1; i < 14; i++) { sheet.setRowHeight(i, 15); } sheet.setColumnWidth(0, 80); sheet.setColumnWidth(2, 200); sheet.setColumnWidth(3, 200); sheet.setColumnWidth(4, 200); 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/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/en/purejs/node_modules/@grapecity/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="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; }