CODEWITHSUNDEEP
pythonexcelopenpyxl
I Am Arien
I Am Arien

Reputation: 79

Delete an entire row in an excel file using Python

I am currently using work_sheet.delete_rows() using openpyxl module, but it only seems to delete the values in the row not the actual row itself.. How can I delete the entire row ??

This is the actual Excel Data I am currently accessing..

enter image description here

This is now the output after deleting the rows.. The process only deletes the values of each rows not the entire row itself...

enter image description here

Upvotes: 0

Views: 623

Answers (1)

paxdiablo
paxdiablo

Reputation: 882078

I'm guessing there's something wrong with the way you're calling it. While you've stated what you're using, you haven't given us the actual code that you're using.

The actual source code for OpenPyXl shows that it does actually move the rows below the area being deleted up to the start of that area.

You can actually see this by using the following simple spreadsheet:

      A       B
--+-------+-------+
1 |   1   |   a   |
2 |   3   |   b   |
3 |   5   |   c   |
4 |   7   |   d   |
5 |   9   |   e   |

The following script shows the deletion of the second, third, and fourth row:

import openpyxl

def dumpWs(desc, ws):
    print(f'{desc}: ', end = '')
    for row in ws.values:
        print(end = '<')
        sep = ''
        for value in row:
            print(f'{sep}{value}', end = '')
            sep = ', '
        print(end = '> ')
    print()

wb = openpyxl.load_workbook('version1.xlsx')
ws = wb.active
dumpWs('As loaded   ', ws)

ws.delete_rows(2, 3)
dumpWs('After delete', ws)

wb.save('version2.xlsx')

Running this script results in:

As loaded   : <1, a> <3, b> <5, c> <7, d> <9, e> 
After delete: <1, a> <9, e>

The version saved with the wb.save line is as expected:

      A       B
--+-------+-------+
1 |   1   |   a   |
5 |   9   |   e   |

One thing you may want to check is the version of OpenPyXl that you're using, since there are several bug reports on various iterations of delete_rows. I'm using version 3.0.3 (with Python 3.6.8, for completeness).

Upvotes: 2

Related Questions