Python 擴充openpyxl工具“以相對位置插入圖檔并居中”

• 擴充檔案excel.py

擴充檔案excel.py

import decimal
from tempfile import NamedTemporaryFile
from openpyxl.drawing.image import Image
from openpyxl.worksheet.worksheet import Worksheet
from openpyxl.workbook import Workbook
from openpyxl.styles import Alignment
from openpyxl.styles import Font
from openpyxl.styles import Border
from openpyxl.styles import PatternFill
from openpyxl.drawing.xdr import XDRPositiveSize2D
from openpyxl.drawing.spreadsheet_drawing import AnchorMarker, OneCellAnchor
from openpyxl.utils.units import pixels_to_EMU
from openpyxl.utils.cell import get_column_letter

DEFAULT_FONT = Font()
DEFAULT_FILL = PatternFill()
DEFAULT_ALIGNMENT = Alignment()
DEFAULT_BORDER = Border()
DEFAULT_NUMBER_FORMATE = ''


class AddImage(Image):
    def __init__(self, img, max_width=None, max_height=None):
        super().__init__(img)

        self.max_width = max_width or self.height
        self.max_height = max_height or self.width
        self.r_width, self.r_height = self.height, self.width

    def image_resizer(self):
        aspect_ratio = self.r_width / self.r_height
        if aspect_ratio <= 2:
            target_size_width = self.max_height * aspect_ratio
            self.width, self.height = target_size_width, self.max_height
        else:
            target_size_height = self.max_width / aspect_ratio  # w:h     150:shr_image_height
            self.width, self.height = self.max_width, target_size_height
        return self


class EWorkbook(Workbook):

    def __init__(self, write_only=False, iso_dates=False):
        super().__init__(write_only=write_only, iso_dates=iso_dates)

    def create_sheet(self, title=None, index=None):
        new_ws = EWorksheet(parent=self, title=title)
        self._add_sheet(sheet=new_ws, index=index)
        return new_ws

    def temporary_save(self):
        with NamedTemporaryFile() as tmp:
            file_path = tmp.name + '.xlsx'
            self.save(file_path)
        return file_path


