Python Xlsxwriter 简明教程

Python XlsxWriter - Adding Charts

Excel 最重要的功能之一是其将数据转换为图表的能力。图表是数据的可视化表示。可以从 Chart 菜单生成不同类型的图表。

One of the most important features of Excel is its ability to convert data into chart. A chart is a visual representation of data. Different types of charts can be generated from the Chart menu.

为了以编程方式生成图表，XlsxWriter 库具有一个 Chart 类。它的对象可以通过调用 Workbook 类的 add_chart() 方法获得。接下来，借助 add_series() 方法，它与工作表中的数据范围关联起来。然后，使用其 insert_chart() 方法在工作表中插入图表对象。

To generate charts programmatically, XlsxWriter library has a Chart class. Its object is obtained by calling add_chart() method of the Workbook class. It is then associated with the data ranges in the worksheet with the help of add_series() method. The chart object is then inserted in the worksheet using its insert_chart() method.

Example

下面给出了用于显示简单柱状图的代码。

Given below is the code for displaying a simple column chart.

import xlsxwriter
wb = xlsxwriter.Workbook('hello.xlsx')
worksheet = wb.add_worksheet()
chart = wb.add_chart({'type': 'column'})

data = [
   [10, 20, 30,  40,  50],
   [20, 40, 60,  80, 100],
   [30, 60, 90, 120, 150],
]
worksheet.write_column('A1', data[0])
worksheet.write_column('B1', data[1])
worksheet.write_column('C1', data[2])

chart.add_series({'values': '=Sheet1!$A$1:$A$5'})
chart.add_series({'values': '=Sheet1!$B$1:$B$5'})
chart.add_series({'values': '=Sheet1!$C$1:$C$5'})

worksheet.insert_chart('B7', chart)

wb.close()

Output

生成的图表嵌入到工作表中，并显示如下 −

The generated chart is embedded in the worksheet and appears as follows −

add_series() 方法具有以下附加参数 −

The add_series() method has following additional parameters −

Values − This is the most important property mandatory option. It links the chart with the worksheet data that it displays.
Categories − This sets the chart category labels. If not given, the chart will just assume a sequential series from 1…n.
Name − Set the name for the series. The name is displayed in the formula bar.
Line − Set the properties of the series line type such as color and width.
Border − Set the border properties of the series such as color and style.
Fill − Set the solid fill properties of the series such as color.
Pattern − Set the pattern fill properties of the series.
Gradient − Set the gradient fill properties of the series.
data_labels − Set data labels for the series.
Points − Set properties for individual points in a series.

在以下示例中，在添加数据序列时，定义了 value 和 categories 属性。用于示例的数据为 −

In the following examples, while adding the data series, the value and categories properties are defined. The data for the example is −

# Add the worksheet data that the charts will refer to.
headings = ['Name', 'Phy', 'Maths']
data = [
   ["Jay",   30, 60],
   ["Mohan", 40, 50],
   ["Veeru", 60, 70],
]

在创建图表对象后，第一个数据序列对应于 name 属性值为 phy 的列。第一列中的学生姓名用作分类

After creating the chart object, the first data series corresponds to the column with phy as the value of name property. Names of the students in the first column are used as categories

chart1.add_series({
   'name': '=Sheet1!$B$1',
   'categories': '=Sheet1!$A$2:$A$4',
   'values': '=Sheet1!$B$2:$B$4',
})

第二个数据序列也引用列 A 中的姓名作为分类，列 C 以 heading 作为 Maths 作为 values 属性。

The second data series too refers to names in column A as categories and column C with heading as Maths as the values property.

chart1.add_series({
   'name': ['Sheet1', 0, 2],
   'categories': ['Sheet1', 1, 0, 3, 0],
   'values': ['Sheet1', 1, 2, 3, 2],
})

Example

以下是完整的示例代码 −

Here is the complete example code −

import xlsxwriter
wb = xlsxwriter.Workbook('hello.xlsx')
worksheet = wb.add_worksheet()
chart1 = wb.add_chart({'type': 'column'})

# Add the worksheet data that the charts will refer to.
headings = ['Name', 'Phy', 'Maths']
data = [
   ["Jay",   30, 60],
   ["Mohan", 40, 50],
   ["Veeru", 60, 70],
]

worksheet.write_row(0,0, headings)
worksheet.write_row(1,0, data[0])
worksheet.write_row(2,0, data[1])
worksheet.write_row(3,0, data[2])

chart1.add_series({
   'name': '=Sheet1!$B$1',
   'categories': '=Sheet1!$A$2:$A$4',
   'values': '=Sheet1!$B$2:$B$4',
})

