Python: Add Tables to Excel Documents or Delete Tables from Excel Documents

Tables in Excel are powerful tools for organizing, storing, analyzing, and visualizing data. They are widely used in various industries and fields, including finance, business, science, education, and more. The table functionality in Excel makes data processing easier and provides users with flexibility and efficiency to make decisions and solve problems. This article will explain how to use Spire.XLS for Python to add or delete tables in Excel documents using Python.

• Add Tables to an Excel Document in Python
• Delete Tables from an Excel Document in Python

Install Spire.XLS for Python

This scenario requires Spire.XLS for Python and plum-dispatch v1.7.4. They can be easily installed in your Windows through the following pip commands.

pip install Spire.XLS

If you are unsure how to install, please refer to this tutorial: How to Install Spire.XLS for Python on Windows

Add Tables to an Excel Document in Python

Spire.XLS for Python creates table objects for the specified data source using the Worksheet.ListObjects.Create(tableName, range) method. The following are the detailed steps:

• Create an object of the Workbook class.
• Use the Workbook.LoadFromFile() method to load an Excel document.
• Use the Workbook.Worksheets[] property to retrieve the desired worksheet.
• Create a table object using the Worksheet.ListObjects.Create() method.
• Set the table style using the Worksheet.ListObjects[].BuiltInTableStyle property.
• Use the Workbook.SaveToFile() method to save the resulting file.

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()

# Load an .xlsx document
workbook.LoadFromFile("Data/sample1.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Create a table named "table" in the worksheet, with range [1,1,13,5]
sheet.ListObjects.Create("table", sheet.Range[1,1,13,5])

# Set the built-in table style of the first table to TableStyleLight9
sheet.ListObjects[0].BuiltInTableStyle = TableBuiltInStyles.TableStyleLight9

# Save the workbook to a file
workbook.SaveToFile("AddTable.xlsx", ExcelVersion.Version2016)

# Release resources and clean up the workbook object
workbook.Dispose()

Delete Tables from an Excel Document in Python

Excel table objects are located in the Worksheet.ListObjects collection. To delete a table object, you need to iterate through the collection, find the table object based on its name, and remove it from the collection. Here are the detailed steps:

• Create an object of the Workbook class.
• Use the Workbook.LoadFromFile() method to load an Excel document.
• Use the Workbook.Worksheets[] property to retrieve the desired worksheet.
• Iterate through the Worksheet.ListObjects collection in the worksheet to obtain the name of each ListObject object for finding the table object to delete.
• Use the Worksheet.ListObjects.RemoveAt() method to remove the table object.
• Use the Workbook.SaveToFile() method to save the resulting file.

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()

# Load an .xlsx document
workbook.LoadFromFile("Data/Sample2.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Iterate through all the tables in the worksheet
for i in range(len(sheet.ListObjects)):

    # Check if the table's name is "FruitTable"
if sheet.ListObjects[i].Name == "FruitTable":

        # If a matching table is found, remove it
        sheet.ListObjects.RemoveAt(i)

# Save the workbook to a file
workbook.SaveToFile("DeleteTable.xlsx", ExcelVersion.Version2016)

# Release resources and clean up the workbook object
workbook.Dispose()

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.