Use python openpyxl package read and edit excel files

Pub Date: 2023-09-01

单元格居中 设置单元格格式文本 设置所有单元格的框线

from openpyxl import Workbook
from openpyxl.styles import Font
from openpyxl import load_workbook
from openpyxl.styles import Border, Side, Alignment
import os

current_dir = os.getcwd()  # 获取当前目录

xlsx_files = []
for file in os.listdir(current_dir):  # 遍历当前目录下的文件和文件夹
    if file.endswith('.xlsx'):  # 判断文件是否以 .xlsx 结尾
        xlsx_files.append(file)
        
for xlsx in xlsx_files:
    # 打开Excel文件
    workbook = load_workbook(xlsx)
    # 选择要读取的工作表
    sheet = workbook['camscanner']

    # 设置所有单元格的框线
    border = Border(left=Side(border_style='thin'),
                    right=Side(border_style='thin'),
                    top=Side(border_style='thin'),
                    bottom=Side(border_style='thin'))
    # 设置所有单元格的水平居中
    alignment = Alignment(horizontal='center')

    for row in sheet.iter_rows():
        for cell in row:
            cell.border = border
            cell.alignment = alignment
             # 设置所有单元格的格式为文本
            cell.number_format = "@"

    workbook.save(xlsx)
    # 关闭Excel文件
    workbook.close()