Removing characteristic rows in a simple Excel file

Question

I need to remove some rows in an simple and straight-forward Excel file.

For example, to remove the rows that column B is not blank.

enter image description here

What I can think of is not really a ‘remove’ way, but rename a new created file:

import os
import xlwt
from xlrd import open_workbook

old_file = open_workbook('C:\\file.xls',formatting_info=True)
old_sheet = old_file.sheet_by_index(0)

new_file = xlwt.Workbook(encoding='utf-8', style_compression = 0)
new_sheet = new_file.add_sheet('Sheet1', cell_overwrite_ok = True)

contents = []

for row in range(old_sheet.nrows):
    a = old_sheet.cell(row,0).value
    b = old_sheet.cell(row,1).value
    if len(b) < 1:
        contents.append(a)

for c, content in enumerate(contents):
    new_sheet.write(c, 0, content)


new_file.save('C:\\file_1.xls')

os.remove('C:\\file.xls')
os.rename('C:\\file_1.xls', 'C:\\file.xls')

Well, it’s not really deleting the rows but anyhow it could be a suitable way.

What better ways are there to do this, such as considering more conditions?

You've done it right. see this thread [Python to delete a row in excel spreadsheet](http://stackoverflow.com/questions/5635054/python-to-delete-a-row-in-excel-spreadsheet) — linxie, Sep 11 '14 at 05:29
Ultimately, this is what Excel itself does anyway. There is *no such thing* as "modifying" a file "in place". It's just an illusion created by the Excel user interface. If you are in Excel and make a change, and then press Ctrl+S, what it does is blow away the old file, replacing it with a brand-new one that has the same name. — John Y, Sep 23 '14 at 20:38

score 2 · Answer 1 · answered Sep 11 '14 at 06:36

2

It's easier to handle when you use csv file instead of xls file.

answered Sep 11 '14 at 06:36

Thinsky

4,226
3
13
22

score 2 · Answer 2 · answered Sep 18 '14 at 22:34

try pyexcel:

>>> import pyexcel
>>> r=pyexcel.FilterableReader("mysample.xls")
>>> keep_row_func = lambda row: row[1] == ''
>>> r.filter(pyexcel.filters.RowValueFilter(keep_row_func))
>>> pyexcel.utils.to_array(r)
[111.0, '', 222.0, '', 444.0, '', 666.0, '', 777.0, '']
>>> w=pyexcel.Writer("output.xlsx") # or output.ods, output.csv
>>> w.write_reader(r)
>>> w.close()

With pyexel, you can use above script over any of these file formats: ods, csv, xls, xlsx and xlsm. Documentation is here: http://pythonhosted.org//pyexcel/

score 1 · Accepted Answer · edited May 23 '17 at 12:23

1

Pure Python library haven't good solition for work with xls. I would decide this problem on two ways:

Use COM interface.
Use macro and call excel as external process.

Also you can consider use xlsx, jython and Apache POI

edited May 23 '17 at 12:23

Community

1
1

answered Sep 11 '14 at 05:45

Michael Kazarian

4,376
1
21
25

Removing characteristic rows in a simple Excel file

3 Answers3