Python Excel 读写

模板

导出结果

代码

# 导入模块
from openpyxl import load_workbook
from openpyxl.worksheet.datavalidation import DataValidation
 

# 获取数据
path='./material/学生.xlsx'
path_result='./material/学生_result.xlsx'
wb  = load_workbook(path)
ws = wb.active

students=[
    {'学号':'S001','名字':'学生1','性别':'男','语文':100,'数学':90,'英语':100},
    {'学号':'S002','名字':'学生2','性别':'男','语文':90,'数学':90,'英语':100},
    {'学号':'S003','名字':'学生3','性别':'女','语文':80,'数学':90,'英语':100},
    {'学号':'S004','名字':'学生4','性别':'男','语文':70,'数学':90,'英语':100},
    {'学号':'S005','名字':'学生5','性别':'男','语文':60,'数学':90,'英语':100},
]

count=2
for student in students:
    # 绑定数据
    ws['A'+str(count)]=student['学号']
    ws['B'+str(count)]=student['名字']
    ws['C'+str(count)]=student['性别']
    ws['D'+str(count)]=student['语文']
    ws['E'+str(count)]=student['数学']
    ws['F'+str(count)]=student['英语']
    
    # 下拉
    dv = DataValidation(type="list", formula1='"男,女"', allow_blank=True)
    dv.add('C'+str(count))
    ws.add_data_validation(dv)

    count=count+1

wb.save(path_result)