import urllib.request import json import concurrent.futures import time import re from openpyxl import Workbook from openpyxl.styles import Font, PatternFill, Alignment, Border, Side from openpyxl.utils import get_column_letter API_URL = "https://ready-api.vercel.app/api/drugs-eg" LIMIT = 100 MAX_WORKERS = 10 # 10 parallel threads to fetch pages # Regex to strip control characters that are illegal in Excel worksheets ILLEGAL_CHARACTERS_RE = re.compile( r'[\000-\010]|[\013-\014]|[\016-\037]|[\x00-\x08]|[\x0b-\x0c]|[\x0e-\x1f]' ) def clean_value(val): if isinstance(val, str): return ILLEGAL_CHARACTERS_RE.sub("", val) return val def fetch_page(page_num): url = f"{API_URL}?page={page_num}&limit={LIMIT}" retries = 3 for attempt in range(retries): try: req = urllib.request.Request( url, headers={'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64)'} ) with urllib.request.urlopen(req, timeout=15) as response: res_data = json.loads(response.read().decode('utf-8')) drugs = res_data.get('data', []) print(f"[+] Page {page_num} fetched successfully: {len(drugs)} drugs.") return drugs except Exception as e: print(f"[!] Error fetching page {page_num} (attempt {attempt+1}/{retries}): {e}") if attempt < retries - 1: time.sleep(1.5) else: return [] def main(): print("=== Starting Drugs Downloader ===") # 1. Fetch first page to get total number of items print("Fetching page 1 to check total count...") first_page_drugs = fetch_page(1) if not first_page_drugs: print("[-] Failed to fetch initial page. Exiting.") return # Get total and calculate total pages # Note: we need to make another fetch or reuse the first page, but the API response contains pagination metadata. # Let's get total pages. url = f"{API_URL}?page=1&limit={LIMIT}" try: req = urllib.request.Request(url, headers={'User-Agent': 'Mozilla/5.0'}) with urllib.request.urlopen(req) as response: meta = json.loads(response.read().decode('utf-8')).get('pagination', {}) total_items = meta.get('total', 24868) total_pages = meta.get('totalPages', 249) except Exception as e: print(f"[-] Error parsing metadata: {e}. Using defaults.") total_items = 24868 total_pages = 249 print(f"[i] Total drugs to download: {total_items}") print(f"[i] Total pages to fetch: {total_pages}") all_drugs = [] # Add first page drugs all_drugs.extend(first_page_drugs) # Remaining pages pages_to_fetch = list(range(2, total_pages + 1)) # Fetch in parallel print(f"Fetching remaining {len(pages_to_fetch)} pages in parallel using {MAX_WORKERS} workers...") start_time = time.time() with concurrent.futures.ThreadPoolExecutor(max_workers=MAX_WORKERS) as executor: results = executor.map(fetch_page, pages_to_fetch) for page_drugs in results: if page_drugs: all_drugs.extend(page_drugs) end_time = time.time() print(f"[+] Download complete! Mapped {len(all_drugs)} drugs in {end_time - start_time:.2f} seconds.") # Remove duplicates if any (by commercial_name_en) seen = set() unique_drugs = [] for d in all_drugs: name_en = d.get('commercial_name_en', '') if name_en not in seen: seen.add(name_en) unique_drugs.append(d) print(f"[i] Filtered to {len(unique_drugs)} unique drugs (removed {len(all_drugs) - len(unique_drugs)} duplicates).") # 2. Save to Styled Excel Workbook print("Writing to Excel workbook...") wb = Workbook() ws = wb.active ws.title = "أدوية مصر (Drugs EG)" # Set RTL layout ws.views.sheetView[0].showGridLines = True ws.sheet_view.rightToLeft = True # Define headers (Arabic and English) headers = [ "الاسم التجاري (إنجليزي)", "الاسم التجاري (عربي)", "الاسم العلمي (المادة الفعالة)", "الشركة المصنعة", "الفئة الدوائية", "طريقة الاستخدام", "السعر الحالي (ج.م.)" ] ws.append(headers) # Styles teal_fill = PatternFill(start_color="0D9488", end_color="0D9488", fill_type="solid") white_bold_font = Font(name="Calibri", size=11, bold=True, color="FFFFFF") regular_font = Font(name="Calibri", size=11) bold_mono_font = Font(name="Calibri", size=11, bold=True) center_align = Alignment(horizontal="center", vertical="center", wrap_text=True) right_align = Alignment(horizontal="right", vertical="center", wrap_text=True) left_align = Alignment(horizontal="left", vertical="center", wrap_text=True) thin_border_side = Side(style='thin', color='E5E7EB') thin_border = Border(left=thin_border_side, right=thin_border_side, top=thin_border_side, bottom=thin_border_side) # Apply Header Styling ws.row_dimensions[1].height = 28 for col_idx in range(1, len(headers) + 1): cell = ws.cell(row=1, column=col_idx) cell.fill = teal_fill cell.font = white_bold_font cell.alignment = center_align cell.border = thin_border # Append data rows row_count = 2 for drug in unique_drugs: row_data = [ clean_value(drug.get('commercial_name_en', '')), clean_value(drug.get('commercial_name_ar', '')), clean_value(drug.get('scientific_name', '')), clean_value(drug.get('manufacturer', '')), clean_value(drug.get('drug_class', '')), clean_value(drug.get('route', '')), drug.get('price_egp') ] ws.append(row_data) ws.row_dimensions[row_count].height = 20 # Apply styles to cell for col_idx in range(1, len(row_data) + 1): cell = ws.cell(row=row_count, column=col_idx) cell.font = regular_font cell.border = thin_border # Alignments & formatting based on column if col_idx in [1, 3, 4, 5, 6]: # English fields cell.alignment = left_align elif col_idx == 2: # Arabic Name cell.alignment = right_align elif col_idx == 7: # Price cell.alignment = center_align cell.font = bold_mono_font cell.number_format = '#,##0.00" ج.م."' row_count += 1 # Auto-fit column widths for col in ws.columns: max_len = 0 col_letter = get_column_letter(col[0].column) for cell in col: val = str(cell.value or '') # Handle arabic characters representation length if cell.row == 1: val_len = len(val) * 1.5 else: val_len = len(val) if val_len > max_len: max_len = val_len ws.column_dimensions[col_letter].width = min(max(max_len + 3, 12), 45) filename = "drugs_egypt.xlsx" wb.save(filename) print(f"[+] Saved successfully to: {filename}") print("=== Finished Downloader successfully ===") if __name__ == "__main__": main()