excel_to_json.py

"""
读取 data_merged.xlsx 文件，提取数据并保存为JSON，同时提取WPS嵌入图片。

WPS嵌入图片的特殊处理：
- xlsx本质是zip包
- 通过 xl/cellimages.xml 获取 DISPIMG_id 与 rId 的映射
- 通过 xl/_rels/cellimages.xml.rels 获取 rId 与图片路径的映射
- 组合映射后从 xl/media/ 提取图片

输出格式：
- 序号：从00001开始的递增数列
- 图片：以对应序号命名（如序号14350对应图片14350.png）
- 修改后图片：序号_modified命名（如14350_modified.png）
- 图片字段：直接使用字段名存储文件名
"""

import zipfile
import os
import json
import xml.etree.ElementTree as ET
import openpyxl
from pathlib import Path
from datetime import datetime, time, date


def convert_value_to_json_serializable(value):
    """将值转换为JSON可序列化的格式"""
    if isinstance(value, datetime):
        return value.isoformat()
    if isinstance(value, time):
        return value.isoformat()
    if isinstance(value, date):
        return value.isoformat()
    if value is None:
        return None
    return value


def read_excel_data(filename_path):
    """
    读取Excel数据，提取DISPIMG_id
    
    Returns:
        tuple: (data列表, image_info列表, headers列表)
            - data: 数据列表
            - image_info: [(行索引, 列名, 图片ID), ...] 记录每行的图片ID
            - headers: 表头列表
    """
    workbook = openpyxl.load_workbook(filename_path, data_only=False)
    sheet = workbook.active
    
    data = []
    image_info = []  # [(行索引, 列名, 图片ID), ...]
    headers = []
    
    for row_idx, row in enumerate(sheet.iter_rows(min_row=1, values_only=False)):
        row_data = {}
        for col_idx, cell in enumerate(row):
            # 第一行作为表头
            if row_idx == 0:
                header_name = cell.value if cell.value else f"column_{col_idx}"
                headers.append(header_name)
                continue
            
            # 处理数据行
            header = headers[col_idx] if col_idx < len(headers) else f"column_{col_idx}"
            
            if cell.value and isinstance(cell.value, str) and 'DISPIMG' in cell.value:
                # 提取嵌入的图片ID
                # 格式: =DISPIMG("ID_xxx",1) 或 =_xlfn.DISPIMG("ID_xxx",1)
                formula = cell.value
                start = formula.find('"') + 1
                end = formula.find('"', start)
                if start > 0 and end > start:
                    image_id = formula[start:end]
                    # 记录图片ID，稍后会替换为文件名
                    row_data[header] = image_id
                    image_info.append((row_idx - 1, header, image_id))  # row_idx - 1 是数据行索引（从0开始）
                else:
                    row_data[header] = cell.value
            else:
                row_data[header] = convert_value_to_json_serializable(cell.value)
        
        if row_idx > 0:  # 跳过表头行
            data.append(row_data)
    
    return data, image_info, headers


def get_xml_id_image_map(xlsx_file_path):
    """
    解析xlsx中的XML文件，建立DISPIMG_id到图片路径的映射
    
    Returns:
        dict: {DISPIMG_id: image_path}
    """
    try:
        with zipfile.ZipFile(xlsx_file_path, 'r') as zfile:
            # 检查必要的XML文件是否存在
            file_list = zfile.namelist()
            
            if 'xl/cellimages.xml' not in file_list:
                print("警告: xl/cellimages.xml 不存在，可能没有嵌入图片")
                return {}
            
            if 'xl/_rels/cellimages.xml.rels' not in file_list:
                print("警告: xl/_rels/cellimages.xml.rels 不存在")
                return {}
            
            with zfile.open('xl/cellimages.xml') as file:
                xml_content = file.read()
            with zfile.open('xl/_rels/cellimages.xml.rels') as file:
                relxml_content = file.read()
    except Exception as e:
        print(f"读取XML文件失败: {e}")
        return {}
    
    # 解析 cellimages.xml
    root = ET.fromstring(xml_content)
    
    # 命名空间
    namespaces = {
        'xdr': 'http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing',
        'a': 'http://schemas.openxmlformats.org/drawingml/2006/main'
    }
    
    # 建立 name -> embed(rId) 映射
    name_to_embed_map = {}
    for pic in root.findall('.//xdr:pic', namespaces=namespaces):
        nvPicPr = pic.find('.//xdr:nvPicPr', namespaces=namespaces)
        if nvPicPr is not None:
            cNvPr = nvPicPr.find('.//xdr:cNvPr', namespaces=namespaces)
            if cNvPr is not None:
                name = cNvPr.attrib.get('name', '')
                blip = pic.find('.//a:blip', namespaces=namespaces)
                if blip is not None:
                    embed = blip.attrib.get('{http://schemas.openxmlformats.org/officeDocument/2006/relationships}embed', '')
                    if name and embed:
                        name_to_embed_map[name] = embed
    
    # 解析 cellimages.xml.rels
    root1 = ET.fromstring(relxml_content)
    namespaces_rels = {'r': 'http://schemas.openxmlformats.org/package/2006/relationships'}
    
    # 建立 rId -> Target 映射
    id_target_map = {}
    for child in root1.findall('.//r:Relationship', namespaces=namespaces_rels):
        rel_id = child.attrib.get('Id', '')
        target = child.attrib.get('Target', '')
        if rel_id and target:
            id_target_map[rel_id] = target
    
    # 组合映射: name -> target
    name_to_target_map = {}
    for name, embed in name_to_embed_map.items():
        if embed in id_target_map:
            name_to_target_map[name] = id_target_map[embed]
    
    return name_to_target_map


