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?
To delete rows that contain these cells, right-click anywhere in the data range and from the drop-down menu, choose Delete.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With