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