Settings

This sample shows how 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>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.getSheet(0); sheet.suspendPaint(); sheet.options.allowCellOverflow = true; sheet.setValue(0, 0, 'Data Range is A2-A9'); sheet.setValue(1, 0, 1); sheet.setValue(2, 0, -2); sheet.setValue(3, 0, -1); sheet.setValue(4, 0, 6); sheet.setValue(5, 0, 4); sheet.setValue(6, 0, -4); sheet.setValue(7, 0, 3); sheet.setValue(8, 0, 8); sheet.setValue(0, 1, 'Data Range is A2-A9'); sheet.setValue(1, 1, 1); sheet.setValue(2, 1, 3); sheet.setValue(3, 1, -1); sheet.setValue(4, 1, 6); sheet.setValue(5, 1, -4); sheet.setValue(6, 1, 4); sheet.setValue(7, 1, -2); sheet.setValue(8, 1, 8); sheet.setValue(0, 3, 'Date axis range is C2-C9'); sheet.setValue(1, 3, new Date(2011, 0, 5)); sheet.setValue(2, 3, new Date(2011, 0, 1)); sheet.setValue(3, 3, new Date(2011, 1, 11)); sheet.setValue(4, 3, new Date(2011, 2, 1)); sheet.setValue(5, 3, new Date(2011, 1, 1)); sheet.setValue(6, 3, new Date(2011, 1, 3)); sheet.setValue(7, 3, new Date(2011, 2, 6)); sheet.setValue(8, 3, new Date(2011, 1, 19)); var data = new spreadNS.Range(1, 0, 8, 1); var dateAxis = new spreadNS.Range(1, 3, 8, 1); sheet.getCell(9, 0).text('Sparkline with dateAxis:'); sheet.getCell(10, 0).text('(1) Line'); sheet.getCell(10, 3).text('(2)Column'); sheet.getCell(10, 6).text('(3)Winloss'); //line sheet.addSpan(11, 0, 4, 3); var setting = new spreadNS.Sparklines.SparklineSetting(); setting.options.showMarkers = true; setting.options.displayXAxis = true; setting.options.showFirst = true; setting.options.showLast = true; setting.options.showLow = true; setting.options.showHigh = true; setting.options.showNegative = true; sheet.setSparkline( 11, 0, data, spreadNS.Sparklines.DataOrientation.vertical, spreadNS.Sparklines.SparklineType.line, setting, dateAxis, spreadNS.Sparklines.DataOrientation.vertical ); //column sheet.addSpan(11, 3, 4, 3); setting = new spreadNS.Sparklines.SparklineSetting(); setting.options.displayXAxis = true; setting.options.showFirst = true; setting.options.showLast = true; setting.options.showLow = true; setting.options.showHigh = true; setting.options.showNegative = true; sheet.setSparkline( 11, 3, data, spreadNS.Sparklines.DataOrientation.vertical, spreadNS.Sparklines.SparklineType.column, setting, dateAxis, spreadNS.Sparklines.DataOrientation.vertical ); //winloss sheet.addSpan(11, 6, 4, 3); setting = new spreadNS.Sparklines.SparklineSetting(); setting.options.displayXAxis = true; setting.options.showNegative = true; sheet.setSparkline( 11, 6, data, spreadNS.Sparklines.DataOrientation.vertical, spreadNS.Sparklines.SparklineType.winloss, setting, dateAxis, spreadNS.Sparklines.DataOrientation.vertical ); sheet.bind(spreadNS.Events.SelectionChanged, selectionChangedCallback); sheet.resumePaint(); function selectionChangedCallback() { var sheet = spread.getActiveSheet(); var sparkline = sheet.getSparkline(sheet.getActiveRowIndex(), sheet.getActiveColumnIndex()); if (sparkline) { getSparklineSettings(sparkline); _getElementById('btnChangeSetting').disabled = false; } else { initSparklineSettings(); _getElementById('btnChangeSetting').disabled = true; } } function getActualRange(range, maxRowCount, maxColCount) { var row = range.row < 0 ? 0 : range.row; var col = range.col < 0 ? 0 : range.col; var rowCount = range.rowCount < 0 ? maxRowCount : range.rowCount; var colCount = range.colCount < 0 ? maxColCount : range.colCount; return new spreadNS.Range(row, col, rowCount, colCount); } function findSparkLine(sheet, range) { var row = range.row, col = range.col, rowCount = range.rowCount, colCount = range.colCount; for (var i = 0; i < rowCount; i++) { for (var j = 0; j < colCount; j++) { var sparkline = sheet.getSparkline(row + i, col + j); if (sparkline != null) { return sparkline; } } } return null; } _getElementById('btnChangeSetting').addEventListener('click', function () { var sheet = spread.getActiveSheet(); sheet.suspendPaint(); var sels = sheet.getSelections(); var setting = new spreadNS.Sparklines.SparklineSetting(); var sparklinetype = _getElementById("sparklinetype"); var index = sparklinetype.selectedIndex; var sparklineType = parseInt(sparklinetype.options[index].value, 10); if (sels && sels.length > 0) { var sel = getActualRange(sels[0], sheet.getRowCount(), sheet.getColumnCount()); var sparkline = findSparkLine(sheet, sel); if (sparkline != null) { sparkline.setting(buildSparklineSettings(setting)); sparkline.sparklineType(sparklineType); } } sheet.resumePaint(); }); function initSparklineSettings() { var sparklinetype = _getElementById("sparklinetype"); _selectOption(sparklinetype, '0'); _getElementById('firstMarkerColor').setAttribute('value', '(none)'); _getElementById('highMarkerColor').setAttribute('value', 'Blue'); _getElementById('lastMarkerColor').setAttribute('value', '(none)'); _getElementById('lowMarkerColor').setAttribute('value', 'Blue'); _getElementById('negativeMarkerColor').setAttribute('value', 'Brown'); _getElementById('markersColor').setAttribute('value', '(none)'); _getElementById('AxisColor').setAttribute('value', 'Black'); _getElementById('SeriesColor').setAttribute('value', '(none)'); _getElementById('showFirst').checked = false; _getElementById('showHigh').checked = false; _getElementById('showLast').checked = false; _getElementById('showLow').checked = false; _getElementById('showNegative').checked = false; _getElementById('showMarkers').checked = false; _getElementById('displayXAxis').checked = true; } function getSparklineSettings(sparkline) { var setting = sparkline.setting(); var sparklinetype = _getElementById("sparklinetype"); _selectOption(sparklinetype, sparkline.sparklineType() + ""); _getElementById('firstMarkerColor').setAttribute('value', setting.options.firstMarkerColor); _getElementById('highMarkerColor').setAttribute('value', setting.options.highMarkerColor); _getElementById('lastMarkerColor').setAttribute('value', setting.options.lastMarkerColor); _getElementById('lowMarkerColor').setAttribute('value', setting.options.lowMarkerColor); _getElementById('negativeMarkerColor').setAttribute('value', setting.options.negativeColor); _getElementById('markersColor').setAttribute('value', setting.options.markersColor); _getElementById('AxisColor').setAttribute('value', setting.options.axisColor); _getElementById('SeriesColor').setAttribute('value', setting.options.seriesColor); _getElementById('showFirst').checked = setting.options.showFirst; _getElementById('showHigh').checked = setting.options.showHigh; _getElementById('showLast').checked = setting.options.showLast; _getElementById('showLow').checked = setting.options.showLow; _getElementById('showNegative').checked = setting.options.showNegative; _getElementById('showMarkers').checked = setting.options.showMarkers; _getElementById('displayXAxis').checked = setting.options.displayXAxis; } function buildSparklineSettings(setting) { if (setting == null) setting = new spreadNS.Sparklines.SparklineSetting(); var firstMarkerColor = _getElementById('firstMarkerColor'); if (firstMarkerColor.value != '(none)') setting.options.firstMarkerColor = firstMarkerColor.value; var highMarkerColor = _getElementById('highMarkerColor'); if (highMarkerColor.value != '(none)') setting.options.highMarkerColor = highMarkerColor.value; var lastMarkerColor = _getElementById('lastMarkerColor'); if (lastMarkerColor.value != '(none)') setting.options.lastMarkerColor = lastMarkerColor.value; var lowMarkerColor = _getElementById('lowMarkerColor'); if (lowMarkerColor.value != '(none)') setting.options.lowMarkerColor = lowMarkerColor.value; var negativeMarkerColor = _getElementById('negativeMarkerColor'); if (negativeMarkerColor.value != '(none)') setting.options.negativeColor = negativeMarkerColor.value; var markersColor = _getElementById('markersColor'); if (markersColor.value != '(none)') setting.options.markersColor = markersColor.value; var AxisColor = _getElementById('AxisColor'); if (AxisColor.value != '(none)') setting.options.axisColor = AxisColor.value; var SeriesColor = _getElementById('SeriesColor'); if (SeriesColor.value != '(none)') setting.options.seriesColor = SeriesColor.value; setting.options.showFirst = _getElementById('showFirst').checked; setting.options.showHigh = _getElementById('showHigh').checked; setting.options.showLast = _getElementById('showLast').checked; setting.options.showLow = _getElementById('showLow').checked; setting.options.showNegative = _getElementById('showNegative').checked; setting.options.showMarkers = _getElementById('showMarkers').checked; setting.options.displayXAxis = _getElementById('displayXAxis').checked; return setting; } } function _getElementById(id) { return document.getElementById(id); } function _selectOption(select, value) { for (var i = 0; i < select.length; i++) { var op = select.options[i]; if (op.value === value) { op.selected = true; } else { op.selected = false; } } }
<!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" class="sample-spreadsheets"></div> <div class="options-container"> <p style="font-size:20px;padding:4px 0px">Select a sparkline in the spreadsheet then change its properties</p> <div class="option-row"> <div class="option"> <input id='btnChangeSetting' type='button' value='Change Setting'/> </div> </div> <div class="option-row"> <div class="option"> <input id='showFirst' type='checkbox'/> <label for="showFirst">showFirst</label> </div> <div class="option"> <input id='showHigh' type='checkbox'/> <label for="showHigh">showHigh</label> </div> <div class="option"> <input id='showLast' type='checkbox'/> <label for="showLast">showLast</label> </div> <div class="option"> <input id='showLow' type='checkbox'/> <label for="showLow">showLow</label> </div> <div class="option"> <input id='showNegative' type='checkbox'/> <label for="showNegative">showNegative</label> </div> <div class="option"> <input id='showMarkers' type='checkbox'/> <label for="showMarkers">showMarkers</label> </div> <div class="option"> <input id="displayXAxis" type="checkbox"/> <label for="displayXAxis">displayXAxis</label> </div> </div> <div class="option-row"> <div class="option"> <label for="firstMarkerColor" class="block">First Marker Color</label> <input id='firstMarkerColor' class="control" value="(none)"/> </div> <div class="option"> <label for="sparklinetype" class="block">Type:</label> <select id="sparklinetype" class="control"> <option value="0">line</option> <option value="1">column</option> <option value="2">winloss</option> </select> </div> <div class="option"> <label for="highMarkerColor" class="block">High Marker Color</label> <input id='highMarkerColor' class="control" value="Blue"/> </div> <div class="option"> <label for="lastMarkerColor" class="block">Last Marker Color</label> <input id='lastMarkerColor' class="control" value="(none)"/> </div> <div class="option"> <label for="lowMarkerColor" class="block">Low Marker Color</label> <input id='lowMarkerColor' class="control" value="Blue"/> </div> <div class="option"> <label for="negativeMarkerColor" class="block">Negative Marker Color</label> <input id='negativeMarkerColor' class="control" value="Brown"/> </div> <div class="option"> <label for="markersColor" class="block">Markers Color</label> <input id='markersColor' class="control" value="(none)"/> </div> <div class="option"> <label for="AxisColor" class="block">Axis Color</label> <input id='AxisColor' class="control" value="Black"/> </div> <div class="option"> <label for="SeriesColor" class="block">SeriesColor</label> <input id='SeriesColor' class="control" value="(none)"/> </div> </div> </div> </div> </body> </html>
.sample { position: relative; height: 100%; overflow: auto; } .sample::after { display: block; content: ""; clear: both; } .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; margin-top: 10px; } .option { padding-bottom: 6px; } .block { display: block; padding: 6px 0; } .control { padding: 4px 8px; box-sizing: border-box; width: 100%; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; }