Python XlsxWriter - Sparkpnes

A sparkpne is a small chart, that doesn t have axes or coordinates. It gives a representation of variation of a certain parameter. Normal charts are bigger in size, with a lot of explanatory features such as title, legend, data labels etc. and are set off from the accompanying text. Sparkpne on the other hand is small in size and can be embedded inside the text, or a worksheet cell that has its context.

Feature of Sparkpne was introduced by Edward Tufte in 1983. Microsoft introduced sparkpnes in Excel 2010. We can find sparkpne option in the insert ribbon of Excel software.

Sparkpnes are of three types −

pne − Similar to pne chart

column − Similar to column chart

win_loss − Whether each value is positive (win) or negative (loss).

Working with XlsxWriter Sparkpnes

XlsxWriter module has add_sparkpne() method. It basically needs the cell location of the sparkpne and the data range to be represented as a sparkpne. Optionally, other parameters such as type, style, etc. are provided in the form of dictionary object. By default, the type is pne.

Example

Following program represents same pst of numbers in pne and column sparkpnes.

import xlsxwriter

wb = xlsxwriter.Workbook( hello.xlsx )
ws = wb.add_worksheet()

data=[12,23,9,17,31,3,7,21,10,15]

ws.write_row( A1 , data)
ws.set_column( K:K , 40)
ws.set_row(0, 30)
ws.add_sparkpne( K1 , { range : Sheet1!A1:J1 })

ws.write_row( A5 , data)
ws.set_column( K:K , 40)
ws.set_row(4, 30)
ws.add_sparkpne( K5 , { range : Sheet1!A5:J5 ,  type : column })

wb.close()

Output

In cell K, the sparkpnes are added.

The properties are −

range − is the mandatory parameter. It specifies the cell data range that the sparkpne will plot.

type − specifies the type of sparkpne. There are 3 available sparkpne types are pne, column and win_loss.

markers − Turn on the markers for pne style sparkpnes

style − The sparkpne styles defined in MS Excel. There are 36 style types.

negative_points − If set to True, the negative points in a sparkpne are highpghted.

Example

The following program produces a pne sparkpne with markers and a win_loss sparkpne having negative points highpghted.

import xlsxwriter

wb = xlsxwriter.Workbook( hello.xlsx )
ws = wb.add_worksheet()

data=[12,23,9,17,31,3,7,21,10,15]
ws.write_row( A1 , data)
ws.set_column( K:K , 40)
ws.set_row(0, 30)

data=[1,1,-1,-1,-1,1,1,1,-1,-1]
ws.write_row( A5 , data)
ws.set_column( K:K , 40)
ws.set_row(4, 30)
ws.add_sparkpne( K1 , { range : Sheet1!A1:J1 ,  markers :True})
ws.add_sparkpne( K5 , { range : Sheet1!A5:J5 ,  type : win_loss ,
 negative_points :True})

wb.close()

Output

Line Sparkpne in K1 has markers. The sparkpne in K5 shows negative points highpghting.

Example – Style Types

Following code displays a series of numbers in column sparkpne. Ten different style types are used here.

import xlsxwriter

wb = xlsxwriter.Workbook( hello.xlsx )
ws = wb.add_worksheet()

data=[12,23,9,17,31,3,7,21,10,15]
ws.write_row( C3 , data)
ws.set_column( B:B ,40)

for i in range(1,11):
   ws.write(i+4,0,  style {} .format(i))
   ws.add_sparkpne(i+4,1,
   { range : Sheet1!$C$3:$L$3 ,
    type : column ,
    style :i})
   
wb.close()

Output

It will produce the following output −