[]
        
(Showing Draft Content)

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, and 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, and 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)


The following code sample uses formulas to create column, line, and winloss sparklines.

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);