# -*- coding: UTF-8 -*-
'''
@Project ：45-Excel嵌入圖片提取 
@File    ：通用版本.py
@IDE     ：PyCharm 
@Author  ：一晌小貪歡（278865463@qq.com）
@Date    ：2025/3/18 20:49 
'''
import pandas as pd
import os
import re
import xmltodict
import shutil
import tempfile


class ExcelImageProcessor:
    def __init__(self, excel_path, id_column, image_column):
        """
        初始化處理器
        Args:
            excel_path: Excel文件路徑
            id_column: 用于重命名的依據(jù)列名
            image_column: 包含圖片的列名
        """
        self.excel_path = excel_path
        self.id_column = id_column
        self.image_column = image_column
        self.temp_dir = None
        self.extract_dir = None
        self.output_dir = "圖片輸出"

    def create_temp_workspace(self):
        """創(chuàng)建臨時(shí)工作空間并復(fù)制Excel文件"""
        self.temp_dir = tempfile.mkdtemp()

        # 復(fù)制Excel文件到臨時(shí)目錄
        excel_name = os.path.basename(self.excel_path)
        temp_excel = os.path.join(self.temp_dir, excel_name)
        shutil.copy2(self.excel_path, temp_excel)

        # 創(chuàng)建解壓目錄
        self.extract_dir = os.path.join(self.temp_dir, 'extracted')
        os.makedirs(self.extract_dir)

        # 復(fù)制Excel為zip并解壓
        zip_path = os.path.join(self.temp_dir, 'temp.zip')
        shutil.copy2(temp_excel, zip_path)
        shutil.unpack_archive(zip_path, self.extract_dir, 'zip')

    def cleanup_temp_workspace(self):
        """清理臨時(shí)工作空間"""
        if self.temp_dir and os.path.exists(self.temp_dir):
            shutil.rmtree(self.temp_dir)

    def extract_image_id(self, text):
        """從字符串中提取圖片ID"""
        match = re.search(r'ID_[A-F0-9]+', text)
        return match.group() if match else None

    def get_cell_image_mapping(self, sheet_xml_path):
        """
        從sheet.xml文件中提取單元格和圖片ID的映射關(guān)系
        Args:
            sheet_xml_path: sheet.xml文件路徑
        Returns:
            dict: 單元格位置和圖片ID的映射字典
        """
        cell_image_dict = {}

        # 檢查文件是否存在
        if not os.path.exists(sheet_xml_path):
            return cell_image_dict

        # 讀取并解析XML文件
        with open(sheet_xml_path, 'r', encoding='utf-8') as file:
            xml_content = file.read()
            sheet_dict = xmltodict.parse(xml_content)

        # 遍歷worksheet中的sheetData下的row數(shù)據(jù)
        for row in sheet_dict['worksheet']['sheetData']['row']:
            # 遍歷每行中的單元格數(shù)據(jù)
            if 'c' in row:
                for cell in row['c']:
                    # 檢查單元格是否包含DISPIMG函數(shù)
                    if 'v' in cell and 'DISPIMG' in cell['v']:
                        cell_pos = cell['@r']
                        image_id = self.extract_image_id(cell['v'])
                        if image_id:
                            cell_image_dict[cell_pos] = image_id

        return cell_image_dict

    def get_image_rId_mapping(self, cellimages_xml):
        """
        從cellimages.xml文件中提取圖片ID和rId的映射關(guān)系
        Args:
            cellimages_xml: cellimages.xml文件路徑
        Returns:
            dict: 圖片ID和rId的映射字典
        """
        image_rId = {}

        # 讀取并解析XML文件
        with open(cellimages_xml, 'r', encoding='utf-8') as file:
            xml_content = file.read()
            cellimages_dict = xmltodict.parse(xml_content)

        # 遍歷cellimages_dict中的圖片數(shù)據(jù)
        for image in cellimages_dict['etc:cellImages']['etc:cellImage']:
            image_id = image['xdr:pic']['xdr:nvPicPr']['xdr:cNvPr']['@name']
            r_id = image['xdr:pic']['xdr:blipFill']['a:blip']['@r:embed']
            image_rId[image_id] = r_id

        return image_rId

    def get_cellimages_rels(self, cellimages_rels_xml):
        """
        從cellimages.xml.rels文件中讀取并解析關(guān)系映射
        Args:
            cellimages_rels_xml: cellimages.xml.rels文件路徑
        Returns:
            dict: rId和目標(biāo)文件的映射字典
        """
        rels_dict = {}

        # 讀取并解析XML文件
        with open(cellimages_rels_xml, 'r', encoding='utf-8') as file:
            xml_content = file.read()
            rels = xmltodict.parse(xml_content)

        # 遍歷Relationships中的Relationship數(shù)據(jù)
        for rel in rels['Relationships']['Relationship']:
            r_id = rel['@Id']
            # 確保路徑以 media/ 開頭
            target = rel['@Target']
            if not target.startswith('media/'):
                target = f"media/{target}"
            rels_dict[r_id] = target

        return rels_dict

    def process_excel(self):
        """處理Excel文件并提取圖片映射關(guān)系"""
        # 創(chuàng)建臨時(shí)工作空間
        self.create_temp_workspace()

        # 構(gòu)建解壓后的文件路徑
        sheet_xml = os.path.join(self.extract_dir, "xl/worksheets/sheet1.xml")
        cellimages_xml = os.path.join(self.extract_dir, "xl/cellimages.xml")
        rels_xml = os.path.join(self.extract_dir, "xl/_rels/cellimages.xml.rels")

        # 獲取各層映射
        cell_to_id = self.get_cell_image_mapping(sheet_xml)
        id_to_rid = self.get_image_rId_mapping(cellimages_xml)
        rid_to_file = self.get_cellimages_rels(rels_xml)

        # 構(gòu)建單元格到文件位置的直接映射
        cell_to_file = {}
        for cell, image_id in cell_to_id.items():
            rid = id_to_rid[image_id]
            file_path = rid_to_file[rid]
            cell_to_file[cell] = file_path

        # 讀取Excel文件
        df = pd.read_excel(self.excel_path)

        return df, cell_to_file

    def copy_and_rename_images(self, df, cell_to_file):
        """
        復(fù)制并重命名圖片
        Args:
            df: DataFrame對(duì)象
            cell_to_file: 單元格到文件路徑的映射
        """
        # 創(chuàng)建輸出目錄
        if not os.path.exists(self.output_dir):
            os.makedirs(self.output_dir)

        # 獲取圖片列的列號(hào)
        image_column_idx = None
        for idx, col in enumerate(df.columns):
            if col == self.image_column:
                image_column_idx = idx
                break
        column_letter = chr(65 + image_column_idx)

        # 處理每一行
        for index, row in df.iterrows():
            id_value = row[self.id_column]
            cell_ref = f"{column_letter}{index + 2}"

            if cell_ref in cell_to_file:
                # 獲取源圖片路徑
                image_rel_path = cell_to_file[cell_ref]
                # 修正圖片路徑，確保包含完整的xl路徑
                image_path = os.path.join(self.extract_dir, "xl", image_rel_path)

                # 獲取文件擴(kuò)展名
                _, ext = os.path.splitext(image_rel_path)

                # 構(gòu)建新的文件名和路徑
                new_filename = f"{id_value}{ext}"
                new_path = os.path.join(self.output_dir, new_filename)

                # 復(fù)制并重命名圖片
                if os.path.exists(image_path):
                    shutil.copy2(image_path, new_path)
                else:
                    print(f"警告: 找不到圖片文件 {image_path}")

    def process(self):
        """處理主流程"""
        try:
            # 處理Excel獲取映射關(guān)系
            df, cell_to_file = self.process_excel()

            # 復(fù)制并重命名圖片
            self.copy_and_rename_images(df, cell_to_file)

            print(f"處理完成！圖片已保存到 {self.output_dir} 目錄")

        except Exception as e:
            print(f"處理過(guò)程中出現(xiàn)錯(cuò)誤: {str(e)}")
            raise
        finally:
            # 在所有操作完成后再清理臨時(shí)目錄
            self.cleanup_temp_workspace()


def main():
    # 使用示例
    """
        傳入 Excel 路徑、命名列、圖片列 三個(gè)參數(shù)
        自動(dòng)根據(jù)順序命名圖片，并導(dǎo)出到 “圖片輸出” 文件夾
    """
    if not os.path.exists('./圖片輸出/'):
        os.makedirs('./圖片輸出/')
    excel_path = "./數(shù)據(jù)源/" + os.listdir('./數(shù)據(jù)源/')[0]
    processor = ExcelImageProcessor(
        excel_path=excel_path,
        id_column="訂單號(hào)",
        image_column="圖片"
    )
    processor.process()


if __name__ == "__main__":
    main()