python在列中搜索字符串,并从同一行返回另一个列值 [英] python Search a string in a column, and return another column value from that same row
问题描述
我当前正在尝试编写一个脚本,该脚本将对电子表格进行评估以在A列中搜索字符串,然后一旦找到该字符串,就在同一行中打印I列的值.到目前为止,我有以下代码,但并没有使我走的太远.
I am currently trying to write a script that will evaluate a spreadsheet to search column A for a string, and then once it finds that string, print the value of column I in the same row. So far I have the following code but it isn't getting me very far.
任何帮助将不胜感激.
any help will be appreciated.
import openpyxl
wb = openpyxl.load_workbook("2016_SF.xlsx", data_only=True)
ws = wb["161226-161228"]
last_r=70
search_str = raw_input("What plant are you looking for? > ")
last_r = cell.row
def FindXlCell(search_str,last_r):
for row in ws.iter_rows(row_offset=last_r):
for cell in row:
if (search_str == cell.value):
print(search_str, last_r, cell.row,)
推荐答案
问题:在A列中搜索字符串,然后找到该字符串,则在同一行中打印I列的值
Question: search column A for a string, and then once it finds that string, print the value of column I in the same row
注意:由于我的"text.xlsx"使用列"C"和"keyX",而没有列"I",因此我使用"C"和"D"代替按要求提供"A"和"I".
Note: As my 'text.xlsx' uses Column 'C' with "keyX" and doesn't have a Column 'I', I use 'C' and 'D' instead of 'A' and 'I' as requested.
test.xlsx中的工作表:
Worksheet in test.xlsx:
from openpyxl import load_workbook
def FindXlCell(search_str, range=None):
"""
Iterate over a given 'range' match 'cell.value' with 'search_str'
:param search_str: String to find
:param range: Range to iterate, defaults to whole sheet
:return: 'None' if no match else
all cells from the matching Row as 'list of cell objects'
"""
global ws
if not range:
range = ws.iter_rows() # Defaults to whole sheet
for tupleOfCells in range:
for cell in tupleOfCells:
if (cell.value == search_str):
return [_tuple[0] for _tuple in ws.iter_cols(min_row=cell.row, max_row=cell.row)]
wb = openpyxl.load_workbook("test.xlsx")
ws = wb.worksheets[0]
# search_str = raw_input("What plant are you looking for? > ")
search_str = "key2"
# Search only Column 'C' == 3, openpyxl is 1-based
cellsOfFoundRow = FindXlCell(search_str, ws.iter_rows(min_col=3, max_col=3))
if cellsOfFoundRow:
# List cellsOfFoundRow is 0-based, Index of D == 3
print("Found:{}, the value of Column D is {}"
.format(" ".join([cell.value for cell in cellsOfFoundRow]),
cellsOfFoundRow[3].value))
else:
print("Could not find '{}' in the given cell range!".format(search_str))
数量:
找到:A3 B3 key2 200,D列的值为200
Found:A3 B3 key2 200, the value of Column D is 200
与其他范围示例一起使用:
搜索整张纸
cellsOfFoundRow = FindXlCell(search_str)
从第2行开始,并仅在第3列上匹配仅 =='C':
Starting with Row 2 and match only on Column 3 == 'C':
cellsOfFoundRow = FindXlCell(search_str, ws.iter_rows(min_row=2, min_col=3, max_col=3))
限制从第2行到第3行的范围.
Limit the Range from Row 2 to Row 3:
cellsOfFoundRow = FindXlCell(search_str, ws.iter_rows(min_row=2, max_row=3))
也可以使用'ws.iter_cols(...)或其他任何会返回 squared_range 的函数:
It's also posible to use 'ws.iter_cols(...' or any other function that will return a squared_range:
cellsOfFoundRow = FindXlCell(search_str, ws.iter_cols(min_row=2, min_col=3, max_col=3))
使用Python:3.4.2测试-openpyxl:2.4.1-LibreOffice:4.3.3.2
Tested with Python:3.4.2 - openpyxl:2.4.1 - LibreOffice:4.3.3.2
这篇关于python在列中搜索字符串,并从同一行返回另一个列值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!