Search a word in a text string in Excel sheet using openpyxl

Question

I'm trying to search for a word in a cell that has a text string that looks like this (Energy;Green Buildings;High Performance Buildings). Here is the code I wrote, I get a syntax error

for row in ws.iter_rows('D2:D11'):
    for cell in row:
        if 'Energy' in ws.cell.value :
            Print 'yes'

Obviously, I don't want to print yes, this was to test the search function.

Additionally, I want to get the cell location, and then tell openpyxl to assign a color to a cell in the same row under column E. here is a snap shot of my Excel sheet. I know how to assign a color using this command

c.fill = PatternFill(start_color='FFFFE0', end_color='FFFFE0' fill_type='solid')

I just need help getting the cell location (the cell that has a matching text) and assign its row number to another cell in column E

UPDATE: I wrote this code below that is working fine for me:

import xml.etree.ElementTree as ET



fhand = open ('My_Collection')    
tree =ET.parse('My_Collection.xml')
data= fhand.read()
root = tree.getroot()
tree = ET.fromstring(data)

title_list= ['Title']
year_list = ['Year']
author_list= ['Author']
label_list = ['Label']



for child in tree:
    for children in child:
        if children.find('.//title')is None :
            t='N'
        else:
            t=children.find('.//title').text
        title_list.append(t)
    print title_list
    print len(title_list)


for child in tree:
    for children in child:
        if children.find('.//year')is None :
            y='N'
        else:
            y=children.find('.//year').text
        year_list.append(y)
    print year_list
    print len(year_list)


for child in tree:
    for children in child:
        if children.find('.//author')is None :
            a='N'
        else:
            a=children.find('.//author').text
        author_list.append(a)
    print author_list
    print len(author_list)


for child in tree:
    for children in child:
        if children.find('label')is None :
            l='N'
        else:
            l=children.find('label').text
        label_list.append(l)
    print label_list
print len(author_list) 





Modified_label_list=list()        
import re
for labels in label_list:

    all_labels=labels.split(';')

    for a_l in all_labels:
        if a_l not in  Modified_label_list: 
            Modified_label_list.append(a_l)
        else:
            continue
print Modified_label_list
print len(Modified_label_list)
label_list_for_col_header= Modified_label_list[1:]
print label_list_for_col_header
print len(label_list_for_col_header)




from openpyxl import Workbook 
wb = Workbook() 
ws = wb.active 


for row in zip(title_list, year_list, author_list, label_list): 
        ws.append(row)




r = 5
for N in label_list_for_col_header:
    ws.cell(row=1, column=r).value = str(N)
    r += 1


from openpyxl.styles import PatternFill 


general_lst= list()



COLOR_INDEX = ['FF000000', 'FFFFFFFF', 'FFFF0000', 'FF00FF00', 'FF0000FF',
               'FFFFFF00', 'FFFF00FF', 'FF00FFFF', 'FF800000', 'FF008000', 'FF000080',
               'FF808000', 'FF800080', 'FF008080', 'FFC0C0C0', 'FF808080', 'FF9999FF',
               'FF993366', 'FFFFFFCC', 'FFCCFFFF', 'FF660066', 'FFFF8080', 'FF0066CC',
               'FFCCCCFF', 'FF000080', 'FFFF00FF', 'FFFFFF00', 'FF00FFFF', 'FF800080',
               'FF800000', 'FF008080', 'FF0000FF', 'FF00CCFF', 'FFCCFFFF', 'FFCCFFCC',
               'FFFFFF99', 'FF99CCFF', 'FFFF99CC', 'FFCC99FF', 'FFFFCC99', 'FF3366FF',
               'FF33CCCC', 'FF99CC00', 'FFFFCC00', 'FFFF9900', 'FFFF6600', 'FF666699',
               'FF969696', 'FF003366', 'FF339966', 'FF003300', 'FF333300', 'FF993300',
               'FF993366', 'FF333399', 'FF333333']

import random
color_lst= random.sample(COLOR_INDEX, len(label_list_for_col_header))
print color_lst

print int(label_list_for_col_header.index(label_list_for_col_header[0]))

h= len(title_list)
m= 0    
for lbls in label_list_for_col_header: 
    j= int(label_list_for_col_header.index(lbls))+5
    for row in ws.iter_rows('D2:D11'):
        for cell in  row:

            if lbls in cell.value : 
                general_lst.append(cell.row)
                for items in range(len(general_lst)):

                    ws.cell(row = general_lst[items], column = j).fill = PatternFill(start_color=str(color_lst[m]), end_color=str(color_lst[m]) , fill_type='solid')
    general_lst = []
    m +=1       


