Pareto

The pareto sparkline can be used to highlight the most important items in a set of values.

<p>You can create a pareto sparkline using the ParetoSparkline function in a formula: <strong>=PARETOSPARKLINE(points, pointIndex, colorRange?, target?, target2?, highlightPosition?, label?, vertical?, targetColor?, target2Color?, labelColor?, barSize?)</strong>.</p> <p>The function has the following parameters:</p> <p><strong>points</strong>: Reference that represents the range of cells that contains the values, such as "B2:B7"</p> <p><strong>pointIndex</strong>: Number or reference that represents the segment's index of the points, The pointIndex is &gt;= 1 such as 1 or "D2".</p> <p><strong>colorRange</strong>: (optional) Reference that represents the range of cells containing the color for the segment box, such as "D2:D7"; default value is null.</p> <p><strong>target</strong>: (optional) Number or reference that represents the "target" line position, such as 0.5; default value is null.</p> <p><strong>target2</strong>: (optional) Number or reference that represents the "target2" line position, such as 0.5; default value is null.</p> <p><strong>highlightPosition</strong>: (optional) Number or reference that represents the rank of the segment to be colored in red, such as 3; default value is null.</p> <p><strong>label</strong>: (optional) Number that represents whether the segment's label is displayed as the cumulated percentage (label = 1) or the single percentage (label = 2) or none (label = 0), default value is none.</p> <p><strong>vertical</strong>: (optional) Boolean that represents whether the box's direction is vertical; default value is false.</p> <p><strong>targetColor</strong>: (optional) String that represents the target line color.</p> <p><strong>target2Color</strong>: (optional) String that represents the target2 line color.</p> <p><strong>labelColor</strong>: (optional) String that represents the label fore color.</p> <p><strong>barSize</strong>: (optional) Number that represents that the percentage of bar width/height according to the cell height/ width. (value &gt; 0 &amp;&amp; value &lt;= 1)</p>
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); }; 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) { formulaPart1 = "=PARETOSPARKLINE(B2:B7,", formulaPart2 = ",C2:C7,B8,B9," + highlightPosition + "," + labelType + ",false,B10,B11,C2:C7,D2:D7)"; for (var i = 1; i <= 6; i++) { var f = formulaPart1 + i + formulaPart2; sheet.setFormula(i, 4, formulaPart1 + i + formulaPart2); } } else { formulaPart1 = "=PARETOSPARKLINE(A3:F3,", formulaPart2 = ",A4:F4,B8,B9," + highlightPosition + "," + labelType + ",true,B10,B11,A4:F4,A5:F5)"; for (var i = 1; i <= 6; i++) { var f = formulaPart1 + i + formulaPart2; sheet.setFormula(5, i - 1, formulaPart1 + i + formulaPart2); } } } } 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, 5); sheet.getCell(0, 0).value("The Reason of Being Late") .font("20px Arial") .hAlign(spreadNS.HorizontalAlign.center) .vAlign(spreadNS.VerticalAlign.center) .backColor("purple") .foreColor("white"); sheet.getRange(1, 2, 6, 1).setBorder(new spreadNS.LineBorder("transparent", spreadNS.LineStyle.thin), { inside: true }, spreadNS.SheetArea.viewport); sheet.setValue(1, 0, "Traffic"); sheet.setValue(2, 0, "Child care"); sheet.setValue(3, 0, "Public transportation"); sheet.setValue(4, 0, "Weather"); sheet.setValue(5, 0, "Overslept"); sheet.setValue(6, 0, "Emergency"); sheet.setValue(7, 0, "target"); sheet.setValue(8, 0, "target2"); sheet.setValue(9, 0, "targetColor"); sheet.setValue(10, 0, "target2Color"); sheet.setValue(1, 1, 20); sheet.setValue(2, 1, 15); sheet.setValue(3, 1, 13); sheet.setValue(4, 1, 5); sheet.setValue(5, 1, 4); sheet.setValue(6, 1, 1); sheet.setValue(7, 1, 0.5); sheet.setValue(8, 1, 0.8); sheet.setValue(9, 1, "#006400"); sheet.setValue(10, 1, "#00BFFF"); sheet.setValue(1, 2, "#FF1493"); sheet.setValue(2, 2, "#FFE7BA"); sheet.setValue(3, 2, "#FFD700"); sheet.setValue(4, 2, "#FFAEB9"); sheet.setValue(5, 2, "#FF8C69"); sheet.setValue(6, 2, "#FF83FA"); sheet.setValue(1, 3, 0.1); sheet.setValue(2, 3, 0.3); sheet.setValue(3, 3, 0.5); sheet.setValue(4, 3, 0.7); sheet.setValue(5, 3, 0.9); sheet.setValue(6, 3, 1); sheet.addSpan(7, 2, 4, 3); sheet.getCell(7, 2).wordWrap(true); sheet.setValue(7, 2, '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".'); sheet.setColumnWidth(0, 140); sheet.setColumnWidth(1, 80); sheet.setColumnWidth(2, 80); sheet.setColumnWidth(3, 80); sheet.setColumnWidth(4, 340); sheet.setRowHeight(0, 30); sheet.setRowHeight(1, 30); sheet.setRowHeight(2, 30); sheet.setRowHeight(3, 30); sheet.setRowHeight(4, 30); sheet.setRowHeight(5, 30); sheet.setRowHeight(6, 30); sheet.setRowHeight(7, 30); for (var index = 0; index < 7; index++) { sheet.setFormula(index, 4, '=PARETOSPARKLINE($B$2:$B$7,ROW()-1,$C$2:$C$7,$B$8,$B$9,4,2,false,$B$10,$B$11,C2:C7,D2:D7)'); } sheet.setText(0, 1, "Points", spreadNS.SheetArea.colHeader); sheet.setText(0, 2, "Color range", spreadNS.SheetArea.colHeader); sheet.resumePaint(); } function initVerticalSparkline(sheet, name) { sheet.suspendPaint(); sheet.name(name); sheet.addSpan(0, 0, 1, 6); sheet.getCell(0, 0).value("The Reason of Being Late") .font("20px Arial") .hAlign(spreadNS.HorizontalAlign.center) .vAlign(spreadNS.VerticalAlign.center) .backColor("purple") .foreColor("white"); sheet.getRange(4, 0, 1, 6).setBorder(new spreadNS.LineBorder("transparent", spreadNS.LineStyle.thin), { inside: true }, spreadNS.SheetArea.viewport); sheet.setValue(1, 0, "Traffic"); sheet.setValue(1, 1, "Child care"); sheet.setValue(1, 2, "Public transportation"); sheet.setValue(1, 3, "Weather"); sheet.setValue(1, 4, "Overslept"); sheet.setValue(1, 5, "Emergency"); sheet.setValue(7, 0, "target"); sheet.setValue(8, 0, "target2"); sheet.setValue(9, 0, "targetColor"); sheet.setValue(10, 0, "target2Color"); sheet.setValue(2, 0, 20); sheet.setValue(2, 1, 15); sheet.setValue(2, 2, 13); sheet.setValue(2, 3, 5); sheet.setValue(2, 4, 4); sheet.setValue(2, 5, 1); sheet.setValue(7, 1, 0.5); sheet.setValue(8, 1, 0.8); sheet.setValue(9, 1, "#006400"); sheet.setValue(10, 1, "#00BFFF"); sheet.setValue(3, 0, "#FF1493"); sheet.setValue(3, 1, "#FFE7BA"); sheet.setValue(3, 2, "#FFD700"); sheet.setValue(3, 3, "#FFAEB9"); sheet.setValue(3, 4, "#FF8C69"); sheet.setValue(3, 5, "#FF83FA"); sheet.setValue(4, 0, 0.1); sheet.setValue(4, 1, 0.3); sheet.setValue(4, 2, 0.5); sheet.setValue(4, 3, 0.7); sheet.setValue(4, 4, 0.9); sheet.setValue(4, 5, 1); sheet.addSpan(7, 2, 2, 4); sheet.getCell(7, 2).wordWrap(true); sheet.setValue(7, 2, '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".'); sheet.setColumnWidth(0, 80); sheet.setColumnWidth(1, 100); sheet.setColumnWidth(2, 140); sheet.setColumnWidth(3, 100); sheet.setColumnWidth(4, 100); sheet.setColumnWidth(5, 100); sheet.setRowHeight(5, 160); sheet.setRowHeight(7, 30); sheet.setRowHeight(8, 30); for (var index = 0; index < 6; index++) { sheet.setFormula(5, index, '=PARETOSPARKLINE($A$3:$F$3,COLUMN(),$A$4:$F$4,$B$8,$B$9,4,2,true,$B$10,$B$11,A4:F4,A5:F5)'); } sheet.resumePaint(); }
<!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> <div class="options-container"> <div class="option-group"> <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-group"> <label for="highlightPosition" >Highlight item:</label> <select id="highlightPosition"> <option value="0">None</option> <option value="1">1st</option> <option value="2">2nd</option> <option value="3">3rd</option> <option value="4" selected>4th</option> <option value="5">5th</option> <option value="6">6th</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; } label { display: inline-block; min-width: 90px; margin-bottom: 6px; } select { padding: 4px 6px; box-sizing: border-box; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; }