CODEWITHSUNDEEP
pythonexcelopenpyxl
Domenico Spidy Tamburro
Domenico Spidy Tamburro

Reputation: 443

openpyxl delete rows from formatted table / error referencing table

I am trying to delete rows from a formatted table in Excel using the delete_rows() method. However, this does not delete the rows but only the content of the cells.

As an info, you can format a range as table using openpyxl as described in the documentation: https://openpyxl.readthedocs.io/en/latest/worksheet_tables.html

I have a formatted table called Table5 in the worksheet Sheet1:

enter image description here

To delete 2 rows from row 5, I use the following commands:

import openpyxl as xl
wb = xl.load_workbook('data.xlsx')
ws = wb['Sheet1']
ws.delete_rows(5, 2) # this is the delete command
wb.save('data.xlsx')

With the command delete_rows(), the range of the formatted table remains till row 6, whereas it shrinks when I delete the rows directly in Excel.

enter image description here

The question: How do I delete properly both the data and the formatted range?

Corollary note:

Also when I insert data, the table range does not expand. For example:

for i in range(4, 9):
    ws.cell(row=i, column=1).value = i - 1
    ws.cell(row=i, column=2).value = (i - 1) * 100

The range stays the same i.e. till row 6, whereas the range expands automatically by inserting data into Excel manually.

enter image description here

Upvotes: 0

Views: 838

Answers (2)

Domenico Spidy Tamburro
Domenico Spidy Tamburro

Reputation: 443

In the following, I expand the answer by ljmc (which I have accepted) containing both ways with the newer and the older version of openpyxl.

After upgrading to openpyxl==3.0.10, you change the reference of a formatted table by simply referencing to the table name in the worksheet with the attribute .tables. Example: in the worksheet Sheet1 there is a formatted table named Table5:

import openpyxl as xl
wb = xl.load_workbook('data.xlsx')
ws = wb['Sheet1']
ws.delete_rows(5, 2) # this is the delete command
ws.tables['Table5'].ref = "A1:B4" # change the reference
wb.save('data.xlsx')

As simple as that: because ws.tables['Table5'] contains the formatted table.

Before the upgrade (openpyxl==3.0.0) you reference the formatted table using the method using the ._tables attribute, but instead of the name Table5 you need the index:

ws.delete_rows(5, 2) # this is the delete command
ws._tables[0].ref = "A1:B4" # change the reference

Using [0] works only if you have one formatted table in your worksheet or if Table5 is the first within the table object.

The proper pythonic way is to first determine the index where Table5 is in the table object:

ws.delete_rows(5, 2) # this is the delete command
ix = next(i for i, t in enumerate(ws._tables) if t.displayName == "Table5")
ws._tables[ix].ref = "A1:B4"  # change the reference

Upvotes: 0

ljmc
ljmc

Reputation: 5294

If you followed the linked doc, you must have:

  1. added data to the Worksheet
  2. created a Table instance which covers the data (ref=A1:B6)
  3. added the Table instance to the worksheet with ws.add_table.

When you later add or remove rows, this does not affect the table which was remains with ref=A1:B6.

You would need to change the Table instance's ref to fit the new data layout.

# access your table (subsitute <Table> for the name of your table)
tab = ws.tables[<Table>]

# change the ref
tab.ref = "A1:B4"

# and save
wb.save('data.xlsx')

NB. the interface to the tables has changed since version 3.0.0, this code is tested with for 3.0.10, please check with documentation for previous/further changes.

Upvotes: 1

Related Questions