def extract_images_with_sequence(xlsx_file_path, image_info, name_to_target_map, output_dir):
    """
    从xlsx中提取图片并保存，使用数据序号命名
    
    Args:
        xlsx_file_path: xlsx文件路径
        image_info: [(行索引, 列名, 图片ID), ...]
        name_to_target_map: {DISPIMG_id: image_path}
        output_dir: 输出目录
    
    Returns:
        dict: {(行索引, 列名): 保存的图片文件名}
    """
    saved_images = {}  # {(行索引, 列名): 图片文件名}
    images_dir = os.path.join(output_dir, 'images')
    os.makedirs(images_dir, exist_ok=True)
    
    with zipfile.ZipFile(xlsx_file_path, 'r') as zfile:
        file_list = zfile.namelist()
        
        for row_idx, col_name, image_id in image_info:
            if image_id not in name_to_target_map:
                print(f"警告: 图片ID {image_id} 未找到对应图片")
                continue
            
            image_path = name_to_target_map[image_id]
            actual_image_path = f'xl/{image_path}'
            
            if actual_image_path not in file_list:
                print(f"警告: 图片文件不存在: {actual_image_path}")
                continue
            
            # 获取数据行的序号（5位数字格式）
            seq_str = f"{row_idx + 1:05d}"
            
            # 获取文件扩展名
            ext = os.path.splitext(image_path)[1]
            if not ext:
                ext = '.png'
            
            # 根据列名决定命名方式
            if col_name == "修改后图片":
                # 修改后图片：序号_modified
                new_filename = f"{seq_str}_modified{ext}"
            else:
                # 普通图片：序号
                new_filename = f"{seq_str}{ext}"
            
            new_file_path = os.path.join(images_dir, new_filename)
            
            # 保存图片
            with zfile.open(actual_image_path) as image_file:
                image_content = image_file.read()
                with open(new_file_path, 'wb') as new_file:
                    new_file.write(image_content)
            
            saved_images[(row_idx, col_name)] = new_filename
            print(f"已保存图片: {new_filename}")
    
    return saved_images


def process_excel_to_json(xlsx_path, output_dir):
    """
    主处理函数：读取Excel，提取数据和图片，保存为JSON
    """
    # 确保输出目录存在
    os.makedirs(output_dir, exist_ok=True)
    
    print(f"正在读取Excel文件: {xlsx_path}")
    
    # 1. 读取Excel数据
    data, image_info, headers = read_excel_data(xlsx_path)
    print(f"读取到 {len(data)} 条数据, {len(image_info)} 个图片")
    
    # 2. 获取图片ID映射
    print("正在解析图片映射关系...")
    name_to_target_map = get_xml_id_image_map(xlsx_path)
    print(f"找到 {len(name_to_target_map)} 个图片映射")
    
    # 3. 提取图片
    print("正在提取图片...")
    saved_images = extract_images_with_sequence(xlsx_path, image_info, name_to_target_map, output_dir)
    print(f"成功提取 {len(saved_images)} 张图片")
    
    # 4. 更新数据中的序号和图片字段
    for idx, row in enumerate(data):
        # 设置序号（5位数字格式）
        row['序号'] = f"{idx + 1:05d}"
        
        # 更新图片字段：将图片ID替换为图片文件名
        for (row_idx, col_name), filename in saved_images.items():
            if row_idx == idx:
                row[col_name] = filename
    
    # 5. 保存为JSON
    json_path = os.path.join(output_dir, 'data.json')
    with open(json_path, 'w', encoding='utf-8') as f:
        json.dump(data, f, ensure_ascii=False, indent=2)
    print(f"数据已保存到: {json_path}")
    
    return data, saved_images


if __name__ == '__main__':
    # 配置路径
    project_root = Path(__file__).parent
    xlsx_path = project_root / 'data' / 'data_merged.xlsx'
    output_dir = project_root / 'output'
    
    # 执行处理
    data, images = process_excel_to_json(str(xlsx_path), str(output_dir))
    
    print(f"\n处理完成!")
    print(f"- 数据条数: {len(data)}")
    print(f"- 图片数量: {len(images)}")
    print(f"- JSON文件: {output_dir / 'data.json'}")
    print(f"- 图片目录: {output_dir / 'images'}")