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