pyExcelerator or xlrd - How to FIND/SEARCH a row for the given few column data?


Python communicating with EXCEL... i need to find a way so that I can find/search a row for given column datas. Now, i m scanning entire rows one by one... It would be useful, If there is some functions like FIND/SEARCH/REPLACE .... I dont see these features in pyExcelerator or xlrd modules.. I dont want to use win32com modules! it makes my tool windows based!

FIND/SEARCH Excel rows through Python.... Any idea, anybody?

By : steffenj


@John Fouhy: [I'm the maintainer of xlwt, and author of xlrd]

The spreadsheet-reading part of pyExcelerator was so severely deprecated that it vanished completely out of xlwt. To read any XLS files created by Excel 2.0 up to 11.0 (Excel 2003) or compatible software, using Python 2.1+, use xlrd

That "simple optimi[sz]ation" isn't needed with xlrd:

import xlrd
book = xlrd.open_workbook("foo.xls")
sheet = book.sheet_by_number(0) # alternatively: sheet_by_name("Budget")
for row_index in xrange(sheet.nrows): 
    for col_index in xrange(sheet.ncols):

With pyExcelerator you can do a simple optimization by finding the maximum row and column indices first (and storing them), so that you iterate over (row, i) for i in range(maxcol+1) instead of iterating over all the dictionary keys. That may be the best you get, unless you want to go through and build up a dictionary mapping value to set of keys.

Incidentally, if you're using pyExcelerator to write spreadsheets, be aware that it has some bugs. I've encountered one involving writing integers between 2**30 and 2**32 (or thereabouts). The original author is apparently hard to contact these days, so xlwt is a fork that fixes the (known) bugs. For writing spreadsheets, it's a drop-in replacement for pyExcelerator; you could do import xlwt as pyExcelerator and change nothing else. It doesn't read spreadsheets, though.

You can't. Those tools don't offer search capabilities. You must iterate over the data in a loop and search yourself. Sorry.

By : nosklo

This video can help you solving your question :)
By: admin