Python Xlsxwriter 简明教程

Python XlsxWriter - Defined Names

在 Excel 中，可以通过用户定义的名称来识别单元格、公式或单元格范围，该名称可用作变量，以便更容易理解公式的定义。这可以使用工作簿类的 define_name() 方法实现。

In Excel, it is possible to identify a cell, a formula, or a range of cells by user-defined name, which can be used as a variable used to make the definition of formula easy to understand. This can be achieved using the define_name() method of the Workbook class.

在以下代码片段中，我们有一个由数字组成的单元格范围。这个范围已命名为 marks。

In the following code snippet, we have a range of cells consisting of numbers. This range has been given a name as marks.

data=['marks',50,60,70, 'Total']
ws.write_row('A1', data)
wb.define_name('marks', '=Sheet1!$A$1:$E$1')

如果名称被分配给单元格区域， define_name() 方法的第二个参数是一个 string ，其中包含表的名称，后跟 " ! " 符号，然后使用绝对寻址方案指定单元格区域。在本例中，sheet1 中的区域 A1:E1 命名为 marks 。

If the name is assigned to a range of cells, the second argument of define_name() method is a string with the name of the sheet followed by "!" symbol and then the range of cells using the absolute addressing scheme. In this case, the range A1:E1 in sheet1 is named as marks.

此名称可用于任何公式。例如，我们计算由名称 marks 识别的范围中数字的和。

This name can be used in any formula. For example, we calculate the sum of numbers in the range identified by the name marks.

ws.write('F1', '=sum(marks)')

我们还可以在 write_formula() 方法中使用带名称的单元格。在以下代码中，此方法用于计算 rate is a defined_name 中数额的利息。

We can also use the named cell in the write_formula() method. In the following code, this method is used to calculate interest on the amount where the rate is a defined_name.

ws.write('B5', 10)
wb.define_name('rate', '=sheet1!$B$5')

ws.write_row('A5', ['Rate', 10])

data=['Amount',1000, 2000, 3000]
ws.write_column('A6', data)
ws.write('B6', 'Interest')
for row in range(6,9):
   ws.write_formula(row, 1, '= rate*$A{}/100'.format(row+1))

我们还可以在上述代码中使用 write_array_formula() 方法来代替循环 -

We can also use write_array_formula() method instead of the loop in the above code −

ws.write_array_formula('D7:D9' , '{=rate/100*(A7:A9)}')

Example

使用 define_name() 方法的完整代码如下所示 -

The complete code using *define_name() * method is given below −

import xlsxwriter

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

data = ['marks',50,60,70, 'Total']
ws.write_row('A1', data)
wb.define_name('marks', '=Sheet1!$A$1:$E$1')
ws.write('F1', '=sum(marks)')

ws.write('B5', 10)
wb.define_name('rate', '=sheet1!$B$5')

ws.write_row('A5', ['Rate', 10])

data=['Amount',1000, 2000, 3000]
ws.write_column('A6', data)
ws.write('B6', 'Interest')

for row in range(6,9):
   ws.write_formula(row, 1, '= rate*$A{}/100'.format(row+1))

wb.close()

Output

运行上述程序并使用 Excel 打开 ex2.xlsx 。

Run the above program and open ex2.xlsx with Excel.

ex2 xlsx