chart1.add_series({
   'name': ['Sheet1', 0, 2],
   'categories': ['Sheet1', 1, 0, 3, 0],
   'values': ['Sheet1', 1, 2, 3, 2],
})

worksheet.insert_chart('B7', chart1)

wb.close()

Output

基于它的工作表和图表显示如下 −

The worksheet and the chart based on it appears as follows −

add_series() 方法还具有 data_labels 属性。如果设置为 True，会找到绘制的数据点值，并将其显示在每列顶部。

The add_series() method also has data_labels property. If set to True, values of the plotted data points are displayed on top of each column.

Example

以下是 add_series() 方法的完整代码示例 −

Here is the complete code example for add_series() method −

import xlsxwriter
wb = xlsxwriter.Workbook('hello.xlsx')
worksheet = wb.add_worksheet()
chart1 = wb.add_chart({'type': 'column'})

# Add the worksheet data that the charts will refer to.
headings = ['Name', 'Phy', 'Maths']
data = [
   ["Jay",   30, 60],
   ["Mohan", 40, 50],
   ["Veeru", 60, 70],
]

worksheet.write_row(0,0, headings)
worksheet.write_row(1,0, data[0])
worksheet.write_row(2,0, data[1])
worksheet.write_row(3,0, data[2])

chart1.add_series({
   'name': '=Sheet1!$B$1',
   'categories': '=Sheet1!$A$2:$A$4',
   'values': '=Sheet1!$B$2:$B$4',
   'data_labels': {'value':True},
})
chart1.add_series({
   'name': ['Sheet1', 0, 2],
   'categories': ['Sheet1', 1, 0, 3, 0],
   'values': ['Sheet1', 1, 2, 3, 2],
   'data_labels': {'value':True},
})
worksheet.insert_chart('B7', chart1)

wb.close()

Output

执行代码并打开 Hello.xlsx 。现在， column 图表显示 data 标签。

Execute the code and open Hello.xlsx. The column chart now shows the data labels.

数据标签可以显示在所有类型的图表中。数据标签的位置参数可以设置为顶部、底部、左侧或右侧。

The data labels can be displayed for all types of charts. Position parameter of data label can be set to top, bottom, left or right.

XlsxWriter 支持以下类型的图表 −

XlsxWriter supports the following types of charts −

Area − Creates an Area (filled line) style chart.
Bar − Creates a Bar style (transposed histogram) chart.
Column − Creates a column style (histogram) chart.
Line − Creates a Line style chart.
Pie − Creates a Pie style chart.
Doughnut − Creates a Doughnut style chart.
Scatter − Creates a Scatter style chart.
Stock − Creates a Stock style chart.
Radar − Creates a Radar style chart.

许多图表类型也有子类型。例如，柱形图、条形图、面积图和折线图的子类型是堆叠和 percent_stacked 。类型和子类型参数可以在 add_chart() 方法中给出。

Many of the chart types also have subtypes. For example, column, bar, area and line charts have sub types as stacked and percent_stacked. The type and subtype parameters can be given in the add_chart() method.

workbook.add_chart({'type': column, 'subtype': 'stacked'})

该图表通过其 insert_chart() 方法嵌入到工作表中，该方法采用以下参数 −

The chart is embedded in the worksheet with its insert_chart() method that takes following parameters −

worksheet.insert_chart(location, chartObj, options)

options 参数是一个字典，用于配置图表的放置和比例。选项属性及其默认值是 −

The options parameter is a dictionary that configures the position and scale of chart. The option properties and their default values are −

{
   'x_offset':        0,
   'y_offset':        0,
   'x_scale':         1,
   'y_scale':         1,
   'object_position': 1,
   'description':     None,
   'decorative':      False,
}

x_offset 和 y_offset 值以像素为单位，而 x_scale 和 y_scale 值用于水平/垂直缩放图表。说明字段可用于为图表指定说明或“替代文本”字符串。

The x_offset and y_offset values are in pixels, whereas x_scale and y_scale values are used to scale the chart horizontally / vertically. The description field can be used to specify a description or "alt text" string for the chart.

decorative 参数用于将图表标记为装饰性的，因此对于自动屏幕阅读器而言是不可理解的。须将其设置为 True/False。最后， object_position 参数控制图表的对象定位。它允许以下值 −

The decorative parameter is used to mark the chart as decorative, and thus uninformative, for automated screen readers. It has to be set to True/False. Finally, the object_position parameter controls the object positioning of the chart. It allows the following values −

1 − Move and size with cells (the default).
2 − Move but don’t size with cells.
3 − Don’t move or size with cells.