Inserting a table with openpyxl

Question

Is there any way with openpyxl (or perhaps another library) to insert a table into an Excel worksheet? By "insert a table", I'm referring to the process outlined here, where--in Excel--one would highlight a group of cells, select the Insert tab, and click on the Table icon.

I haven't found any suitable methods in the worksheet module. I also see a table module, but I can't find any example of how to use it.

Solution 1

Openpyxl version 2.4.0 added support for tables. However, as you noted, the documentation for tables thus far does not provide any examples.

Here is a brief example of how to create a table within a worksheet:

import openpyxl

# create a new workbook and select the active worksheet
workbook = openpyxl.Workbook()
worksheet = workbook.active

# populate some sample data    
worksheet["A1"] = "Fruit"
worksheet["B1"] = "Color"
worksheet["A2"] = "Apple"
worksheet["B2"] = "Red"
worksheet["A3"] = "Banana"
worksheet["B3"] = "Yellow"
worksheet["A4"] = "Coconut"
worksheet["B4"] = "Brown"

# define a table style
mediumStyle = openpyxl.worksheet.table.TableStyleInfo(name='TableStyleMedium2',
                                                      showRowStripes=True)
# create a table
table = openpyxl.worksheet.table.Table(ref='A1:B4',
                                       displayName='FruitColors',
                                       tableStyleInfo=mediumStyle)
# add the table to the worksheet
worksheet.add_table(table)

# save the workbook file
workbook.save('fruit.xlsx')

Note: Be sure that you have the latest version of the openpyxl library installed

Solution 2

openpyxl currently does not support table styles. You might want to look at using Xlsxwriter if you need this. See https://xlsxwriter.readthedocs.org/en/latest/working_with_tables.html

Solution 3

It is supported, there's an example in the docs now:

http://openpyxl.readthedocs.io/en/stable/worksheet_tables.html

Make sure you have unique headers (as stated in the docs) and make sure you change the table name from the example: displayName="Table1" -> displayName="MyTable"

Comments

[Tom]

Thanks, Dan! I seem to get an error opening the workbooks in Excel after running this. It gives me a Repaired Records: Table from /xl/tables/table1.xml part (Table). Do you get similar results?

[Tom]

My error was because I was accidentally assigning the same name two different tables. Thanks!

[Tom]

The documentation now has an example: openpyxl.readthedocs.io/en/stable/worksheet_tables.html

[Cory Kramer]

This answer is now out of date.

[Jean-Francois T.]

Link is broken: did you mean this link openpyxl.readthedocs.io/en/stable/worksheet_tables.html ?