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