Excel Sparklines

xlsgen > overview > Excel Sparklines

native support for Sparklines in xlsgen

Introducing Excel sparklines

Sparklines are small charts supposed to convey a meaningful trend as a message. They were democratized by Edward Tufte.

Sparklines have been introduced in Excel 2010, so they appear in Excel 2010 and any more recent version (such as Excel 2013, 2016 and Office 365). For older Excel versions (Excel 2007, Excel 2003, ...), sparklines simply do not appear anywhere. xlsgen computes sparklines internally so you don't have to worry which version of Excel you are using.

Sparklines in Excel have the following features :

attached to a range of data
is either straight lines, columns, or plus-minus columns (otherwise known as win-loss columns)
can have custom color elements (values, negative values, axis, markers, first value, last value, maximum value, minimum value)
can stress one or more specific points (markers, maximum, minimum, first value, last value, negative values)
can have a custom line weight for sparklines made of straight lines
can have a custom date axis (defined by a range)
can have custom minimum and maximum values
can render empty or invisible data in cells differently

In xlsgen you can create Excel sparklines including all their formatting options. You can also edit or update existing sparklines. The interfaces involved for working with this are : IXlsSparklines, IXlsSparkline and IXlsSparklineElement.

xlsgen also includes its own alternate sparklines model part of the visual component architecture, complete with support for mathematical normal ranges, and the handling of data streams (more powerful, but no integration with Excel).

From a programming perspective, there is a tool that is meant to accelerate the use of sparklines without much knowing of the object model. This is achieved by using our automatic source code generator. Sparklines can be manually created in Excel and then the automatic source code generator produces the corresponding source code in the programming language of your choice (C++, Java, VB, VB.NET, C#).

Sparklines are rendered in print, preview, PDF, XPS, HTML scenarios. xlsgen computes and renders sparklines without having to worry about it.

Working with Excel sparklines

Java code

worksheet.putNumber(6,3,  13);
worksheet.putNumber(7,3   24);
worksheet.putNumber(8,3,  15);
worksheet.putNumber(9,3,  17);
worksheet.putNumber(10,3, 19);

// new sparkline (straight lines)
XlsSparkline sp = worksheet.getSparklines().Add(xlsgen.sparklinetype_line);
XlsSparklineElement sp_data = sp.Add();
sp_data.DataAsRange(6,3,10,3); // row1, col1, row2, col2
sp_data.putLocationRow(11);
sp_data.putLocationColumn(3);

// formatting
sp.putShowMaximumValue(true);
sp.putSparklineColor(xlsgen.sparklineelement_values, 0xFF0000);

VB code

worksheet.Number(6,3)  = 13
worksheet.Number(7,3)  = 24
worksheet.Number(8,3)  = 15
worksheet.Number(9,3)  = 17
worksheet.Number(10,3) = 19

' new sparkline (straight lines)
Dim sp As IXlsSparkline 
Set sp = worksheet.Sparklines.Add(enumSparklineType.sparklinetype_line)
Dim sp_data As IXlsSparklineElement 
Set sp_data = sp.Add
sp_data.DataAsRange(6,3,10,3) ' row1, col1, row2, col2
sp_data.LocationRow = 11
sp_data.LocationColumn = 3

' formatting
sp.ShowMaximumValue = True
sp.SparklineColor(enumSparklineElement.sparklineelement_values) = &HFF0000

C# code

worksheet.set_Number(6,3,  13);
worksheet.set_Number(7,3   24);
worksheet.set_Number(8,3,  15);
worksheet.set_Number(9,3,  17);
worksheet.set_Number(10,3, 19);

// new sparkline (straight lines)
IXlsSparkline sp = worksheet.Sparklines.Add(enumSparklineType.sparklinetype_line);
IXlsSparklineElement sp_data = sp.Add();
sp_data.DataAsRange(6,3,10,3); // row1, col1, row2, col2
sp_data.LocationRow = 11;
sp_data.LocationColumn = 3;

// formatting
sp.ShowMaximumValue = true;
sp.set_SparklineColor(enumSparklineElement.sparklineelement_values, 0xFF0000);

C/C++ code

worksheet->Number[6][3] = 13;
worksheet->Number[7][3] = 24;
worksheet->Number[8][3] = 15;
worksheet->Number[9][3] = 17;
worksheet->Number[10][3] = 19;

// new sparkline (straight lines)
xlsgen::IXlsSparklinePtr sp = worksheet->Sparklines->Add(xlsgen::sparklinetype_line);
xlsgen::IXlsSparklineElementPtr sp_data = sp->Add();
sp_data->DataAsRange(6,3,10,3); // row1, col1, row2, col2
sp_data->LocationRow = 11;
sp_data->LocationColumn = 3;

// formatting
sp->ShowMaximumValue = TRUE;
sp->SparklineColor[xlsgen::sparklineelement_values] = 0xFF0000;