def draw_boiling_sheet(wb, df, skus, sheet_name, cur_row=None, normalize=True):
print(df)
if not cur_row:
cur_row = 3
if not df.empty:
excel_client = ExcelBlock(wb[sheet_name])
values = []
sku_names = [x.name for x in skus]
df_filter = df[df["name"].isin(sku_names)].copy()
for id, grp in df_filter.groupby("id", sort=False):
for i, row in grp.iterrows():
columns = [x for x in row.index if x in COLUMNS.keys()]
v = [row[column] for column in columns]
c = [COLUMNS[column] for column in columns]
values.append(dict(zip(c, v)))
empty_columns = [
COLUMNS["name"],
COLUMNS["output"],
COLUMNS["delimiter"],
]
values.append(dict(zip(empty_columns, ["-"] * len(empty_columns))))
for v in values:
value = v.values()
column = [x.col for x in v.keys()]
formula = '=IF({1}{0}="-", "", 1 + SUM(INDIRECT(ADDRESS(2,COLUMN({2}{0})) & ":" & ADDRESS(ROW(),COLUMN({2}{0})))))'.format(
cur_row,
COLUMNS["delimiter"].col_name,
COLUMNS["delimiter_int"].col_name,
)
colour = get_colour_by_name(v[COLUMNS["name"]], skus)
excel_client.colour = colour[1:]
excel_client.draw_cell(
row=cur_row,
col=COLUMNS["boiling_number"].col,
value=formula,
set_border=False,
)
excel_client.draw_row(row=cur_row,
values=value,
cols=column,
set_border=False)
excel_client.color_cell(row=cur_row,
col=COLUMNS["boiling_type"].col)
cur_row += 1
return wb, cur_row
def draw_boiling_plan(df, df_extra, wb, total_volume=0):
skus = db.session.query(RicottaSKU).all()
draw_boiling_names(wb=wb)
sheet_name = "План варок"
draw_skus(wb, skus)
values = []
excel_client = ExcelBlock(wb[sheet_name])
sku_names = [x.name for x in skus]
df_filter = df[df["name"].isin(sku_names)].copy()
for id, grp in df_filter.groupby("id", sort=False):
for i, row in grp.iterrows():
columns = [x for x in row.index if x in COLUMNS.keys()]
v = [row[column] for column in columns]
c = [COLUMNS[column] for column in columns]
values.append(dict(zip(c, v)))
empty_columns = [
COLUMNS["name"],
COLUMNS["output"],
COLUMNS["number_of_tanks"],
COLUMNS["boiling_count"],
COLUMNS["delimiter"],
]
values.append(dict(zip(empty_columns, ["-"] * len(empty_columns))))
cur_row = 3
boiling_count = 1
number_of_tanks = 0
for v in values:
current_boiling_count = v[COLUMNS["boiling_count"]]
current_number_of_tanks = v[COLUMNS["number_of_tanks"]]
print(current_boiling_count, current_number_of_tanks)
del v[COLUMNS["boiling_count"]]
del v[COLUMNS["output"]]
del v[COLUMNS["number_of_tanks"]]
value = v.values()
column = [x.col for x in v.keys()]
formula = '=IF({1}{0}="-", "", 1 + SUM(INDIRECT(ADDRESS(2,COLUMN({2}{0})) & ":" & ADDRESS(ROW(),COLUMN({2}{0})))))'.format(
cur_row,
COLUMNS["delimiter"].col_name,
COLUMNS["delimiter_int"].col_name,
)
colour = get_colour_by_name(v[COLUMNS["name"]], skus)
excel_client.colour = colour[1:]
excel_client.draw_cell(
row=cur_row,
col=COLUMNS["boiling_number"].col,
value=formula,
set_border=False,
)
excel_client.draw_row(row=cur_row,
values=value,
cols=column,
set_border=False)
if v[COLUMNS["name"]] == "-":
excel_client.draw_cell(
row=cur_row,
col=COLUMNS["boiling_count"].col,
value=int(number_of_tanks),
set_border=False,
)
else:
excel_client.color_cell(col=COLUMNS["boiling_type"].col,
row=cur_row)
excel_client.color_cell(col=COLUMNS["output"].col, row=cur_row)
excel_client.color_cell(col=COLUMNS["number_of_tanks"].col,
row=cur_row)
number_of_tanks = current_number_of_tanks
boiling_count = current_boiling_count
cur_row += 1
excel_client.font_size = 10
excel_client.draw_cell(
row=ROWS["total_volume"],
col=COLUMNS["total_volume"].col,
value=total_volume,
set_border=False,
)
for sheet in wb.sheetnames:
wb[sheet].views.sheetView[0].tabSelected = False
wb.active = 2
return wb