Python Xlsxwriter 简明教程

Python XlsxWriter - Tables

在 MS Excel 中，表是作为单个实体分组的一系列单元格。它可以从公式中引用，并具有公共格式属性。可以在工作表中定义一些诸如列标题、自动筛选器、总行、列公式之类的功能。

In MS Excel, a Table is a range of cells that has been grouped as a single entity. It can be referenced from formulas and has common formatting attributes. Several features such as column headers, autofilters, total rows, column formulas can be defined in a worksheet table.

The add_table() Method

工作表方法 add_table() 用于将单元格范围添加为表。

The worksheet method add_table() is used to add a cell range as a table.

worksheet.add_table(first_row, first_col, last_row, last_col, options)

标准“ A1 ”或“ Row/Column ”符号均可用于指定范围。 add_table() 方法可以采用下面的一个或多个可选参数。请注意，除了范围参数之外，其他参数都是可选的。如果不给出，则创建一个空表。

Both the methods, the standard 'A1' or 'Row/Column' notation are allowed for specifying the range. The add_table() method can take one or more of the following optional parameters. Note that except the range parameter, others are optional. If not given, an empty table is created.

Example

data

data

该参数可用于指定表中单元格中的数据。看看下列示例 −

This parameter can be used to specify the data in the cells of the table. Look at the following example −

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

data = [
   ['Namrata',  75, 65, 80],
   ['Ravi',     60, 70, 80],
   ['Kiran',    65, 75, 85],
   ['Karishma', 55, 65, 75],
]
ws.add_table("A1:D4", {'data':data})

wb.close()

Output

以下是结果 −

Here’s the result −

add table

header_row

该参数可用于关闭或开启表中的标题行。默认情况下它是打开的。标题行将包含一些默认标题，如 Column 1、Column 2 等等。你可以使用 columns 参数设置必需的标题。

This parameter can be used to turn on or off the header row in the table. It is on by default. The header row will contain default captions such as Column 1, Column 2, etc. You can set required captions by using the columns parameter.

Columns

Example

该属性用于设置列标题。

This property is used to set column captions.

import xlsxwriter

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

data = [
   ['Namrata',  75, 65, 80],
   ['Ravi',     60, 70, 80],
   ['Kiran',    65, 75, 85],
   ['Karishma', 55, 65, 75],
]

ws.add_table("A1:D4",
{'data':data,
   'columns': [
   {'header': 'Name'},
   {'header': 'physics'},
   {'header': 'Chemistry'},
   {'header': 'Maths'}]
})
wb.close()

Output

标题行现在设置为如下所示 −

The header row is now set as shown −

header row

autofilter

该参数默认情况下是 ON。将其设置成 OFF 时，标题行不显示下拉箭头来设置筛选条件。

This parameter is ON, by default. When set to OFF, the header row doesn’t show the dropdown arrows to set the filter criteria.

Name

在 Excel 工作表中，表被命名为 Table1、Table2 等。name 参数可用于根据需要设置表名称。

In Excel worksheet, the tables are named as Table1, Table2, etc. The name parameter can be used to set the name of the table as required.

ws.add_table("A1:E4", {'data':data, 'name':'marklist'})

Formula

可以在列选项中指定公式子属性来创建包含公式的列。

Column with a formula can be created by specifying formula sub-property in columns options.

Example

在以下示例中，表的名称属性设置为“marklist”。“总计”列 E 的公式执行成绩的总和，并分配给公式子属性的值。

In the following example, the table’s name property is set to 'marklist'. The formula for 'Total' column E performs sum of marks, and is assigned the value of formula sub-property.

import xlsxwriter

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

data = [
   ['Namrata',  75, 65, 80],
   ['Ravi',     60, 70, 80],
   ['Kiran',    65, 75, 85],
   ['Karishma', 55, 65, 75],
]
formula = '=SUM(marklist[@[physics]:[Maths]])'
tbl = ws.add_table("A1:E5",
{'data': data,
   'autofilter': False,
   'name': 'marklist',
   'columns': [
      {'header': 'Name'},
      {'header': 'physics'},
      {'header': 'Chemistry'},
      {'header': 'Maths'},
      {'header': 'Total', 'formula': formula}
   ]
})
wb.close()

Output

执行以上代码时，工作表将显示 Total 列，其中包含成绩总和。

When the above code is executed, the worksheet shows the Total column with the sum of marks.

autofilter