set excel format

import openpyxl
from openpyxl.styles import Alignment
from openpyxl.utils import get_column_letter
from openpyxl.styles import PatternFill, Border, Side

class SetExcelFormat:
    def __init__(self, filepath, sheetname):
        self.filepath = filepath
        self.workbook = openpyxl.load_workbook(filepath)
        self.worksheet = self.workbook[sheetname]
        max_col = self.worksheet.max_column
        self.cols = [get_column_letter(col) for col in range(1, max_col + 1)]

    def save(self):
        self.workbook.save(self.filepath)

    def set_color(self, min_row: int=1, max_row: int=1, color_str='C0C0C0'):
        fillcolor = PatternFill(start_color=color_str, end_color=color_str, fill_type='solid')
        for row in self.worksheet.iter_rows(min_row=min_row, max_row=max_row, values_only=False):
            for cell in row:
                cell.fill = fillcolor

    def set_border(self, min_row: int=1, max_row: int=1):
        border = Border(
            left=Side(style='thin'),
            right=Side(style='thin'),
            top=Side(style='thin'),
            bottom=Side(style='thin')
        )
        for row in self.worksheet.iter_rows(min_row=min_row, max_row=max_row, values_only=False):
            for cell in row:
                cell.border = border

    def set_title_center(self, **kwargs):
        cols = kwargs.get('cols')
        if cols is None:
            cols = self.cols
        titles = [str(x) + '1' for x in cols]
        center_alignment = Alignment(horizontal='center')
        for cell in titles:
            self.worksheet[cell].alignment = center_alignment

    def set_num_format(self, **kwargs):
        cols = kwargs.get('cols')
        if cols is None:
            cols = self.cols
        for col in cols:
            column_range = self.worksheet[col][1:]
            for cell in column_range:
                cell.number_format = '#,##0'

    def set_col_auto_width(self, **kwargs):
        dims = {}
        cols = kwargs.get('cols')
        if cols is None:
            cols = self.cols
        for row in self.worksheet.rows:
            for cell in row:
                if cell.value:
                    """
                    首先获取每个单元格中的长度；如果有换行则按单行的长度计算，先分割再计算；
                    长度计算中：len('中文')>>>2, len('中文'.encode('utf-8'))>>>6，通过运算，将中文的字节数定义为2；
                    字典存储每列的宽度：将cell每列中 列名作为键名，cell长度计算的最大长度作为键值。
                    """
                    len_cell = max(
                        [(len(line.encode('utf-8')) - len(line)) / 2 + len(line) for line in
                         str(cell.value).split('\n')])
                    # dims[chr(64+cell.column)] = max((dims.get(chr(64+cell.column), 0), len(str(cell.value))))
                    dims[cell.column_letter] = max(dims.get(cell.column_letter, 0), len_cell)
        for col, value in dims.items():
            if col in cols:
                """最后通过遍历存储每列的宽度的字典，来设置相关列的宽度"""
                self.worksheet.column_dimensions[col].width = value + 2 if value + 2 <= 50 else 50


if __name__ == '__main__':
    xlsformat = SetExcelFormat(filepath='采购订单ATP和PO价格对比_2024-07-08.xlsx', sheetname='Sheet1')
    xlsformat.set_col_auto_width()
    xlsformat.set_num_format(cols=['N', 'O', 'P'])
    xlsformat.set_title_center()
    xlsformat.save()