Skip to main content Skip to footer

How to Integrate Sparklines into a JavaScript Spreadsheet Application

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)

HBarSparkline

VbarSparkline

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)

BulletSparkline

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)

GaugeSparkline

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)

WinLossSparkline

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)

LineSparkline

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)

AreaSparkline

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)

VarianceSparkline

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)

ScatterSparkline

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)

ParetoSparkline

Cascade Sparkline

Cascade Sparkline shows the progressive changes between two values.

=CASCADESPARKLINE(pointsRange, pointIndex, labelsRange, minimum, maximum, colorPositive, colorNegative, vertical, itemTypeRange)

CascadeSparkline

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)

StackedSparkline

Pie Sparkline

Pie Sparkline is a circular statistical diagram, which is divided into slices to illustrate numerical proportion.

=PIESPARKLINE(percentage,color1,color2,…..)

PieSparkline

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)

BoxPlotSparkline

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)

SpreadSparkline

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)

HistogramSparkline

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)

YearMonthSparkline

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)

ImageSparkline

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)

RangeBlock

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:

Clock

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.


Jola Hoxha

Product Assistant
comments powered by Disqus