- Python XlsxWriter - VBA Macro
- Python XlsxWriter - Working with Pandas
- Python XlsxWriter - Cell Comments
- Python XlsxWriter - Header & Footer
- Python XlsxWriter - Page Setup
- Python XlsxWriter - Insert Image
- Python XlsxWriter - Textbox
- Python XlsxWriter - Hide/Protect Worksheet
- Python XlsxWriter - Freeze & Split Panes
- Python XlsxWriter - Outlines & Grouping
- Python XlsxWriter - Data Validation
- Python XlsxWriter - Sparklines
- Python XlsxWriter - Pie Chart
- Python XlsxWriter - Line Chart
- Python XlsxWriter - Bar Chart
- Python XlsxWriter - Chart Legends
- Python XlsxWriter - Chart Formatting
- Python XlsxWriter - Adding Charts
- Python XlsxWriter - Conditional Formatting
- Python XlsxWriter - Hyperlinks
- Python XlsxWriter - Border
- Python XlsxWriter - Number Formats
- Python XlsxWriter - Fonts & Colors
- Python XlsxWriter - Applying Filter
- Python XlsxWriter - Tables
- Python XlsxWriter - Date and Time
- Python XlsxWriter - Formula & Function
- Python XlsxWriter - Defined Names
- Python XlsxWriter - Cell Notation & Ranges
- Python XlsxWriter - Important classes
- Python XlsxWriter - Hello World
- Python XlsxWriter - Environment Setup
- Python XlsxWriter - Overview
- Python XlsxWriter - Home
Python XlsxWriter Useful Resources
Selected Reading
- Who is Who
- Computer Glossary
- HR Interview Questions
- Effective Resume Writing
- Questions and Answers
- UPSC IAS Exams Notes
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 : markpst })
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 markpst . 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(markpst[@[physics]:[Maths]]) tbl = ws.add_table("A1:E5", { data : data, autofilter : False, name : markpst , 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.
Advertisements