Vari

A vari sparkline is useful for comparing different options of an item to similar items.

<p>You can create a variance sparkline using the VariSparkline function in a formula: <strong>=VARISPARKLINE(variance, reference?, mini?, maxi?, mark?, tickunit?, legend?, colorPositive?, colorNegative?, vertical?)</strong>.</p> <p>The function has the following parameters:</p> <p><strong>variance</strong>: Number or reference that represents the length of the bar, such as 2 or "A1".</p> <p><strong>reference</strong>: (optional) Number or reference that represents the location of the reference line, such as 0 or "A2"; default value is 0.</p> <p><strong>mini</strong>: (optional) Number or reference that represents the minimum values of the sparkline, such as -5 or "A3"; default value is -1.</p> <p><strong>maxi</strong>: (optional) Number or reference that represents the maximum values of the sparkline, such as 5 or "A4"; default value is 1.</p> <p><strong>mark</strong>: (optional) Number or reference that represents the position of the mark line, such as 3 or "A5"; default value is 0.</p> <p><strong>tickunit</strong>: (optional) Number or reference that represents the tick unit, such as 1 or "A6"; default value is 0.</p> <p><strong>legend</strong>: (optional) Boolean that represents whether to display the text. The default value is false.</p> <p><strong>colorPositive</strong>: (optional) String that represents the color scheme for when the variance is larger than the reference; default value is "green".</p> <p><strong>colorNegative</strong>: (optional) String that represents the color scheme for when the variance is smaller than the reference; default value is "red".</p> <p><strong>vertical</strong>: (optional) Boolean that represents whether the box's direction is vertical or horizontal; default value is false.</p>
window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 2 }); initSpread(spread); }; function initSpread(spread) { var common = ["Screen Size(inches)", "RAM Capacity(G)", "Front-facing Camera", "Rear Camera", "Weight(g)", "Battery Capacity(mAH)", "CPU Cores Number", "CPU Frequency(GHZ)"]; var typea = { lscreenSize: 5, lramCap: 3, lfCamera: 800, lrCamera: 1300, lweight: 149, lbatteryCap: 3080, lcpuCoreNum: 4, lcpuFreq: 2.5 }; var typeb = { lscreenSize: 5.36, lramCap: 2, lfCamera: 200, lrCamera: 2070, lweight: 147, lbatteryCap: 3100, lcpuCoreNum: 8, lcpuFreq: 2 }; var phone1 = { rscreenSize: 4, rramCap: 1, rfCamera: 120, rrCamera: 800, rweight: 112, rbatteryCap: 1440, rcpuCoreNum: 2, rcpuFreq: 1.04 }; var phone2 = { rscreenSize: 4.7, rramCap: 1, rfCamera: 120, rrCamera: 800, rweight: 129, rbatteryCap: 1800, rcpuCoreNum: 2, rcpuFreq: 1.32 }; var mobileType = ["Type A", "Type B", "Phone I", "Phone II"]; var dataSource = { "Type A": typea, "Type B": typeb, "Phone I": phone1, "Phone II": phone2 }; var spreadNS = GC.Spread.Sheets; spread.options.newTabVisible = false; var sheet = spread.sheets[0], sheet2 = spread.sheets[1]; sheet.name("Horizontal"); sheet2.name("Vertical"); sheet.bind(spreadNS.Events.ValueChanged, function (event, data) { var col = data.col; var row = data.row; var sheet = data.sheet; if (row === 1 && (col === 1 || col === 3)) { var newValue = data.newValue; var updateSource; if (col === 1) { var rValue = sheet.getValue(1, 3); updateSource = extend({}, dataSource[newValue], dataSource[rValue]); } else { var lValue = sheet.getValue(1, 1); updateSource = extend({}, dataSource[newValue], dataSource[lValue]); } var source = new spreadNS.Bindings.CellBindingSource(updateSource); sheet.setDataSource(source); } }) sheet.suspendPaint(); sheet.addSpan(0, 0, 1, 5); sheet.getCell(0, 0) .value("Mobile Phone Contrast") .font("25px 'Comic Sans MS'") .hAlign(spreadNS.HorizontalAlign.center) .vAlign(spreadNS.VerticalAlign.center) .backColor("purple") .foreColor("white"); sheet.addSpan(1, 1, 1, 2); sheet.addSpan(1, 3, 1, 2); sheet.setRowHeight(0, 60); sheet.setRowHeight(1, 40); sheet.setColumnWidth(0, 160); sheet.setColumnWidth(2, 200); sheet.setColumnWidth(3, 200); for (var i = 2; i < 10; i++) { sheet.setRowHeight(i, 40); sheet.getCell(i, 0).value(common[i - 2]).font("bold 14px Arial"); } var androidType = new spreadNS.CellTypes.ComboBox(); androidType.items([mobileType[0], mobileType[1]]); sheet.setCellType(1, 1, androidType); sheet.getCell(1, 1).value(mobileType[0]).hAlign(spreadNS.HorizontalAlign.center).vAlign(spreadNS.VerticalAlign.center); var iphoneType = new spreadNS.CellTypes.ComboBox(); iphoneType.items([mobileType[2], mobileType[3]]); sheet.setCellType(1, 3, iphoneType); sheet.getCell(1, 3).value(mobileType[3]).hAlign(spreadNS.HorizontalAlign.center).vAlign(spreadNS.VerticalAlign.center); sheet.setBindingPath(2, 1, "lscreenSize"); sheet.setBindingPath(3, 1, "lramCap"); sheet.setBindingPath(4, 1, "lfCamera"); sheet.setBindingPath(5, 1, "lrCamera"); sheet.setBindingPath(6, 1, "lweight"); sheet.setBindingPath(7, 1, "lbatteryCap"); sheet.setBindingPath(8, 1, "lcpuCoreNum"); sheet.setBindingPath(9, 1, "lcpuFreq"); sheet.setBindingPath(2, 4, "rscreenSize"); sheet.setBindingPath(3, 4, "rramCap"); sheet.setBindingPath(4, 4, "rfCamera"); sheet.setBindingPath(5, 4, "rrCamera"); sheet.setBindingPath(6, 4, "rweight"); sheet.setBindingPath(7, 4, "rbatteryCap"); sheet.setBindingPath(8, 4, "rcpuCoreNum"); sheet.setBindingPath(9, 4, "rcpuFreq"); var source = new spreadNS.Bindings.CellBindingSource(extend({}, typea, phone2)); sheet.setDataSource(source); setFormula(sheet, sheet2); sheet.resumePaint(); // custom sheet2 sheet2.suspendPaint(); sheet2.setRowHeight(0, 60); sheet2.addSpan(0, 0, 1, 9); sheet2.getCell(0, 0) .value("Mobile Phone Contrast") .font("25px 'Comic Sans MS'") .hAlign(spreadNS.HorizontalAlign.center) .vAlign(spreadNS.VerticalAlign.center) .backColor("purple") .foreColor("white"); for (var r = 2; r <= 10; r++) { sheet2.setValue(1, r - 1, sheet.getValue(r, 0)); sheet2.setColumnWidth(r - 1, 120); } sheet2.setRowHeight(1, 40); sheet2.setRowHeight(2, 140); sheet2.setRowHeight(3, 140); sheet2.getRange(1, 0, 1, 9).wordWrap(true).font("bold 14px Arial"); sheet2.resumePaint(); }; function setFormula(sheet, sheet2) { var sheetName = sheet.name(); sheet2.setFormula(2, 0, '=Horizontal!B2'); sheet2.setFormula(3, 0, '=Horizontal!D2'); for (var i = 2; i < 10; i++) { var lValue = sheetName + "!B" + (i+1); var rValue = sheetName + "!E" + (i+1); var lvariance, rvariance; if (i === 7) { //weight lvariance = "ROUND(-1*" + "(" + lValue + "-" + rValue + ")/" + lValue + ",2)"; } else { lvariance = "ROUND(" + "(" + lValue + "-" + rValue + ")/" + lValue + ",2)"; } rvariance = "-1*" + lvariance; if (i === 10) { sheet.setFormula(i, 2, "VARISPARKLINE(" + lvariance + ",0,,,,0.2,true)"); sheet.setFormula(i, 3, "VARISPARKLINE(" + rvariance + ",0,,,,0.2,true)"); sheet2.setFormula(2, i - 1, "VARISPARKLINE(" + lvariance + ",0,,,,0.2,true,,,true)"); sheet2.setFormula(3, i - 1, "VARISPARKLINE(" + lvariance + ",0,,,,0.2,true,,,true)"); } else { sheet.setFormula(i, 2, "VARISPARKLINE(" + lvariance + ",0,,,,,true)"); sheet.setFormula(i, 3, "VARISPARKLINE(" + rvariance + ",0,,,,,true)"); sheet2.setFormula(2, i - 1, "VARISPARKLINE(" + lvariance + ",0,,,,,true,,,true)"); sheet2.setFormula(3, i - 1, "VARISPARKLINE(" + rvariance + ",0,,,,,true,,,true)"); } } } function extend() { var deep = false; var name, options, src, copy, clone, copyIsArray; var length = arguments.length; var i = 1; var target = arguments[0] || {}; if (typeof target == 'boolean') { deep = target; target = arguments[i] || {}; i++; } if (typeof target !== "object" && !isFunction(target)) { target = {}; } for (; i < length; i++) { options = arguments[i]; if (options != null) { for (name in options) { src = target[name]; copy = options[name]; if (target === copy) { continue; } if (deep && copy && (isPlainObject(copy) || (copyIsArray = Array.isArray(copy)))) { if (copyIsArray) { copyIsArray = false; clone = src && Array.isArray(src) ? src : []; } else { clone = src && isPlainObject(src) ? src : {}; } target[name] = extend(deep, clone, copy); } else if (copy !== undefined) { target[name] = copy; } } } } return target; };
<!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; }