阶梯瀑布函数迷你图

阶梯瀑布函数迷你图可以用来突出一组值中最重要的项。

你可以通过阶梯瀑布函数创建一个阶梯瀑布函数迷你图,比如: =PARETOSPARKLINE(points, pointIndex, colorRange?, target?, target2?, highlightPosition?, label?, vertical?). 此函数有以下参数: points: 表示所描述的数据区域,比如 "B2:B7"。 pointIndex: 类型为数字或者引用,表示某个数据点在所有数据点中的索引,而且这个索引大于等于1,比如 1 或者 "D2"。 colorRange: (可选参数)这个引用范围内的单元格中包含了所有盒子的颜色,比如 "D2:D7";默认值是空(null)。 target: (可选参数)类型为数字或者引用,表示"目标"线的位置,比如 0.5;默认值是空(null)。 target2: (可选参数)类型为数字或者引用,表示"目标二"线的位置,比如 0.5;默认值是空(null)。 highlightPosition: (可选参数)类型为数字或者引用,表示哪个数据点对应的盒子应该被设置为红色,比如 3;默认值是空(null)。 label: (可选参数)类型为数字,表示这个数据点对应的标签应该显示成累积的百分比(label = 1),还是这个数据点单个的百分比(label = 2)或者不显示标签(label = 0),默认值是不显示。 vertical: (可选参数)类型为布尔值,表示这个盒子是垂直显示还是水平显示;默认值是false,也就是水平显示。 targetColor: (可选参数) 类型为颜色字符串,表示目标线的颜色。 target2Color: (可选参数) 类型为颜色字符串,表示目标线2的颜色。 labelColor: (可选参数) 类型为颜色字符串,表示标记的前景色。 barSize: (可选参数) 类型为数值,表示线形相对于单元格宽高的百分比数,值需大于0且小于等于1.
var spreadNS = GC.Spread.Sheets, spread; window.onload = function () { spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 2 }); initSpread(spread); document.getElementById("labelType").addEventListener('change',applyChanges); document.getElementById("highlightPosition").addEventListener('change',applyChanges); document.getElementById("target1").addEventListener('change',applyChanges); document.getElementById("target2").addEventListener('change',applyChanges); document.getElementById("color1").addEventListener('change',applyChanges); document.getElementById("color2").addEventListener('change',applyChanges); }; function applyChanges() { var sheet = spread.getActiveSheet(); var labelType = parseInt(document.getElementById("labelType").value, 10), highlightPosition = parseInt(document.getElementById("highlightPosition").value, 10); if (!isNaN(labelType) && !isNaN(highlightPosition)) { var formulaPart1, formulaPart2; if (spread.getActiveSheetIndex() == 0) { var formula = '=PARETOSPARKLINE($B$3:$B$8,ROW()-2,$C$3:$C$8,'+ document.getElementById('target1').value + ',' + document.getElementById('target2').value +',' + highlightPosition + ','+ labelType +',false,"'+ document.getElementById('color1').value + '","' + document.getElementById('color2').value +'",$C$3:$C$8,$D$3:$D$8)'; for (var i = 2; i <= 7; i++) { sheet.setFormula(i, 4, formula); } } else { var formulaVertical = '=PARETOSPARKLINE($B$3:$G$3,COLUMN()-1,$B$4:$G$4,'+ document.getElementById('target1').value + ',' + document.getElementById('target2').value +',' + highlightPosition + ','+ labelType +',true,"'+ document.getElementById('color1').value + '","' + document.getElementById('color2').value +'",$B$4:$G$4,$B$5:$G$5)'; for (var index = 1; index < 7; index++) { sheet.setFormula(5, index, formulaVertical); } } } } 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.setArray(1, 0, [ ["Reason","Cases","Color","Bar Size","Diagram"], ["Traffic",20,"#CCCCCC",0.1], ["Child care",15,"#82BC00",0.3], ["Public transportation",13,"#F7A711",0.5], ["Weather",6,"#00C2D6",0.7], ["Overslept",4,"#FFE7BA",0.8], ["Emergency",1,"#000000",1] ]); sheet.setValue(10, 0, '*Result: By the sparkline above can draw a conclusion that the reasons for 80% of the employees be late are "traffic", "child care" and "public transportation".'); for (var index = 2; index < 8; index++) { sheet.setFormula(index, 4, '=PARETOSPARKLINE($B$3:$B$8,ROW()-2,$C$3:$C$8,'+ document.getElementById('target1').value + ',' + document.getElementById('target2').value +',0,2,false,"'+ document.getElementById('color1').value + '","' + document.getElementById('color2').value +'",$C$3:$C$8,$D$3:$D$8)'); } //styling for(var i = 1; i<4; i++) { sheet.setColumnWidth(i, 80); } sheet.setColumnWidth(4, 340); sheet.setColumnWidth(0, 140); sheet.setRowHeight(0, 35); sheet.addSpan(0, 0, 1, 5); sheet.getCell(0, 0).value("Late Arrivals by Reported Cause") .font("17px Arial") .vAlign(spreadNS.VerticalAlign.center) .backColor("gray") .foreColor("white"); sheet.getRange(2,0,6,4).hAlign(spreadNS.HorizontalAlign.left); sheet.getRange(1, 0, 1, 5) .font("bold 13px Arial") .setBorder(new spreadNS.LineBorder("black", spreadNS.LineStyle.thin), { bottom: true }); sheet.resumePaint(); } function initVerticalSparkline(sheet, name) { sheet.suspendPaint(); sheet.name(name); sheet.setArray(1, 0, [ ["Reason","Traffic","Child care","Public transportation","Weather","Overslept","Emergency"], ["Cases",20,15,13,5,4,1], ["Color","#CCCCCC","#82BC00","#F7A711","#00C2D6","#FFE7BA","#000000"], ["Bar Size",0.1,0.3,0.5,0.7,0.8,1], ["Diagram"] ]); sheet.setValue(8, 0, '*Result: By the sparkline above can draw a conclusion that the reasons for 80% of the employees be late are "traffic", "child care" and "public transportation".'); for (var index = 1; index < 7; index++) { sheet.setFormula(5, index, '=PARETOSPARKLINE($B$3:$G$3,COLUMN()-1,$B$4:$G$4,'+ document.getElementById('target1').value + ',' + document.getElementById('target2').value +',0,2,true,"'+ document.getElementById('color1').value + '","' + document.getElementById('color2').value +'",$B$4:$G$4,$B$5:$G$5)'); } //styling for(var i = 0; i<7; i++) { sheet.setColumnWidth(i, 120); } sheet.setColumnWidth(3, 150); sheet.setRowHeight(5, 160); sheet.setRowHeight(0, 35); sheet.addSpan(0, 0, 1, 7); sheet.getCell(0, 0).value("Late Arrivals by Reported Cause") .font("17px Arial") .vAlign(spreadNS.VerticalAlign.center) .backColor("gray") .foreColor("white"); sheet.getRange(1, 0, 5, 1) .font("bold 13px Arial") .setBorder(new spreadNS.LineBorder("black", spreadNS.LineStyle.thin), { right: true }); sheet.getRange(1,0,4,7).hAlign(spreadNS.HorizontalAlign.left); 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-software/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/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> <div class="options-container"> <div class="option-row"> <label><b>Sparkline Settings:</b></label> </div> <hr> <div class="option-row"> <label for="labelType">Label type:</label> <select id="labelType"> <option value="0">None</option> <option value="1">Cumulated percentage</option> <option value="2" selected>Single percentage</option> </select> </div> <div class="option-row"> <label for="highlightPosition">Highlight item:</label> <select id="highlightPosition"> <option value="0" selected>None</option> <option value="1">1st</option> <option value="2">2nd</option> <option value="3">3rd</option> <option value="4">4th</option> <option value="5">5th</option> <option value="6">6th</option> </select> </div> <div class="option-row"> <label for="target1">Target1:</label> <input type="text" style="margin: 0 20px 0 6px;width:120px;" id="target1" value="0.5" /> </div> <div class="option-row"> <label for="target2">Target2:</label> <input type="text" style="margin: 0 20px 0 6px;width:120px;" id="target2" value="0.8" /> </div> <div class="option-row"> <label for="color1">Target1 Color:</label> <select id="color1" style="margin: 0 20px 0 6px"> <option value="#BBBBBB" selected>Grey</option> <option value="#82BC00">Green</option> <option value="#000000">Black</option> <option value="#F7A711">Orange</option> </select> </div> <div class="option-row"> <label for="color2">Target2 Color:</label> <select id="color2" style="margin: 0 20px 0 6px"> <option value="#BBBBBB">Grey</option> <option value="#82BC00">Green</option> <option value="#000000">Black</option> <option value="#F7A711" selected>Orange</option> </select> </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-group { margin-bottom: 6px; } input, select { padding: 4px 6px; box-sizing: border-box; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; }