代码功能
对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)
标签:execl,sheet,openpyxl,示例,column,cell,length,new,password
From: https://www.cnblogs.com/wangguishe/p/18196330