python批量更改excel的格式且进行保存

最新推荐文章于 2024-01-09 08:54:45 发布

Naive_Bayes616

最新推荐文章于 2024-01-09 08:54:45 发布

阅读量553

点赞数

文章标签： python

本文链接：https://blog.csdn.net/qq_27180927/article/details/125386970

版权

openpyxl Excel格式化 Python脚本单元格样式列宽设置

关键词由CSDN通过智能技术生成

from openpyxl.styles import Font,Border,Side
import os
#from openpyxl.utils.cell import get_column_letter
import openpyxl

def modifyformat(oldfile):
wb = openpyxl.load_workbook(oldfile)
#wb.active.column_dimensions
#wb.active.row_dimensions
wb.sheetnames
#sheets = wb.sheetnames
ws = wb['xxx']

fontStyle = Font(size = "8")
fontStyle2 = Font(size = "8",bold=True)
border = Border(left=Side(style='thin'),bottom=Side(style='thin'),right=Side(style='thin'),top=Side(style='thin'))
maxRows = ws.max_row
maxColumns = ws.max_column
for i in range(1,maxColumns+1):
# print(ws[get_column_letter(i+1)])
# print(i)
for k in range(1,maxRows+1):
# print(k)
# print(get_column_letter(i+1))
# cell = str(ws[get_column_letter(i+1)+str(k+1)].value)
cell = ws.cell(row = k,column=i)
# print(cell)
cell1 = ws.cell(row = 1,column=i)
cell3 = ws.cell(row = 3,column=i)
cell.font = fontStyle
cell1.font = fontStyle2
cell3.font = fontStyle2
cell.border = border
ws.column_dimensions['A'].width = 3.36
ws.column_dimensions['B'].width = 16
ws.column_dimensions['C'].width = 31
ws.column_dimensions['D'].width = 14
ws.column_dimensions['E'].width = 4.8
ws.column_dimensions['F'].width = 9.36
ws.column_dimensions['G'].width = 3.36
newfile = oldfile.split('\\')[-1][:27] + oldfile.split('\\')[-2] + '.xlsx'
newefile = os.path.join(targetpth,newfile)
wb.save(newefile)
wb.close()

if __name__ =='__main__':
pth = r'路径'
targetpth = r'路径'
for folder in os.listdir(pth):
absfolder = os.path.join(pth,folder)
if os.path.isdir(absfolder):
for file in os.listdir(absfolder):
if 'xxxx' in file:
filepath = os.path.join(absfolder,file)
modifyformat(filepath)
# print(filepath.split('\\')[-1][:27])
# print(filepath.split('\\')[-2])
# newfile = filepath.split('\\')[-1][:27] + filepath.split('\\')[-2] + '.xlsx'
# print(newfile)