使用openpyxl在Excel工作表的文本字符串中搜索单词 [英] Search a word in a text string in Excel sheet using openpyxl

问题描述

我正在尝试在一个单元格中搜索一个单词，该单元格的文本字符串如下所示(能源；绿色建筑；高性能建筑).这是我写的代码，出现语法错误

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.

此外，我想获取单元格位置，然后告诉openpyxl将颜色分配给E列下同一行中的单元格.这是我的Excel工作表的快照. 我知道如何使用此命令分配颜色

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')

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

我只需要帮助获取单元格位置(具有匹配文本的单元格)并将其行号分配给E列中的另一个单元格

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")      

推荐答案

有关搜索的方法，建议您查看 .上周第一次我自己使用了这个. Excel API像往常一样是愚蠢的，但是您可以对其进行抽象，然后将各种格式添加到文件中，而无需亲自搜索.

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.

这篇关于使用openpyxl在Excel工作表的文本字符串中搜索单词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！