我要评论在企业日常办公中，工资表、考勤表、迟到统计、扣款统计经常需要重复整理。如果数据量不大，人工处理看起来也能完成；但只要员工数量增加、统计周期变长，手动复制、筛选、套公式、改样式就会变得非常耗时，而且容易出错。
python 的 openpyxl 库非常适合处理这类 excel 自动化任务。它可以帮助我们创建 excel 工作簿、写入员工数据、设置表头样式、合并标题单元格、根据考勤记录自动计算迟到次数和工资扣款，并最终导出一份格式清晰的工资统计表。
本文面向 python 办公自动化初学者，会通过一个完整案例带你实现“自动生成工资表与员工考勤统计”。
一、openpyxl 库简介
openpyxl 是 python 中常用的 excel 读写库，主要用于操作 .xlsx 格式文件。它不依赖本机安装 excel，因此非常适合在 windows、linux、服务器、自动化脚本或定时任务中使用。
使用 openpyxl 可以完成这些常见工作：
- 创建新的 excel 工作簿
- 读取已有 excel 文件
- 写入单元格内容
- 设置字体、颜色、边框、对齐方式
- 合并单元格
- 插入公式
- 设置列宽、行高
- 批量生成报表
- 保存为
.xlsx文件
安装命令如下：
pip install openpyxl
导入方式：
from openpyxl import workbook
本文重点使用 workbook 创建工作簿，并使用 font、patternfill、alignment、border 等样式对象美化工资表。
二、案例需求分析
我们要生成一份员工工资与考勤统计表，表格内容包括：
- 员工编号
- 员工姓名
- 部门
- 基本工资
- 出勤天数
- 迟到次数
- 每次迟到扣款
- 总扣款
- 实发工资
- 备注
其中：
- 迟到次数根据每天打卡时间自动统计
- 每次迟到扣款固定为 50 元
- 总扣款 = 迟到次数 × 每次迟到扣款
- 实发工资 = 基本工资 - 总扣款
- 迟到次数大于 0 的员工用浅红色高亮
- 表格标题自动合并单元格
- 最后导出为
salary_attendance_report.xlsx
假设公司规定上班时间为 09:00，超过 09:00 打卡就记为迟到。
三、准备员工考勤数据
真实业务中，员工考勤数据可能来自：
- 企业微信导出的 excel
- 钉钉考勤报表
- 门禁系统导出的 csv
- hr 系统数据库
- 手工维护的员工信息表
为了让初学者更容易理解，本文先使用 python 字典和列表模拟数据。
示例数据如下：
employees = [
{
"id": "e001",
"name": "张三",
"department": "技术部",
"base_salary": 12000,
"attendance": ["08:56", "09:03", "08:59", "09:12", "08:50"]
},
{
"id": "e002",
"name": "李四",
"department": "产品部",
"base_salary": 10000,
"attendance": ["08:45", "08:58", "09:01", "08:55", "08:57"]
},
{
"id": "e003",
"name": "王五",
"department": "运营部",
"base_salary": 8500,
"attendance": ["09:10", "09:08", "08:52", "08:49", "09:20"]
}
]
这里的 attendance 表示员工连续几个工作日的上班打卡时间。后面程序会自动判断哪些时间晚于 09:00。
四、创建 excel 工作簿
使用 openpyxl 创建工作簿非常简单：
from openpyxl import workbook wb = workbook() ws = wb.active ws.title = "工资考勤统计"
说明：
workbook()用来创建一个新的 excel 工作簿wb.active获取默认工作表ws.title设置工作表名称
最终导出文件时使用：
wb.save("salary_attendance_report.xlsx")
五、自动合并标题单元格
工资统计表通常需要一个醒目的大标题。我们可以将第一行多个单元格合并成一个标题区域。
ws.merge_cells("a1:j1")
ws["a1"] = "员工工资与考勤统计表"
merge_cells("a1:j1") 表示把 a1 到 j1 合并成一个单元格。之后只需要给左上角单元格 a1 写入标题即可。
标题还可以设置字体、颜色和居中效果：
ws["a1"].font = font(bold=true, size=16, color="ffffff")
ws["a1"].fill = patternfill("solid", fgcolor="305496")
ws["a1"].alignment = alignment(horizontal="center", vertical="center")
六、设置表头样式
表头是 excel 报表中非常重要的一部分，清晰的表头能让报表更专业。
我们需要的表头字段如下：
headers = [
"员工编号", "姓名", "部门", "基本工资",
"出勤天数", "迟到次数", "每次迟到扣款",
"总扣款", "实发工资", "备注"
]
写入表头：
for col_index, header in enumerate(headers, start=1):
cell = ws.cell(row=2, column=col_index, value=header)
设置表头样式：
cell.font = font(bold=true, color="ffffff")
cell.fill = patternfill("solid", fgcolor="4472c4")
cell.alignment = alignment(horizontal="center", vertical="center")
为了让表格边界更清楚，还可以统一设置边框：
thin_border = border(
left=side(style="thin", color="bfbfbf"),
right=side(style="thin", color="bfbfbf"),
top=side(style="thin", color="bfbfbf"),
bottom=side(style="thin", color="bfbfbf")
)
七、自动计算迟到次数与工资扣款
判断迟到的核心逻辑是：如果打卡时间晚于 09:00，则迟到一次。
可以用字符串比较，也可以用 datetime 转换。为了更稳妥，建议使用 datetime.strptime()。
from datetime import datetime
work_start_time = "09:00"
def count_late_times(attendance_times):
start_time = datetime.strptime(work_start_time, "%h:%m").time()
late_count = 0
for time_text in attendance_times:
check_time = datetime.strptime(time_text, "%h:%m").time()
if check_time > start_time:
late_count += 1
return late_count
计算扣款：
late_count = count_late_times(employee["attendance"]) deduction = late_count * late_deduction_per_time actual_salary = employee["base_salary"] - deduction
在这个案例中：
late_deduction_per_time = 50
如果某员工迟到 3 次，则扣款：
3 × 50 = 150 元
八、写入员工数据
员工数据从第 3 行开始写入，因为：
- 第 1 行是合并后的标题
- 第 2 行是表头
- 第 3 行开始是明细数据
示例：
start_row = 3
for row_index, employee in enumerate(employees, start=start_row):
late_count = count_late_times(employee["attendance"])
attendance_days = len(employee["attendance"])
deduction = late_count * late_deduction_per_time
actual_salary = employee["base_salary"] - deduction
row_data = [
employee["id"],
employee["name"],
employee["department"],
employee["base_salary"],
attendance_days,
late_count,
late_deduction_per_time,
deduction,
actual_salary,
"正常" if late_count == 0 else "存在迟到"
]
for col_index, value in enumerate(row_data, start=1):
ws.cell(row=row_index, column=col_index, value=value)
这样就完成了员工信息、考勤统计和工资结果的写入。
九、单元格颜色高亮
为了让 hr 或行政人员快速发现异常，可以对迟到员工所在行进行高亮。
例如：迟到次数大于 0 的员工使用浅红色背景。
late_fill = patternfill("solid", fgcolor="fce4d6")
if late_count > 0:
for col_index in range(1, len(headers) + 1):
ws.cell(row=row_index, column=col_index).fill = late_fill
也可以只高亮“迟到次数”“总扣款”“备注”等关键单元格：
ws.cell(row=row_index, column=6).fill = late_fill ws.cell(row=row_index, column=8).fill = late_fill ws.cell(row=row_index, column=10).fill = late_fill
本文完整代码中会采用整行浅红色高亮，这样更直观。
十、设置列宽与数字格式
为了让 excel 报表打开后更易读，可以设置列宽：
column_widths = {
"a": 12,
"b": 10,
"c": 12,
"d": 12,
"e": 12,
"f": 12,
"g": 16,
"h": 12,
"i": 12,
"j": 14
}
for column, width in column_widths.items():
ws.column_dimensions[column].width = width
工资金额可以设置为数字格式：
for row in range(3, ws.max_row + 1):
ws.cell(row=row, column=4).number_format = '#,##0'
ws.cell(row=row, column=7).number_format = '#,##0'
ws.cell(row=row, column=8).number_format = '#,##0'
ws.cell(row=row, column=9).number_format = '#,##0'
这样 12000 在 excel 中会显示为更易读的 12,000。
十一、完整案例代码
下面是一份可以直接运行的完整代码。运行后会在当前目录生成 salary_attendance_report.xlsx 文件。
from datetime import datetime
from openpyxl import workbook
from openpyxl.styles import alignment, border, font, patternfill, side
work_start_time = "09:00"
late_deduction_per_time = 50
output_file = "salary_attendance_report.xlsx"
employees = [
{
"id": "e001",
"name": "张三",
"department": "技术部",
"base_salary": 12000,
"attendance": ["08:56", "09:03", "08:59", "09:12", "08:50"],
},
{
"id": "e002",
"name": "李四",
"department": "产品部",
"base_salary": 10000,
"attendance": ["08:45", "08:58", "09:01", "08:55", "08:57"],
},
{
"id": "e003",
"name": "王五",
"department": "运营部",
"base_salary": 8500,
"attendance": ["09:10", "09:08", "08:52", "08:49", "09:20"],
},
{
"id": "e004",
"name": "赵六",
"department": "财务部",
"base_salary": 9500,
"attendance": ["08:51", "08:53", "08:58", "08:56", "08:59"],
},
{
"id": "e005",
"name": "孙七",
"department": "销售部",
"base_salary": 11000,
"attendance": ["09:15", "09:06", "09:02", "08:57", "08:54"],
},
]
def count_late_times(attendance_times):
"""统计迟到次数。"""
start_time = datetime.strptime(work_start_time, "%h:%m").time()
late_count = 0
for time_text in attendance_times:
check_time = datetime.strptime(time_text, "%h:%m").time()
if check_time > start_time:
late_count += 1
return late_count
def create_salary_report():
wb = workbook()
ws = wb.active
ws.title = "工资考勤统计"
headers = [
"员工编号",
"姓名",
"部门",
"基本工资",
"出勤天数",
"迟到次数",
"每次迟到扣款",
"总扣款",
"实发工资",
"备注",
]
title_fill = patternfill("solid", fgcolor="305496")
header_fill = patternfill("solid", fgcolor="4472c4")
late_fill = patternfill("solid", fgcolor="fce4d6")
normal_fill = patternfill("solid", fgcolor="e2f0d9")
title_font = font(bold=true, size=16, color="ffffff")
header_font = font(bold=true, color="ffffff")
normal_font = font(color="000000")
center_alignment = alignment(horizontal="center", vertical="center")
thin_border = border(
left=side(style="thin", color="bfbfbf"),
right=side(style="thin", color="bfbfbf"),
top=side(style="thin", color="bfbfbf"),
bottom=side(style="thin", color="bfbfbf"),
)
# 1. 合并标题单元格
ws.merge_cells("a1:j1")
title_cell = ws["a1"]
title_cell.value = "员工工资与考勤统计表"
title_cell.font = title_font
title_cell.fill = title_fill
title_cell.alignment = center_alignment
ws.row_dimensions[1].height = 30
# 2. 写入并设置表头样式
for col_index, header in enumerate(headers, start=1):
cell = ws.cell(row=2, column=col_index, value=header)
cell.font = header_font
cell.fill = header_fill
cell.alignment = center_alignment
cell.border = thin_border
# 3. 写入员工数据并计算考勤、扣款、实发工资
start_row = 3
for row_index, employee in enumerate(employees, start=start_row):
late_count = count_late_times(employee["attendance"])
attendance_days = len(employee["attendance"])
deduction = late_count * late_deduction_per_time
actual_salary = employee["base_salary"] - deduction
remark = "正常" if late_count == 0 else "存在迟到"
row_data = [
employee["id"],
employee["name"],
employee["department"],
employee["base_salary"],
attendance_days,
late_count,
late_deduction_per_time,
deduction,
actual_salary,
remark,
]
for col_index, value in enumerate(row_data, start=1):
cell = ws.cell(row=row_index, column=col_index, value=value)
cell.font = normal_font
cell.alignment = center_alignment
cell.border = thin_border
if late_count > 0:
cell.fill = late_fill
else:
cell.fill = normal_fill
# 4. 设置列宽
column_widths = {
"a": 12,
"b": 10,
"c": 12,
"d": 12,
"e": 12,
"f": 12,
"g": 16,
"h": 12,
"i": 12,
"j": 14,
}
for column, width in column_widths.items():
ws.column_dimensions[column].width = width
# 5. 设置工资相关列的数字格式
for row in range(3, ws.max_row + 1):
ws.cell(row=row, column=4).number_format = '#,##0'
ws.cell(row=row, column=7).number_format = '#,##0'
ws.cell(row=row, column=8).number_format = '#,##0'
ws.cell(row=row, column=9).number_format = '#,##0'
# 6. 冻结表头，方便查看大量数据
ws.freeze_panes = "a3"
# 7. 导出工资统计表
wb.save(output_file)
print(f"工资考勤统计表已生成：{output_file}")
if __name__ == "__main__":
create_salary_report()
运行命令：
python salary_attendance_report.py
运行成功后，会生成：
salary_attendance_report.xlsx
打开 excel 后可以看到：
- 第一行是合并后的报表标题
- 第二行是蓝底白字表头
- 员工数据已经自动写入
- 迟到员工所在行被浅红色高亮
- 未迟到员工所在行被浅绿色高亮
- 总扣款和实发工资已经自动计算完成
十二、把案例改造成真实企业数据流程
上面的代码使用了固定的员工列表。实际企业中，可以进一步扩展为更完整的自动化流程。
1. 从考勤 excel 中读取数据
如果考勤系统导出的是 excel，可以使用 openpyxl.load_workbook() 读取：
from openpyxl import load_workbook
wb = load_workbook("attendance.xlsx")
ws = wb.active
for row in ws.iter_rows(min_row=2, values_only=true):
employee_id = row[0]
employee_name = row[1]
check_time = row[2]
这样可以把外部考勤数据接入到工资统计脚本中。
2. 从员工信息表中读取基本工资
企业通常会有一份员工基础信息表，例如：
员工编号 | 姓名 | 部门 | 基本工资
程序可以先读取员工信息表，再读取考勤表，最后通过员工编号进行匹配，自动生成工资报表。
3. 增加请假、早退、缺勤等规则
工资计算规则也可以继续扩展：
- 请假半天扣款
- 缺勤一天扣款
- 早退次数统计
- 加班工资计算
- 绩效奖金计算
- 社保、公积金、个税扣除
这些规则都可以封装成函数，让工资计算逻辑更清晰。
例如：
def calculate_actual_salary(base_salary, late_count, absence_days, bonus):
late_deduction = late_count * 50
absence_deduction = absence_days * 300
return base_salary - late_deduction - absence_deduction + bonus
十三、企业办公自动化实际应用场景
这个案例虽然简单，但背后的思路非常实用。企业办公自动化中经常会遇到类似需求：
1. hr 工资核算
hr 每月需要统计考勤、迟到、请假、缺勤、绩效奖金等数据。使用 python + openpyxl 可以把重复的 excel 操作自动化，减少手动汇总时间。
2. 行政考勤统计
行政人员可以定期从打卡系统导出考勤数据，然后运行脚本自动生成部门考勤汇总表，把异常记录高亮显示。
3. 财务工资复核
财务部门可以拿到自动生成的工资表后，快速检查扣款、实发工资、异常员工，提高复核效率。
4. 部门月度报表
除了工资表，类似的方式还可以生成销售统计表、项目工时报表、库存明细表、费用报销表等。
5. 定时任务自动生成报表
如果脚本部署在服务器上，还可以结合 windows 任务计划程序或 linux crontab，每月自动生成工资统计表，并通过邮件发送给相关负责人。
十四、初学者容易踩的坑
1. 文件正在被 excel 打开
如果 salary_attendance_report.xlsx 正在被 excel 打开，python 保存时可能会报错。解决方法是先关闭 excel 文件，再运行脚本。
2. 时间格式不统一
考勤数据中可能出现 9:00、09:00、09:00:00 等不同格式。真实项目中需要先做数据清洗。
3. 中文字体显示问题
如果需要设置指定中文字体，可以使用：
font(name="微软雅黑", bold=true)
不过不同电脑上字体环境可能不同，建议使用常见系统字体。
4. 公式和 python 计算的选择
工资扣款既可以由 python 直接计算后写入，也可以写成 excel 公式。例如：
ws["h3"] = "=f3*g3" ws["i3"] = "=d3-h3"
如果希望用户打开 excel 后能看到公式，可以使用 excel 公式；如果希望结果固定、方便系统导入，可以使用 python 直接计算。本文采用 python 直接计算，更适合自动导出报表。
十五、总结
本文使用 python + openpyxl 实现了一个“自动生成工资表与员工考勤统计”的完整案例，覆盖了办公自动化中非常常见的 excel 报表处理流程。
你已经学习了：
- openpyxl 库的基本用途
- 如何创建 excel 工作簿
- 如何自动合并标题单元格
- 如何设置表头样式
- 如何写入员工数据
- 如何统计迟到次数
- 如何计算工资扣款和实发工资
- 如何对异常员工进行颜色高亮
- 如何导出完整工资统计表
- 如何把案例扩展到企业真实办公场景
对于 python 办公自动化初学者来说，掌握这个案例后，就可以继续扩展到更多 excel 自动化任务，例如批量生成报表、合并多份表格、自动整理数据、自动发送邮件等。
只要你的工作中存在大量重复性的 excel 操作，就可以考虑用 python 把它自动化。
以上就是python+openpyxl自动生成工资表与员工考勤统计的详细内容，更多关于python openpyxl自动生成表的资料请关注代码网其它相关文章！
发表评论