Python - openpyxl -使用openpyxl获取包含特定值的行数

ukxgm1gy 于 2022-12-02 发布在 Python

关注(0)|答案(3)|浏览(315)

我是Python新手，我正在用openpyxl为我哥哥做一个SEO项目，我试图得到一些包含特定值的行。
我有一个电子表格，看起来像这样：

我想写一个程序，它将获得关键字，并按状态将它们解析为字符串，如：密苏里州=“搜索项1，搜索项2，搜索项5，搜索项6”Illinois =“搜索项3，搜索项4”
到目前为止，我已经创建了一个类似这样的程序：

#first, import openpyxl
    import openpyxl

    #next, give location of file
    path = "testExcel.xlsx"

    #Open workbook by creating object
    wb_object = openpyxl.load_workbook(path)

    #Get workbook active sheet object
    sheet_object = wb_object.active

    #Getting the value of maximum rows
    #and column
    row = sheet_object.max_row
    column = sheet_object.max_column
    print("Total Rows:", row)
    print("Total Columns:", column)

    #printing the value of forth column, state
    #Loop will print all values
    #of first column
    print("\nValue of fourth column")
    for i in range(4, row + 1):
        cell_object = sheet_object.cell(row=i, column=4)
        split_item_test = cell_object.value.split(",")
        split_item_test_result = split_item_test[0]
        state = split_item_test_result
        print(state)
        if (state == 'Missouri'):
            print(state.count('Missouri'))
    print("All good")

问题是这样做后，我看到它打印1重复，但不是密苏里州州的总数。我想要一个州的总提及次数，然后最终得到一个字符串与每个搜索条件。
这在openpyxl中可能吗？或者我需要一个不同的库吗？

python

来源：https://stackoverflow.com/questions/74649003/python-openpyxl-use-openpyxl-to-get-number-of-rows-that-contain-a-specific-v

3条答案

按热度按时间

ozxc1zmp1#

RanemirusG是对的，有几种方法可以得到同样的结果。这里有另一个选择...我试图保存你的思维过程，祝你好运。

print("\nValue of fourth column")

missouri_list = [] # empty list
illinois_list = [] # empty list

for i in range(2, row+1): # It didn't look like "4, row+1" captured the full sheet, try (2, row+1)
    cell_object = sheet_object.cell(row=i, column=4)
    keyword = sheet_object.cell(row=i, column=1)
    keyword_fmt = keyword.value # Captures values in Keyword column
    split_item_test = cell_object.value.split(",")
    split_item_test_result = split_item_test[1] # 1 captures states
    state = split_item_test_result
    print(state)

    # simple if statement to capture results in a list
    if 'Missouri' in state:
        missouri_list.append(keyword_fmt)
    if 'Illinois' in state:
        illinois_list.append(keyword_fmt)
print(missouri_list)
print(len(missouri_list)) # Counts the number of occurances
print(illinois_list)
print(len(illinois_list)) # Counts the number of occurances
print("All good")

赞(0）回复(0）举报 2022-12-02

eeq64g8w2#

是的，用openpyxl是可以做到的。要实现你的真实的目标，试试这样的方法：

states_and_keywords  = {}
for i in range(4, row + 1):
    cell_object = sheet_object.cell(row=i, column=4)
    split_item_test = cell_object.value.split(",")
    split_item_test_result = split_item_test[1] #note that the element should be 1 for the state
    state = split_item_test_result.strip(" ") #trim whitespace (after comma)
    keyword = cell_object.offset(0,-3).value #this gets the value of the keyword for that row
    if state not in states_and_keywords:
        states_and_keywords[state] = [keyword]
    else:
        states_and_keywords[state].append(keyword) 
print(states_and_keywords)

赞(0）回复(0）举报 2022-12-02

wko9yo5t3#

确定其他选项
这将创建一个字典'state_dict'，其格式为每个问题
密苏里州=“搜索项1，搜索项2，搜索项5，搜索项6”
Illinois =“搜索项目3，搜索项目4”

...
print("\nValue of fourth column")
state_dict = {}
for row in sheet_object.iter_rows(min_row=2, max_row=sheet_object.max_row):
    k = row[3].value.split(',')[1].strip()
    v = row[0].value
    if k in state_dict:
        state_dict[k] += [v]
    else:
        state_dict[k] = [v]

### Print values
for key, value in state_dict.items():
    print(f'{key}, Total {len(value)}', end='; ')
    for v in value:
        print(f'{v}', end=', ')
    print('')

将创建字典'state_dict'作为so;

'Missouri' = {list: 4} ['search item 1', 'search item 2', 'search item 5', 'search item 6']
'Illinois' = {list: 2} ['search item 3', 'search item 4']
'Alabama' = {list: 1} ['search item 7']
'Colorado' = {list: 1} ['search item 8']

打印输出

Value of fourth column
Missouri = Total 4; search item 1, search item 2, search item 5, search item 6, 
Illinois = Total 2; search item 3, search item 4, 
Alabama = Total 1; search item 7, 
Colorado = Total 1; search item 8,

赞(0）回复(0）举报 2022-12-02

我来回答

Python - openpyxl -使用openpyxl获取包含特定值的行数

3条答案

相关问题

热门标签

最新问答