set excel format

import openpyxl
from openpyxl.styles import Alignment
from openpyxl.utils import get_column_letter


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_title_center(self, **kwargs):
        cols = kwargs.get('cols')
        if cols is None:
            cols = self.cols
        center_alignment = Alignment(horizontal='center')
        for col in cols:
            column_range = self.worksheet[col][1:]
            for cell in column_range:
                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.save()