Python XlsxWriter - Defined Names

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.

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 )

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.

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) )

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))

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

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

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