import io import time import pandas as pd import streamlit as st # @st.cache_data # def convert_dfs(dfs: list[pd.DataFrame], sheet_names: list[str]) -> bytes: # # IMPORTANT: Cache the conversion to prevent computation on every rerun # # Create a BytesIO object # bytes_io = io.BytesIO() # # Write the dataframes to the BytesIO object # with pd.ExcelWriter(bytes_io, engine="xlsxwriter") as writer: # for df, sheet_name in zip(dfs, sheet_names): # df.to_excel(writer, sheet_name=sheet_name, index=True) # # Get the bytes data # bytes_data = bytes_io.getvalue() # # Close the BytesIO object # bytes_io.close() # return bytes_data def get_formats(workbook): return { "green": workbook.add_format( {"bg_color": "#37CC73", "bold": True, "border": 1} ), "green_light": workbook.add_format( {"bg_color": "#87E0AB", "bold": True, "border": 1} ), "blue": workbook.add_format({"bg_color": "#1A64FF", "bold": True, "border": 1}), "blue_light": workbook.add_format( {"bg_color": "#00B0F0", "bold": True, "border": 1} ), "beurre": workbook.add_format( {"bg_color": "#FFE699", "bold": True, "border": 1} ), "orange": workbook.add_format( {"bg_color": "#F47F31", "bold": True, "border": 1} ), "purple5": workbook.add_format( {"bg_color": "#E03DCD", "bold": True, "border": 1} ), "purple6": workbook.add_format( {"bg_color": "#AE83F8", "bold": True, "border": 1} ), "gray": workbook.add_format({"bg_color": "#D9D9D9", "bold": True, "border": 1}), "red": workbook.add_format({"bg_color": "#FF0000", "bold": True, "border": 1}), "yellow": workbook.add_format( {"bg_color": "#FFFF00", "bold": True, "border": 1} ), } def get_format_map_by_format_type(formats: dict, format_type: str) -> dict: if format_type == "GSM_Analysis": return { # "name": formats["blue"], "amrSegLoadDepTchRateLower": formats["beurre"], "amrSegLoadDepTchRateUpper": formats["beurre"], "btsSpLoadDepTchRateLower": formats["beurre"], "btsSpLoadDepTchRateUpper": formats["beurre"], "amrWbFrCodecModeSet": formats["beurre"], "dedicatedGPRScapacity": formats["beurre"], "defaultGPRScapacity": formats["beurre"], "number_trx_per_cell": formats["blue"], "number_trx_per_bcf": formats["blue"], "number_tch_per_cell": formats["blue"], "number_sd_per_cell": formats["blue"], "number_bcch_per_cell": formats["blue"], "number_ccch_per_cell": formats["blue"], "number_cbc_per_cell": formats["blue"], "number_total_channels_per_cell": formats["blue"], "number_signals_per_cell": formats["blue"], "hf_rate_coef": formats["purple5"], "GPRS": formats["purple5"], "TCH Actual HR%": formats["green"], "Offered Traffic BH": formats["green"], "Max_Traffic BH": formats["green"], "Avg_Traffic BH": formats["green"], "TCH UTILIZATION (@Max Traffic)": formats["red"], "Tch utilization comments": formats["orange"], "ErlabngB_value": formats["purple6"], "Target FR CHs": formats["purple6"], "Target HR CHs": formats["purple6"], "Target TCHs": formats["purple6"], "Target TRXs": formats["purple6"], "Number of required TRXs": formats["purple6"], "max_tch_call_blocking_bh": formats["yellow"], "avg_tch_call_blocking_bh": formats["yellow"], "number_of_days_with_tch_blocking_exceeded_bh": formats["yellow"], "tch_call_blocking_bh_comment": formats["orange"], "max_sdcch_real_blocking_bh": formats["yellow"], "avg_sdcch_real_blocking_bh": formats["yellow"], "number_of_days_with_sdcch_blocking_exceeded_bh": formats["yellow"], "sdcch_real_blocking_bh_comment": formats["orange"], "Average_cell_availability_bh": formats["yellow"], "number_of_days_exceeding_availability_threshold_bh": formats["yellow"], "availability_comment_bh": formats["orange"], "max_tch_abis_fail_bh": formats["yellow"], "avg_tch_abis_fail_bh": formats["yellow"], "number_of_days_with_tch_abis_fail_exceeded_bh": formats["yellow"], "tch_abis_fail_bh_comment": formats["orange"], "Average_cell_availability_daily": formats["green_light"], "number_of_days_exceeding_availability_threshold_daily": formats[ "green_light" ], "availability_comment_daily": formats["orange"], "max_tch_abis_fail_daily": formats["green_light"], "avg_tch_abis_fail_daily": formats["green_light"], "number_of_days_with_tch_abis_fail_exceeded_daily": formats["green_light"], "tch_abis_fail_daily_comment": formats["orange"], "BH Congestion status": formats["gray"], "operational_comment": formats["gray"], "Final comment": formats["gray"], "Final comment summary": formats["gray"], # Operational Neighbours Distance Sheet "Source_ID_BTS": formats["blue"], "Source_name": formats["blue"], "Source_BH Congestion status": formats["blue"], "Source_Longitude": formats["blue"], "Source_Latitude": formats["blue"], "Neighbour_ID_BTS": formats["green_light"], "Neighbour_name": formats["green_light"], "Neighbour_operational_comment": formats["green_light"], "Neighbour_Longitude": formats["green_light"], "Neighbour_Latitude": formats["green_light"], "Distance_km": formats["beurre"], } elif format_type == "database": return { "code": formats["blue"], "Azimut": formats["green"], "Longitude": formats["green"], "Latitude": formats["green"], "Hauteur": formats["green"], "City": formats["green"], "Adresse": formats["green"], "Commune": formats["green"], "Cercle": formats["green"], "number_trx_per_cell": formats["blue_light"], "number_trx_per_bcf": formats["blue_light"], "number_trx_per_site": formats["blue_light"], } elif format_type == "LTE_Analysis": return { "code": formats["blue"], "code_sector": formats["blue"], "Region": formats["blue"], "site_config_band": formats["blue"], "Longitude": formats["blue"], "Latitude": formats["blue"], # "name_l800": formats["beurre"], # "name_l1800": formats["purple5"], # "name_l2300": formats["purple6"], # "name_l2600": formats["blue_light"], # "name_l1800s": formats["gray"], "prb_l800": formats["beurre"], "prb_l1800": formats["beurre"], "prb_l2300": formats["beurre"], "prb_l2600": formats["beurre"], "prb_l1800s": formats["beurre"], "prb_l800_2nd": formats["purple5"], "prb_l1800_2nd": formats["purple5"], "prb_l2300_2nd": formats["purple5"], "prb_l2600_2nd": formats["purple5"], "prb_l1800s_2nd": formats["purple5"], "act_ues_l800": formats["purple6"], "act_ues_l1800": formats["purple6"], "act_ues_l2300": formats["purple6"], "act_ues_l2600": formats["purple6"], "act_ues_l1800s": formats["purple6"], "dl_thp_l800": formats["blue_light"], "dl_thp_l1800": formats["blue_light"], "dl_thp_l2300": formats["blue_light"], "dl_thp_l2600": formats["blue_light"], "dl_thp_l1800s": formats["blue_light"], "ul_thp_l800": formats["gray"], "ul_thp_l1800": formats["gray"], "ul_thp_l2300": formats["gray"], "ul_thp_l2600": formats["gray"], "ul_thp_l1800s": formats["gray"], "num_congested_cells": formats["orange"], "num_cells": formats["orange"], "num_cell_with_kpi": formats["orange"], "num_down_or_no_kpi_cells": formats["orange"], "prb_diff_between_cells": formats["orange"], "load_balance_required": formats["orange"], "congestion_comment": formats["orange"], "final_comments": formats["green"], } elif format_type == "WCEL_capacity": return { "code": formats["blue"], "Region": formats["blue"], "name": formats["blue"], "Avg_availability": formats["blue_light"], "Avail_exceed_days": formats["blue_light"], "availability_comment": formats["blue_light"], "sum_traffic_cs": formats["beurre"], "sum_traffic_dl": formats["beurre"], "max_dl_throughput": formats["beurre"], "avg_dl_throughput": formats["beurre"], "max_users": formats["beurre"], "max_iub_frameloss": formats["purple5"], "iub_frameloss_exceed_days": formats["purple5"], "max_hsdpa_congestion_rate_iub": formats["purple5"], "hsdpa_iub_exceed_days": formats["purple5"], "max_rrc_fail_ac": formats["purple6"], "ac_fail_exceed_days": formats["purple6"], "max_rrc_fail_ac_ul": formats["purple6"], "ac_ul_fail_exceed_days": formats["purple6"], "max_rrc_fail_ac_dl": formats["purple6"], "ac_dl_fail_exceed_days": formats["purple6"], "max_rrc_fail_code": formats["purple6"], "code_fail_exceed_days": formats["purple6"], "max_rrc_fail_bts": formats["yellow"], "bts_fail_exceed_days": formats["yellow"], "tx_congestion_comments": formats["green"], "operational_comments": formats["green"], "fails_comments": formats["green"], "final_comments": formats["green"], } else: return {} # No formatting if format_type not matched def _apply_custom_formatting( writer, df: pd.DataFrame, sheet_name: str, format_type: str ): workbook = writer.book worksheet = writer.sheets[sheet_name] formats = get_formats(workbook) format_map = get_format_map_by_format_type(formats, format_type) for col_idx, col_name in enumerate(df.columns): fmt = format_map.get(col_name) if fmt: worksheet.write(0, col_idx + 1, col_name, fmt) def _write_to_excel( dfs: list[pd.DataFrame], sheet_names: list[str], index=True, format_type: str = None ) -> bytes: bytes_io = io.BytesIO() with pd.ExcelWriter(bytes_io, engine="xlsxwriter") as writer: for df, name in zip(dfs, sheet_names): # df.index.name = "index" df.to_excel(writer, sheet_name=name, index=index) if format_type: _apply_custom_formatting(writer, df, name, format_type) return bytes_io.getvalue() @st.cache_data def convert_dfs(dfs: list[pd.DataFrame], sheet_names: list[str]) -> bytes: return _write_to_excel(dfs, sheet_names, index=True) @st.cache_data def convert_gsm_dfs(dfs, sheet_names) -> bytes: return _write_to_excel(dfs, sheet_names, index=True, format_type="GSM_Analysis") @st.cache_data def convert_lte_analysis_dfs(dfs, sheet_names) -> bytes: return _write_to_excel(dfs, sheet_names, index=True, format_type="LTE_Analysis") @st.cache_data def convert_wcel_capacity_dfs(dfs, sheet_names) -> bytes: return _write_to_excel(dfs, sheet_names, index=True, format_type="WCEL_capacity") @st.cache_data def convert_database_dfs(dfs, sheet_names) -> bytes: return _write_to_excel(dfs, sheet_names, index=True, format_type="database") def save_dataframe(df: pd.DataFrame, sheet_name: str): """ Save the dataframe to a csv file. Args: df (pd.DataFrame): The dataframe to save. sheet_name (str): The name of the sheet. """ df.to_csv(f"data2/{sheet_name}_{time.time()}.csv", index=False, encoding="latin1")