Spread.Sheets Documentation
Creating Column, Line, and Winloss Sparklines with Formulas
Spread.Sheets Documentation > Developer's Guide > Customizing the Appearance > Understanding Sparklines > Creating Column, Line, and Winloss Sparklines with Formulas

You can use formulas to create column, line, and winloss sparklines.

The formulas have the following options:

Option Description
Data A range reference that represents sparkline data. For example: A1:C3.
DataOrientation A number that represents the sparkline data orientation, 0 is vertical, 1 is horizontal.
DateAxisData A range reference that represents sparkline date axis data. For example: D1:F3. This setting is optional.
DateAxisOrientation A number that represents the sparkline date axis orientation, 0 is vertical, 1 is horizontal. This setting is optional.
Setting A string with JSON format. For example: "{axisColor:rgb(255,255,0),firstMarkerColor:brown,highMarkerColor:red,lastMarkerColor:blue,lowMarkerColor:green,markersColor:purple,negativeColor:yellowgreen,
seriesColor:pink,displayXAxis:true,showFirst:true,showHigh:true,showLast:true,showLow:true,showNegative:true,showMarkers:true,lineWeight:3,displayHidden:false,
displayEmptyCellsAs:1,rightToLeft:false,minAxisType:1,maxAxisType:1,manualMax:5,manualMin:-3}". This setting is optional.

The following table lists the JSON format string and the equivalent sparkline setting.

Json Property SparklineSetting.Options Property
AXISCOLOR axisColor
FIRSTMARKERCOLOR firstMarkerColor
HIGHMARKERCOLOR highMarkerColor
LASTMARKERCOLOR lastMarkerColor
LOWMARKERCOLOR lowMarkerColor
MARKERSCOLOR markersColor
NEGATIVECOLOR negativeColor
SERIESCOLOR seriesColor
DISPLAYEMPTYCELLSAS displayEmptyCellsAs
RIGHTTOLEFT rightToLeft
DISPLAYHIDDEN displayHidden
DISPLAYXAXIS displayXAxis
SHOWFIRST showFirst
SHOWHIGH showHigh
SHOWLAST showLast
SHOWLOW showLow
SHOWNEGATIVE showNegative
SHOWMARKERS showMarkers
MANUALMAX manualMax
MANUALMIN manualMin
MAXAXISTYPE maxAxisType
MINAXISTYPE minAxisType
LINEWEIGHT lineWeight

The line, column, and winloss formulas have the following format:

=LINESPARKLINE(data, dataOrientation, dateAxisData, dateAxisOrientation, setting)
=COLUMNSPARKLINE(data, dataOrientation, dateAxisData, dateAxisOrientation, setting)
=WINLOSSSPARKLINE(data, dataOrientation, dateAxisData, dateAxisOrientation, setting)

Using Code

This example uses formulas to create column, line, and winloss sparklines.

JavaScript
Copy Code
activeSheet.addSpan(0, 0, 1, 5);
activeSheet.getCell(0, 0, GC.Spread.Sheets.SheetArea.viewport).value("The company revenue in 2014").font("20px Arial").hAlign(GC.Spread.Sheets.HorizontalAlign.center).vAlign(GC.Spread.Sheets.VerticalAlign.center);
var table1 = activeSheet.tables.add("table1", 1, 0, 13, 5, GC.Spread.Sheets.Tables.TableThemes.medium4);
table1.filterButtonVisible(false);
activeSheet.setValue(1, 0, "Month");
activeSheet.setValue(1, 1, "Revenue");
activeSheet.setValue(1, 2, "Diagram 1");
activeSheet.setValue(1, 3, "Diagram 2");
activeSheet.setValue(1, 4, "Diagram 3");
for (var i = 2; i < 14; i++) {
    activeSheet.setValue(i, 0, new Date(2014, i - 1, 1));
}
activeSheet.getRange(-1, 0, -1, 1).formatter("mm/dd/yyyy");
activeSheet.setValue(2, 1, 10);
activeSheet.setValue(3, 1, 20);
activeSheet.setValue(4, 1, 50);
activeSheet.setValue(5, 1, 100);
activeSheet.setValue(6, 1, 30);
activeSheet.setValue(7, 1, - 10);
activeSheet.setValue(8, 1, -25);
activeSheet.setValue(9, 1, 60);
activeSheet.setValue(10, 1, 50);
activeSheet.setValue(11, 1, 30);
activeSheet.setValue(12, 1, 80);
activeSheet.setValue(13, 1, 88);
activeSheet.addSpan(2, 2, 10, 1);
activeSheet.setFormula(2, 2, '=LINESPARKLINE(B3:B14,0,A3:A14,0,"{axisColor:rgb(255,255,0),firstMarkerColor:brown,highMarkerColor:red,lastMarkerColor:blue,lowMarkerColor:green,markersColor:purple,negativeColor:yellowgreen,seriesColor:pink,displayXAxis:true,showFirst:true,showHigh:true,showLast:true,showLow:true,showNegative:true,showMarkers:true,lineWeight:3,displayHidden:false,displayEmptyCellsAs:1,rightToLeft:false,minAxisType:1,maxAxisType:1,manualMax:5,manualMin:-3}")');
activeSheet.addSpan(2, 3, 10, 1);
activeSheet.setFormula(2, 3, '=COLUMNSPARKLINE(B3:B14,0,A3:A14,0,"{axisColor:rgb(255,255,0),firstMarkerColor:brown,highMarkerColor:red,lastMarkerColor:blue,lowMarkerColor:green,markersColor:purple,negativeColor:yellowgreen,seriesColor:pink,displayXAxis:true,showFirst:true,showHigh:true,showLast:true,showLow:true,showNegative:true,showMarkers:true,lineWeight:3,displayHidden:false,displayEmptyCellsAs:1,rightToLeft:false,minAxisType:1,maxAxisType:1,manualMax:5,manualMin:-3}")');
activeSheet.addSpan(2, 4, 10, 1);
activeSheet.setFormula(2, 4, '=WINLOSSSPARKLINE(B3:B14,0,A3:A14,0)');
activeSheet.setRowHeight(0, 50);
for (var i = 1; i < 14; i++) {
    activeSheet.setRowHeight(i, 25);
}
activeSheet.setColumnWidth(0, 80);
activeSheet.setColumnWidth(2, 200);
activeSheet.setColumnWidth(3, 200);
activeSheet.setColumnWidth(4, 200);
See Also