Example of how to add tables to an XlsxWriter worksheet.
Tables in Excel are used to group rows and columns of data into a single structure that can be referenced in a formula or formatted collectively.
See also Working with Worksheet Tables.
###############################################################################
#
# Example of how to add tables to an XlsxWriter worksheet.
#
# Tables in Excel are used to group rows and columns of data into a single
# structure that can be referenced in a formula or formatted collectively.
#
# SPDX-License-Identifier: BSD-2-Clause
# Copyright 2013-2022, John McNamara, jmcnamara@cpan.org
#
import xlsxwriter
workbook = xlsxwriter.Workbook('tables.xlsx')
worksheet1 = workbook.add_worksheet()
worksheet2 = workbook.add_worksheet()
worksheet3 = workbook.add_worksheet()
worksheet4 = workbook.add_worksheet()
worksheet5 = workbook.add_worksheet()
worksheet6 = workbook.add_worksheet()
worksheet7 = workbook.add_worksheet()
worksheet8 = workbook.add_worksheet()
worksheet9 = workbook.add_worksheet()
worksheet10 = workbook.add_worksheet()
worksheet11 = workbook.add_worksheet()
worksheet12 = workbook.add_worksheet()
worksheet13 = workbook.add_worksheet()
currency_format = workbook.add_format({'num_format': '$#,##0'})
# Some sample data for the table.
data = [
['Apples', 10000, 5000, 8000, 6000],
['Pears', 2000, 3000, 4000, 5000],
['Bananas', 6000, 6000, 6500, 6000],
['Oranges', 500, 300, 200, 700],
]
###############################################################################
#
# Example 1.
#
caption = 'Default table with no data.'
# Set the columns widths.
worksheet1.set_column('B:G', 12)
# Write the caption.
worksheet1.write('B1', caption)
# Add a table to the worksheet.
worksheet1.add_table('B3:F7')
###############################################################################
#
# Example 2.
#
caption = 'Default table with data.'
# Set the columns widths.
worksheet2.set_column('B:G', 12)
# Write the caption.
worksheet2.write('B1', caption)
# Add a table to the worksheet.
worksheet2.add_table('B3:F7', {'data': data})
###############################################################################
#
# Example 3.
#
caption = 'Table without default autofilter.'
# Set the columns widths.
worksheet3.set_column('B:G', 12)
# Write the caption.
worksheet3.write('B1', caption)
# Add a table to the worksheet.
worksheet3.add_table('B3:F7', {'autofilter': 0})
# Table data can also be written separately, as an array or individual cells.
worksheet3.write_row('B4', data[0])
worksheet3.write_row('B5', data[1])
worksheet3.write_row('B6', data[2])
worksheet3.write_row('B7', data[3])
###############################################################################
#
# Example 4.
#
caption = 'Table without default header row.'
# Set the columns widths.
worksheet4.set_column('B:G', 12)
# Write the caption.
worksheet4.write('B1', caption)
# Add a table to the worksheet.
worksheet4.add_table('B4:F7', {'header_row': 0})
# Table data can also be written separately, as an array or individual cells.
worksheet4.write_row('B4', data[0])
worksheet4.write_row('B5', data[1])
worksheet4.write_row('B6', data[2])
worksheet4.write_row('B7', data[3])
###############################################################################
#
# Example 5.
#
caption = 'Default table with "First Column" and "Last Column" options.'
# Set the columns widths.
worksheet5.set_column('B:G', 12)
# Write the caption.
worksheet5.write('B1', caption)
# Add a table to the worksheet.
worksheet5.add_table('B3:F7', {'first_column': 1, 'last_column': 1})
# Table data can also be written separately, as an array or individual cells.
worksheet5.write_row('B4', data[0])
worksheet5.write_row('B5', data[1])
worksheet5.write_row('B6', data[2])
worksheet5.write_row('B7', data[3])
###############################################################################
#
# Example 6.
#
caption = 'Table with banded columns but without default banded rows.'
# Set the columns widths.
worksheet6.set_column('B:G', 12)
# Write the caption.
worksheet6.write('B1', caption)
# Add a table to the worksheet.
worksheet6.add_table('B3:F7', {'banded_rows': 0, 'banded_columns': 1})
# Table data can also be written separately, as an array or individual cells.
worksheet6.write_row('B4', data[0])
worksheet6.write_row('B5', data[1])
worksheet6.write_row('B6', data[2])
worksheet6.write_row('B7', data[3])
###############################################################################
#
# Example 7.
#
caption = 'Table with user defined column headers.'
# Set the columns widths.
worksheet7.set_column('B:G', 12)
# Write the caption.
worksheet7.write('B1', caption)
# Add a table to the worksheet.
worksheet7.add_table('B3:F7', {'data': data,
'columns': [{'header': 'Product'},
{'header': 'Quarter 1'},
{'header': 'Quarter 2'},
{'header': 'Quarter 3'},
{'header': 'Quarter 4'},
]})
###############################################################################
#
# Example 8.
#
caption = 'Table with user defined column headers.'
# Set the columns widths.
worksheet8.set_column('B:G', 12)
# Write the caption.
worksheet8.write('B1', caption)
# Formula to use in the table.
formula = '=SUM(Table8[@[Quarter 1]:[Quarter 4]])'
# Add a table to the worksheet.
worksheet8.add_table('B3:G7', {'data': data,
'columns': [{'header': 'Product'},
{'header': 'Quarter 1'},
{'header': 'Quarter 2'},
{'header': 'Quarter 3'},
{'header': 'Quarter 4'},
{'header': 'Year',
'formula': formula},
]})
###############################################################################
#
# Example 9.
#
caption = 'Table with totals row (but no caption or totals).'
# Set the columns widths.
worksheet9.set_column('B:G', 12)
# Write the caption.
worksheet9.write('B1', caption)
# Formula to use in the table.
formula = '=SUM(Table9[@[Quarter 1]:[Quarter 4]])'
# Add a table to the worksheet.
worksheet9.add_table('B3:G8', {'data': data,
'total_row': 1,
'columns': [{'header': 'Product'},
{'header': 'Quarter 1'},
{'header': 'Quarter 2'},
{'header': 'Quarter 3'},
{'header': 'Quarter 4'},
{'header': 'Year',
'formula': formula
},
]})
###############################################################################
#
# Example 10.
#
caption = 'Table with totals row with user captions and functions.'
# Set the columns widths.
worksheet10.set_column('B:G', 12)
# Write the caption.
worksheet10.write('B1', caption)
# Options to use in the table.
options = {'data': data,
'total_row': 1,
'columns': [{'header': 'Product', 'total_string': 'Totals'},
{'header': 'Quarter 1', 'total_function': 'sum'},
{'header': 'Quarter 2', 'total_function': 'sum'},
{'header': 'Quarter 3', 'total_function': 'sum'},
{'header': 'Quarter 4', 'total_function': 'sum'},
{'header': 'Year',
'formula': '=SUM(Table10[@[Quarter 1]:[Quarter 4]])',
'total_function': 'sum'
},
]}
# Add a table to the worksheet.
worksheet10.add_table('B3:G8', options)
###############################################################################
#
# Example 11.
#
caption = 'Table with alternative Excel style.'
# Set the columns widths.
worksheet11.set_column('B:G', 12)
# Write the caption.
worksheet11.write('B1', caption)
# Options to use in the table.
options = {'data': data,
'style': 'Table Style Light 11',
'total_row': 1,
'columns': [{'header': 'Product', 'total_string': 'Totals'},
{'header': 'Quarter 1', 'total_function': 'sum'},
{'header': 'Quarter 2', 'total_function': 'sum'},
{'header': 'Quarter 3', 'total_function': 'sum'},
{'header': 'Quarter 4', 'total_function': 'sum'},
{'header': 'Year',
'formula': '=SUM(Table11[@[Quarter 1]:[Quarter 4]])',
'total_function': 'sum'
},
]}
# Add a table to the worksheet.
worksheet11.add_table('B3:G8', options)
###############################################################################
#
# Example 12.
#
caption = 'Table with Excel style removed.'
# Set the columns widths.
worksheet12.set_column('B:G', 12)
# Write the caption.
worksheet12.write('B1', caption)
# Options to use in the table.
options = {'data': data,
'style': None,
'total_row': 1,
'columns': [{'header': 'Product', 'total_string': 'Totals'},
{'header': 'Quarter 1', 'total_function': 'sum'},
{'header': 'Quarter 2', 'total_function': 'sum'},
{'header': 'Quarter 3', 'total_function': 'sum'},
{'header': 'Quarter 4', 'total_function': 'sum'},
{'header': 'Year',
'formula': '=SUM(Table12[@[Quarter 1]:[Quarter 4]])',
'total_function': 'sum'
},
]}
# Add a table to the worksheet.
worksheet12.add_table('B3:G8', options)
###############################################################################
#
# Example 13.
#
caption = 'Table with column formats.'
# Set the columns widths.
worksheet13.set_column('B:G', 12)
# Write the caption.
worksheet13.write('B1', caption)
# Options to use in the table.
options = {'data': data,
'total_row': 1,
'columns': [{'header': 'Product', 'total_string': 'Totals'},
{'header': 'Quarter 1',
'total_function': 'sum',
'format': currency_format,
},
{'header': 'Quarter 2',
'total_function': 'sum',
'format': currency_format,
},
{'header': 'Quarter 3',
'total_function': 'sum',
'format': currency_format,
},
{'header': 'Quarter 4',
'total_function': 'sum',
'format': currency_format,
},
{'header': 'Year',
'formula': '=SUM(Table13[@[Quarter 1]:[Quarter 4]])',
'total_function': 'sum',
'format': currency_format,
},
]}
# Add a table to the worksheet.
worksheet13.add_table('B3:G8', options)
workbook.close()