CameraMachine/python/service/excel_base_func.py

from PySide6.QtWidgets import QApplication
from PIL import Image
import settings
import time
import os

def get_clip_image(image_path):
    try:
        cb = QApplication.clipboard()
        if cb.mimeData().hasImage():
            qt_img = cb.image()
            pil_img = Image.fromqimage(qt_img)  # 转换为PIL图像
            if pil_img.width > 10:
                pil_img = pil_img.convert("RGB")
                pil_img.save(image_path)
                print("图片剪切板保存成功：{}".format(image_path))

            return True
        return False
    except BaseException as e:
        print(e)
        settings.logger.info("获取剪切板图片异常：{}".format(e))
        return False


def get_one_cell_shape_image(shape, image_out_path):
    try:
        flag = True
        _address = shape.TopLeftCell.Address
        print("_address", _address)  # $E$2
        shape.LockAspectRatio = True
        old_w = shape.Width
        shape.Width = 500
        print("shape.Copy")
        shape.Copy()

        time.sleep(0.1)

        if not get_clip_image(image_out_path):
            flag = False
        shape.Width = old_w
    except BaseException as e:
        print("get_one_cell_shape_image", e)
        return False

    return flag


def add_pic_with_wps(sheet, row, column, image_path, pic_w=35):
    cell = sheet.Cells(row, column)
    cell_value = cell.Value
    if cell_value:
        if "DISPIMG" in cell_value:
            print("已有图片")
            return
    cell.ColumnWidth = 10
    cell.RowHeight = 42
    im = Image.open(image_path)
    w = pic_w
    h = int((im.height * w) / im.width)

    # pic = sheet.Shapes.AddPicture(FileName=image_path, LinkToFile=False, SaveWithDocument=True,
    #                               Left=cell.Left + 2, Top=cell.Top + 2, Width=-1, Height=-1)
    # https://learn.microsoft.com/zh-tw/office/vba/api/excel.shapes.addpicture
    pic = sheet.Shapes.AddPicture(image_path, 0, 1, cell.Left + 2, cell.Top + 2, w, h)
    pic.LockAspectRatio = True
    pic.Placement = 1  # 随单元格大小变化
    # os.remove(image_path)
    # r = pic.ShapeRange
    # r.LockAspectRatio = True


def add_pic_with_office(sheet, row, column, image_path, pic_w=35):
    # self.add_pic_with_wps(sheet, row, column, image_path, pic_w=35)
    # return
    cell = sheet.Cells(row, column)
    cell_value = cell.Value
    if cell_value:
        if "DISPIMG" in cell_value:
            print("已有图片")
            return

    cell.ColumnWidth = 10
    cell.RowHeight = 42

    im = Image.open(image_path)
    w = pic_w
    h = int((im.height * w) / im.width)

    pic_shape = sheet.Shapes.AddShape(1, cell.Left + 2, cell.Top + 2, w, h)
    pic_shape.Fill.UserPicture(image_path)
    pic_shape.Line.Weight = 0
    pic_shape.Placement = 1  # 随单元格大小变化


def get_all_row_sheet_images(sheet):
    """
    返回每行中是否存在形状，第二行为index=0
    """
    row_address_data_dict = {}

    for i, shape in enumerate(sheet.Shapes):
        try:
            _address = shape.TopLeftCell.Address
            _row = 0
            if isinstance(_address, str):
                _row = _address.split("$")[2]
                _row = int(_row) - 2
            if _row == -1:
                break
            row_address_data_dict[_row] = shape
        except BaseException as e:
            print(e)
            break

    return row_address_data_dict


def save_log_text():
    # 移除30条以外的文件
    f_list = []
    for new_file_name in os.listdir(r"log\upload_log"):
        _file_path = "{}\{}".format(r"log\upload_log", new_file_name)
        f_list.append({"file_name": new_file_name,
                       "file_path": _file_path,
                       "create_time": os.path.getctime(_file_path)
                       })

    if f_list:
        f_list.sort(key=lambda x: x["create_time"], reverse=True)
        while 1:
            if len(f_list) > 30:
                del_file_dict = f_list.pop()
                del_file_path = "{}\{}".format(os.getcwd(), del_file_dict["file_path"])
                os.remove(del_file_path)
            else:
                break
    pass