Sparklines are small charts that you can use to visualize your spreadsheet data at the cell level, such as trends in a series of values, seasonal increases or decreases, or economic cycles. These tiny representations of data are created to boost the content of your reports and dashboards, making them more readable and understandable.
SpreadJS supports the standard Excel sparkline visualizations and adds an enhanced set of visualizations using graphical functions.
Standard Sparklines
You can create column, line, or winloss sparklines with the setSparkline method. Sparklines are stored as groups, and a group contains at least one sparkline. You can group and ungroup sparklines.
Use the getSparkline method to get the sparkline and removeSparkline to delete it from the specified cell. For example:
var spread = GC.Spread.Sheets.findControl(document.getElementById('ss'));
var sheet = spread.getActiveSheet();
var data = new GC.Spread.Sheets.Range(1, 0, 8, 1);
var dateAxis = new GC.Spread.Sheets.Range(1, 2, 8, 1);
sheet.addSpan(11, 0, 4, 3);
var setting = new GC.Spread.Sheets.Sparklines.SparklineSetting();
sheet.setSparkline(11, 0, data
, GC.Spread.Sheets.Sparklines.DataOrientation.vertical
, GC.Spread.Sheets.Sparklines.SparklineType.line
, setting
, dateAxis
, GC.Spread.Sheets.Sparklines.DataOrientation.vertical
);
sheet.getSparkline(11, 0);
The SparklineType enumeration used here represents one of the three sparklines that can be inserted with this method. These are:
- Line
- Column
- Winloss
You can use all three sparklines above in Excel. As a plus, SpreadJS offers the possibility to enhance these sparklines by adding other style and technical properties that can make more appealing the presentation of your data. You can display colors for the marker points. You can set colors for the high, low, negative, first, and last points. Use the SparklineSetting class to specify colors and other options. The data for the column, line, or winloss, sparkline is limited to one column or row of values.
Enhanced Sparkline Functions
As said before, you can insert amazing sparklines into your javascript applications using simple formulas with SpreadJS, and customize their appearance with the given parameters. The sparklines you create with formulas also have options to set colors and other options in the formula. Sparklines created using formulas are not exported to an Excel file.
Below are some examples of sparklines that SpreadJS provides using simple formulas, separated into different categories that can help you choose the specific sparklines you need.
Performance Sparkline
These sparklines are used to show performance for any common business metrics visually. Easy to read and can be customized to suit your needs. Usually have a target, a forecast, and an actual parameter that will show when you are at the current time with the performance of a specific business metric.
HBar and VBar Sparklines
These sparkline types present categorical data with rectangular bars with heights or lengths proportional to the values that they represent. colorScheme parameter is used to create a scheme that will determine the bar's color depending on the value. The VBar example has this parameter applied.
=HBARSPARKLINE(value, colorScheme, axisVisible, barHeight) =VBARSPARKLINE(value, colorScheme, axisVisible, barWidth)
Bullet Sparkline
This sparkline is a variation of a bar graph. The sparkline series features a single measure, compares it to a target, a forecast, and displays it in the context of qualitative ranges of performance.
=BULLETSPARKLINE(measure, target, maxi, good, bad, forecast, tickunit, colorScheme, vertical, measureColor, targetColor, maxiColor, goodColor, badColor, forecastColor, allowMeasureOverMaxi, barSize)
Gauge KPI Sparkline
Provides a "snapshot" of organization performance, giving a visual representation of the data that shows the performance of a specific metric and its progress towards a goal.
=GAUGEKPISPARKLINE(targetValue, currentValue, minValue, maxValue, showLabel, targetValueLabel, currentValueLabel, minValueLabel, maxValueLabel, fontArray, minAngle, maxAngle, radiusRatio, gaugeType, colorRange)
WinLoss Sparkline
If the data is of positive value, then the column would be lying on the upper axis. For negative data value, the column would be below the axis. Likewise, for zero value, the column would not be displayed, and empty space would be left at that data point.
=WINLOSSSPARKLINE(data, dataOrientation, dateAxisData, dateAxisOrientation, setting)
Trend Sparklines
Trend Sparklines are types of graphs inserted into a single cell that allows you to have a quick at-a-glance story of measurement trends over time.
Line Sparkline
Line Sparklines show the evolution of values from a beginning time to another one in the form of lines, where high values will indicate fluctuations in height difference.
=LINESPARKLINE(data, dataOrientation, dateAxisData, dateAxisOrientation, setting)
Area Sparkline
Area Sparklines are presented as a Line Sparkline graph in which the area between the series and the X axis is filled with color.
=AREASPARKLINE(points, min, max, line1, line2, colorPositive, colorNegative)
Comparison Sparklines
These single-cell charts show the differences between different sets of data. You can then use this comparison to distinguish the best items if the purpose is to choose the units with less costs or more profit or see the relationship between those.
Column Sparkline
Column Sparklines are column charts where each bar shows an individual value.
=COLUMNSPARKLINE(data, dataOrientation, dateAxisData, dateAxisOrientation, setting)
Variance Sparkline
Variance Sparklines are graphs that compare two sets of series with each other to calculate the difference between each iteration.
=VARISPARKLINE(variance, reference, mini, maxi, mark, tickunit, legend, colorPositive, colorNegative, vertical)
Scatter Sparkline
Scatter Sparklines plots a pair of numerical data, with one variable on each axis, to visualize correlations between them.
=SCATTERSPARKLINE(points1, points2, minX, maxX, minY, maxY, hLine, vLine, xMinZone, xMaxZone, yMinZone, yMaxZone, tags, drawSymbol, drawLines, color1, color2, dash)
Composition Sparklines
This category of sparklines is used to show the composition of your dataset.
Pareto Sparkline
Pareto Sparkline is used to highlight the most important items in a set of values. This sparkline usually is taken as a quality tool since it helps analyze and prioritize issue resolution.
=PARETOSPARKLINE(points, pointIndex, colorRange, target, target2, highlightPosition, label, vertical, targetColor, target2Color, labelColor, barSize)
Cascade Sparkline
Cascade Sparkline shows the progressive changes between two values.
=CASCADESPARKLINE(pointsRange, pointIndex, labelsRange, minimum, maximum, colorPositive, colorNegative, vertical, itemTypeRange)
Stacked Sparkline
Stacked sparkline is used to show a sliced breakdown of a value in different categories.
=STACKEDSPARKLINE(points, colorRange, labelRange, maximum, targetRed, targetGreen, targetBlue, tragetYellow, color, highlightPosition, vertical, textOrientation, textSize)
Pie Sparkline
Pie Sparkline is a circular statistical diagram, which is divided into slices to illustrate numerical proportion.
=PIESPARKLINE(percentage,color1,color2,…..)
Distribution Sparklines
These are sparklines used to show how variables are distributed over time, helping identify outliers and trends.
BoxPlot Sparkline
BoxPlot Sparkline is a graph that gives you a good indication of how the values in the data are spread out. Boxplots are a standardized way of displaying the distribution of data based on a five-number summary (minimum, first quartile (Q1), median, third quartile (Q3), and maximum). This type of graph is used to show the shape of the distribution, its central value, and its variability. Box plots are useful as they provide a visual summary of the data enabling researchers to quickly identify mean values, the dispersion of the data set, and signs of skewness.
=BOXPLOTSPARKLINE(points, boxPlotClass, showAverage, scaleStart, scaleEnd, acceptableStart, acceptableEnd, colorScheme, style, vertical)
Spread Sparkline
Spread Sparkline is data distribution sparklines with different styles. The style parameter is the one that determines the presentation of the data. Below you can find the values for this parameter and how the sparkline is presented using that specific style.
=SPREADSPARKLINE(points, showAverage, scaleStart, scaleEnd, style, colorScheme, vertical)
Histogram Sparkline
Histogram Sparkline is a graphical representation of a grouped frequency distribution with continuous classes. It is an area diagram and can be defined as a set of rectangles with bases and intervals between class boundaries and areas proportional to frequencies in the corresponding classes.
=HISTOGRAMSPARKLINE(dataRange, continuous, paintLabel, scale, barWidth, barColor, labelColor,edgeColor)
Heatmap Sparklines
These types of sparklines are helpful in comparing categories, using color to emphasize relationships between data values that would be much harder to understand in a simple table with raw numbers.
Month Sparkline
Month Sparkline is used for spotting data trends within a month.
=MONTHSPARKLINE(year, month, dataRange, emptyColor, startColor, middleColor, endColor)
=MONTHSPARKLINE(year, month, dataRange, colorRange)
Year Sparkline
Year Sparkline is used for spotting annual trends in your data.
=YEARSPARKLINE(year, dataRange, emptyColor, startColor, middleColor, endColor). =YEARSPARKLINE(year, dataRange, colorRange)
Special Sparklines
These function-sparklines are different from other predefined sparklines that will show you trends, differences, correlations of data and used for easing the process of displaying a piece of certain information or data into a single cell.
Image Sparkline
Image Sparkline renders an Image from an image file URL or from a base64 string. Images can be scaled and clipped within the cell.
=IMAGE(URL, mode, height, width, clipX, clipY, clipHeight, clipWidth, vAlign, hAlign)
RangeBlock Sparkline
A RangeBlock Sparkline renders data in a cell-based on a predefined range template that defines the rendered fields' shape and layout.
=RANGEBLOCKSPAKLINE(template_range, data_expr)
Custom Sparklines
The synonym of our product is flexibility. We aim to provide a steady structure that can be adapted to your business logic, creating in this way added value for you.
That is why SpreadJS allows developers to create their own sparkline templates by implementing their custom logic.
- SparklineEx
The SparklineEx exposes two methods that help you customize your sparkline:
- createFunction: The function will define the sparkline formula. It provides the data and settings for sparkline.
- paint: Renders the Sparkline with the data and settings from the sparkline formula.
Below, there is an example of creating a customized clock for different time zones and applying that in a single cell by calling a customized sparkline.
function Clock() {
GcSpread.Sheets.SparklineEx.call(this);
}
Clock.prototype = new GcSpread.Sheets.SparklineEx();
Clock.prototype.createFunction = function () {
var func = new GcSpread.Sheets.Calc.Functions.Function("CLOCK", 1, 1);
func.evaluate = function (args) {
return args[0];
};
return func;
};
Clock.prototype._drawCircle = function (context, centerX, centerY, radius) {
context.beginPath();
context.arc(centerX, centerY, radius, 0, Math.PI * 2, true);
context.stroke();
};
Clock.prototype._drawCenter = function (context, centerX, centerY, radius) {
context.beginPath();
context.arc(centerX, centerY, radius, 0, Math.PI * 2, true);
context.fill();
};
Clock.prototype._drawHand = function (context, centerX, centerY, loc, radius) {
var angle = (Math.PI * 2) * (loc / 60) - Math.PI / 2;
context.beginPath();
context.moveTo(centerX, centerY);
context.lineTo(centerX + Math.cos(angle) * radius, centerY + Math.sin(angle) * radius);
context.stroke();
};
Clock.prototype._drawHands = function (context, value, centerX, centerY, radius) {
var date = value, hour = date.getHours();
hour = hour > 12 hour - 12 : hour;
this._drawHand(context, centerX, centerY, hour * 5 + (date.getMinutes() / 60) * 5, radius / 2);
this._drawHand(context, centerX, centerY, date.getMinutes(), radius * 3 / 4);
context.strokeStyle = "red";
this._drawHand(context, centerX, centerY, date.getSeconds(), radius * 3 / 4);
};
Clock.prototype._drawNumerals = function (context, centerX, centerY, radius) {
var numerals = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12],
angle,
numeralWidth;
if (radius > 0) {
numerals.forEach(function (numeral) {
angle = Math.PI / 6 * (numeral - 3);
numeralWidth = context.measureText(numeral).width;
context.beginPath();
context.fillText(numeral, centerX + Math.cos(angle) * radius + numeralWidth / 2, centerY + Math.sin(angle) * radius + numeralWidth / 2);
});
}
};
Clock.prototype.paint = function (context, value, x, y, width, height) {
if (!(value instanceof Date)) {
return;
}
var centerX = x + width / 2,
centerY = y + height / 2,
margin = 10,
padding = 10,
radius = Math.min(width, height) / 2 - margin;
if (radius <= 0) {
return;
}
context.save();
//draw circle
this._drawCircle(context, centerX, centerY, radius);
//draw center
this._drawCenter(context, centerX, centerY, 3);
//draw hands
this._drawHands(context, value, centerX, centerY, radius);
//draw numerals
this._drawNumerals(context, centerX, centerY, radius - padding);
context.restore();
};
spread.addSparklineEx(new Clock());
var sheet = spread.getActiveSheet();
var style = new GcSpread.Sheets.Style();
style.hAlign = GcSpread.Sheets.HorizontalAlign.center;
style.vAlign = GcSpread.Sheets.VerticalAlign.center;
sheet.setDefaultStyle(style);
sheet.getCell(0, 1).value("Universal Time").font("20px Arial");
sheet.setValue(1, 0, "Tokyo");
sheet.setValue(1, 1, "London");
sheet.setValue(1, 2, "New York");
//call the function in spread
sheet.setFormula(2, 0, '=CLOCK(A4)');
sheet.setFormula(2, 1, '=CLOCK(B4)');
sheet.setFormula(2, 2, '=CLOCK(C4)');
sheet.getRow(3).formatter("hh:mm:ss tt");
sheet.setRowHeight(0, 50);
sheet.setRowHeight(2, 200);
sheet.setColumnWidth(0, 200);
sheet.setColumnWidth(1, 200);
sheet.setColumnWidth(2, 200);
function updateTime() {
var now = new Date();
var utcNow = new Date(now.getUTCFullYear(), now.getUTCMonth(), now.getUTCDate(), now.getUTCHours(), now.getUTCMinutes(), now.getUTCSeconds(), now.getUTCMilliseconds());
sheet.setValue(3, 0, new Date(utcNow.setHours(utcNow.getHours() + 8)));//+8
sheet.setValue(3, 1, new Date(utcNow.setHours(utcNow.getHours() - 8)));//0
sheet.setValue(3, 2, new Date(utcNow.setHours(utcNow.getHours() - 5)));//-4
}
setInterval(updateTime, 1000);
updateTime();
And the output:
Using Sparklines is a beneficial process. These tiny charts allow you to present a large amount of information in a single cell. Additionally, since sparklines are small, they make it easier for users to see a general trend rather than getting bogged down in individual data points. SpreadJS provides a wide range of sparklines covering all the areas of graphical and visual representation of data and more since you can use the SparklineEx to add these types of visualization that bind to your business logic.
Get the free trial of SpreadJS and see how you can add value to your javascript applications by adding Excel-like spreadsheet experiences that this javascript component offers.
Download here the example file and follow along.
Note: This is a .ssjson file. Please ensure your browser doesn't change the file extension on download. After downloading, import the file into the SpreadJS designer.
Contact us to learn more at us.sales@grapecity.com and visit the SpreadJS page.
