utils/convert_to_excel.py

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")