BoxPlot

A boxplot sparkline uses quartiles to display data and is a quick way of examining data sets graphically.

<p>You can create a boxplot sparkline using the BoxPlotSparkline function in a formula: <strong>=BOXPLOTSPARKLINE(points, boxPlotClass?, showAverage?, scaleStart?, scaleEnd?, acceptableStart?, acceptableEnd?, colorScheme?, style?, vertical?)</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 "A1:A4".</p> <p><strong>boxPlotClass</strong>: (optional) The class of the boxPlot. One of the following:</p> <ul> <li><strong>5ns (default)</strong></li> <li><strong>7ns</strong></li> <li><strong>tukey</strong></li> <li><strong>bowley</strong></li> <li><strong>sigma3</strong></li> </ul> <p><strong>showAverage</strong>: (optional) Boolean that represents whether to display the average. The default value is false.</p> <p><strong>scaleStart</strong>: (optional) Number or reference that represents the minimum boundary of the sparkline, such as 1 or "A6"; default value is the minimum of all values.</p> <p><strong>scaleEnd</strong>: (optional) Number or reference that represents the maximum boundary of the sparkline, such as 8 or "A7"; default value is the maximum of all values.</p> <p><strong>acceptableStart</strong>: (optional) Number or reference that represents the start of the acceptable line, such as 3 or "A8"; default value is null.</p> <p><strong>acceptableEnd</strong>: (optional) Number or reference that represents the end of the acceptable line, such as 5 or "A9"; default value is null.</p> <p><strong>colorScheme</strong>: (optional) String that represents the color of the sparkline's box; default value is "#D2D2D2".</p> <p><strong>style</strong>: (optional) Number or reference that represents the style of the boxPlot sparkline. One of the following:</p> <ul> <li><strong>0: (default) Classical style - the whisker is line and outlier is circle.</strong></li> <li><strong>1: Neo style - the whisker is rectangle and outlier is line.</strong></li> </ul> <p><strong>vertical</strong>: (optional) Boolean that represents whether to display the sparkline vertically. The default value is false.</p>
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.options.allowCellOverflow = true; sheet.setColumnWidth(0, 25); for (var c = 2; c < 14; c++) { sheet.setColumnWidth(c, 38); } sheet.setColumnWidth(14, 170); sheet.setRowHeight(1, 40); sheet.setRowHeight(2, 50); for (var r = 3; r < 12; r++) { sheet.setRowHeight(r, 30); } sheet.getCell(1, 1).value("The Company Sales in 2014 (Month)").font("20px Arial").hAlign(spreadNS.HorizontalAlign.left); var table1 = sheet.tables.add("htable1", 2, 1, 10, 14, spreadNS.Tables.TableThemes.medium2); table1.rowFilter().filterButtonVisible(false); for (var i = 2; i < 14; i++) { sheet.setValue(2, i, new Date(2014, i - 2, 1)); } sheet.setFormatter(2, 2, "mm"); sheet.setFormatter(2, 3, "mm"); sheet.setFormatter(2, 4, "mm"); sheet.setFormatter(2, 5, "mm"); sheet.setFormatter(2, 6, "mm"); sheet.setFormatter(2, 7, "mm"); sheet.setFormatter(2, 8, "mm"); sheet.setFormatter(2, 9, "mm"); sheet.setFormatter(2, 10, "mm"); sheet.setFormatter(2, 11, "mm"); sheet.setFormatter(2, 12, "mm"); sheet.setFormatter(2, 13, "mm"); sheet.setValue(2, 14, "Actual Sales (mn)"); sheet.setValue(2, 15, " "); sheet.getRange(2, 2, 1, 14).hAlign(spreadNS.HorizontalAlign.center).vAlign(spreadNS.VerticalAlign.center).wordWrap(true); sheet.setValue(2, 1, "Region"); sheet.setValue(3, 1, "Alabama"); sheet.setValue(4, 1, "Alaska"); sheet.setValue(5, 1, "Arizona"); sheet.setValue(6, 1, "Idaho"); sheet.setValue(7, 1, "Indiana"); sheet.setValue(8, 1, "Ohio"); sheet.setValue(9, 1, "Oklahoma"); sheet.setValue(10, 1, "Oregon"); sheet.setValue(11, 1, "Vermont"); sheet.setValue(12, 1, "Start scope of the sale:"); sheet.setValue(13, 1, "End scope of the sale:"); sheet.setValue(14, 1, "Start scope of expected sale:"); sheet.setValue(15, 1, "End scope of expected sale:"); sheet.addSpan(12, 1, 1, 4); sheet.addSpan(13, 1, 1, 4); sheet.addSpan(14, 1, 1, 4); sheet.addSpan(15, 1, 1, 4); sheet.setValue(12, 5, 0); sheet.setValue(13, 5, 10000); sheet.setValue(14, 5, 1000); sheet.setValue(15, 5, 8000); for (var index = 3; index < 12; index++) { sheet.setFormula(index, 14, '=BOXPLOTSPARKLINE($C' + (index + 1) + ':$N' + (index + 1) + ',"5ns",true,$F$13,$F$14,$F$15,$F$16,"#00FF7F",0,false)'); } var data = [[5268, 6281, 8921, 1069, 1239, 2818, 6327, 5173, 5780, 3595, 4346, 3719], [2837, 5739, 993, 4247, 9514, 5986, 44, 2682, 8951, 691, 4945, 7513], [6661, 4172, 9777, 1282, 9535, 2540, 2972, 4400, 6604, 3459, 5959, 3785], [1696, 5521, 4525, 8269, 7239, 5311, 762, 928, 8602, 5852, 4344, 6708], [9726, 4286, 874, 5885, 6740, 1732, 3358, 1675, 2539, 9958, 97, 1639], [3937, 6168, 3720, 3215, 6365, 8781, 3985, 2538, 9879, 9315, 5691, 2342], [3096, 4173, 3631, 633, 9395, 266, 1948, 2988, 2524, 7800, 9837, 5253], [5128, 7681, 6078, 6648, 1991, 5156, 880, 2354, 7328, 2565, 9202, 6875], [6853, 205, 3104, 3430, 7275, 824, 5031, 3727, 7621, 2372, 225, 7547]]; sheet.setArray(3, 2, data); sheet.resumePaint(); } function initVerticalSparkline(sheet, name) { sheet.suspendPaint(); sheet.name(name); sheet.addSpan(0, 0, 1, 4); sheet.getCell(0, 0).value("Math Scores").font("20px Arial").hAlign(spreadNS.HorizontalAlign.center).vAlign(spreadNS.VerticalAlign.center); var table1 = sheet.tables.add("vtable1", 1, 0, 21, 4, spreadNS.Tables.TableThemes.light12); table1.rowFilter().filterButtonVisible(false); sheet.setValue(1, 0, "Student ID"); sheet.setValue(1, 1, "Class One"); sheet.setValue(1, 2, "Class Two"); sheet.setValue(1, 3, "Class Three"); sheet.setValue(1, 4, "Class One"); sheet.setValue(1, 5, "Class Two"); sheet.setValue(1, 6, "Class Three"); var score = [ { id: 1, classOne: 65, classTwo: 60, classThree: 98 }, { id: 2, classOne: 91, classTwo: 70, classThree: 99 }, { id: 3, classOne: 70, classTwo: 86, classThree: 92 }, { id: 4, classOne: 62, classTwo: 62, classThree: 78 }, { id: 5, classOne: 98, classTwo: 79, classThree: 71 }, { id: 6, classOne: 89, classTwo: 99, classThree: 68 }, { id: 7, classOne: 85, classTwo: 100, classThree: 88 }, { id: 8, classOne: 65, classTwo: 78, classThree: 74 }, { id: 9, classOne: 65, classTwo: 66, classThree: 85 }, { id: 10, classOne: 65, classTwo: 84, classThree: 80 }, { id: 11, classOne: 65, classTwo: 98, classThree: 79 }, { id: 12, classOne: 65, classTwo: 87, classThree: 50 }, { id: 13, classOne: 51, classTwo: 68, classThree: 64 }, { id: 14, classOne: 65, classTwo: 78, classThree: 73 }, { id: 15, classOne: 67, classTwo: 81, classThree: 88 }, { id: 16, classOne: 87, classTwo: 83, classThree: 85 }, { id: 17, classOne: 72, classTwo: 84, classThree: 84 }, { id: 18, classOne: 74, classTwo: 82, classThree: 86 }, { id: 19, classOne: 85, classTwo: 55, classThree: 92 }, { id: 20, classOne: 65, classTwo: 60, classThree: 69 } ]; for (var i = 0, len = score.length; i < len; i++) { var student = score[i]; sheet.setValue(i + 2, 0, student.id); sheet.setValue(i + 2, 1, student.classOne); sheet.setValue(i + 2, 2, student.classTwo); sheet.setValue(i + 2, 3, student.classThree); } sheet.setColumnWidth(0, 80); sheet.setColumnWidth(1, 80); sheet.setColumnWidth(2, 80); sheet.setColumnWidth(3, 80); sheet.setColumnWidth(4, 75); sheet.setColumnWidth(5, 75); sheet.setColumnWidth(6, 75); sheet.addSpan(2, 4, 20, 1); sheet.addSpan(2, 5, 20, 1); sheet.addSpan(2, 6, 20, 1); sheet.setFormula(2, 4, '=BOXPLOTSPARKLINE(B3:B22,"5ns",true,0,100,60,100,,0, true)'); sheet.setFormula(2, 5, '=BOXPLOTSPARKLINE(C3:C22,"5ns",true,0,100,60,100,,0, true)'); sheet.setFormula(2, 6, '=BOXPLOTSPARKLINE(D3:D22,"5ns",true,0,100,60,100,,0, true)'); 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" 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; }