def _search(self, sheet: Sheet, column: str, target: str, only_one=False):
"""
Generic search method
:param sheet: A Sheet XLRD Object where perform the search
:param column: Name of the column where perform the search
:param target: Value to find
:param only_one: (boolean) search one (true) or multiple values (false)
:return dict: a dictionary with a complete row of data
"""
target_rows = []
column_names = pyrvtools.pyrvtools.PyRvtools.get_columns_names(sheet)
for row_number in range(sheet.nrows):
if sheet.cell_value(row_number, column_names[column]) == target:
target_rows.append(row_number)
if target_rows and only_one:
break
all_answer = []
for target_row in target_rows:
one_answer = {}
for col_name, col_number in column_names.items():
one_answer[col_name] = sheet.cell_value(target_row, col_number)
all_answer.append(one_answer)
return all_answer
def _find_column_index(sheet: Sheet, column_name: str) -> int:
distribution_index = -1
for i in range(sheet.ncols):
if sheet.cell_value(0, i) == column_name:
distribution_index = i
break
return distribution_index
def get_columns_names(sheet: Sheet):
"""
Return a dictionary with COLUMN_NAME:ID_COLUMN
:param sheet: a Sheet object
"""
mapping = {}
for col_index in range(sheet.ncols):
mapping[sheet.cell_value(0, col_index)] = col_index
return mapping
def get_column_values(sheet: Sheet, column_name: str) -> List:
column_index = _find_column_index(sheet, column_name)
if column_index == -1:
raise Exception(f"Sheet does not contain column {column_name}")
values = []
for i in range(1, sheet.nrows):
values.append(sheet.cell_value(i, column_index))
return values
def __parse_sheet(self, sheet_id: int, sheet: Sheet) -> Table:
n_rows = sheet.nrows
n_cols = sheet.ncols
res = []
for row_id in range(n_rows):
row = []
for col_id in range(n_cols):
value = sheet.cell_value(rowx=row_id, colx=col_id)
row.append(value)
res.append(row)
metadata = TableMetadata(page_id=sheet_id)
return Table(cells=res, metadata=metadata)
def Get_Excel_Row_Values(filepath,sheetName,uniqueValue):
Book = xlrd.open_workbook(filepath)
Sheet = Book.sheet_by_name(sheetName)
row_count = Sheet.nrows
col_count = Sheet.ncols
for i in range(0,row_count):
for j in range(0,col_count):
value = Sheet.cell_value(i, j)
if value == uniqueValue:
row_values = Sheet.row_values(i, 0)
return row_values
def get_merged_cells_value(sheet: Sheet, row_index, col_index):
"""
先判断给定的单元格，是否属于合并单元格；
如果是合并单元格，就返回合并单元格的内容
:return:
"""
merged = get_merged_cells(sheet)
for (rlow, rhigh, clow, chigh) in merged:
if rlow <= row_index < rhigh:
if clow <= col_index < chigh:
cell_value = sheet.cell_value(rlow, clow)
# print('该单元格[%d,%d]属于合并单元格，值为[%s]' % (row_index, col_index, cell_value))
return cell_value
break
return None
def extract_header(ws: Sheet):
return (ws.cell_value(1, 1)), (ws.cell_value(1, 6))
