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)