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

Question

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?

Answer 1

"Now, i m scanning entire rows one by one"

What's wrong with that? "search" -- in a spreadsheet context -- is really complicated. Search values? Search formulas? Search down rows then across columns? Search specific columns only? Search specific rows only?

A spreadsheet isn't simple text -- simple text processing design patterns don't apply.

Spreadsheet search is hard and you're doing it correctly. There's nothing better because it's hard.

Answer 2

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

Answer 3

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.

Answer 4

@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 to 2.6, 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):