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")
See Question&Answers more detail:
os