Searching for a value in an excel document to return its row

Question

I am using python to create a simple utility for an organisation at university. The python script will search an excel document for a name and then using the row value for that name will return information about the member they searched for.

I am using openpyxl to do this. I have read through the documentation and understand reading cells and worksheets etc. including from a range of cells, rows or columns.

I am unsure how I would go about using this to search for a name in the first column.

I am thinking of using:

name = raw_input('insert name > ')

and then iterating through the cells in the first column to search for name and then using string manipulated to get the row value for that cell.

However looking through the docs I can't find actually find any way of searching for cell content.

Can anybodyshed some light on this for me?

http://stackoverflow.com/questions/25205863/openpyxl-how-can-i-search-for-content-in-a-cell-in-excel-and-if-the-content-m http://stackoverflow.com/questions/10939450/openpyxl-basic-search Try these — Ajay, May 16 '15 at 22:39

score 2 · Answer 1 · answered Aug 14 '18 at 23:03

You can upload a xslx or csv into python, then do your searches and operations, and then save the data back as a csv or xslx to an existing or new file. Here is an example with word frequency analysis.

#Note DO not Read/Write to the same files over and over make new ones`


from collections import Counter
import xlrd
import xlwt
book = xlrd.open_workbook('/Users/galon/Desktop/cleanmqlresulty5.xlsx')
sheet = book.sheet_by_name('cleanmqlresulty5')
csv = sheet.col_values(0)
#print((len(csv)))
csvlist = []
for i in range(len(csv)):
    csvlist = csvlist + (csv[i].split())
#print(csvlist)
counts = Counter(csvlist)

key = []
value = []
for k,v in counts.items():
         key.append(k)
         value.append(v)


import csv
csvfile = "/Users/galon/Desktop/001.csv"

#Assuming output is a flat list
with open(csvfile, "w") as output:
    writer = csv.writer(output, lineterminator='\n')
    for val in key:
        writer.writerow([val])

import csv
csvfile = "/Users/galon/Desktop/002.csv"

#Assuming output is a flat list
with open(csvfile, "w") as output:
    writer = csv.writer(output, lineterminator='\n')
    for val in value:
        writer.writerow([val])

score 0 · Answer 2 · answered May 17 '15 at 10:17

0

There is nothing in the docs about this because there is no such function in the library. Data is stored per cell so looping over all cells (by row) is the only way to approach the problem.

answered May 17 '15 at 10:17

Charlie Clark

18,477
4
49
55

Searching for a value in an excel document to return its row

2 Answers2