def Modify_format(table_name):
wb = openpyxl.load_workbook(table_name)
# ws = wb.active
# 处理多个sheet
for ws in wb:
# ws.alignment = openpyxl.styles.Alignment(horizontal='left', vertical='center') # 设置所有行列 左对齐
# ws.alignment = openpyxl.styles.Alignment(horizontal='center', vertical='center') # 设置所有行列居中
# 单元格cell自动换行 wrapText=True
for r in ws:
for c in r:
c.alignment = openpyxl.styles.Alignment(horizontal='left', vertical='center', wrapText=True)
lks = [] # 英文变量太费劲,用汉语首字拼音代替
for i in range(1, ws.max_column + 1): # 每列循环
lk = 1 # 定义初始列宽,并在每个行循环完成后重置
for j in range(1, ws.max_row + 1): # 每行循环
sz = ws.cell(row=j, column=i).value # 每个单元格内容
if isinstance(sz, str): # 中文占用多个字节,需要分开处理
lk1 = len(sz.encode('gbk')) # gbk解码一个中文两字节,utf-8一个中文三字节,gbk合适
else:
lk1 = len(str(sz))
if lk < lk1:
lk = lk1 # 借助每行循环将最大值存入lk中
# print(lk)
lks.append(lk) # 将每列最大宽度加入列表。(犯了一个错,用lks = lks.append(lk)报错,append会修改列表变量,返回值none,而none不能继续用append方法)
# 第二步:设置列宽
for i in range(1, ws.max_column + 1):
# 排除O列
if i == 15:
continue
k = get_column_letter(i) # 将数字转化为列名,26个字母以内也可以用[chr(i).upper() for i in range(97, 123)],不用导入模块
ws.column_dimensions[k].width = lks[i - 1] + 2 # 设置列宽,一般加两个字节宽度,可以根据实际情况灵活调整
# 设置边框
for row in ws.iter_rows(min_row=1, max_row=ws.max_row, min_col=1, max_col=ws.max_column):
for cell in row:
cell.border = Border(left=Side(border_style="thin", color='000000'),
right=Side(border_style="thin", color='000000'),
top=Side(border_style="thin", color='000000'),
bottom=Side(border_style="thin", color='000000'))
# 设置冻结首行
ws.freeze_panes = 'A2'
# 设置数据筛选
ws.auto_filter.ref = ws.dimensions
# 指定列宽
# ws.column_dimensions['A'].width = 56
# ws.column_dimensions['B'].width = 22
# ws.column_dimensions['C'].width = 33
ws.column_dimensions['D'].width = 60
# ws.column_dimensions['H'].width = 40
ws.row_dimensions[1].height = 22 # 设置第1行的行高 33
# 设置2-100行的行高 99
for i in range(2, 615):
ws.row_dimensions[i].height = 33
wb.save(table_name)
Modify_format(table_name) # 修改格式