| import re |
|
|
| import pandas as pd |
|
|
|
|
| TECH_SHEET_KEYWORDS: dict[str, tuple[str, ...]] = { |
| "2g": ("2g", "gsm", "geran"), |
| "3g": ("3g", "wcdma", "umts", "utra"), |
| "4g": ("4g", "lte", "eutran"), |
| } |
|
|
|
|
| def _normalize_sheet_name(sheet_name: object) -> tuple[str, str]: |
| normalized = re.sub(r"[^a-z0-9]+", " ", str(sheet_name).strip().lower()).strip() |
| collapsed = normalized.replace(" ", "") |
| return normalized, collapsed |
|
|
|
|
| def _score_sheet_name(sheet_name: str, technology: str) -> int: |
| keywords = TECH_SHEET_KEYWORDS.get(technology.lower(), (technology.lower(),)) |
| normalized, collapsed = _normalize_sheet_name(sheet_name) |
| tokens = normalized.split() |
|
|
| best_score = 0 |
| for keyword in keywords: |
| if normalized == keyword: |
| best_score = max(best_score, 100) |
| elif keyword in tokens: |
| best_score = max(best_score, 80) |
| elif collapsed.endswith(keyword) or collapsed.startswith(keyword): |
| best_score = max(best_score, 60) |
| elif keyword in collapsed: |
| best_score = max(best_score, 40) |
|
|
| return best_score |
|
|
|
|
| def _get_workbook_sheet_names(ciq_file) -> list[str]: |
| if hasattr(ciq_file, "seek"): |
| ciq_file.seek(0) |
|
|
| workbook = pd.ExcelFile(ciq_file, engine="calamine") |
| return list(workbook.sheet_names) |
|
|
|
|
| def get_ciq_sheet_selection_details(ciq_file, technology: str) -> tuple[str, list[str]]: |
| sheet_names = _get_workbook_sheet_names(ciq_file) |
|
|
| if not sheet_names: |
| raise ValueError("The uploaded Excel file does not contain any sheet.") |
|
|
| if len(sheet_names) == 1: |
| return sheet_names[0], sheet_names |
|
|
| scored_matches = [] |
| for idx, sheet_name in enumerate(sheet_names): |
| score = _score_sheet_name(sheet_name, technology) |
| if score > 0: |
| scored_matches.append((score, idx, sheet_name)) |
|
|
| if not scored_matches: |
| joined = ", ".join(str(name) for name in sheet_names) |
| raise ValueError( |
| f"Unable to detect the {technology.upper()} sheet automatically. " |
| f"Available sheets: {joined}" |
| ) |
|
|
| scored_matches.sort(key=lambda item: (-item[0], item[1])) |
| return scored_matches[0][2], sheet_names |
|
|
|
|
| def select_ciq_sheet_name(ciq_file, technology: str) -> str: |
| selected_sheet, _sheet_names = get_ciq_sheet_selection_details( |
| ciq_file, technology=technology |
| ) |
| return selected_sheet |
|
|
|
|
| def read_ciq_excel( |
| ciq_file, technology: str, sheet_name: str | None = None |
| ) -> pd.DataFrame: |
| if sheet_name is None: |
| sheet_name = select_ciq_sheet_name(ciq_file, technology=technology) |
| else: |
| sheet_names = _get_workbook_sheet_names(ciq_file) |
| if sheet_name not in sheet_names: |
| joined = ", ".join(str(name) for name in sheet_names) |
| raise ValueError( |
| f"Sheet '{sheet_name}' does not exist in the uploaded Excel file. " |
| f"Available sheets: {joined}" |
| ) |
|
|
| if hasattr(ciq_file, "seek"): |
| ciq_file.seek(0) |
|
|
| return pd.read_excel(ciq_file, sheet_name=sheet_name, engine="calamine") |
