Skip to main content Skip to footer

SpreadJS and Sparklines

Sparklines are small charts that you can use to provide visual representation of your data. Sparklines use data from a range of cells. SpreadJS supports sparklines in cells. SpreadJS provides two different ways to create sparklines. You can use methods or formulas. You can create column, line, or winloss sparklines with the setSparkline method. 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. You can create the following sparkline types using formulas and cell data:

  • Area
  • Pie
  • Scatter
  • Bullet
  • Spread
  • Stacked
  • Hbar
  • Vbar
  • Box plot
  • Vari
  • Cascade
  • Pareto

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. You can create a custom sparkline in SpreadJS with the createFunction method from the SparklineEx class. Sparklines have horizontal and vertical axes. Sparklines are stored as groups. A group contains at least one sparkline. You can group and ungroup sparklines. This example creates a column sparkline. This example uses ANNUAL_sample_csv.csv from https://www.ncdc.noaa.gov/cdo-web/datasets. The data in this example was copied and pasted from the designer. SpreadJSSparkCol Column Sparkline JavaScript

activeSheet.setFormula(15, 6, '=COLUMNSPARKLINE(G2:G13,0)');

This example creates an area sparkline. SpreadJSSparkArea Area Sparkline JavaScript

var datasource1 = [  
{ Name: "Apple", Category: "Fruit", Total: 11 },  
{ Name: "Orange", Category: "Fruit", Total: 10 },  
{ Name: "Broccoli", Category: "Vegetable", Total: 5 },  
{ Name: "Kiwi", Category: "Fruit", Total: 4 },  
{ Name: "Rice", Category: "Cereal", Total: 15 },  
{ Name: "Strawberry", Category: "Fruit", Total: 12 },  
{ Name: "Yogurt", Category: "Dairy", Total: 20 },  
{ Name: "Plum", Category: "Fruit", Total: 3 },  
{ Name: "Celery", Category: "Vegetable", Total: 6 },  
{ Name: "Grape", Category: "Fruit", Total: 18 },  
{ Name: "Oats", Category: "Cereal", Total: 14 },  
{ Name: "Quinoa", Category: "Cereal", Total: 2 },  
{ Name: "Maize", Category: "Cereal", Total: 7 },  
{ Name: "Okra", Category: "Vegetable", Total: 8 },  
{ Name: "Corn", Category: "Vegetable", Total: 25 },  
{ Name: "Wheat", Category: "Cereal", Total: 30 },  
{ Name: "Barley", Category: "Cereal", Total: 4 },  
{ Name: "Cream", Category: "Dairy", Total: 13 },  
{ Name: "Millet", Category: "Cereal", Total: 5 },  
{ Name: "Rye", Category: "Cereal", Total: 7 },  
{ Name: "Artichoke", Category: "Vegetable", Total: 5 },  
{ Name: "Buckwheat", Category: "Cereal", Total: 3 },  
{ Name: "Gooseberry", Category: "Fruit", Total: 10 },  
{ Name: "Amaranth", Category: "Cereal", Total: 6 },  
{ Name: "Carrot", Category: "Vegetable", Total: 16 },  
{ Name: "Cheese", Category: "Dairy", Total: 30 },  
{ Name: "Fig", Category: "Fruit", Total: 5 },  
{ Name: "Milk", Category: "Dairy", Total: 20 },  
{ Name: "Butter", Category: "Dairy", Total: 25 },  
               ];  
activeSheet.setDataSource(datasource1);  
var row = activeSheet.getRowCount();  
//Add rows after the last row  
activeSheet.addRows(row, 1);  
activeSheet.getRow(29).height(50);  

activeSheet.setFormula(29, 2, '=AREASPARKLINE(C1:C29,-15,50,5,-5,"red","green")');  
activeSheet.setColumnWidth(2, 200,GcSpread.Sheets.SheetArea.viewport);  

This example creates a custom sparkline. SpreadJSCustomSpark Custom Sparkline JavaScript

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, "Beijing");  
            sheet.setValue(1, 1, "Tokyo");  
            sheet.setValue(1, 2, "New York");  
            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() + 1)));//+9  
                sheet.setValue(3, 2, new Date(utcNow.setHours(utcNow.getHours() - 14)));//-5  
            }  
            setInterval(updateTime, 1000);  
            updateTime();  

MESCIUS inc.

comments powered by Disqus