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 −

Add Table

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 −

Header Row

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.

AutoFilter
Advertisements