class EWorksheet(Worksheet):
    def __init__(self, parent, title=None):
        super().__init__(parent, title=title)

    def add_cell(self, row: int, col: int, value: [int, str, float] = None, **kwargs):
        """
        向單元格寫入資料
        :param row: The beginning row（開始寫入的行）
        :param col: The beginning col（開始寫入的列）
        :param value: 向單元格寫入的資料
        :return: None
        """
        if row < 1 or col < 1:
            raise ValueError("Row or column values must be at least 1")

        cell = self._get_cell(row, col)
        cell.font = kwargs.get('font', DEFAULT_FONT)
        cell.alignment = kwargs.get('alignment', DEFAULT_ALIGNMENT)
        cell.fill = kwargs.get('fill', DEFAULT_FILL)
        cell.border = kwargs.get('border', DEFAULT_BORDER)
        cell.number_format = kwargs.get('number_format', DEFAULT_NUMBER_FORMATE)

        if value is 0:
            cell.value = 0
        elif value is None:
            cell.value = None
        elif isinstance(value, decimal.Decimal):
            cell.value = decimal.Decimal(str(value))
        else:
            cell.value = str(value).strip()
        return cell

    def add_row(self, row: int, col: int, values: list = None, **kwargs):
        """
        向sheet寫入一列資料
        :param row: The beginning row（開始寫入的行）
        :param col: The beginning col（開始寫入的列）
        :param values: 向單元格寫入一列的資料
        :return: None
        """
        if not values and not isinstance(values, list):
            values = []
        for idx, val in enumerate(values):
            self.add_cell(row, col + idx, value=val, **kwargs)

    def add_col(self, row: int, col: int, values: list = None, **kwargs):
        """
        向sheet寫入一行資料
        :param row: The beginning row（開始寫入的行）
        :param col: The beginning col（開始寫入的列）
        :param values: 向sheet寫入一行的資料
        :return: None
        """
        if not values and not isinstance(values, list):
            values = []
        for idx, val in enumerate(values):
            self.add_cell(row + idx, col, value=val, **kwargs)

    def add_merge_row(self, row: int, col: int, v_pcs: int = None, value: [int, str, float] = None, **kwargs):
        """
        向單元格寫入資料，并向垂直方向合并單元格
        :param row: The beginning row（開始寫入的行）
        :param col: The beginning col（開始寫入的列）
        :param v_pcs: 垂直合并的行數
        :param value: 向單元格寫入資料
        :return: None
        """
        if not v_pcs and not isinstance(v_pcs, int):
            h_pcs = 0
        self.add_cell(row, col, value=value, **kwargs)
        self.merge_cells(start_row=row,
                         start_column=col,
                         end_row=row + v_pcs,
                         end_column=col)

    def add_merge_rows(self, row: int, col: int, v_pcs: int = None, values: list = None, **kwargs):
        """
        向sheet寫入一行資料，并向垂直方向合并單元格
        :param row: The beginning row（開始寫入的行）
        :param col: The beginning col（開始寫入的列）
        :param v_pcs: 垂直合并的行數
        :param values: 向sheet寫入一列的資料
        :return: None
        """
        if not values and not isinstance(values, list):
            values = []

        for idx, val in enumerate(values):
            column_letter = get_column_letter(col + idx)
            if isinstance(val, tuple) and len(val) == 2:
                self.column_dimensions[column_letter].width = val[1]
                self.add_merge_row(row, col + idx, v_pcs=v_pcs, value=val[0], **kwargs)
            else:
                self.add_merge_row(row, col + idx, v_pcs=v_pcs, value=val, **kwargs)

    def add_merge_col(self, row: int, col: int, h_pcs: int = None, value: [int, str, float] = None, **kwargs):
        """
        向單元格寫入資料，并向水準方向合并單元格
        :param row: The beginning row（開始寫入的行）
        :param col: The beginning col（開始寫入的列）
        :param h_pcs: 水準合并的列數
        :param value: 單元格寫入的資料
        :return: None
        """
        if not h_pcs and not isinstance(h_pcs, int):
            h_pcs = 0
        self.add_cell(row, col, value=value, **kwargs)
        self.merge_cells(start_row=row,
                         start_column=col,
                         end_row=row,
                         end_column=col + h_pcs)

    def add_merge_cols(self, row: int, col: int, h_pcs: int = None, values: list = None, **kwargs):
        """
        向sheet寫入一列資料，并向水準方向合并單元格
        :param row: The beginning row（開始寫入的行）
        :param col: The beginning col（開始寫入的列）
        :param h_pcs: 水準合并的列數
        :param values: 向sheet寫入一行的值
        :return: None
        """
        if not values and not isinstance(values, list):
            values = []
        for idx, val in enumerate(values):
            # column_letter = get_column_letter(col + idx)
            # if isinstance(val, tuple) and len(val) == 2:
            #     self.column_dimensions[column_letter].width = val[1]
            #     self.add_merge_col(row + idx, col, v_pcs=v_pcs, value=val[0], **kwargs)
            # else:
            #     self.add_merge_col(row + idx, col, v_pcs=v_pcs, value=val, **kwargs)
            self.add_merge_col(row + idx, col, h_pcs=h_pcs, value=val, **kwargs)

    def block_height(self, row, v_pcs=None):
        """
        v 垂直方向； h 水準方向
        單元格em換算像素 高比率=4/3
        單元格em換算像素 寬比率=200/25
        :param row: The beginning row of Block
        :param v_pcs: The number of rows occupied by Block
        :return: 該塊中每個單元格的高，傳回的是一個清單
        """
        if not v_pcs and not isinstance(v_pcs, int):
            v_pcs = 1

        # 一個block包含多個單元格，擷取每個單元格的高度，并以清單傳回
        block_height_detail = []
        for i in range(v_pcs):
            height = self.row_dimensions[row + i].height
            if not height:
                # 如果沒有設定高，預設值卻為None？
                self.row_dimensions[row + i].height = 16
                block_height_detail.append(int(16/0.761886))
            else:
                block_height_detail.append(int(height/0.761886))
        return block_height_detail

    def block_width(self, col, h_pcs=None):
        """
        v 垂直方向； h 水準方向
        單元格em換算像素 高比率=4/3
        單元格em換算像素 寬比率=200/25
        :param col: The beginning col of Block
        :param h_pcs: The number of cols occupied by Block
        :return: 該塊中每個單元格的寬，傳回的是一個清單
        """
        if not h_pcs and not isinstance(h_pcs, int):
            h_pcs = 1

        # 一個block包含多個單元格，擷取每個單元格的寬，并以清單傳回
        block_width_detail = []
        for i in range(h_pcs):
            column_letter = get_column_letter(col+i)
            width = self.column_dimensions[column_letter].width
            # print(width, end='-')
            block_width_detail.append(width * (72/9))
        return block_width_detail

    @staticmethod
    def _get_location(image_size, block_size_lst):
        """
        Current position calculation
        :param image_size: Width / Height of Image
        :param block_size_lst: Width / height of each cell in Block
        :return: 定位的單元格(行/列)，定位的單元格(寬/高)，定位的單元格需要偏移的量
        """
        total_size = 0
        current_size = 0
        current = 0
        block_off_size = abs(sum(block_size_lst)-image_size) / 2
        for idx, size in enumerate(block_size_lst):
            total_size += size
            current_size = size
            current = idx
            if total_size > block_off_size:
                break
        current_off_size = int(current_size - total_size + block_off_size)
        return current, current_size, current_off_size

    def add_relative_image(self, row, col, image_url, v_pcs=None, h_pcs=None, max_width=None, max_height=None):
        """
        v 垂直方向； h 水準方向
        Add an image to the sheet.
        Optionally provide a cell for the top-left anchor
        :param row: The beginning row of Block
        :param col: The beginning col of Block
        :param image_url: Image Path
        :param v_pcs: The number of rows occupied by Block
        :param h_pcs: The number of cols occupied by Block
        :param max_width: The maximum width of the Image
        :param max_height: The height width of the Image
        :return: None
        """
        try:
            img = AddImage(image_url, max_width=max_width, max_height=max_height).image_resizer()
            p2e = pixels_to_EMU
            img_size = XDRPositiveSize2D(p2e(img.height), p2e(img.width))
            block_height_lst = self.block_height(row, v_pcs=v_pcs)
            block_width_lst = self.block_width(col, h_pcs=h_pcs)
            current_row, current_row_height, current_off_height = self._get_location(img.width, block_height_lst)
            current_col, current_col_width, current_off_width = self._get_location(img.height, block_width_lst)
            marker = AnchorMarker(col=col + current_col - 1, colOff=p2e(current_off_width),
                                  row=row + current_row - 1, rowOff=p2e(current_off_height))
            img.anchor = OneCellAnchor(_from=marker, ext=img_size)
            self.add_image(img)
        except Exception as e:
            pass


if __name__ == '__main__':
    import tkinter as tk
    from tkinter import filedialog
    '''打開選擇檔案夾對話框'''
    root = tk.Tk()
    root.withdraw()
    filepath = filedialog.askopenfilename()  # 獲得選擇好的檔案
    wb = EWorkbook()
    ws = wb.create_sheet("sss", 0)
    ws.add_relative_image(12, 1, filepath, 5, 4, max_width=180, max_height=80)
    ws.cell(1, 2, "jacky")
    ws.add_row(2, 2, ["jacky", '4444', None, False, True, 0, '00000'])
    ws.add_col(3, 2, ["ccc", 'dddd444'])
    wb.save('2224.xlsx')