python操作excel

最新推荐文章于 2024-10-07 16:45:09 发布

且菜且折腾

最新推荐文章于 2024-10-07 16:45:09 发布

阅读量182

点赞数

分类专栏： python

本文链接：https://blog.csdn.net/weixin_43933139/article/details/117772796

版权

python 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

import openpyxl
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment

# 打开文件,遍历sheet
def open_file(file_path):
    wb=openpyxl.load_workbook(file_path)
    all_sheet_name=wb.sheetnames
    for item in all_sheet_name:
        sheet=wb[item]
        
        set_frist_row(sheet)
        set_table_title(sheet)
        set_column_width(sheet)
        set_other_row(sheet)
    return wb  
       
# 设置sheet标题
def set_frist_row(sheet):
    font =Font(size=24,bold=True,color='0000ff')
    alignment=Alignment(horizontal='center',vertical='center')
    fill=PatternFill('solid',fgColor='DDDDDD')
    sheet['A1'].fill=fill
    sheet['A1'].font=font
    sheet['A1'].alignment=alignment

# 设置表格标题
def set_table_title(sheet):
    font =Font(size=18,bold=True,color='0000ff',italic=True)
    alignment=Alignment(horizontal='center',vertical='center')
    sheet.row_dimensions[2].height=35
    for c in('A','B','C','D'):
        sheet[f'{c}{2}'].font=font
        sheet[f'{c}{2}'].alignment=alignment

# 设置列宽
def set_column_width(sheet):
    sheet.column_dimensions['A'].width=60
    sheet.column_dimensions['B'].width=120
    sheet.column_dimensions['C'].width=15
    sheet.column_dimensions['D'].width=20

# 设置其他行样式
def set_other_row(sheet):
    maxrow=sheet.max_row
    # 冻结
    sheet.freeze_panes="A3"
    # 边框
    side=Side(border_style="thin",color='000000')
    bd=Border(
        left=side,
        top=side,
        right=side,
        bottom=side,
    )
    if maxrow>=3:
        for i in range(3,maxrow+1):
            sheet.row_dimensions[i].height=35
            font =Font(size=14,color='000000')
            alignment=Alignment(vertical='center')
            for c in ('A','B','C','D'):
                sheet[f'{c}{i}'].font=font
                sheet[f'{c}{i}'].alignment=alignment
                sheet[f'{c}{i}'].border=bd

if __name__ == '__main__':
    file_path="./公司信息.xlsx"
    wb=open_file(file_path)
    wb.save(file_path)