Python Xlsxwriter 简明教程

Python XlsxWriter - Sparklines

sparkline 是一种小型图表，没有坐标轴或坐标。它提供某个参数变化的表示形式。普通图表尺寸更大，具有许多说明性特征，例如标题、图例、数据标签等，并且与附带文本分开。另一方面，迷你图尺寸很小，可以嵌入文本中，也可以嵌入具有其上下文的文本或工作表单元格中。

A sparkline 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. Sparkline on the other hand is small in size and can be embedded inside the text, or a worksheet cell that has its context.

Edward Tufte 于 1983 年引入了火花图功能。Microsoft 于 2010 年在 Excel 中引入了火花图。我们可以在 Excel 软件的插入功能区中找到火花图选项。

Feature of Sparkline was introduced by Edward Tufte in 1983. Microsoft introduced sparklines in Excel 2010. We can find sparkline option in the insert ribbon of Excel software.

火花图有三种类型 -

Sparklines are of three types −

  1. line − Similar to line chart

  2. column − Similar to column chart

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

Working with XlsxWriter Sparklines

XlsxWriter 模块有 add_sparkline() 方法。它基本上需要火花图的单元格位置和要表示为火花图的数据范围。此外，还可以以字典对象的形式提供其他参数，例如类型、样式等。默认情况下，类型为折线。

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

Example

以下程序表示在折线和柱状火花图中相同的数字列表。

Following program represents same list of numbers in line and column sparklines.

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_sparkline('K1', {'range':'Sheet1!A1:J1'})

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

wb.close()

Output

火花图被添加到单元格 K 中。

In cell K, the sparklines are added.

sparklines

属性为 -

The properties are −

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

  2. type − specifies the type of sparkline. There are 3 available sparkline types are line, column and win_loss.

  3. markers − Turn on the markers for line style sparklines

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

  5. negative_points − If set to True, the negative points in a sparkline are highlighted.

Example

以下程序生成一个有 markers 的 line sparkline 和一个突出显示负值点的 win_loss sparkline 。

The following program produces a line sparkline with markers and a win_loss sparkline having negative points highlighted.

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_sparkline('K1', {'range':'Sheet1!A1:J1', 'markers':True})
ws.add_sparkline('K5', {'range':'Sheet1!A5:J5', 'type':'win_loss',
'negative_points':True})

wb.close()

Output

K1 中的折线火花图有标记。K5 中的火花图显示负值突出显示。

Line Sparkline in K1 has markers. The sparkline in K5 shows negative points highlighting.

line sparkline

Example – Style Types

以下代码显示了柱状火花图中的一系列数字。这里使用了十种不同的样式类型。

Following code displays a series of numbers in column sparkline. 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_sparkline(i+4,1,
   {'range':'Sheet1!$C$3:$L$3',
   'type':'column',
   'style':i})

wb.close()

Output

它将生成如下输出：

It will produce the following output −

column sparkline