set excel format

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81

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()