Python Xlsxwriter 简明教程

Python XlsxWriter - Formula & Function

Worksheet 类提供了三种方法来使用公式。

The Worksheet class offers three methods for using formulas.

  1. write_formula()

  2. write_array_formula()

  3. write_dynamic_array_formula()

所有这些方法都用于将公式和函数分配给单元格。

All these methods are used to assign formula as well as function to a cell.

The write_formula() Method

write_formula() 方法需要单元格的地址和包含有效 Excel 公式的字符串。在公式字符串中，仅接受 A1 样式的地址表示法。但是，单元格地址参数可以是标准 Excel 类型，也可以是零为基础的行和列号表示法。

The write_formula() method requires the address of the cell, and a string containing a valid Excel formula. Inside the formula string, only the A1 style address notation is accepted. However, the cell address argument can be either standard Excel type or zero based row and column number notation.

Example

在下面的示例中，各种语句使用 write_formula() 方法。第一个使用标准 Excel 表示法分配公式。第二个语句使用行和列号来指定设置公式的目标单元格的地址。在第三个示例中， IF() 函数被分配给 G2 单元格。

In the example below, various statements use write_formula() method. The first uses a standard Excel notation to assign a formula. The second statement uses row and column number to specify the address of the target cell in which the formula is set. In the third example, the IF() function is assigned to G2 cell.

import xlsxwriter

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

data=[
   ['Name', 'Phy', 'Che', 'Maths', 'Total', 'percent', 'Result' ],
   ['Arvind', 50,60,70]
]
ws.write_row('A1', data[0])
ws.write_row('A2', data[1])
ws.write_formula('E2', '=B2+C2+D2')
ws.write_formula(1,5, '=E2*100/300')
ws.write_formula('G2', '=IF(F2>=50, "PASS","FAIL")')

wb.close()

Output

Excel 文件显示以下结果 −

The Excel file shows the following result −

write formula

The write_array_formula() Method

write_array_formula() 方法用于将公式扩展到某个范围。在 Excel 中，数组公式对一组值执行计算。它可以返回一个值或一系列值。

The write_array_formula() method is used to extend the formula over a range. In Excel, an array formula performs a calculation on a set of values. It may return a single value or a range of values.

数组公式通过公式周围的一对大括号 {=SUM(A1:B1*A2:B2)} 来表示。该范围可以通过范围中第一个和最后一个单元格的行号和列号（例如 0,0, 2,2）或字符串表示法 'A1:C2' 来指定。

An array formula is indicated by a pair of braces around the formula − {=SUM(A1:B1*A2:B2)}. The range can be either specified by row and column numbers of first and last cell in the range (such as 0,0, 2,2) or by the string representation 'A1:C2'.

Example

在以下示例中，数组公式用于列 E、F 和 G 以计算范围 B2:D4 中分数的总计、百分比和结果。

In the following example, array formulas are used for columns E, F and G to calculate total, percent and result from marks in the range B2:D4.

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

data=[
   ['Name', 'Phy', 'Che', 'Maths', 'Total', 'percent', 'Result'],
   ['Arvind', 50,60,70],
   ['Amar', 55,65,75],
   ['Asha', 75,85,80]
]

for row in range(len(data)):
   ws.write_row(row,0, data[row])

ws.write_array_formula('E2:E4', '{=B2:B4+C2:C4+D2:D4}')
ws.write_array_formula(1,5,3,5, '{=(E2:E4)*100/300}')
ws.write_array_formula('G2:G4', '{=IF((F2:F4)>=50, "PASS","FAIL")}')

wb.close()

Output

以下是使用 MS Excel 打开工作表时的显示方式 −

Here is how the worksheet appears when opened using MS Excel −

write array formula

The write_dynamic_array_data() Method

write_dynamic_array_data() 方法在单元格区域中写入一个动态数组公式。在 EXCEL 的 365 版本中引入了动态数组的概念，并且还引入了一些利用动态数组优势的新函数。这些函数有 −

The write_dynamic_array_data() method writes an dynamic array formula to a cell range. The concept of dynamic arrays has been introduced in EXCEL’s 365 version, and some new functions that leverage the advantage of dynamic arrays have been introduced. These functions are −

Sr.No

Functions & Description

1

FILTER Filter data and return matching records

2

RANDARRAY Generate array of random numbers

3

SEQUENCE Generate array of sequential numbers

4

SORT Sort range by column

5

SORTBY Sort range by another range or array

6

UNIQUE Extract unique values from a list or range

7

XLOOKUP replacement for VLOOKUP

8

XMATCH replacement for the MATCH function

动态数组是其大小可以根据结果而变化的返回值区域。例如，函数 FILTER() 返回一个数组，其大小可以根据筛选结果而变化。

Dynamic arrays are ranges of return values whose size can change based on the results. For example, a function such as FILTER() returns an array of values that can vary in size depending on the filter results.

Example

在下面的示例中，数据范围是 A1:D17。筛选函数使用此范围，标准范围是 C1:C17，其中给出了产品名称。 FILTER() 函数产生动态数组，因为满足标准的行数可能发生变化。

In the example below, the data range is A1:D17. The filter function uses this range and the criteria range is C1:C17, in which the product names are given. The FILTER() function results in a dynamic array as the number of rows satisfying the criteria may change.

import xlsxwriter

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

data = (
   ['Region', 'SalesRep', 'Product', 'Units'],
   ['East',   'Tom',      'Apple',    6380],
   ['West',   'Fred',     'Grape',    5619],
   ['North',  'Amy',      'Pear',     4565],
   ['South',  'Sal',      'Banana',   5323],
   ['East',   'Fritz',    'Apple',    4394],
   ['West',   'Sravan',   'Grape',    7195],
   ['North',  'Xi',       'Pear',     5231],
   ['South',  'Hector',   'Banana',   2427],
   ['East',   'Tom',      'Banana',   4213],
   ['West',   'Fred',     'Pear',     3239],
   ['North',  'Amy',      'Grape',    6520],
   ['South',  'Sal',      'Apple',    1310],
   ['East',   'Fritz',    'Banana',   6274],
   ['West',   'Sravan',   'Pear',     4894],
   ['North',  'Xi',       'Grape',    7580],
   ['South',  'Hector',   'Apple',    9814])
for row in range(len(data)):
   ws.write_row(row,0, data[row])

ws.write_dynamic_array_formula('F1', '=FILTER(A1:D17,C1:C17="Apple")')
wb.close()

Output

请注意，write_dynamic_array_formula() 的公式字符串不必包含大括号。 The resultant hello.xlsx must be opened with Excel 365 app.

Note that the formula string to write_dynamic_array_formula() need not contain curly brackets. The resultant hello.xlsx must be opened with Excel 365 app.

write dynamic array data