Python XlsxWriter - Formula & Function

The Worksheet class offers three methods for using formulas.

write_formula()

write_array_formula()

write_dynamic_array_formula()

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

The write_formula() Method

The write_formula() method requires the address of the cell, and a string containing a vapd 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

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

The Excel file shows the following result −

The write_array_formula() Method

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.

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

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

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

The write_dynamic_array_data() Method

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 −

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

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

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.