Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to delete rows that satisfy some criteria in an excel spreadsheet?

I would like to create a "reduced" version of an Excel (xlsx) spreadsheet (i.e. by removing some rows according to some criterion), and I'd like to know if this can be done with openpyxl.

In (pythonish) pseudo-code, what I want to do would look something like:

wb = openpyxl.reader.excel.load_workbook('/path/to/workbook.xlsx')
sh = wb.get_sheet_by_name('someworksheet')

# weed out the rows of sh according to somecriterion
sh.rows[:] = [r for r in sh.rows if somecriterion(r)]

# save the workbook, with the weeded-out sheet
wb.save('/path/to/workbook_reduced.xlsx')

Can something like this be done with openpyxl, and if so, how?

like image 242
kjo Avatar asked Feb 15 '13 23:02

kjo


People also ask

How do I delete all rows containing certain data?

To delete rows that contain these cells, right-click anywhere in the data range and from the drop-down menu, choose Delete.

How do I hide certain rows based on cell value?

To do this, first select the data that you want to filter. Then, click the Data tab on the ribbon and click the Filter button. In the drop-down menu that appears, click the column that you want to filter by and then uncheck the box next to the value that you want to hide. Finally, click OK.


1 Answers

2018 update: I was searching how to delete a row today and found that the functionality is added in openpyxl 2.5.0-b2. Just tried and it worked perfectly. Here's the link where I found the answer: https://bitbucket.org/openpyxl/openpyxl/issues/964/delete_rows-does-not-work-on-deleting

And here's the syntax to delete one row:

ws.delete_rows(index, 1)

where: 'ws' is the worksheet, 'index' is the row number, and '1' is the number of rows to delete.

There's also the ability to delete columns, but I haven't tried that.

like image 175
jhughs Avatar answered Oct 12 '22 16:10

jhughs