From 4910356db2585e55d2876001e40b21e9b148bcc4 Mon Sep 17 00:00:00 2001 From: admin Date: Mon, 4 Aug 2025 00:35:10 +0200 Subject: added fgla search --- scripts/excel_to_json.py | 313 ++++++++++++++++++++++++++++++++++++++++++++--- 1 file changed, 299 insertions(+), 14 deletions(-) (limited to 'scripts/excel_to_json.py') diff --git a/scripts/excel_to_json.py b/scripts/excel_to_json.py index 73e52e6..5627540 100755 --- a/scripts/excel_to_json.py +++ b/scripts/excel_to_json.py @@ -11,27 +11,282 @@ import os from pathlib import Path def convert_schott_excel(excel_path): - """Convert SCHOTT Excel catalog to glasses list.""" - df = pd.read_excel(excel_path, skiprows=6) + """Convert SCHOTT Excel catalog to glasses list with comprehensive data extraction.""" glasses = [] - for _, row in df.iterrows(): - if pd.isna(row.iloc[0]) or row.iloc[0] == '': - continue - + # Process both preferred and inquiry sheets + sheet_info = [ + ("Preferred glasses", "preferred"), + ("Inquiry glasses", "inquiry") + ] + + for sheet_name, availability in sheet_info: try: - glass = { - "name": str(row.iloc[0]).strip(), - "nd": float(row.iloc[1]), - "vd": float(row.iloc[3]), - "manufacturer": "SCHOTT" - } - glasses.append(glass) - except (ValueError, IndexError, TypeError): + print(f" Processing sheet: {sheet_name}") + df = pd.read_excel(excel_path, sheet_name=sheet_name, skiprows=3) + + for _, row in df.iterrows(): + # Skip empty rows + if pd.isna(row.iloc[0]) or str(row.iloc[0]).strip() == '': + continue + + try: + glass_name = str(row.iloc[0]).strip() + + # Basic glass properties + glass = { + "name": glass_name, + "nd": safe_float(row.iloc[1]), # nd + "vd": safe_float(row.iloc[3]), # vd + "manufacturer": "SCHOTT", + "availability": availability + } + + # Add glass code if available (column 158) + if len(row) > 158 and not pd.isna(row.iloc[158]): + glass_code_raw = str(row.iloc[158]).strip() + # SCHOTT uses format like "517642.251" where first part is the 6-digit code + if '.' in glass_code_raw: + integer_part = glass_code_raw.split('.')[0] + glass_code_digits = ''.join(c for c in integer_part if c.isdigit()) + if len(glass_code_digits) > 0: + glass["glass_code"] = glass_code_digits.zfill(6) + else: + # Fallback for codes without decimal point + glass_code_digits = ''.join(c for c in glass_code_raw if c.isdigit()) + if len(glass_code_digits) >= 6: + glass["glass_code"] = glass_code_digits[:6] + elif len(glass_code_digits) > 0: + glass["glass_code"] = glass_code_digits.zfill(6) + + # Additional refractive indices + refractive_indices = {} + if not pd.isna(row.iloc[2]): refractive_indices["ne"] = safe_float(row.iloc[2]) + if not pd.isna(row.iloc[4]): refractive_indices["ve"] = safe_float(row.iloc[4]) + + # Add spectral refractive indices (columns 116-138) + spectral_indices = { + "n2325.4": safe_float(row.iloc[116]) if len(row) > 116 else None, + "n1970.1": safe_float(row.iloc[117]) if len(row) > 117 else None, + "n1529.6": safe_float(row.iloc[118]) if len(row) > 118 else None, + "n1060.0": safe_float(row.iloc[119]) if len(row) > 119 else None, + "nt": safe_float(row.iloc[120]) if len(row) > 120 else None, + "ns": safe_float(row.iloc[121]) if len(row) > 121 else None, + "nr": safe_float(row.iloc[122]) if len(row) > 122 else None, + "nC": safe_float(row.iloc[123]) if len(row) > 123 else None, + "nC_prime": safe_float(row.iloc[124]) if len(row) > 124 else None, + "n632.8": safe_float(row.iloc[125]) if len(row) > 125 else None, + "nD": safe_float(row.iloc[126]) if len(row) > 126 else None, + "nd_spectral": safe_float(row.iloc[127]) if len(row) > 127 else None, + "ne_spectral": safe_float(row.iloc[128]) if len(row) > 128 else None, + "nF": safe_float(row.iloc[129]) if len(row) > 129 else None, + "nF_prime": safe_float(row.iloc[130]) if len(row) > 130 else None, + "ng": safe_float(row.iloc[131]) if len(row) > 131 else None, + "nh": safe_float(row.iloc[132]) if len(row) > 132 else None, + "ni": safe_float(row.iloc[133]) if len(row) > 133 else None, + "n334.1": safe_float(row.iloc[134]) if len(row) > 134 else None, + "n312.6": safe_float(row.iloc[135]) if len(row) > 135 else None, + "n296.7": safe_float(row.iloc[136]) if len(row) > 136 else None, + "n280.4": safe_float(row.iloc[137]) if len(row) > 137 else None, + "n248.3": safe_float(row.iloc[138]) if len(row) > 138 else None + } + + # Filter out None values + spectral_indices = {k: v for k, v in spectral_indices.items() if v is not None} + if spectral_indices: + refractive_indices.update(spectral_indices) + + if refractive_indices: + glass["refractive_indices"] = refractive_indices + + # Color code + if not pd.isna(row.iloc[5]): + glass["colour_code"] = str(row.iloc[5]) + + # Dispersion coefficients (Schott formula) + dispersion = {} + if not pd.isna(row.iloc[6]): dispersion["B1"] = safe_float(row.iloc[6]) + if not pd.isna(row.iloc[7]): dispersion["B2"] = safe_float(row.iloc[7]) + if not pd.isna(row.iloc[8]): dispersion["B3"] = safe_float(row.iloc[8]) + if not pd.isna(row.iloc[9]): dispersion["C1"] = safe_float(row.iloc[9]) + if not pd.isna(row.iloc[10]): dispersion["C2"] = safe_float(row.iloc[10]) + if not pd.isna(row.iloc[11]): dispersion["C3"] = safe_float(row.iloc[11]) + + if dispersion: + dispersion["dispersion_formula"] = "Schott" + glass["dispersion"] = dispersion + + # Temperature coefficients + temperature_coeffs = {} + if not pd.isna(row.iloc[12]): temperature_coeffs["D0"] = safe_float(row.iloc[12]) + if not pd.isna(row.iloc[13]): temperature_coeffs["D1"] = safe_float(row.iloc[13]) + if not pd.isna(row.iloc[14]): temperature_coeffs["D2"] = safe_float(row.iloc[14]) + if not pd.isna(row.iloc[15]): temperature_coeffs["E0"] = safe_float(row.iloc[15]) + if not pd.isna(row.iloc[16]): temperature_coeffs["E1"] = safe_float(row.iloc[16]) + if not pd.isna(row.iloc[17]): temperature_coeffs["lambda"] = safe_float(row.iloc[17]) + + if temperature_coeffs: + glass["temperature_coefficients"] = temperature_coeffs + + # Transmission data (TAUI25 and TAUI10 series) + transmission = {} + + # TAUI25 series (columns 37-66) + taui25 = {} + wavelengths_25 = ["2500", "2325", "1970", "1530", "1060", "700", "660", "620", "580", "546", + "500", "460", "436", "420", "405", "400", "390", "380", "370", "365", + "350", "334", "320", "310", "300", "290", "280", "270", "260", "250"] + for i, wl in enumerate(wavelengths_25): + if len(row) > 37 + i: + val = safe_float(row.iloc[37 + i]) + if val is not None: + taui25[f"{wl}nm"] = val + + # TAUI10 series (columns 67-96) + taui10 = {} + for i, wl in enumerate(wavelengths_25): + if len(row) > 67 + i: + val = safe_float(row.iloc[67 + i]) + if val is not None: + taui10[f"{wl}nm"] = val + + if taui25: + transmission["TAUI25"] = taui25 + if taui10: + transmission["TAUI10"] = taui10 + + if transmission: + glass["transmission"] = transmission + + # Chemical resistance (columns 98-102) + chemical_resistance = {} + if len(row) > 98 and not pd.isna(row.iloc[98]): chemical_resistance["CR"] = safe_float(row.iloc[98]) + if len(row) > 99 and not pd.isna(row.iloc[99]): chemical_resistance["FR"] = safe_float(row.iloc[99]) + if len(row) > 100 and not pd.isna(row.iloc[100]): chemical_resistance["SR"] = safe_float(row.iloc[100]) + if len(row) > 101 and not pd.isna(row.iloc[101]): chemical_resistance["AR"] = safe_float(row.iloc[101]) + if len(row) > 102 and not pd.isna(row.iloc[102]): chemical_resistance["PR"] = safe_float(row.iloc[102]) + + if chemical_resistance: + glass["chemical_resistance"] = chemical_resistance + + # Physical properties + physical_properties = {} + if len(row) > 103 and not pd.isna(row.iloc[103]): physical_properties["density"] = safe_float(row.iloc[103]) + if len(row) > 104 and not pd.isna(row.iloc[104]): physical_properties["Tg"] = safe_float(row.iloc[104]) + if len(row) > 105 and not pd.isna(row.iloc[105]): physical_properties["T13"] = safe_float(row.iloc[105]) + if len(row) > 106 and not pd.isna(row.iloc[106]): physical_properties["T7_6"] = safe_float(row.iloc[106]) + if len(row) > 107 and not pd.isna(row.iloc[107]): physical_properties["heat_capacity"] = safe_float(row.iloc[107]) + if len(row) > 108 and not pd.isna(row.iloc[108]): physical_properties["heat_conductivity"] = safe_float(row.iloc[108]) + if len(row) > 109 and not pd.isna(row.iloc[109]): physical_properties["alpha_minus30_70"] = safe_float(row.iloc[109]) + if len(row) > 110 and not pd.isna(row.iloc[110]): physical_properties["alpha_20_300"] = safe_float(row.iloc[110]) + + if physical_properties: + glass["physical_properties"] = physical_properties + + # Mechanical properties + mechanical_properties = {} + if len(row) > 111 and not pd.isna(row.iloc[111]): mechanical_properties["youngs_modulus"] = safe_float(row.iloc[111]) + if len(row) > 112 and not pd.isna(row.iloc[112]): mechanical_properties["poisson_ratio"] = safe_float(row.iloc[112]) + if len(row) > 113 and not pd.isna(row.iloc[113]): mechanical_properties["knoop_hardness"] = safe_float(row.iloc[113]) + if len(row) > 114 and not pd.isna(row.iloc[114]): mechanical_properties["abrasion_hardness"] = safe_float(row.iloc[114]) + if len(row) > 36 and not pd.isna(row.iloc[36]): mechanical_properties["stress_optical_coefficient"] = safe_float(row.iloc[36]) + + if mechanical_properties: + glass["mechanical_properties"] = mechanical_properties + + # Partial dispersions and ratios (columns 139-157) + partial_dispersions = {} + pd_names = ["nF_minus_nC", "nF_prime_minus_nC_prime", "P_s_t", "P_C_s", "P_d_C", "P_e_d", + "P_g_F", "P_i_h", "P_prime_s_t", "P_prime_C_prime_s", "P_prime_d_C_prime", + "P_prime_e_d", "P_prime_g_F_prime", "P_prime_i_h", "DP_C_t", "DP_C_s", + "DP_F_e", "DP_g_F", "DP_i_g"] + + for i, pd_name in enumerate(pd_names): + if len(row) > 139 + i: + val = safe_float(row.iloc[139 + i]) + if val is not None: + partial_dispersions[pd_name] = val + + if partial_dispersions: + glass["partial_dispersions"] = partial_dispersions + + # Additional properties + additional_properties = {} + if len(row) > 159 and not pd.isna(row.iloc[159]): additional_properties["remarks"] = str(row.iloc[159]) + if len(row) > 160 and not pd.isna(row.iloc[160]): additional_properties["date"] = str(row.iloc[160]) + if len(row) > 161 and not pd.isna(row.iloc[161]): additional_properties["relative_price"] = safe_float(row.iloc[161]) + + # Quality ratings + if len(row) > 162 and not pd.isna(row.iloc[162]): additional_properties["WR_J"] = safe_float(row.iloc[162]) + if len(row) > 163 and not pd.isna(row.iloc[163]): additional_properties["SR_J"] = safe_float(row.iloc[163]) + if len(row) > 164 and not pd.isna(row.iloc[164]): additional_properties["AT"] = safe_float(row.iloc[164]) + if len(row) > 165 and not pd.isna(row.iloc[165]): additional_properties["abrasion_HG_J"] = safe_float(row.iloc[165]) + + if additional_properties: + glass["additional_properties"] = additional_properties + + # Only add glasses with valid nd and vd + if glass["nd"] is not None and glass["vd"] is not None: + glasses.append(glass) + + except (ValueError, IndexError, TypeError) as e: + print(f" Warning: Skipping glass '{glass_name}': {e}") + continue + + except Exception as e: + print(f" Error processing sheet {sheet_name}: {e}") continue return glasses +def safe_float(value): + """Safely convert value to float, return None if not possible.""" + if pd.isna(value) or value == '' or value == ' ': + return None + try: + return float(value) + except (ValueError, TypeError): + return None + +def extract_glass_code(code_value, manufacturer=""): + """Extract and format glass code to 6-digit string with manufacturer-specific handling.""" + if pd.isna(code_value) or code_value == '' or code_value == ' ': + return None + + try: + code_str = str(code_value).strip() + + # Handle different manufacturer formats: + if manufacturer.upper() == "CDGM": + # CDGM uses format like "51270.0" which should become "051270" + # Split on decimal point and take integer part, then pad to 6 digits + if '.' in code_str: + integer_part = code_str.split('.')[0] + digits_only = ''.join(c for c in integer_part if c.isdigit()) + if len(digits_only) > 0: + return digits_only.zfill(6) + else: + digits_only = ''.join(c for c in code_str if c.isdigit()) + if len(digits_only) > 0: + return digits_only.zfill(6) + else: + # HOYA: "554-718" -> "554718" + # Ohara: "528765" -> "528765" + # SCHOTT: handled separately + + # Remove dashes and decimal points, keep only digits + digits_only = ''.join(c for c in code_str if c.isdigit()) + + if len(digits_only) >= 6: + return digits_only[:6] # Take first 6 digits + elif len(digits_only) > 0: + return digits_only.zfill(6) # Pad with leading zeros + + return None + + except (ValueError, TypeError): + return None + def convert_hoya_excel(excel_path): """Convert HOYA Excel catalog to glasses list.""" df = pd.read_excel(excel_path, skiprows=3) @@ -48,6 +303,16 @@ def convert_hoya_excel(excel_path): "vd": float(row.iloc[5]), "manufacturer": "HOYA" } + + # Add glass code (column 3: Code) + glass_code = extract_glass_code(row.iloc[3], "HOYA") + if glass_code: + glass["glass_code"] = glass_code + + # Add dispersion formula for HOYA + dispersion = {"dispersion_formula": "HOYA"} + glass["dispersion"] = dispersion + glasses.append(glass) except (ValueError, IndexError, TypeError): continue @@ -71,6 +336,16 @@ def convert_cdgm_excel(excel_path): "vd": float(row.iloc[23]), # Abbe number "manufacturer": "CDGM" } + + # Add glass code (column 1: Code) + glass_code = extract_glass_code(row.iloc[1], "CDGM") + if glass_code: + glass["glass_code"] = glass_code + + # Add dispersion formula for CDGM + dispersion = {"dispersion_formula": "CDGM"} + glass["dispersion"] = dispersion + glasses.append(glass) except (ValueError, IndexError, TypeError): continue @@ -93,6 +368,16 @@ def convert_ohara_excel(excel_path): "vd": float(row.iloc[24]), # vd column (first valid Abbe number) "manufacturer": "Ohara" } + + # Add glass code (column 2: Code(d)) + glass_code = extract_glass_code(row.iloc[2], "Ohara") + if glass_code: + glass["glass_code"] = glass_code + + # Add dispersion formula for Ohara + dispersion = {"dispersion_formula": "Ohara"} + glass["dispersion"] = dispersion + glasses.append(glass) except (ValueError, IndexError, TypeError): continue -- cgit v1.2.3