import pandas as pd
import json
import os
from openpyxl import load_workbook
from openpyxl.drawing.image import Image as OpenpyxlImage

excel_path = r'f:\laravel\job\abality - Copy\public\ABILITY_PRICE_LIST_2026-1.xlsx'
output_img_dir = r'f:\laravel\job\abality - Copy\public\Images\extracted'
os.makedirs(output_img_dir, exist_ok=True)

wb = load_workbook(excel_path)
ws = wb.active

extracted_images = []

# Try to find images in the workbook
if hasattr(ws, '_images'):
    for i, img in enumerate(ws._images):
        try:
            # Get position
            row = img.anchor._from.row
            col = img.anchor._from.col
            
            # Save image
            img_filename = f"row_{row}_col_{col}_{i}.png"
            img_path = os.path.join(output_img_dir, img_filename)
            
            # Save the image data
            # Note: img.ref is the image data
            with open(img_path, "wb") as f:
                f.write(img.ref.read())
            
            extracted_images.append({
                "row": row,
                "col": col,
                "filename": img_filename
            })
        except Exception as e:
            print(f"Error extracting image {i}: {e}")

df = pd.read_excel(excel_path, engine='openpyxl')

# Map images to rows
# Row in openpyxl is 0-indexed if using _from.row (verify this)
# Excel data in pandas starts after header. 
# Row 0 in Excel is headers (usually). 
# Row 1 is data.

with open(r'f:\laravel\job\abality - Copy\scratch\image_map.json', 'w', encoding='utf-8') as f:
    json.dump(extracted_images, f, ensure_ascii=False, indent=4)

print(f"Extracted {len(extracted_images)} images.")