openpyxl 操作 execl 示例

代码功能

对execl 的主机生成唯一不重复的密码。

代码示例

import secrets
import string
from datetime import datetime
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
from openpyxl.styles import Border, Side, Alignment


def create_password(password_length, minimum_digits):
    """
    生成符合安全要求的密码。
    """
    characters = string.ascii_letters + string.digits + string.punctuation
    while True:
        password = ''.join(secrets.choice(characters) for _ in range(password_length))
        if sum(c.isdigit() for c in password) >= minimum_digits:
            return password

def get_unique_password(existing_passwords, password_length, minimum_digits):
    while True:
        unique_password = create_password(password_length, minimum_digits)
        if unique_password not in existing_passwords:
            existing_passwords.add(unique_password)
            return unique_password
        
def update_excel_passwords(file_path, start_row, column_index, password_length, minimum_digits):
    """
    更新Excel文件中的密码，并设置样式。
    """
    try:
        book = load_workbook(file_path)
        sheet = book.active

        existing_passwords = set()
        
        date_str = datetime.now().strftime("%Y-%m-%d-%H-%M-%S")
        new_sheet = book.create_sheet(title=date_str, index=0)

        # 单元格格式设置
        thin_border = Side(style='thin')
        center_alignment = Alignment(horizontal='center', vertical='center')
        left_alignment = Alignment(horizontal='left', vertical='top')

        # 设置单元格边框和对齐方式
        for i, row in enumerate(sheet.iter_rows(), start=1):
            for j, cell in enumerate(row, start=1):
                new_cell = new_sheet.cell(row=i, column=j, value=cell.value)
                new_cell.border = Border(left=thin_border, right=thin_border, top=thin_border, bottom=thin_border)
                new_cell.alignment = center_alignment if i == 1 else left_alignment

        # 更新密码
        for i in range(start_row, new_sheet.max_row + 1):
            unique_password = get_unique_password(existing_passwords, password_length, minimum_digits)
            cell = new_sheet.cell(row=i, column=column_index)
            cell.value = unique_password

        # 计算并设置每列的最大宽度
        for col in range(1, new_sheet.max_column + 1):
            column_letter = get_column_letter(col)
            max_lengths = [len(str(cell.value)) for cell in new_sheet[column_letter]]
            max_length = max(max_lengths) if max_lengths else 0
            new_sheet.column_dimensions[column_letter].width = (max_length + 2) * 1.2

        book.save(file_path)
        book.close()

    except Exception as e:
        print(f"发生错误: {e}")


if __name__ == '__main__':
   # Excel 文件路径
    file_path = r'F:\work\python\files\password.xlsx'
    # 密码所在列的索引
    column_index = 2
    # 密码长度
    password_length = 16
    # 密码中至少包含的数字个数
    minimum_digits = 3
    # 从第二行开始更新，保留标题行
    start_row = 2

    # 更新 Excel 中指定列的密码，自动调整列宽并为每个单元格添加边框和对齐样式
    update_excel_passwords(file_path, start_row, column_index, password_length, minimum_digits)