SpreadJS supports cascade sparkline in addition to the standard sparklines supported by MS Excel. DsExcel Java supports export of SpreadJS files containing cascade sparklines to JSON I/O, HTML, image, and PDF formats. This topic discusses about these extended sparklines and how to create them in DsExcel Java.
A cascade sparkline is generally used to analyze a value over time like yearly sales, total profit, net tax etc. It is used widely in finance, sales, legal and construction sectors, to name a few. For example, you can use cascade sparkline to compare expenses and earnings of a salesman.
DsExcel Java provides CASCADESPARKLINE formula for creating cascade sparkline.
Syntax
= CASCADESPARKLINE(pointsRange, pointIndex, labelsRange, minimum, maximum, colorPositive, colorNegative, vertical)
Parameters
| Parameter Name | Description |
|---|---|
| pointsRange(Required) | A reference that represents the range of cells that contains values, such as "B2:B8". |
| pointIndex (Required) | A number or reference that represents the points index. The pointIndex is >= 1 such as 1 or "D2". |
| LabelsRange (Optional) | A reference that represents the range of cells that contains the labels, such as "A2:A8". The default value is no label. |
| Minimum (Optional) | A number or reference that represents the minimum values of the display area. The default value is the minimum of the sum (the sum of the points' value), such as -2000. The minimum you set must be less than the default minimum; otherwise, the default minimum is used. |
| maximum (Optional) | A number or reference that represents the maximum values of the display area. The default value is the maximum of the sum (the sum of the points' value), such as 6000. The maximum you set must be greater than the default maximum; otherwise, the default maximum is used. |
| colorPositive(Optional) | A string that represents the color of the first or last positive sparkline's box (this point's value is positive). The default value is "#8CBF64". If the first or last box represents a positive value, the box's color is set to colorPositive. The middle positive box is set to a lighter color than colorPositive. |
| colorNegative (Optional) | A string that represents the color of the first or last negative sparkline's box (this point's value is negative). The default value is "#D6604D". If the first or last box represents the negative value, the box's color is set to colorNegative. The middle negative box is set to a lighter color than colorNegative. |
| vertical (Optional) | A boolean that represents whether the box's direction is vertical or horizontal. The default value is FALSE. You must set vertical to true or false for a group of formulas, because all the formulas represent the entire sparkline. |
| itemTypeRange (Optional) | An array or reference that represents all the item types of the data range. The values should be {"-", "+", "="} or "A1:A7" that reference the value of {"+", "-", "="}, where "+" indicates positive change, "-" indicates negative change and "=" indicates total columns. |
| colorTotal (Optional) | A string that either represents the color of the last sparkline's box when itemTypeRange does not exist or represents the color of the resulting sparkline's box when itemTypeRange exists. |
Refer to the following example code to add cascade sparkline using formula.
| Java |
Copy Code |
|---|---|
// Add a cascade sparkline with horizontal bars. for (int i = 1; i < 8; i++) { worksheet.getRange(i, 2).setFormula("=CASCADESPARKLINE(B2:B8, ROW() - 1, A2:A8, , , \"#8CBF64\", \"#D6604D\", FALSE)"); } | |
