Overview

Similar to charts, sparklines provide a way to visualize your spreadsheet data at teh cell level, such as trends in a series of values, seasonal increases or decreases, or economic cycles. Sparklines include Cascade, BoxPlot, Bullet, HBar, VBar, Pareto, Pie, Area, Scatter, Spread, Stacked, Vari, Compatible, SparklineEx, Month, Year, and custom sparklines. Excel sparklines can also be imported.

To create a sparkline, use the setSparkline method to set the sparkline for a cell. You can use the getSparkline method to get the sparkline. For example: There are three types of sparklines. The SparklineType enumeration represents the sparkline type. line column winloss You can use all three sparklines above in Excel. But for the other sparklines, such as Compatible sparkline, are not supported in Excel unless you get an add-in extension which provides that support. If you want to remove the sparkline, use the removeSparkline method to remove the sparkline for the specified cell. For example: You also can use formula to create a sparkline, please see Compatible for details.
var spreadNS = GC.Spread.Sheets; window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss")); initSpread(spread); }; function initSpread(spread) { 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, 2, "Date axis range is C2-C9"); sheet.setValue(1, 2, new Date(2011, 0, 5)); sheet.setValue(2, 2, new Date(2011, 0, 1)); sheet.setValue(3, 2, new Date(2011, 1, 11)); sheet.setValue(4, 2, new Date(2011, 2, 1)); sheet.setValue(5, 2, new Date(2011, 1, 1)); sheet.setValue(6, 2, new Date(2011, 1, 3)); sheet.setValue(7, 2, new Date(2011, 2, 6)); sheet.setValue(8, 2, new Date(2011, 1, 19)); sheet.setColumnWidth(2,150); var data = new spreadNS.Range(1, 0, 8, 1); var dateAxis = new spreadNS.Range(1, 2, 8, 1); sheet.getCell(9, 0).text("Sparkline without 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.lineWeight = 3; 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 ); //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 ); //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 ); sheet.getCell(15, 0).text("Sparkline with dateAxis:"); sheet.getCell(16, 0).text("(1) Line"); sheet.getCell(16, 3).text("(2)Column"); sheet.getCell(16, 6).text("(3)Winloss"); //line sheet.addSpan(17, 0, 4, 3); setting = new spreadNS.Sparklines.SparklineSetting(); setting.options.showMarkers = true; setting.options.lineWeight = 3; 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(17, 0, data , spreadNS.Sparklines.DataOrientation.vertical , spreadNS.Sparklines.SparklineType.line , setting , dateAxis , spreadNS.Sparklines.DataOrientation.vertical ); //column sheet.addSpan(17, 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(17, 3, data , spreadNS.Sparklines.DataOrientation.vertical , spreadNS.Sparklines.SparklineType.column , setting , dateAxis , spreadNS.Sparklines.DataOrientation.vertical ); //winloss sheet.addSpan(17, 6, 4, 3); setting = new spreadNS.Sparklines.SparklineSetting(); setting.options.displayXAxis = true; setting.options.showNegative = true; sheet.setSparkline(17, 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) { updateSetting(sparkline); } else { initSetting(); } } function updateSetting(sparkline) { var type = sparkline.sparklineType(), orientation = sparkline.dataOrientation(), row = sparkline.row, column = sparkline.column; _getElementById("line_position").value = row + "," + column; var line_type = _getElementById("line_type"); _selectOption(line_type, type + ""); var line_orientation = _getElementById("line_orientation"); _selectOption(line_orientation, orientation + ""); } function initSetting() { _getElementById("line_position").value = ''; var line_type = _getElementById("line_type"); _selectOption(line_type, '0'); var line_orientation = _getElementById("line_orientation"); _selectOption(line_orientation, '0'); } function getActualCellRange(cellRange, rowCount, columnCount) { if (cellRange.row == -1 && cellRange.col == -1) { return new spreadNS.Range(0, 0, rowCount, columnCount); } else if (cellRange.row == -1) { return new spreadNS.Range(0, cellRange.col, rowCount, cellRange.colCount); } else if (cellRange.col == -1) { return new spreadNS.Range(cellRange.row, 0, cellRange.rowCount, columnCount); } return cellRange; }; _getElementById("btnAddSparkline").addEventListener('click',function () { var sheet = spread.getActiveSheet(); var range = getActualCellRange(sheet.getSelections()[0], sheet.getRowCount(), sheet.getColumnCount()); var rc = _getElementById("line_position").value.split(","); var r = parseInt(rc[0]); var c = parseInt(rc[1]); var orientation = parseInt(_getElementById("line_orientation").value); var type = parseInt(_getElementById("line_type").value); if (!isNaN(r) && !isNaN(c)) { sheet.setSparkline(r, c, range, orientation, type, new spreadNS.Sparklines.SparklineSetting()); } }); _getElementById("btnClearSparkline").addEventListener('click',function () { var sheet = spread.getActiveSheet(); var range = getActualCellRange(sheet.getSelections()[0], sheet.getRowCount(), sheet.getColumnCount()); for (var r = 0; r < range.rowCount; r++) { for (var c = 0; c < range.colCount; c++) { sheet.removeSparkline(r + range.row, c + range.col); } } }); } 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> <p>Add a cell sparkline by selecting the data range in the sheet, enter the destination cell row,column index (i.e. “2,5”) in the “Position” field, change the type and orientation then click the “Add Sparkline” button. You can also delete a sparkline by selecting it and clicking the “Clear Sparkline” button.</p> <div class="options-container"> <div class="option-group"> <label for="line_position">Position:</label> <input id="line_position" value="0,0" /> </div> <div class="option-group"> <label for="line_type" style="width: auto;">Type:</label> <select id="line_type" class="position"> <option value="0">line</option> <option value="1">column</option> <option value="2">winloss</option> </select> </div> <div class="option-group"> <label for="line_orientation">Orientation:</label> <select id="line_orientation" class="position"> <option value="0">Vertical</option> <option value="1">Horizontal</option> </select> </div> <div class="option-group"> <input type="button" value="Add Sparkline" id="btnAddSparkline"> <input type="button" value="Clear Sparkline" id="btnClearSparkline"> </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; } p{ padding:2px 10px; background-color:lavender; } .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-group { margin-bottom: 8px; } input, select { margin-top: 6px; padding: 4px 4px; width: 100%; box-sizing: border-box; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; }