def insert_image_to_matched_cell_value(worksheet: openpyxl.worksheet,
images_in_dir: dict, target_column: str,
lookup_column: str,
start_row: int) -> None or dict:
cells_with_images_not_found = {}
for row in range(start_row, worksheet.max_row + 1):
target_cell_address = target_column + str(row)
lookup_column_index = openpyxl.utils.column_index_from_string(
lookup_column)
image = images_in_dir.get(
worksheet.cell(row, lookup_column_index).value)
if image:
insert_resized_image_to_cell(worksheet,
image=image,
cell_address=target_cell_address)
else:
cells_with_images_not_found.update({
worksheet.cell(row, lookup_column_index).value:
target_cell_address
})
if cells_with_images_not_found:
return cells_with_images_not_found
def count_primary_school(path, suffix, ws: worksheet, index):
# 先打开此表
cur_work_book = load_workbook(path)
# 教基1001信息写入
cur_work_sheet = cur_work_book.get_sheet_by_name("教基1001_" + suffix)
ws.cell(index, 1).value = cur_work_sheet.cell(3, 3).value
ws.cell(index, 2).value = cur_work_sheet.cell(4, 3).value
ws.cell(index, 3).value = cur_work_sheet.cell(14, 3).value
ws.cell(index, 4).value = cur_work_sheet.cell(27, 3).value
ws.cell(index, 5).value = cur_work_sheet.cell(28, 3).value
# 教基1102信息写入
cur_work_sheet = cur_work_book.get_sheet_by_name("教基1102_" + suffix)
ws.cell(index, 6).value = cur_work_sheet.cell(8, 3).value
ws.cell(index, 7).value = cur_work_sheet.cell(9, 3).value
ws.cell(index, 8).value = cur_work_sheet.cell(10, 3).value
ws.cell(index, 9).value = cur_work_sheet.cell(11, 3).value
ws.cell(index, 10).value = cur_work_sheet.cell(12, 3).value
# 教基1102信息写入
cur_work_sheet = cur_work_book.get_sheet_by_name("教基1102_" + suffix)
ws.cell(index, 11).value = cur_work_sheet.cell(8, 3).value
ws.cell(index, 12).value = cur_work_sheet.cell(9, 3).value
ws.cell(index, 13).value = cur_work_sheet.cell(10, 3).value
ws.cell(index, 14).value = cur_work_sheet.cell(11, 3).value
ws.cell(index, 15).value = cur_work_sheet.cell(12, 3).value
def WriteToWorkSheet(Worksheet: worksheet, RowIndex: int, ColumnValues: list):
try:
for item in range(len(ColumnValues)):
Worksheet.cell(row=RowIndex,
column=item + 1,
value=ColumnValues[item])
except:
logging.getLogger('Dtvt_Logger').error(
"Unexpected Error in Module: Utility.py, Method;WriteToWorkSheet",
sys.exc_info()[0])
def insert_resized_image_to_cell(worksheet: openpyxl.worksheet,
image: pathlib.Path,
cell_address: str,
width=100,
height=100) -> None:
image = openpyxl.drawing.image.Image(image)
worksheet.add_image(image, anchor=cell_address)
image.width = width
image.height = height
def get_value(key: str, sheet: worksheet, row: int,
header_index: Dict[str, Optional[int]]) -> str:
"""
gets value from sheet row by the key
:param row: row of value
:param key: key of header
:param sheet: sheet with data
:param header_index: list of header indexes
:return: value
"""
if header_index[key] and sheet.cell(row=row, column=header_index[key]):
return sheet.cell(row=row, column=header_index[key]).value
return ""
def get_timetable(table: openpyxl.worksheet) -> dict:
"""
Функция, которая из таблицы Excel с расписанием выделяет расписание для каждой группы
:param table:
:return: Словарь, ключи в котором являются номерами групп, а
значение, соответствующее ключу – расписание для этой группы
"""
groups = {} # список расписаний для групп
for j in range(3, table.max_column): # смотрим на значения по столбцам
name = table.cell(5, j).value # номер группы
if name in ['Дни',
'Часы']: # если это не номер группы, то пропускаем столбец
continue
# иначе если столбец - это номер группы, то составляем для него расписание
elif name is not None:
# group - словарь с расписанием для группы
group = dict(Понедельник={},
Вторник={},
Среда={},
Четверг={},
Пятница={},
Суббота={},
Воскресенье={})
for k in range(6, table.max_row): # проходимся по столбцу
# если клетки относятся ко дню недели (не разделители)
if get_value_merged(table, table.cell(k, 1)) in group.keys():
day = get_value_merged(table, table.cell(
k, 1)) # значение дня недели
time = get_value_merged(table, table.cell(
k, 2)) # клетка, в которой лежит значение времени
pair = get_value_merged(table, table.cell(
k, j)) # клетка, в которой лежит значение пары
# рассматриваем только те клетки, для которых определено значение как пары, так и времени
if (time, pair) != (None, None):
time = time.split(
) # преобразуем время пары к формату hh:mm – hh:mm
if len(time[0][:-2]) == 1:
time[0] = '0' + time[0]
time = time[0][:-2] + ':' + time[0][
-2:] + ' – ' + time[2][:-2] + ':' + time[2][-2:]
group[day][
time] = pair # записываем значение в расписание
group = pd.DataFrame(group) # заменяем None на спящие смайлики
group.replace(to_replace=[None], value='😴', inplace=True)
groups[name] = group # заносим расписание для группы в словарь
return groups # словарь с группами
def get_cells_of_not_none_value_in_row(row_number, ws: worksheet):
cell_list = []
full_row = ws.iter_rows(min_row=row_number, max_col=ws.max_column, max_row=row_number)
for cells in full_row:
for n in range(0, len(cells)):
if cells[n].value:
cell_list.append(cells[n])
return cell_list
def map_team_names(name_sheet: worksheet) -> dict:
"""
Mapeia os nomes dos times e suas variações
"""
row = 0
names = {}
last_row = name_sheet.max_row
while True:
row += 1
next_row = row + 1
team = name_sheet.cell(row, 1).value
variation = name_sheet.cell(next_row, 1).value
if row > last_row:
# Para quando chega à última linha
break
if not team:
continue
team = format_name(team)
names[team] = {
'wins': 0,
'losses': 0,
'draws': 0,
'total_games': 0,
'points': 0,
'variations': []
}
while variation:
variation = format_name(variation)
if variation not in names[team]['variations']:
names[team]['variations'].append(variation)
next_row += 1
variation = name_sheet.cell(next_row, 1).value
row = next_row
return names
def write_list_2d(
sheet: worksheet,
l_2d: List[List[str]],
start_row: int,
start_col: int,
) -> None:
"""2次元配列を指定したセル位置から書き込む
https://github.com/nkmk/python-snippets/blob/507a7fcb5b0a212d88015ffad77914c50509d4bc/notebook/openpyxl_example.py#L99-L116
Args:
sheet (sheet): Xlsxのsheetオブジェクト
l_2d (list): 2次元配列
start_row (int): 行(y)開始位置
start_col (int): 列(x)開始位置
"""
for y, row in enumerate(l_2d):
for x, cell in enumerate(row):
sheet.cell(row=start_row + y,
column=start_col + x,
value=l_2d[y][x])
def get_value_merged(sheet: openpyxl.worksheet, cell: openpyxl.cell) -> any:
"""
Функция, возвращающая значение, лежащее в клетке, вне зависимости от того,
является ли клетка merged, или нет
:param sheet:
:param cell:
:return: значение, лежащее в клетке
"""
for merged in sheet.merged_cells: # смотрим в списке слитых клеток (структура данных openpyxl.worksheet)
if within_range(merged.bounds, cell):
return sheet.cell(merged.min_row, merged.min_col).value
return cell.value
def check_for_values_in_row(row_number, ws: worksheet):
"""
Checks for any occurrences of value(s) in given row in given worksheet as defined in
[GRADING_SHEET_COLUMN_MAPPING][IgnoreGradingOnEntryOfValue]
:param row_number: Number of row to check.
:param ws: Worksheet(data_only mode!) to check row from.
:return: True if value(s) are in row - otherwise False.
"""
values = Config.get_column_mapping_ignore_grading_on_entry()
for i in range(1, ws.max_column + 1):
if any(val in str(ws.cell(row=row_number, column=i).value) for val in values):
return True
return False
def format_and_map_games_data(games_sheet: worksheet,
teams_dict: dict) -> dict:
games = {}
last_row_games = games_sheet.max_row
for row in range(2, last_row_games):
game_id = games_sheet.cell(row, 1).value
home_team = games_sheet.cell(row, 7).value
home_score = games_sheet.cell(row, 9).value
away_team = games_sheet.cell(row, 12).value
away_score = games_sheet.cell(row, 11).value
game_date = games_sheet.cell(row, 2).value
championship_name = format_name(games_sheet.cell(
row, 15).value) if games_sheet.cell(row, 15).value else None
if not away_team or not home_team:
print(
f'JOGO NÃO MAPEADO: O jogo "{game_id}" não tem os nomes das duas equipes.'
)
continue
home_team = format_name(home_team)
away_team = format_name(away_team)
if home_team not in teams_dict.keys():
this_name = away_team
home_team = find_official_name(home_team, teams_dict)
if not home_team:
print(
f'JOGO NÃO MAPEADO: O time "{this_name}" não está na lista de nomes.'
)
continue
if away_team not in teams_dict.keys():
this_name = away_team
away_team = find_official_name(away_team, teams_dict)
if not away_team:
print(
f'JOGO NÃO MAPEADO: O time "{this_name}" não está na lista de nomes.'
)
continue
if str(home_score) == 'None' or str(away_score) == 'None':
print(
f'JOGO NÃO MAPEADO: O jogo "{game_id}" não tem pontuação das duas equipes.'
)
continue
if not game_date or not isinstance(game_date, date):
# Pula caso o jogo não tenha data ou data não esteja bem formatada
print(
f'JOGO NÃO MAPEADO: O jogo "{game_id}" não tem data ou a data está mal formatada.'
)
continue
if home_score > away_score:
winner = 'home'
elif home_score < away_score:
winner = 'away'
else:
winner = 'draw'
games[game_id] = {
'game_date':
game_date,
'game_time':
games_sheet.cell(row, 3).value,
'game_class':
games_sheet.cell(row, 4).value,
'genre':
games_sheet.cell(row, 5).value,
'home_team':
home_team,
'home_team_state':
games_sheet.cell(row, 8).value,
'home_score':
home_score,
'away_score':
away_score,
'away_team':
away_team,
'away_team_state':
games_sheet.cell(row, 13).value,
'game_location':
games_sheet.cell(row, 14).value,
'championship':
championship_name,
'game_city':
games_sheet.cell(row, 16).value,
'game_state':
games_sheet.cell(row, 17).value,
# Campos calculados
'row':
row,
'winner':
winner,
'double_points':
True if championship_name
and 'CAMPEONATO BRASILEIRO - SÉRIE A' in championship_name else
False,
'15+':
True if (home_score - away_score) >= 15 or
(home_score - away_score) <= -15 else False,
}
return games
def write(row: int, col: int, val: str, sheet: openpyxl.worksheet):
sheet.cell(row=row, column=col).value = val