子弹函数迷你图

子弹函数迷你图是一种可以在一小片地方展示大量数据的迷你图。

你可以通过子弹函数创建一个子弹函数迷你图,例如 =BULLETSPARKLINE(measure, target, maxi, good?, bad?, forecast?, tickunit?, colorScheme?, vertical?). 这个函数有以下这些参数: measure: 类型为数字或者引用,表示测量条的长度,例如 5 或者 "A1"。 target: 类型为数值或者引用,表示目标线的位置,例如 7 或者 "A2"。 maxi: 类型为数值或者引用,表示这个迷你图中的最大值,例如 10 或者 "A3"。 good: (可选参数)类型为数值或者引用,表示好值条的长度,例如 3 或者 "A4";默认值是0. bad: (可选参数)类型为数值或者引用,表示坏值条的长度,例如 1 或者 "A5";默认值是0。 forecast: (可选参数)类型为数值或者引用,表示预测值线的长度,例如 8 或者 "A6";默认值是0。 tickunit: (可选参数)类型为数值或者引用,表示刻度单位,例如 1 或者 "A7";默认值是0。 colorScheme: (可选参数)类型为颜色字符串,表示主体颜色的字符串,主体颜色会生成一组颜色来展示这个迷你图。默认值是 "#A0A0A0"。 vertical: (可选参数)类型为布尔值,表示是否让箱线函数迷你图垂直显示;默认值是false,也就是默认是水平显示。 measureColor: (可选参数) 类型为颜色字符串,表示测量条的颜色。 targetColor: (可选参数) 类型为颜色字符串,表示目标线的颜色。 maxiColor: (可选参数) 类型为颜色字符串,表示最大值的颜色。 goodColor: (可选参数) 类型为颜色字符串,表示好值条的颜色。 badColor: (可选参数) 类型为颜色字符串,表示坏值条的颜色。 forecastColor: (可选参数) 类型为颜色字符串,表示预测值线的颜色。 allowMeasureOverMaxi: (可选参数) 类型为布尔值,表示测量条是否能够超过最大区域,默认值为false。 barSize: (可选参数) 类型为数值,表示线形宽度对于单元格高宽的百分比. (数值需大于0且小于等于1)
var spreadNS = GC.Spread.Sheets; window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 2 }); initSpread(spread); }; function initSpread(spread) { spread.options.newTabVisible = false; initHorizontalSparkline(spread.sheets[0], "Horizontal"); initVerticalSparkline(spread.sheets[1], "Vertical"); }; function initHorizontalSparkline(sheet, name) { sheet.suspendPaint(); sheet.name(name); sheet.addSpan(0, 0, 1, 4); sheet.getCell(0, 0).value("Employee KPI").font("20px Arial").hAlign(spreadNS.HorizontalAlign.center).vAlign(spreadNS.VerticalAlign.center); sheet.setValue(1, 0, "Name"); sheet.setValue(1, 1, "Forecast"); sheet.setValue(1, 2, "Actuality"); sheet.setValue(1, 3, "Diagram"); sheet.getRange(1, 0, 1, 4).foreColor("white").backColor("Accent 4").hAlign(1).vAlign(1); sheet.setValue(2, 0, "Employee 1"); sheet.setValue(2, 1, 6); sheet.setValue(2, 2, 6); sheet.setValue(3, 0, "Employee 2"); sheet.setValue(3, 1, 8); sheet.setValue(3, 2, 7); sheet.setValue(4, 0, "Employee 3"); sheet.setValue(4, 1, 6); sheet.setValue(4, 2, 4); sheet.setValue(5, 0, "Employee 4"); sheet.setValue(5, 1, 7); sheet.setValue(5, 2, 9); sheet.setValue(6, 0, "Employee 5"); sheet.setValue(6, 1, 6); sheet.setValue(6, 2, 8); sheet.setValue(7, 0, "Employee 6"); sheet.setValue(7, 1, 8); sheet.setValue(7, 2, 7); sheet.setValue(8, 0, "Settings:"); sheet.setValue(9, 0, "target"); sheet.setValue(9, 1, 7); sheet.setValue(10, 0, "maxi"); sheet.setValue(10, 1, 10); sheet.setValue(11, 0, "good"); sheet.setValue(11, 1, 8); sheet.setValue(12, 0, "bad"); sheet.setValue(12, 1, 5); sheet.setValue(13, 0, "color scheme"); sheet.setValue(13, 1, "gray"); sheet.setValue(14, 0, "measure color"); sheet.setValue(14, 1, "#FFC0CB"); sheet.setValue(15, 0, "target color"); sheet.setValue(15, 1, "#FF4500"); sheet.setValue(16, 0, "maxi color"); sheet.setValue(16, 1, "#00FFFF"); sheet.setValue(17, 0, "good color"); sheet.setValue(17, 1, "#7FFF00"); sheet.setValue(18, 0, "bad color"); sheet.setValue(18, 1, " #FF0000"); sheet.setValue(19, 0, "forecast color"); sheet.setValue(19, 1, "#000080"); sheet.setValue(20, 0, "allow measure over maxi"); sheet.setValue(20, 1, true); sheet.setValue(21, 0, "bar size"); sheet.setValue(21, 1, 0.8); for (var index = 2; index < 8; index++) { sheet.setFormula(index, 3, '=BULLETSPARKLINE($C' + (index + 1) + ',$B$10,$B$11,$B$12,$B$13,$B' + (index + 1) + ',1,$B$14,false,$B$15,$B$16,$B$17,$B$18,$B$19,$B$20,$B$21,$B$22)'); } sheet.setRowHeight(0, 30); for (var i = 2; i < 8; i++) { sheet.setRowHeight(i, 40); } sheet.setColumnWidth(0, 100); sheet.setColumnWidth(1, 100); sheet.setColumnWidth(2, 100); sheet.setColumnWidth(3, 200); sheet.resumePaint(); } function initVerticalSparkline(sheet, name) { sheet.suspendPaint(); sheet.name(name); sheet.addSpan(0, 0, 1, 7); sheet.getCell(0, 0).value("Employee KPI").font("20px Arial").hAlign(spreadNS.HorizontalAlign.center).vAlign(spreadNS.VerticalAlign.center); sheet.setArray(1, 0, [ [ "Name", "Employee 1", "Employee 2", "Employee 3", "Employee 4", "Employee 5", "Employee 6" ], [ "Forecast", 6, 8, 8, 7, 6, 8 ], ["Actuality", 6, 7, 4, 9, 8, 7], ["Diagram"] ]); sheet.getRange(1, 0, 4, 1).foreColor("white").backColor("Accent 4").hAlign(spreadNS.HorizontalAlign.center).vAlign(spreadNS.VerticalAlign.center); sheet.setValue(8, 0, "Settings:"); sheet.setValue(9, 0, "target"); sheet.setValue(9, 1, 7); sheet.setValue(10, 0, "maxi"); sheet.setValue(10, 1, 10); sheet.setValue(11, 0, "good"); sheet.setValue(11, 1, 8); sheet.setValue(12, 0, "bad"); sheet.setValue(12, 1, 5); sheet.setValue(13, 0, "color scheme"); sheet.setValue(13, 1, "red"); sheet.setValue(14, 0, "measure color"); sheet.setValue(14, 1, "#FFC0CB"); sheet.setValue(15, 0, "target color"); sheet.setValue(15, 1, "#FF4500"); sheet.setValue(16, 0, "maxi color"); sheet.setValue(16, 1, "#00FFFF"); sheet.setValue(17, 0, "good color"); sheet.setValue(17, 1, "#7FFF00"); sheet.setValue(18, 0, "bad color"); sheet.setValue(18, 1, " #FF0000"); sheet.setValue(19, 0, "forecast color"); sheet.setValue(19, 1, "#000080"); sheet.setValue(20, 0, "allow measure over maxi"); sheet.setValue(20, 1, true); sheet.setValue(21, 0, "bar size"); sheet.setValue(21, 1, 0.8); for (var index = 1; index < 7; index++) { var letter = String.fromCharCode(65 + index); sheet.setFormula(4, index, '=BULLETSPARKLINE(' + letter + '$4,$B$10,$B$11,$B$12,$B$13,' + letter + '$3,1,$B$14,true,$B$15,$B$16,$B$17,$B$18,$B$19,$B$20,$B$21,$B$22)'); } sheet.setRowHeight(0, 30); sheet.setColumnWidth(0, 100); for (var i = 1; i < 7; i++) { sheet.setColumnWidth(i, 80); } sheet.setRowHeight(4, 120); sheet.resumePaint(); }
<!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" 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; }