def parse_xlsx_product_option(ws: Worksheet,
product_options_sheet: ProductOptionsSheet,
options_rows: List[int]) -> List[ProductOption]:
res = list()
for index, row_index in enumerate(options_rows):
name = ws.cell(row=row_index,
column=product_options_sheet.name_column_index).value
if isinstance(name, int):
name = str(name)
else:
name.strip()
price = float(
ws.cell(row=row_index,
column=product_options_sheet.price_column_index).value)
res.append(
ProductOption(
name=name,
price=price,
index=index,
is_default=index == 0,
xlsx_row=row_index,
))
return res
def df_to_excel(wb: Workbook, df: pd.DataFrame, sheet_: Worksheet, row_ini: 1,
as_table: False, **kwargs):
"""Agregar pandas DataFrame a hoja de Excel.
Parametros:
df: pandas DataFrame
sheet_: Worksheet object openpyxl
row_ini: fila inicial, por default 1
as_table: boolean, crear Tabla de Excel"""
# Agregar dataframe de Python a Excel
rows = dataframe_to_rows(df, index=False, header=True)
# agregar filas a Excel
for r_idx, row in enumerate(rows, row_ini):
for c_idx, value in enumerate(row, 1):
sheet_.cell(row=r_idx, column=c_idx, value=value)
if as_table:
try:
table_name = kwargs['table_name']
create_excel_table_from_df(df, sheet_, row_ini, table_name)
except KeyError:
raise ValueError(
'A table name must be specified if as_table is True.')
try:
for sheet in ['Sheet', 'Hoja', 'Hoja1']:
wb.remove(wb[sheet])
except KeyError:
pass
def __parseMonthly(worksheet: Worksheet) -> list:
res = []
# Timestamp of the data.
data_timestamp = datetime.now().isoformat()
minRowNum = FIRST_DATA_LINE_NUMBER
maxRowNum = len(worksheet['B'])
for rownum in range(minRowNum, maxRowNum + 1):
row = {}
if worksheet.cell(column=2, row=rownum).value is not None:
DataFileParser.__fillRow(row, PropertyName.TIME_PERIOD.value,
worksheet.cell(column=2, row=rownum),
False)
DataFileParser.__fillRow(row, PropertyName.VOLUME.value,
worksheet.cell(column=3, row=rownum),
True)
DataFileParser.__fillRow(row, PropertyName.ENERGY.value,
worksheet.cell(column=4, row=rownum),
True)
row[PropertyName.TIMESTAMP.value] = data_timestamp
res.append(row)
DataFileParser.logger.debug(
f"Monthly data read successfully between row #{minRowNum} and row #{maxRowNum}"
)
return res
def _fill_neat_settings(self, sheet: Worksheet, col_to_paste: int, row_to_paste: int, neat_settings: list) -> tuple:
next_row = row_to_paste
section: dict
end_column_index = col_to_paste
for section in neat_settings:
should_render = section.get('show')
if not should_render:
continue
translate: str = ReportPredictionResultsExcelMaker.neat_settings_translations[section.get('header')]
sheet.cell(row=next_row, column=col_to_paste, value=translate)
next_row += 1
section_params = section.get('params')
param: dict
for param in section_params:
should_render = param.get('showInGui')
if not should_render:
continue
translate: str = ReportPredictionResultsExcelMaker.neat_settings_translations[param.get('name')]
sheet.cell(row=next_row, column=col_to_paste, value=translate)
value = param.get('value')
if type(value) is list:
for i in range(len(value)):
end_column_index = max(end_column_index, col_to_paste + i + 1)
sheet.cell(row=next_row, column=col_to_paste + i + 1,
value=ReportPredictionResultsExcelMaker.neat_settings_translations[value[i]])
elif type(value) is bool:
sheet.cell(row=next_row, column=col_to_paste + 1, value='Да' if value is True else 'Нет')
else:
sheet.cell(row=next_row, column=col_to_paste + 1, value=value)
next_row += 1
return next_row, end_column_index
def read_von_neumann_excel(sheet: Worksheet, start_row: int, start_column: int,
cells_states: List[Tuple[str, str]]) -> List[int]:
zero_color = sheet.cell(start_row + 1, start_column).fill.start_color.value
zero_color = "#" + zero_color[2:]
zero_type = 0
for i in range(len(cells_states)):
if cells_states[i][1] == zero_color: zero_type = i
one_color = sheet.cell(start_row, start_column + 1).fill.start_color.value
one_color = "#" + one_color[2:]
one_type = 0
for i in range(len(cells_states)):
if cells_states[i][1] == one_color: one_type = i
two_color = sheet.cell(start_row + 1, start_column + 1).fill.start_color.value
two_color = "#" + two_color[2:]
two_type = 0
for i in range(len(cells_states)):
if cells_states[i][1] == two_color: two_type = i
three_color = sheet.cell(start_row + 2, start_column + 1).fill.start_color.value
three_color = "#" + three_color[2:]
three_type = 0
for i in range(len(cells_states)):
if cells_states[i][1] == three_color: three_type = i
four_color = sheet.cell(start_row + 1, start_column + 2).fill.start_color.value
four_color = "#" + four_color[2:]
four_type = 0
for i in range(len(cells_states)):
if cells_states[i][1] == four_color: four_type = i
rule = [zero_type, one_type, two_type, three_type, four_type]
return rule
def saveActiveProjectTaskMapping(worksheet: Worksheet,
startRow: int = 1,
startColumn: int = 1):
"""Saves the active project list and active task list data.
The projects are stored in one row, the tasks corresponding to the project are saved in the same column as the project
Arguments:
worksheet {Worksheet} -- [description]
startRow {int} -- starting row form where the data should be stored
startColumn {int} -- starting column form where the data should be stored
"""
projectList = excelDatabase.getActiveProjects()
if projectList is None:
return
for project in projectList:
taskList = excelDatabase.getActiveTasksByProjectId(project.projectId)
if taskList is None:
taskList = []
task = Task()
task.taskName = "-"
taskList.append(task)
project.taskList = taskList
defaultProject = getDefaultProjects()
projectList.extend(defaultProject)
column = startColumn
for project in projectList:
row = startRow
worksheet.cell(row=row, column=column).value = project.projectName
for task in project.taskList:
row += 1
worksheet.cell(row=row, column=column).value = task.taskName
column += 1
def _get_last_cell(ws: Worksheet,
row=-1,
col=1) -> Tuple[int, datetime.date]:
row_count = 1
while ws.cell(row_count + 1, col).value:
row_count += 1
row = max(row_count + row + 1, 1)
return row, ws.cell(row, col)
def parse_metadata_command(sh: Worksheet, area: AreaTupleType, name: str = None) -> IssuesLabelContentTripleType:
"""
Most "parse" methods are mostly syntactic (as opposed to semantic). They do not check existence of names.
But in this case, the valid field names are fixed beforehand, so they are checked at this time.
Some of the fields will be controlled also, according to some
:param sh: Input worksheet
:param area: Tuple (top, bottom, left, right) representing the rectangular area of the input worksheet where the
command is present
:return: list of issues (issue_type, message), command label, command content
"""
some_error = False
issues = []
controlled = create_dictionary()
mandatory = create_dictionary()
keys = create_dictionary()
for t in metadata_fields:
controlled[t[4]] = t[3]
mandatory[t[4]] = t[2]
keys[t[0]] = t[4]
# Scan the sheet, the first column must be one of the keys of "k_list", following
# columns can contain repeating values
# Map key to a list of values
content = {} # Dictionary of lists, one per metadata key
for r in range(area[0], area[1]):
label = sh.cell(row=r, column=area[2]).value
if label in keys:
key = keys[label]
for c in range(area[2]+1, area[3]):
value = sh.cell(row=r, column=c).value
if value:
value = str(value).strip()
if controlled[key]:
# Control "value" if the field is controllable
cl = {"dimensions": ["water", "energy", "food", "land", "climate"],
"subject_topic_keywords": None,
"geographical_level": ["local", "regional", "region", "country", "europe", "global", "sectoral", "sector"],
"geographical_situation": None, # TODO Read the list of all geographical regions (A long list!!)
"restriction_level": ["internal", "confidential", "public"],
"language": None, # TODO Read the list of ALL languages (or just "English"??)
}
if cl[key] and value.lower() not in cl[key]:
issues.append((3, "The key '"+key+"' should be one of: "+",".join(cl[key])))
if key not in content:
content[key] = []
content[key].append(value)
else:
issues.append((2, "Row "+str(r)+": unknown metadata label '"+label+"'"))
for key in keys.values():
if mandatory[key] and key not in content:
some_error = True
issues.append((3, "The value '"+key+"' is mandatory in the definition of the metadata"))
return issues, None, content
def _fill_sheet(data, sheet: Worksheet, fields):
if sheet.max_row == 1:
for col, field in enumerate(fields, 1):
sheet.cell(1, col, field["name"])
row = sheet.max_row + 1
for col, field in enumerate(fields, 1):
value = data.get(field["name"])
if isinstance(value, (list, dict)):
value = json.dumps(value)
sheet.cell(row, col, value)
def find_or_create_row(self, rls_date: datetime.date, ws: Worksheet):
row = 1
while True:
row += 1
date = ws.cell(row, 1).value
if date == rls_date:
return row
if not date or date > rls_date:
ws.insert_rows(row)
ws.cell(row, 1, rls_date)
return row
def add_headers_and_title(sheet: Worksheet):
headers = ('序号', '考试日期', '考试时间', '考试名称', '开课院系', '考试地点', '班级', '考生人数',
'考生院系')
for index, header in enumerate(headers):
sheet.cell(2, index + 1, header)
title = '东北大学考试日程表'
sheet.merge_cells(start_row=1, end_row=1, start_column=1, end_column=9)
cell = sheet['A1']
cell.value = title
cell.alignment = Alignment(horizontal="center", vertical="center")
def collect_table(worksheet: Worksheet, year: int, fund_name, row: int,
column: int) -> object:
"""
Start to run on the fields and look for the anchor cell which from there
the data lay out.
"""
data = {}
for iterated_row in range(row, row + 21):
row_label: str = worksheet.cell(row=iterated_row, column=column).value
if not row_label:
# Some of the years has less than 21 fields which mean we'll fail
# with older files. Skipping on an empty row label.
continue
row_data = {}
for iterated_column in range(column + 1, column + 1 + 24):
# Getting the matching month of the current cell and resetting
# the cell value.
month_index = int(iterated_column / 2)
if month_index not in months:
continue
month_name = months[month_index]
if month_name not in row_data:
row_data[month_name] = {
'התרומה לתשואה': '',
'שיעור מסך הנכסים': '',
'year': year,
'fund': fund_name,
}
cell_kwargs = {'row': iterated_row, 'column': iterated_column}
cell_value = worksheet.cell(**cell_kwargs).value
if cell_value:
if type(cell_value) is not str:
cell_value = format(cell_value * 100, '.2f')
# Values in cell with even index number have a label and odd
# indexed-cell have a different label.
if iterated_column % 2 == 0:
key = 'התרומה לתשואה'
else:
key = 'שיעור מסך הנכסים'
row_data[month_name][key] = cell_value
data[row_label.strip()] = row_data
return data
def _find_free_cell(self, col_index: int, row_index: int,
sheet: Worksheet) -> Tuple[Cell, int]:
target_cell = sheet.cell(row_index + 1, col_index + 1)
while True:
if isinstance(target_cell, MergedCell):
col_index += 1
target_cell = sheet.cell(row_index + 1, col_index + 1)
else:
break
return target_cell, col_index
def add_sale_data(sheet: Worksheet, sale_record: InvestmentRecord,
recs_and_quants_to_sell: list):
"""Returns the number of rows added for subquantities.
"""
net_capital_gain_formula = "=(" + str(sale_record.quantity) + "*" \
+ cell.get_column_letter(COLUMNS["Average price"]) \
+ str(sale_record.row_idx) + ")"
num_recs_and_quants = len(recs_and_quants_to_sell)
using_subquantities = True if num_recs_and_quants > 1 else False
current_row_idx = sale_record.row_idx + 1 if using_subquantities else sale_record.row_idx
quantity_column_to_use = COLUMNS[
"Subquantity"] if using_subquantities else COLUMNS["Quantity"]
for rec, quant in recs_and_quants_to_sell:
if using_subquantities:
sheet.cell(current_row_idx, quantity_column_to_use).value = quant
# Else we assume the Quantity of the investment record was written earlier
cost_base_formula = "=" + cell.get_column_letter(quantity_column_to_use) + str(current_row_idx) \
+ "*" + cell.get_column_letter(COLUMNS["Average price"]) + str(rec.row_idx) \
+ "+(" + cell.get_column_letter(COLUMNS["Brokerage"]) + str(rec.row_idx) \
+ "*" + cell.get_column_letter(quantity_column_to_use) + str(current_row_idx) \
+ "/" + cell.get_column_letter(COLUMNS["Quantity"]) + str(rec.row_idx) + ")" \
+ "+(" + cell.get_column_letter(COLUMNS["Brokerage"]) + str(sale_record.row_idx) \
+ "*" + cell.get_column_letter(quantity_column_to_use) + str(current_row_idx) \
+ "/" + cell.get_column_letter(COLUMNS["Quantity"]) + str(sale_record.row_idx) + ")"
sheet.cell(current_row_idx,
COLUMNS["Cost base"]).value = cost_base_formula
capital_gain_formula = "=(" + cell.get_column_letter(quantity_column_to_use) + str(current_row_idx) \
+ "*" + cell.get_column_letter(COLUMNS["Average price"]) + str(sale_record.row_idx) \
+ ")-" + cell.get_column_letter(COLUMNS["Cost base"]) + str(current_row_idx)
if sale_record.trade_date > rec.trade_date + timedelta(days=365):
capital_gain_column_to_use = COLUMNS["Capital gain > 1 year"]
else:
capital_gain_column_to_use = COLUMNS["Capital gain <= 1 year"]
sheet.cell(current_row_idx,
capital_gain_column_to_use).value = capital_gain_formula
history = sheet.cell(rec.row_idx, COLUMNS["History"]).value
new_history = "Sold " + str(
quant) + " on " + sale_record.trade_date.strftime("%d/%m/%Y. ")
sheet.cell(rec.row_idx,
COLUMNS["History"]).value = (history + new_history
if history else new_history)
current_row_idx += 1
return num_recs_and_quants if using_subquantities else 0
def getIndexBysheet(sheet: Worksheet):
index: dict = {}
print("sheet的名称是：", sheet.title, "，共有列数是：", sheet.max_column, "，第一行最右边的单元格值是：",
sheet.cell(row=1, column=sheet.max_column).value)
for i in range(1, sheet.max_column + 1):
cell: Cell = sheet.cell(row=1, column=i)
cellValue: str = cell.value
if cellValue is not None and "姓名".__contains__(cellValue):
index.__setitem__("nameIndex", i)
elif cellValue is not None and "项目".__contains__(cellValue):
index.__setitem__("projectIndex", i)
elif cellValue is not None and "实际工时数 (小时)".__contains__(cellValue):
index.__setitem__("hourIndex", i)
return index
def __writeAnchorListData(sheet: Worksheet,
ref: Dict[str, dict],
sub_def: AnchorSubType,
row: int,
col: int,
*,
data_func: Callable[[Dict[str, dict], str],
Union[float, int]],
data_format: str,
number_format: Union[None, str] = None,
number_style: str = 'Percent',
abs_format: Union[None, str] = None,
abs_style: str = 'Comma') -> None:
from .TestSuite import parseSheetLayer
#final_r = row+len(data.keys())
#final_c = col+len(data.keys())
c = col
for (test, anchors) in sub_def.items():
for anchor in anchors:
value_format = data_format
value_style = number_style
anchor_res = []
if not anchor:
if abs_format:
value_format = abs_format
if abs_style:
value_style = abs_style
else:
continue
else:
anchor_res = [
__SR_FORMAT.format(sheet=parseSheetLayer(anchor)[0],
cell=cl)
for cl in data_func(ref, anchor)
]
test_res = [
__SR_FORMAT.format(sheet=parseSheetLayer(test)[0], cell=cl)
for cl in data_func(ref, test)
]
sheet.cell(row=row,
column=c).value = value_format.format(*(anchor_res +
test_res))
sheet.cell(row=row, column=c).style = value_style
if number_format:
sheet.cell(row=row, column=c).number_format = number_format
sheet.cell(
row=row,
column=c).alignment = xl.styles.Alignment(horizontal='center')
c += 1
def load_manual_remote_max(ws: Worksheet, monitor_dict: dict,
monitor_column_dict: dict):
"""
手動で入力された在宅勤務数の上限を読み込み、MonitorScheduleに設定する。
:param ws: 読み込むシート
:param monitor_dict: 監視者の辞書(key:=name, item:=Monitor)
:param monitor_column_dict: 監視者のlatestシートにおける列インデックスの辞書(key:=name, item:=column index)
:return: None
"""
for monitor in monitor_dict.values():
if remote_max := ws.cell(
row=REMOTE_MAX_ROW_IDX,
column=monitor_column_dict[monitor.name]).value:
monitor.role_max[ERole.R] = remote_max
def _write_cell_if_value_provided(self, worksheet: Worksheet, column: int,
row: int, value: str):
if value:
# Scale the size of the column with the input value if necessary. By default width is None.
if worksheet.column_dimensions[get_column_letter(
column)].width is not None:
worksheet.column_dimensions[get_column_letter(
column)].width = max(
worksheet.column_dimensions[get_column_letter(
column)].width, len(value))
else:
worksheet.column_dimensions[get_column_letter(
column)].width = DEFAULT_COL_WIDTH
worksheet.cell(column=column, row=row, value=value)
def __writeAnchorListHeader(sheet: Worksheet,
sub_def: AnchorSubType,
row: int,
col: int,
allow_none: bool = True) -> None:
#Write horizontal headers/test names
tmp_col = col
for (test, anchors) in sub_def.items():
for anchor in anchors:
if not allow_none and not anchor:
continue
sheet.cell(row=row, column=tmp_col).value = test
sheet.cell(row=row + 1, column=tmp_col).value = anchor
tmp_col += 1
return tmp_col - 1
def load_initial_schedules(ws: Worksheet, monitor_dict: dict):
"""
指定シートからあらかじめ代入されている予定を読み取り、各監視者のスケジュールを初期化する。
:param ws: ワークシート
:param monitor_dict: 監視者の辞書(key:=name, item:=Monitor)
:return: 監視者のlatestシートにおける列インデックスの辞書(key:=name, item:=column index),
日付の辞書(key:=行番号, item:=datetime)
"""
monitor_column_dict = create_monitor_col_dict(ws, monitor_dict)
num_of_monitors = len(monitor_dict)
weekday_dict = {}
holiday_col = find_col_idx_by_val(ws, HEADER_ROW_IDX, 'Holiday')
for row_idx, row in enumerate(
ws.iter_rows(min_row=DATA_START_ROW_IDX,
max_col=num_of_monitors + 2), DATA_START_ROW_IDX):
day = row[0].value
if not day:
break
if not is_weekday(day, ws.cell(row=row_idx, column=holiday_col)):
continue
weekday_dict[row_idx] = day
for idx, monitor in enumerate(monitor_dict.values(), 1):
if val := row[idx].value:
role = convert_val_to_role(val)
monitor.schedule[day] = role
def new_line(ws: Worksheet, space: int = 1, init_row: int = 1, col: int = 1) -> int:
row = init_row
while True:
if ws.cell(row=row, column=col).value is None:
break
row += space
return row
def excel_load_sheet(sheet: Worksheet) -> List[Dict]:
print("Processing sheet '{}'".format(sheet.title))
if sheet.max_row <= EXCEL_DATA_START_ROW:
print("WARNING: sheet '{}' contains too little data".format(
sheet.title))
data_names = excel_get_data_names(sheet)
r = []
for row_i in range(EXCEL_DATA_START_ROW, sheet.max_row + 1):
row_data = {}
for col_i in range(1, len(data_names) + 1):
data_name_i = col_i - 1
cell_value = sheet.cell(row=row_i, column=col_i).value
if isinstance(cell_value, str):
cell_value = many_spaces_re.sub(' ', cell_value.strip())
row_data[data_names[data_name_i]] = cell_value
r.append(row_data)
print("{} rows successfully loaded from sheet {}".format(
len(r), sheet.title))
return r
def set_cell_value(sheet: Worksheet,
row_index: int,
column_index: int,
val: Any,
config: Optional[CellConfig] = None):
if config is None:
config = CellConfig()
c = sheet.cell(row_index + 1, column_index + 1)
if isinstance(val, Decimal):
c.number_format = config.decimal_format
c.alignment = config.number_alignment
elif isinstance(val, int):
c.number_format = config.int_format
c.alignment = config.number_alignment
elif isinstance(val, float):
c.number_format = config.float_format
c.alignment = config.number_alignment
elif isinstance(val, (datetime, date)):
c.style = config.date_style
if isinstance(val, datetime):
val = val.replace(tzinfo=None)
else:
val = strip_tags(str(val if val else ""))
c.value = val
return c
def __writeSummaryMatrixHeader(sheet: Worksheet, tests: List[str], row: int,
col: int) -> Tuple[int, int]:
d_row = row + 1
d_col = col + 1
#Write horizontal headers/test names
tmp_col = col + 1
for test in tests:
sheet.cell(row=row, column=tmp_col).value = test
tmp_col += 1
#Write vertical
tmp_row = row + 1
for test in tests:
sheet.cell(row=tmp_row, column=col).value = test
tmp_row += 1
return d_row, d_col
def find_row(column_name: str, value: str, ws: Worksheet) -> int:
column_index = get_columns_names(ws).index(column_name) + 1
for i in range(2, ws.max_row):
if ws.cell(row=i, column=column_index).value == value:
return i
return -1
def parse_cell(cell: Cell, row_nr: int, column_nr: int,
worksheet: Worksheet) -> Expenditure: # noqa: D103
date = worksheet.cell(row=row_nr, column=COLUMN_DATE).value
return Expenditure(
value=float(cell.value),
date=date,
category=COLUMNS[column_nr],
comment=sanitize_comment(cell.comment),
)
def output_schedules(ws: Worksheet, monitor_dict: dict, weekday_dict: dict,
monitor_column_dict: dict):
monitor_name_st_col = find_col_idx_by_val(ws, HEADER_ROW_IDX,
ERole.AM1.name)
monitor_name_cols = {
ERole.AM1: monitor_name_st_col,
ERole.AM2: monitor_name_st_col + 1,
ERole.PM: monitor_name_st_col + 2
}
for row_idx, weekday in weekday_dict.items():
for monitor in monitor_dict.values():
if (role :=
monitor.schedule.get(weekday)) and role in OUTPUT_ROLES:
ws.cell(row=row_idx,
column=monitor_column_dict[monitor.name],
value=role.name)
if col_idx := monitor_name_cols.get(role):
ws.cell(row=row_idx, column=col_idx, value=monitor.name)
def get_cell_name(sheet: Worksheet, row: int, col: int) -> str:
"""
get cell name "A1" ...
:param sheet: sheet get name
:param row: row index
:param col: column index
:return: string name cell
"""
return sheet.cell(row=row + 1, column=col + 1).coordinate
def get_cell(sheet: Worksheet, row: int, col: int) -> Cell:
"""
get cell data
:param sheet: sheet get info cell
:param row: index row
:param col: index col
:return: cell info data
"""
return sheet.cell(row=row + 1, column=col + 1)
def _export_xlsx_data_rows(ws: Worksheet,
amendement_dicts: Iterable[dict]) -> Counter:
counter = Counter({"amendements": 0})
for amend_dict in amendement_dicts:
for column, value in enumerate(HEADERS, 1):
cell = ws.cell(row=counter["amendements"] + 2, column=column)
cell.value = amend_dict[value]
counter["amendements"] += 1
return counter
