File size: 3,101 Bytes
d9f7219 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 | 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") |