import openpyxl
from PIL import Image as PILImage
import io
import os


def extract_images_from_excel(excel_path, output_dir=None):
    """
    按行提取 Excel 中的圖片，存儲到以 Sheet 名稱命名的文件夾下，
    圖片命名為該行第一個字段的值（即A列），保持原始格式。
    """
    if output_dir is None:
        output_dir = os.path.dirname(os.path.abspath(excel_path))

    os.makedirs(output_dir, exist_ok=True)

    wb = openpyxl.load_workbook(excel_path)
    extracted_count = 0

    for sheet_name in wb.sheetnames:
        ws = wb[sheet_name]
        print(f"處理工作表: {sheet_name}")

        safe_sheet_name = "".join(
            c for c in sheet_name if c not in r'\/:*?"<>|'
        ).strip()
        sheet_dir = os.path.join(output_dir, safe_sheet_name)
        os.makedirs(sheet_dir, exist_ok=True)

        # 收集每行A列的值
        row_first_cell = {}
        for row in range(1, ws.max_row + 1):
            cell_value = ws.cell(row=row, column=1).value
            if cell_value is not None:
                row_first_cell[row] = str(cell_value)

        for image in ws._images:
            anchor = image.anchor
            if hasattr(anchor, '_from'):
                row_num = anchor._from.row + 1
            elif hasattr(anchor, 'row'):
                row_num = anchor.row + 1
            else:
                print(f"  ?? 無法確定圖片行號，跳過")
                continue

            first_cell_value = row_first_cell.get(row_num, None)
            if first_cell_value is None:
                print(f"  ?? 第{row_num}行第一個字段為空，跳過")
                continue

            safe_name = "".join(
                c for c in first_cell_value if c not in r'\/:*?"<>|'
            ).strip()
            if not safe_name:
                safe_name = f"row_{row_num}"

            img_data = image._data()
            img = PILImage.open(io.BytesIO(img_data))

            # 獲取原始格式
            img_format = img.format.lower() if img.format else 'png'

            filename = f"{safe_name}.{img_format}"
            filepath = os.path.join(sheet_dir, filename)

            counter = 1
            base_name = safe_name
            while os.path.exists(filepath):
                filename = f"{base_name}_{counter}.{img_format}"
                filepath = os.path.join(sheet_dir, filename)
                counter += 1

            img.save(filepath)
            extracted_count += 1
            print(f"  ? 第{row_num}行 -> {safe_sheet_name}/{filename}")

    print(f"\n?? 完成！共提取 {extracted_count} 張圖片，保存至: {output_dir}")


if __name__ == '__main__':
    excel_path = 'cards.xlsx'   # 修改為你的文件
    extract_images_from_excel(excel_path, output_dir='images')

import openpyxl
from PIL import Image as PILImage
import io
import os


def extract_images_as_jpg(excel_path, output_dir=None, jpg_quality=85):
    """
    按行提取 Excel 中的圖片，統(tǒng)一轉(zhuǎn)換為 JPG 格式存儲。
    參數(shù):
        excel_path: Excel 文件路徑
        output_dir: 輸出根目錄
        jpg_quality: JPG 壓縮質(zhì)量 (1-100)，默認(rèn)85
    """
    if output_dir is None:
        output_dir = os.path.dirname(os.path.abspath(excel_path))

    os.makedirs(output_dir, exist_ok=True)

    wb = openpyxl.load_workbook(excel_path)
    extracted_count = 0

    for sheet_name in wb.sheetnames:
        ws = wb[sheet_name]
        print(f"處理工作表: {sheet_name}")

        safe_sheet_name = "".join(
            c for c in sheet_name if c not in r'\/:*?"<>|'
        ).strip()
        sheet_dir = os.path.join(output_dir, safe_sheet_name)
        os.makedirs(sheet_dir, exist_ok=True)

        # 收集每行A列的值
        row_first_cell = {}
        for row in range(1, ws.max_row + 1):
            cell_value = ws.cell(row=row, column=1).value
            if cell_value is not None:
                row_first_cell[row] = str(cell_value)

        for image in ws._images:
            anchor = image.anchor
            if hasattr(anchor, '_from'):
                row_num = anchor._from.row + 1
            elif hasattr(anchor, 'row'):
                row_num = anchor.row + 1
            else:
                print(f"  ?? 無法確定圖片行號，跳過")
                continue

            first_cell_value = row_first_cell.get(row_num, None)
            if first_cell_value is None:
                print(f"  ?? 第{row_num}行第一個字段為空，跳過")
                continue

            safe_name = "".join(
                c for c in first_cell_value if c not in r'\/:*?"<>|'
            ).strip()
            if not safe_name:
                safe_name = f"row_{row_num}"

            img_data = image._data()
            img = PILImage.open(io.BytesIO(img_data))

            # ----- 轉(zhuǎn)換為 JPG 的關(guān)鍵代碼 -----
            # 若圖片為 RGBA（帶透明通道），需轉(zhuǎn)換為 RGB（白色背景）
            if img.mode in ('RGBA', 'LA', 'P'):
                # 創(chuàng)建白色背景
                background = PILImage.new('RGB', img.size, (255, 255, 255))
                # 將原圖粘貼到背景（使用透明通道作為掩碼）
                if img.mode == 'P':
                    img = img.convert('RGBA')
                background.paste(img, mask=img.split()[-1] if img.mode == 'RGBA' else None)
                img = background
            elif img.mode != 'RGB':
                img = img.convert('RGB')
            # -----------------------------

            filename = f"{safe_name}.jpg"
            filepath = os.path.join(sheet_dir, filename)

            counter = 1
            base_name = safe_name
            while os.path.exists(filepath):
                filename = f"{base_name}_{counter}.jpg"
                filepath = os.path.join(sheet_dir, filename)
                counter += 1

            # 保存為 JPG，可指定質(zhì)量
            img.save(filepath, 'JPEG', quality=jpg_quality)
            extracted_count += 1
            print(f"  ? 第{row_num}行 -> {safe_sheet_name}/{filename} (JPG質(zhì)量={jpg_quality})")

    print(f"\n?? 完成！共提取 {extracted_count} 張圖片，已統(tǒng)一為JPG格式，保存至: {output_dir}")


if __name__ == '__main__':
    excel_path = 'cards.xlsx'   # 修改為你的文件
    extract_images_as_jpg(excel_path, output_dir='images_jpg', jpg_quality=85)

pip install openpyxl pillow

python extract_images.py   # 或 extract_as_jpg.py