ws.column_dimensions['A'].width = 70    
ws.column_dimensions['C'].width = 23
ws.column_dimensions['B'].width = 5        
wb.save("Test61.xlsx")

The answer to [this other SO](http://stackoverflow.com/questions/10614518/iterating-over-a-range-of-rows-using-ws-iter-rows-in-the-optimised-reader-of-ope) might help you with knowing which row you are on. (Using `enumerate`) — jcfollower, Dec 09 '15 at 18:41
jcfollower, Thanks for the tip.I think this should work in my situation. I will comment back once I try it. Now I need to figure out a way to search for matching text. — Julia_arch, Dec 09 '15 at 18:52
Ouch! That code has got pretty verbose! ;-) When you've got time you should look at refactoring it a bit. I'd also highly recommend you get in the habit of using `print()` as a function for Python 3 compatibility. We all hate it but that's the way it is. Start training the muscle memory! — Charlie Clark, Dec 10 '15 at 10:01
I know, very lengthy and perhaps not very efficient code. It does the job for now, I needed to show my professor this file in about 4 hours from now. I'm going to change some font styles and hopefully, I'll have an hour or two for sleeping. Cheers! — Julia_arch, Dec 10 '15 at 10:11

DevLearner · Answer 1 · 2020-06-16T03:02:05.347

I was trying to find a solution for this question, the answers here didn't work for me (maybe the openpyxl module was updated and hence the code has become obsolete). So, I tried a little and came up with the following working code:

import openpyxl

#Path
wb = openpyxl.load_workbook(r'PathOfTheFile')

#active worksheet data
ws = wb.active    

def wordfinder(searchString):
    for i in range(1, ws.max_row + 1):
        for j in range(1, ws.max_column + 1):
            if searchString == ws.cell(i,j).value:
                print("found")
                print(ws.cell(i,j))          


wordfinder("YourString")

Hope this helps. P.S. The code doesnt give an output if there in no match for the word searched

score 3 · Accepted Answer · edited May 23 '17 at 11:48

3

For an approach to searching I suggest you look at the answer to this question. Create a dictionary of terms to search in a single pass and use it as often as you like. However, you might also like to know that openpyxl also supports conditional formatting so that you can delegate the formatting to Excel. See the very bottom of the examples in the documentation. Used this myself for the first time last week. The Excel API is stupid as usual but you could abstract it and add all kinds of formatting to the file without ever searching yourself.

edited May 23 '17 at 11:48

Community

1
1

answered Dec 09 '15 at 18:50

Charlie Clark

18,477
4
49
55

Charlie, thanks for your help again for the third time. I'll look at both links in a little bit. I'll comment back once I try your suggestions. -Rania – Julia_arch Dec 09 '15 at 18:56
Charlie, the links you gave me were a little advanced, so I came up with a simple (might be not efficient though) code that's working for me. I'll post above right now. – Julia_arch Dec 10 '15 at 09:50
I have two quick questions. 1) to search for my text I used (for row in ws.iter_rows('D2:D11')), However, I'm going to parse a larger xml file that will yield 135 rows. I have a list of rows ( rows_lst) how can I change D11 in my original code to the length of my list?. And how do I delete a column? – Julia_arch Dec 10 '15 at 12:16
`iter_rows()` will by default loop over all rows. For more control you might want to look at `get_squared_range()` which will allow you to pass in min_row, min_column, max_row, max_column. This kind of question is better on the ML in my opinion. Rows and columns cannot be deleted or inserted. There's a snippet on the repository which gives some tips but basically you should try and avoid wanting to do it. – Charlie Clark Dec 10 '15 at 16:39
Charile, I really appreciate your help. My professor was really impressed that I just learned python and wrote a script to produce such an Excel file. I had no previous experience in programming other that watching some python programming. A big part of the code success goes to the awesome openpyxl module. – Julia_arch Dec 12 '15 at 15:59

score 1 · Answer 3 · edited Dec 20 '22 at 05:03

1

I think this will help you...

from openpyxl import load_workbook

wb = load_workbook('C:\\Users\luxman\Desktop\T11.xlsx')

ws=wb.active

for row in ws.iter_rows():

    for cell in row:

        if cell.value == "Status":

            #print(ws.cell(row=cell.row, column=3).value)
            print('yes')

edited Dec 20 '22 at 05:03

U13-Forward

69,221
14
89
114

answered Dec 18 '22 at 12:52

lux man

11
1

Search a word in a text string in Excel sheet using openpyxl

3 Answers3

Linked

Related