Spaces:
Sleeping
Sleeping
| import streamlit as st | |
| import pandas as pd | |
| import math | |
| import os | |
| import tempfile | |
| import xlrd | |
| import re | |
| from openpyxl import load_workbook | |
| from openpyxl.utils.cell import coordinate_to_tuple | |
| # ============================================================ | |
| # PAGE CONFIG | |
| # ============================================================ | |
| st.set_page_config( | |
| page_title="HTRI Excel Extractor", | |
| layout="wide" | |
| ) | |
| st.title("HTRI Excel Cell Extractor") | |
| st.markdown(""" | |
| Upload HTRI Excel datasheets (`.xls` or `.xlsx`) and extract: | |
| - Shell Flow | |
| - Tube Flow | |
| - Heat Duty | |
| - LMTD | |
| - U Value | |
| - Shell Passes | |
| - Tube Passes | |
| - Shell OD | |
| - Tube Length | |
| - Tube OD | |
| - Tube Quantity | |
| """) | |
| # ============================================================ | |
| # FILE UPLOADER | |
| # ============================================================ | |
| uploaded_files = st.file_uploader( | |
| "Upload Excel Files", | |
| type=["xls", "xlsx"], | |
| accept_multiple_files=True | |
| ) | |
| run = st.button("🚀 Extract Data") | |
| # ============================================================ | |
| # SAFE NUMERIC CONVERSION | |
| # PICKS VALUE OUTSIDE BRACKETS | |
| # ============================================================ | |
| def safe_float(v): | |
| try: | |
| if v is None: | |
| return math.nan | |
| s = str(v).strip() | |
| # Remove text inside brackets | |
| # Example: | |
| # 12500 (5670) -> 12500 | |
| s = re.sub(r"\(.*?\)", "", s).strip() | |
| # Remove commas | |
| s = s.replace(",", "") | |
| # Extract valid number | |
| match = re.search( | |
| r"[-+]?\d*\.?\d+(?:[eE][-+]?\d+)?", | |
| s | |
| ) | |
| if match: | |
| return float(match.group()) | |
| return math.nan | |
| except: | |
| return math.nan | |
| # ============================================================ | |
| # NORMALIZE UNITS | |
| # ============================================================ | |
| def norm(u): | |
| if u is None: | |
| return "" | |
| return ( | |
| str(u) | |
| .lower() | |
| .replace(" ", "") | |
| .strip() | |
| ) | |
| # ============================================================ | |
| # UNIT CONVERSION FUNCTIONS | |
| # ============================================================ | |
| def flow(v, u): | |
| if pd.isna(v): | |
| return math.nan | |
| u = norm(u) | |
| if u in ["kg/h", "kg/hr"]: | |
| return round(v * 2.20462, 3) | |
| return round(v, 3) | |
| def heat(v, u): | |
| if pd.isna(v): | |
| return math.nan | |
| u = norm(u) | |
| if u == "kw": | |
| return round(v * 3412.14, 3) | |
| if u == "w": | |
| return round(v * 3.41214, 3) | |
| if u == "mmbtu/hr": | |
| return round(v * 1_000_000, 3) | |
| return round(v, 3) | |
| def lmtd(v, u): | |
| if pd.isna(v): | |
| return math.nan | |
| u = norm(u) | |
| if u == "c": | |
| return round((v * 1.8) + 32, 3) | |
| return round(v, 3) | |
| def uval(v, u): | |
| if pd.isna(v): | |
| return math.nan | |
| u = norm(u) | |
| if "w/m" in u: | |
| return round(v * 0.1761, 3) | |
| return round(v, 3) | |
| def mm_to_in(v, u): | |
| if pd.isna(v): | |
| return math.nan | |
| u = norm(u) | |
| if u == "mm": | |
| return round(v / 25.4, 3) | |
| return round(v, 3) | |
| def mm_to_ft(v, u): | |
| if pd.isna(v): | |
| return math.nan | |
| u = norm(u) | |
| if u == "mm": | |
| return round(v / 304.8, 3) | |
| if u == "m": | |
| return round(v * 3.28084, 3) | |
| return round(v, 3) | |
| # ============================================================ | |
| # SAVE TEMP FILE | |
| # ============================================================ | |
| def save_temp(uploaded_file): | |
| suffix = os.path.splitext(uploaded_file.name)[1] | |
| tmp = tempfile.NamedTemporaryFile( | |
| delete=False, | |
| suffix=suffix | |
| ) | |
| tmp.write(uploaded_file.read()) | |
| tmp.close() | |
| return tmp.name | |
| # ============================================================ | |
| # LOAD TEMA SHEET | |
| # ============================================================ | |
| def load_sheet(path): | |
| ext = path.lower().split(".")[-1] | |
| # ======================================================== | |
| # XLSX | |
| # ======================================================== | |
| if ext == "xlsx": | |
| wb = load_workbook( | |
| path, | |
| data_only=True, | |
| read_only=True | |
| ) | |
| for ws in wb.worksheets: | |
| if "tema" in ws.title.lower(): | |
| return ws, "openpyxl" | |
| # ======================================================== | |
| # XLS | |
| # ======================================================== | |
| elif ext == "xls": | |
| wb = xlrd.open_workbook(path) | |
| for name in wb.sheet_names(): | |
| if "tema" in name.lower(): | |
| return wb.sheet_by_name(name), "xlrd" | |
| return None, None | |
| # ============================================================ | |
| # UNIVERSAL CELL READER | |
| # ============================================================ | |
| def get(ws, cell, engine): | |
| try: | |
| if engine == "openpyxl": | |
| return ws[cell].value | |
| elif engine == "xlrd": | |
| r, c = coordinate_to_tuple(cell) | |
| return ws.cell_value(r - 1, c - 1) | |
| except: | |
| return None | |
| # ============================================================ | |
| # TUBE QUANTITY RULE | |
| # IF "U" EXISTS -> MULTIPLY BY 2 | |
| # ============================================================ | |
| def process_tube_qty(v): | |
| if v is None: | |
| return math.nan | |
| s = str(v).strip() | |
| nums = re.findall(r"[\d.]+", s) | |
| if not nums: | |
| return math.nan | |
| num = float(nums[0]) | |
| if "u" in s.lower(): | |
| return num * 2 | |
| return num | |
| # ============================================================ | |
| # PROCESS FILE | |
| # ============================================================ | |
| def process(path, name): | |
| ws, engine = load_sheet(path) | |
| if ws is None: | |
| return None, f"{name}: No TEMA sheet found" | |
| try: | |
| flow_u = get(ws, "M14", engine) | |
| shell = flow( | |
| safe_float(get(ws, "T14", engine)), | |
| flow_u | |
| ) | |
| tube = flow( | |
| safe_float(get(ws, "AR14", engine)), | |
| flow_u | |
| ) | |
| result = { | |
| "File_Name": name, | |
| "Shell_Flow_lb_hr": shell, | |
| "Tube_Flow_lb_hr": tube, | |
| "Heat_Duty_Btu_hr": heat( | |
| safe_float(get(ws, "M32", engine)), | |
| get(ws, "T32", engine) | |
| ), | |
| "Corrected_LMTD_F": lmtd( | |
| safe_float(get(ws, "BB32", engine)), | |
| get(ws, "BH32", engine) | |
| ), | |
| "U_Btu_ft2_hr_F": uval( | |
| safe_float(get(ws, "BB33", engine)), | |
| get(ws, "BH33", engine) | |
| ), | |
| "Shell_Passes": safe_float( | |
| get(ws, "T38", engine) | |
| ), | |
| "Tube_Passes": safe_float( | |
| get(ws, "AF38", engine) | |
| ), | |
| "Shell_OD_in": mm_to_in( | |
| safe_float(get(ws, "AC45", engine)), | |
| get(ws, "AH45", engine) | |
| ), | |
| "Tube_Length_ft": mm_to_ft( | |
| safe_float(get(ws, "AR43", engine)), | |
| get(ws, "AW43", engine) | |
| ), | |
| "Tube_OD_in": mm_to_in( | |
| safe_float(get(ws, "N43", engine)), | |
| get(ws, "R43", engine) | |
| ), | |
| "Tube_Quantity": process_tube_qty( | |
| get(ws, "F43", engine) | |
| ) | |
| } | |
| return result, None | |
| except Exception as e: | |
| return None, f"{name}: {str(e)}" | |
| # ============================================================ | |
| # RUN EXTRACTION | |
| # ============================================================ | |
| if run: | |
| if not uploaded_files: | |
| st.warning("Please upload files first.") | |
| st.stop() | |
| results = [] | |
| errors = [] | |
| progress = st.progress(0) | |
| for i, file in enumerate(uploaded_files): | |
| path = save_temp(file) | |
| data, err = process(path, file.name) | |
| if data: | |
| results.append(data) | |
| if err: | |
| errors.append(err) | |
| progress.progress((i + 1) / len(uploaded_files)) | |
| # ======================================================== | |
| # DATAFRAME | |
| # ======================================================== | |
| df = pd.DataFrame(results) | |
| st.success("Extraction Completed") | |
| st.dataframe( | |
| df, | |
| use_container_width=True | |
| ) | |
| # ======================================================== | |
| # DOWNLOAD EXCEL | |
| # ======================================================== | |
| output_file = "htri_extracted_results.xlsx" | |
| df.to_excel( | |
| output_file, | |
| index=False | |
| ) | |
| with open(output_file, "rb") as f: | |
| st.download_button( | |
| label="📥 Download Excel", | |
| data=f, | |
| file_name=output_file, | |
| mime="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" | |
| ) | |
| # ======================================================== | |
| # ERROR DISPLAY | |
| # ======================================================== | |
| if errors: | |
| st.subheader("Errors") | |
| for e in errors: | |
| st.error(e) |