【批量办公技巧】一键对多个Excel文件盖章（覆盖图片）

import base64
import os
import logging
from openpyxl import load_workbook
from openpyxl.drawing.image import Image
from PIL import Image as PILImage
import io

from api.excel_api import ExcelAPI
from utils.pdfs import PDFUtils


class ExcelZhangService:
    """Excel盖章服务类"""
 
    def __init__(self):
        self.logger = logging.getLogger(__name__)

        self.excel_api = ExcelAPI()


    @staticmethod
    def mk_out_dir(root_directory):
        # 创建输出目录 out
        out_directory = os.path.join(root_directory, "out")
        if not os.path.exists(out_directory):
            os.makedirs(out_directory)
        # 创建错误目录 error
        error_directory = os.path.join(root_directory, "error")
        if not os.path.exists(out_directory):
            os.makedirs(out_directory)

        return out_directory,error_directory
 
    def add_stamp_to_excel(self, output_format, excel_path, stamp_image_path, output_path=None, position=None, stamp_size_dic=None):
        """
        为Excel文件添加印章

        Args:
            output_format (str): 输出格式 ('Excel', '图片', 'PDF')
            excel_path (str): Excel文件路径
            stamp_image_path (str): 印章图片路径
            output_path (str): 输出路径
            position (dict): 位置信息，格式为:
                - 坐标模式: {'mode': 'coordinate', 'x': 100, 'y': 100}
                - 单元格模式: {'mode': 'cell', 'cell': 'D2', 'x_offset': 0, 'y_offset': 0}
            stamp_size (dict): 印章尺寸信息
        """
        stamp_size = (  stamp_size_dic.get('width') ,  stamp_size_dic.get('height'))
        cell_address = None
        pixel_x = None
        pixel_y = None
        x_offset = 0
        y_offset = 0
 
        # 处理位置参数兼容性
        if position.get('mode') == 'coordinate':
            # 坐标模式，直接返回像素坐标
            pixel_x = position.get('x')
            pixel_y = position.get('y')

        elif position.get('mode') == 'cell':
            # 单元格模式，需要转换为像素坐标
            cell_address = position.get('cell')
            # 获取偏移量
            x_offset = position.get('x_offset', 0)
            y_offset = position.get('y_offset', 0)
            cell_address = cell_address + "&("+str(x_offset) +","+str(y_offset)+")"

        if output_format == '图片':
            res = self.excel_api.add_img(excel_path, stamp_image_path, stamp_size, pixel_x, pixel_y, cell_address, x_offset, y_offset)
            if res.get('code') != 0:
                raise RuntimeError(res.get('msg'))
            ## 成功
            base64_str = res['data']
            imgdata = base64.b64decode(base64_str)
            base_name = os.path.basename(excel_path)  # 获取文件名（含扩展名）
            filename_without_ext = os.path.splitext(base_name)[0] + ".png"
            img_path = os.path.join(output_path, filename_without_ext)
            with open(img_path, 'wb') as f:
                f.write(imgdata)
            return

        if output_format == 'PDF':
            res = self.excel_api.add_img(excel_path, stamp_image_path, stamp_size, pixel_x, pixel_y, cell_address, x_offset, y_offset)
            if res.get('code') != 0:
                raise RuntimeError(res.get('msg'))
            ## 成功
            base64_str = res['data']
            imgdata = base64.b64decode(base64_str)
            base_name = os.path.basename(excel_path)  # 获取文件名（含扩展名）
            filename_without_ext = os.path.splitext(base_name)[0] + ".pdf"
            pdf_path = os.path.join(output_path, filename_without_ext)
            PDFUtils.images_to_pdf(imgdata, pdf_path)
            return

        # 转换成 excel
        base_name = os.path.basename(excel_path)  # 获取文件名（含扩展名）
        excel_save_path = os.path.join(output_path, base_name)
        self.excel_api.add_img_to_excel(excel_path, stamp_image_path, stamp_size, pixel_x, pixel_y, excel_save_path, cell_address, x_offset, y_offset)


    def _process_stamp_image(self, image_path, stamp_size=None):
        """
        处理印章图片，确保格式兼容

        Args:
            image_path (str): 图片路径
            stamp_size (tuple): 印章尺寸 (width, height)，如果为None则使用原始尺寸

        Returns:
            io.BytesIO: 处理后的图片数据流
        """
        try:
            # 使用PIL打开图片
            pil_image = PILImage.open(image_path)

            # 转换为RGBA模式以支持透明度
            if pil_image.mode != 'RGBA':
                pil_image = pil_image.convert('RGBA')

            # 调整图片尺寸
            if stamp_size:
                pil_image = pil_image.resize(stamp_size, PILImage.Resampling.LANCZOS)

            # 将图片保存到内存中
            img_buffer = io.BytesIO()
            pil_image.save(img_buffer, format='PNG')
            img_buffer.seek(0)

            return img_buffer

        except Exception as e:
            self.logger.error(f"处理印章图片失败: {str(e)}")
            return None

    def _get_column_letter(self, col_num):
        """
        将列号转换为Excel列字母

        Args:
            col_num (int): 列号（从1开始）

        Returns:
            str: 列字母
        """
        result = ""
        while col_num > 0:
            col_num -= 1
            result = chr(col_num % 26 + ord('A')) + result
            col_num //= 26
        return result

    def batch_add_stamp(self, excel_files, stamp_image_path, output_dir=None, position=(100, 100)):
        """
        批量为Excel文件添加印章

        Args:
            excel_files (list): Excel文件路径列表
            stamp_image_path (str): 印章图片路径
            output_dir (str): 输出目录，如果为None则覆盖原文件
            position (tuple): 印章位置 (x像素, y像素)

        Returns:
            dict: 处理结果统计
        """
        results = {'success': 0, 'failed': 0, 'errors': []}

        for excel_file in excel_files:
            try:
                output_path = None
                if output_dir:
                    filename = os.path.basename(excel_file)
                    output_path = os.path.join(output_dir, filename)

                if self.add_stamp_to_excel(excel_file, stamp_image_path, output_path, position):
                    results['success'] += 1
                else:
                    results['failed'] += 1
                    results['errors'].append(f"处理失败: {excel_file}")
 
            except Exception as e:
                results['failed'] += 1
                results['errors'].append(f"处理异常 {excel_file}: {str(e)}")
                self.logger.error(f"批量处理异常: {str(e)}")
 
        return results
 
    def validate_excel_file(self, file_path):
        """
        验证Excel文件是否有效
 
        Args:
            file_path (str): 文件路径
 
        Returns:
            bool: 文件是否有效
        """
        try:
            if not os.path.exists(file_path):
                return False
 
            # 检查文件扩展名
            _, ext = os.path.splitext(file_path)
            if ext.lower() not in ['.xlsx', '.xlsm']:
                return False
 
            # 尝试加载工作簿
            workbook = load_workbook(file_path)
            workbook.close()
            return True
 
        except Exception:
            return False
 
    def validate_image_file(self, file_path):
        """
        验证图片文件是否有效
 
        Args:
            file_path (str): 文件路径
 
        Returns:
            bool: 文件是否有效
        """
        try:
            if not os.path.exists(file_path):
                return False
 
            # 检查文件扩展名
            _, ext = os.path.splitext(file_path)
            if ext.lower() not in ['.png', '.jpg', '.jpeg', '.bmp', '.gif']:
                return False
 
            # 尝试打开图片
            with PILImage.open(file_path) as img:
                img.verify()
            return True
 
        except Exception:
            return False