- SpreadJS Overview
- Getting Started
- JavaScript Frameworks
- Best Practices
-
Features
- Workbook
- Worksheet
- Rows and Columns
- Headers
- Cells
- Data Binding
- TableSheet
- GanttSheet
- ReportSheet
- JSON Schema with SpreadJS
- SpreadJS File Format
- Data Validation
- Conditional Formatting
- Sort
- Group
- Formulas
- Serialization
- Keyboard Actions
- Shapes
- Form Controls
- Floating Objects
- Barcodes
- Charts
-
Sparklines
- Column, Line, and Winloss Sparklines with Methods
- Markers and Points
- Horizontal and Vertical Axes
- Column, Line, and Winloss Sparklines with Formulas
- Area Sparkline
- Pie Sparkline
- Scatter Sparkline
- Bullet Sparkline
- Spread Sparkline
- Stacked Sparkline
- Hbar Sparkline
- Vbar Sparkline
- Box Plot Sparkline
- Vari Sparkline
- Cascade Sparkline
-
Pareto Sparkline
- Month Sparkline
- Year Sparkline
- Custom Sparkline
- Rangeblock Sparkline
- Image Sparkline
- Histogram Sparkline
- Gauge KPI Sparkline
- Lollipop Variance Sparkline
- Tables
- Pivot Table
- Slicer
- Theme
- Culture
- SpreadJS Designer
- SpreadJS Designer Component
- Touch Support
- Formula Reference
- Import and Export Reference
- Frequently Used Events
- API Documentation
- Release Notes
Pareto Sparkline
You can add a pareto sparkline using the PARETOSPARKLINE formula and cell values.
The pareto sparkline has the following options:
Option | Description |
|---|---|
points | A reference that represents the range of cells that contains all values, such as "B2:B7". |
pointIndex | A number or reference that represents the segment's index of the points, such as 1 or "D2". The pointIndex is >= 1. |
colorRange | A reference that represents the range of cells that contain the color for the segment box, such as "D2:D7". This setting is optional. The default value is none. |
target | A number or reference that represents the 'target' line position, such as 0.5. This setting is optional. The default value is none. The target line color is #8CBF64 if shown. |
target2 | A number or reference that represents the 'target2' line position, such as 0.8. This setting is optional. The default value is none. The target2 line color is #EE5D5D if shown. |
highlightPosition | A number or reference that represents the rank of the segment to be colored in red, such as 3. This setting is optional. The default value is none. If you set the |
label | A number that represents whether the segment's label is displayed as the cumulated percentage (label = 1) or the single percentage or none (label = 2) or none, such as 2,1. This setting is optional. The default value is 0. |
vertical | A boolean that represents whether the box's direction is vertical or horizontal. This setting is optional. The default value is FALSE. |
targetColor | A color string to indicate the color of the target line. |
target2Color | A color string that indicates the color of the target2 line. |
labelColor | A color string that indicates the label fore color. |
barSize | A number value greater than 0 and less than or equal to 1 indicates the percentage of bar width or height according to the cell width or height. |
The pareto sparkline formula has the following format:
=PARETOSPARKLINE(points, pointIndex, colorRange, target, target2, hightlightPosition, lable, vertical, targetColor, target2Color, labelColor, barSize)
You must set vertical to true or false for a group of formulas, because all the formulas represent the entire sparkline.
The point index (second formula parameter) refers to the point value's index of the points range. In the following example, 2 refers to the 15.
The following code sample creates a pareto sparkline using a group of formulas.
// initializing Spread
var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), { sheetCount: 1 });
// get the activesheet
var activeSheet = spread.getSheet(0);
activeSheet.addSpan(0, 0, 1, 3);
activeSheet.getCell(0, 0, GC.Spread.Sheets.SheetArea.viewport).value("Reason for Being Late")
.font("20px Arial")
.hAlign(GC.Spread.Sheets.HorizontalAlign.center)
.vAlign(GC.Spread.Sheets.VerticalAlign.center)
.backColor("purple")
.foreColor("white");
activeSheet.getRange(1, 2, 6, 1, GC.Spread.Sheets.SheetArea.viewport).setBorder(new GC.Spread.Sheets.LineBorder("transparent", GC.Spread.Sheets.LineStyle.thin),
{ inside: true });
activeSheet.setValue(1, 0, "Traffic");
activeSheet.setValue(2, 0, "Child care");
activeSheet.setValue(3, 0, "Public transportation");
activeSheet.setValue(4, 0, "Weather");
activeSheet.setValue(5, 0, "Overslept");
activeSheet.setValue(6, 0, "Emergency");
activeSheet.setValue(7, 0, "target");
activeSheet.setValue(8, 0, "target2");
activeSheet.setValue(1, 1, 20);
activeSheet.setValue(2, 1, 15);
activeSheet.setValue(3, 1, 13);
activeSheet.setValue(4, 1, 5);
activeSheet.setValue(5, 1, 4);
activeSheet.setValue(6, 1, 1);
activeSheet.setValue(7, 1, 0.5);
activeSheet.setValue(8, 1, 0.8);
activeSheet.setValue(1, 2, "#FF1493");
activeSheet.setValue(2, 2, "#FFE7BA");
activeSheet.setValue(3, 2, "#FFD700");
activeSheet.setValue(4, 2, "#FFAEB9");
activeSheet.setValue(5, 2, "#FF8C69");
activeSheet.setValue(6, 2, "#FF83FA");
activeSheet.setValue(9, 0, "targetColor");
activeSheet.setValue(10, 0, "target2Color");
activeSheet.setValue(9, 1, "#006400");
activeSheet.setValue(10, 1, "#00BFFF");
activeSheet.addSpan(7, 2, 2, 2);
activeSheet.getCell(7, 2, GC.Spread.Sheets.SheetArea.viewport).wordWrap(true);
activeSheet.setValue(7, 2, 'Result: The reason 80% of the employees are late - "traffic", "child care" and "public transportation".');
activeSheet.setColumnWidth(0, 120);
activeSheet.setColumnWidth(1, 80);
activeSheet.setColumnWidth(2, 80);
activeSheet.setColumnWidth(3, 340);
activeSheet.setRowHeight(0, 30);
activeSheet.setRowHeight(1, 30);
activeSheet.setRowHeight(2, 30);
activeSheet.setRowHeight(3, 30);
activeSheet.setRowHeight(4, 30);
activeSheet.setRowHeight(5, 30);
activeSheet.setRowHeight(6, 30);
activeSheet.setRowHeight(7, 30);
//$B$10, $B$11, C2: C7, D2: D7
//targetColor, target2Color, labelColor, barSize)
activeSheet.setFormula(1, 3, '=PARETOSPARKLINE(B2:B7,1,C2:C7,B8,B9,4,2,false,B10,B11,C2,1)');
activeSheet.setFormula(2, 3, '=PARETOSPARKLINE(B2:B7,2,C2:C7,B8,B9,4,2,false,B10,B11,C3,0.8)');
activeSheet.setFormula(3, 3, '=PARETOSPARKLINE(B2:B7,3,C2:C7,B8,B9,4,2,false,B10,B11,C4,0.6)');
activeSheet.setFormula(4, 3, '=PARETOSPARKLINE(B2:B7,4,C2:C7,B8,B9,4,2,false,B10,B11,C5,0.5)');
activeSheet.setFormula(5, 3, '=PARETOSPARKLINE(B2:B7,5,C2:C7,B8,B9,4,2,false,B10,B11,C6,0.1)');
activeSheet.setFormula(6, 3, '=PARETOSPARKLINE(B2:B7,6,C2:C7,B8,B9,4,2,false,B10,B11,C7,0.3)');