- 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 - Tables
In MS Excel, a Table is a range of cells that has been grouped as a single entity. It can be referenced from formulas and has common formatting attributes. Several features such as column headers, autofilters, total rows, column formulas can be defined in a worksheet table.
The add_table() Method
The worksheet method add_table() is used to add a cell range as a table.
worksheet.add_table(first_row, first_col, last_row, last_col, options)
Both the methods, the standard 'A1' or 'Row/Column' notation are allowed for specifying the range. The add_table() method can take one or more of the following optional parameters. Note that except the range parameter, others are optional. If not given, an empty table is created.
Example
data
This parameter can be used to specify the data in the cells of the table. Look at the following example −
import xlsxwriter wb = xlsxwriter.Workbook('hello.xlsx') ws = wb.add_worksheet() data = [ ['Namrata', 75, 65, 80], ['Ravi', 60, 70, 80], ['Kiran', 65, 75, 85], ['Karishma', 55, 65, 75], ] ws.add_table("A1:D4", {'data':data}) wb.close()
Output
Here's the result −
header_row
This parameter can be used to turn on or off the header row in the table. It is on by default. The header row will contain default captions such as Column 1, Column 2, etc. You can set required captions by using the columns parameter.
Columns
Example
This property is used to set column captions.
import xlsxwriter wb = xlsxwriter.Workbook('hello.xlsx') ws = wb.add_worksheet() data = [ ['Namrata', 75, 65, 80], ['Ravi', 60, 70, 80], ['Kiran', 65, 75, 85], ['Karishma', 55, 65, 75], ] ws.add_table("A1:D4", {'data':data, 'columns': [ {'header': 'Name'}, {'header': 'physics'}, {'header': 'Chemistry'}, {'header': 'Maths'}] }) wb.close()
Output
The header row is now set as shown −
autofilter
This parameter is ON, by default. When set to OFF, the header row doesn't show the dropdown arrows to set the filter criteria.
Name
In Excel worksheet, the tables are named as Table1, Table2, etc. The name parameter can be used to set the name of the table as required.
ws.add_table("A1:E4", {'data':data, 'name':'marklist'})
Formula
Column with a formula can be created by specifying formula sub-property in columns options.
Example
In the following example, the table's name property is set to 'marklist'. The formula for 'Total' column E performs sum of marks, and is assigned the value of formula sub-property.
import xlsxwriter wb = xlsxwriter.Workbook('hello.xlsx') ws = wb.add_worksheet() data = [ ['Namrata', 75, 65, 80], ['Ravi', 60, 70, 80], ['Kiran', 65, 75, 85], ['Karishma', 55, 65, 75], ] formula = '=SUM(marklist[@[physics]:[Maths]])' tbl = ws.add_table("A1:E5", {'data': data, 'autofilter': False, 'name': 'marklist', 'columns': [ {'header': 'Name'}, {'header': 'physics'}, {'header': 'Chemistry'}, {'header': 'Maths'}, {'header': 'Total', 'formula': formula} ] }) wb.close()
Output
When the above code is executed, the worksheet shows the Total column with the sum of marks.
To Continue Learning Please Login
Login with Google