- Python XlsxWriter Tutorial
- Python XlsxWriter - Home
- Python XlsxWriter - Overview
- Python XlsxWriter - Environment Setup
- Python XlsxWriter - Hello World
- Python XlsxWriter - Important classes
- Python XlsxWriter - Cell Notation & Ranges
- Python XlsxWriter - Defined Names
- Python XlsxWriter - Formula & Function
- Python XlsxWriter - Date and Time
- Python XlsxWriter - Tables
- Python XlsxWriter - Applying Filter
- Python XlsxWriter - Fonts & Colors
- Python XlsxWriter - Number Formats
- Python XlsxWriter - Border
- Python XlsxWriter - Hyperlinks
- Python XlsxWriter - Conditional Formatting
- Python XlsxWriter - Adding Charts
- Python XlsxWriter - Chart Formatting
- Python XlsxWriter - Chart Legends
- Python XlsxWriter - Bar Chart
- Python XlsxWriter - Line Chart
- Python XlsxWriter - Pie Chart
- Python XlsxWriter - Sparklines
- Python XlsxWriter - Data Validation
- Python XlsxWriter - Outlines & Grouping
- Python XlsxWriter - Freeze & Split Panes
- Python XlsxWriter - Hide/Protect Worksheet
- Python XlsxWriter - Textbox
- Python XlsxWriter - Insert Image
- Python XlsxWriter - Page Setup
- Python XlsxWriter - Header & Footer
- Python XlsxWriter - Cell Comments
- Python XlsxWriter - Working with Pandas
- Python XlsxWriter - VBA Macro
- Python XlsxWriter Useful Resources
- Python XlsxWriter - Quick Guide
- Python XlsxWriter - Useful Resources
- Python XlsxWriter - Discussion
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 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
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 −
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 |
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.