基本应用

迷你图在显示 SpreadJS 数据的趋势时非常有用,特别是当你与其他人共享数据时。 使用迷你图(单个工作表单元格内的微型图表)可以直观地表示和显示数据趋势。 迷你图可以通过不同颜色吸引对某些内容(如季节性增加或降低或经济周期)的注意,并突出显示最大和最小值。

你可以使用 setSparkline 方法来给某个单元格设置迷你图。 并使用 getSparkline 方法来获取迷你图。例如: SpreadJS 支持 3 种迷你图. SparklineType 枚举表示了各种迷你图的类型: line column winloss 您可以在Excel中使用上面的所有三个sparklines。但是对于其他的sparklines,比如Compatible sparkline, Excel中不支持,除非您得到一个提供这种支持的外接程序扩展。 你可以使用 removeSparkline 方法来移除指定单元格的迷你图。例如: 你也可以使用公式来创建迷你图, 参见 Compatible.
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 name="spreadjs culture" content="zh-cn" /> <meta charset="utf-8" /> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <link rel="stylesheet" type="text/css" href="$DEMOROOT$/zh/purejs/node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity/spread-sheets-resources-zh/dist/gc.spread.sheets.resources.zh.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; }