Python+openpyxl自動生成工資表與員工考勤統(tǒng)計
在企業(yè)日常辦公中，工資表、考勤表、遲到統(tǒng)計、扣款統(tǒng)計經(jīng)常需要重復(fù)整理。如果數(shù)據(jù)量不大，人工處理看起來也能完成；但只要員工數(shù)量增加、統(tǒng)計周期變長，手動復(fù)制、篩選、套公式、改樣式就會變得非常耗時，而且容易出錯。
Python 的 openpyxl 庫非常適合處理這類 Excel 自動化任務(wù)。它可以幫助我們創(chuàng)建 Excel 工作簿、寫入員工數(shù)據(jù)、設(shè)置表頭樣式、合并標(biāo)題單元格、根據(jù)考勤記錄自動計算遲到次數(shù)和工資扣款，并最終導(dǎo)出一份格式清晰的工資統(tǒng)計表。
本文面向 Python 辦公自動化初學(xué)者，會通過一個完整案例帶你實現(xiàn)“自動生成工資表與員工考勤統(tǒng)計”。
一、openpyxl 庫簡介
openpyxl 是 Python 中常用的 Excel 讀寫庫，主要用于操作 .xlsx 格式文件。它不依賴本機安裝 Excel，因此非常適合在 Windows、Linux、服務(wù)器、自動化腳本或定時任務(wù)中使用。
使用 openpyxl 可以完成這些常見工作：
- 創(chuàng)建新的 Excel 工作簿
- 讀取已有 Excel 文件
- 寫入單元格內(nèi)容
- 設(shè)置字體、顏色、邊框、對齊方式
- 合并單元格
- 插入公式
- 設(shè)置列寬、行高
- 批量生成報表
- 保存為
.xlsx文件
安裝命令如下：
pip install openpyxl
導(dǎo)入方式：
from openpyxl import Workbook
本文重點使用 Workbook 創(chuàng)建工作簿，并使用 Font、PatternFill、Alignment、Border 等樣式對象美化工資表。
二、案例需求分析
我們要生成一份員工工資與考勤統(tǒng)計表，表格內(nèi)容包括：
- 員工編號
- 員工姓名
- 部門
- 基本工資
- 出勤天數(shù)
- 遲到次數(shù)
- 每次遲到扣款
- 總扣款
- 實發(fā)工資
- 備注
其中：
- 遲到次數(shù)根據(jù)每天打卡時間自動統(tǒng)計
- 每次遲到扣款固定為 50 元
- 總扣款 = 遲到次數(shù) × 每次遲到扣款
- 實發(fā)工資 = 基本工資 - 總扣款
- 遲到次數(shù)大于 0 的員工用淺紅色高亮
- 表格標(biāo)題自動合并單元格
- 最后導(dǎo)出為
salary_attendance_report.xlsx
假設(shè)公司規(guī)定上班時間為 09:00，超過 09:00 打卡就記為遲到。
三、準(zhǔn)備員工考勤數(shù)據(jù)
真實業(yè)務(wù)中，員工考勤數(shù)據(jù)可能來自：
- 企業(yè)微信導(dǎo)出的 Excel
- 釘釘考勤報表
- 門禁系統(tǒng)導(dǎo)出的 CSV
- HR 系統(tǒng)數(shù)據(jù)庫
- 手工維護的員工信息表
為了讓初學(xué)者更容易理解，本文先使用 Python 字典和列表模擬數(shù)據(jù)。
示例數(shù)據(jù)如下：
employees = [
{
"id": "E001",
"name": "張三",
"department": "技術(shù)部",
"base_salary": 12000,
"attendance": ["08:56", "09:03", "08:59", "09:12", "08:50"]
},
{
"id": "E002",
"name": "李四",
"department": "產(chǎn)品部",
"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 表示員工連續(xù)幾個工作日的上班打卡時間。后面程序會自動判斷哪些時間晚于 09:00。
四、創(chuàng)建 Excel 工作簿
使用 openpyxl 創(chuàng)建工作簿非常簡單：
from openpyxl import Workbook wb = Workbook() ws = wb.active ws.title = "工資考勤統(tǒng)計"
說明：
Workbook()用來創(chuàng)建一個新的 Excel 工作簿wb.active獲取默認(rèn)工作表ws.title設(shè)置工作表名稱
最終導(dǎo)出文件時使用：
wb.save("salary_attendance_report.xlsx")
五、自動合并標(biāo)題單元格
工資統(tǒng)計表通常需要一個醒目的大標(biāo)題。我們可以將第一行多個單元格合并成一個標(biāo)題區(qū)域。
ws.merge_cells("A1:J1")
ws["A1"] = "員工工資與考勤統(tǒng)計表"
merge_cells("A1:J1") 表示把 A1 到 J1 合并成一個單元格。之后只需要給左上角單元格 A1 寫入標(biāo)題即可。
標(biāo)題還可以設(shè)置字體、顏色和居中效果：
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")
六、設(shè)置表頭樣式
表頭是 Excel 報表中非常重要的一部分，清晰的表頭能讓報表更專業(yè)。
我們需要的表頭字段如下：
headers = [
"員工編號", "姓名", "部門", "基本工資",
"出勤天數(shù)", "遲到次數(shù)", "每次遲到扣款",
"總扣款", "實發(fā)工資", "備注"
]
寫入表頭：
for col_index, header in enumerate(headers, start=1):
cell = ws.cell(row=2, column=col_index, value=header)
設(shè)置表頭樣式：
cell.font = Font(bold=True, color="FFFFFF")
cell.fill = PatternFill("solid", fgColor="4472C4")
cell.alignment = Alignment(horizontal="center", vertical="center")
為了讓表格邊界更清楚，還可以統(tǒng)一設(shè)置邊框：
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")
)
七、自動計算遲到次數(shù)與工資扣款
判斷遲到的核心邏輯是：如果打卡時間晚于 09:00，則遲到一次。
可以用字符串比較，也可以用 datetime 轉(zhuǎn)換。為了更穩(wěn)妥，建議使用 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 元
八、寫入員工數(shù)據(jù)
員工數(shù)據(jù)從第 3 行開始寫入，因為：
- 第 1 行是合并后的標(biāo)題
- 第 2 行是表頭
- 第 3 行開始是明細(xì)數(shù)據(jù)
示例：
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)
這樣就完成了員工信息、考勤統(tǒng)計和工資結(jié)果的寫入。
九、單元格顏色高亮
為了讓 HR 或行政人員快速發(fā)現(xiàn)異常，可以對遲到員工所在行進(jìn)行高亮。
例如：遲到次數(shù)大于 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
也可以只高亮“遲到次數(shù)”“總扣款”“備注”等關(guān)鍵單元格：
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
本文完整代碼中會采用整行淺紅色高亮，這樣更直觀。
十、設(shè)置列寬與數(shù)字格式
為了讓 Excel 報表打開后更易讀，可以設(shè)置列寬：
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
工資金額可以設(shè)置為數(shù)字格式：
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。
十一、完整案例代碼
下面是一份可以直接運行的完整代碼。運行后會在當(dāng)前目錄生成 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": "技術(shù)部",
"base_salary": 12000,
"attendance": ["08:56", "09:03", "08:59", "09:12", "08:50"],
},
{
"id": "E002",
"name": "李四",
"department": "產(chǎn)品部",
"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": "財務(wù)部",
"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):
"""統(tǒng)計遲到次數(shù)。"""
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 = "工資考勤統(tǒng)計"
headers = [
"員工編號",
"姓名",
"部門",
"基本工資",
"出勤天數(shù)",
"遲到次數(shù)",
"每次遲到扣款",
"總扣款",
"實發(fā)工資",
"備注",
]
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. 合并標(biāo)題單元格
ws.merge_cells("A1:J1")
title_cell = ws["A1"]
title_cell.value = "員工工資與考勤統(tǒng)計表"
title_cell.font = title_font
title_cell.fill = title_fill
title_cell.alignment = center_alignment
ws.row_dimensions[1].height = 30
# 2. 寫入并設(shè)置表頭樣式
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. 寫入員工數(shù)據(jù)并計算考勤、扣款、實發(fā)工資
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. 設(shè)置列寬
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. 設(shè)置工資相關(guān)列的數(shù)字格式
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. 凍結(jié)表頭，方便查看大量數(shù)據(jù)
ws.freeze_panes = "A3"
# 7. 導(dǎo)出工資統(tǒng)計表
wb.save(OUTPUT_FILE)
print(f"工資考勤統(tǒng)計表已生成：{OUTPUT_FILE}")
if __name__ == "__main__":
create_salary_report()
運行命令：
python salary_attendance_report.py
運行成功后，會生成：
salary_attendance_report.xlsx
打開 Excel 后可以看到：
- 第一行是合并后的報表標(biāo)題
- 第二行是藍(lán)底白字表頭
- 員工數(shù)據(jù)已經(jīng)自動寫入
- 遲到員工所在行被淺紅色高亮
- 未遲到員工所在行被淺綠色高亮
- 總扣款和實發(fā)工資已經(jīng)自動計算完成
十二、把案例改造成真實企業(yè)數(shù)據(jù)流程
上面的代碼使用了固定的員工列表。實際企業(yè)中，可以進(jìn)一步擴展為更完整的自動化流程。
1. 從考勤 Excel 中讀取數(shù)據(jù)
如果考勤系統(tǒng)導(dǎo)出的是 Excel，可以使用 openpyxl.load_workbook() 讀?。?/p>
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]
這樣可以把外部考勤數(shù)據(jù)接入到工資統(tǒng)計腳本中。
2. 從員工信息表中讀取基本工資
企業(yè)通常會有一份員工基礎(chǔ)信息表，例如：
員工編號 | 姓名 | 部門 | 基本工資
程序可以先讀取員工信息表，再讀取考勤表，最后通過員工編號進(jìn)行匹配，自動生成工資報表。
3. 增加請假、早退、缺勤等規(guī)則
工資計算規(guī)則也可以繼續(xù)擴展：
- 請假半天扣款
- 缺勤一天扣款
- 早退次數(shù)統(tǒng)計
- 加班工資計算
- 績效獎金計算
- 社保、公積金、個稅扣除
這些規(guī)則都可以封裝成函數(shù)，讓工資計算邏輯更清晰。
例如：
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
十三、企業(yè)辦公自動化實際應(yīng)用場景
這個案例雖然簡單，但背后的思路非常實用。企業(yè)辦公自動化中經(jīng)常會遇到類似需求：
1. HR 工資核算
HR 每月需要統(tǒng)計考勤、遲到、請假、缺勤、績效獎金等數(shù)據(jù)。使用 Python + openpyxl 可以把重復(fù)的 Excel 操作自動化，減少手動匯總時間。
2. 行政考勤統(tǒng)計
行政人員可以定期從打卡系統(tǒng)導(dǎo)出考勤數(shù)據(jù)，然后運行腳本自動生成部門考勤匯總表，把異常記錄高亮顯示。
3. 財務(wù)工資復(fù)核
財務(wù)部門可以拿到自動生成的工資表后，快速檢查扣款、實發(fā)工資、異常員工，提高復(fù)核效率。
4. 部門月度報表
除了工資表，類似的方式還可以生成銷售統(tǒng)計表、項目工時報表、庫存明細(xì)表、費用報銷表等。
5. 定時任務(wù)自動生成報表
如果腳本部署在服務(wù)器上，還可以結(jié)合 Windows 任務(wù)計劃程序或 Linux crontab，每月自動生成工資統(tǒng)計表，并通過郵件發(fā)送給相關(guān)負(fù)責(zé)人。
十四、初學(xué)者容易踩的坑
1. 文件正在被 Excel 打開
如果 salary_attendance_report.xlsx 正在被 Excel 打開，Python 保存時可能會報錯。解決方法是先關(guān)閉 Excel 文件，再運行腳本。
2. 時間格式不統(tǒng)一
考勤數(shù)據(jù)中可能出現(xiàn) 9:00、09:00、09:00:00 等不同格式。真實項目中需要先做數(shù)據(jù)清洗。
3. 中文字體顯示問題
如果需要設(shè)置指定中文字體，可以使用：
Font(name="微軟雅黑", bold=True)
不過不同電腦上字體環(huán)境可能不同，建議使用常見系統(tǒng)字體。
4. 公式和 Python 計算的選擇
工資扣款既可以由 Python 直接計算后寫入，也可以寫成 Excel 公式。例如：
ws["H3"] = "=F3*G3" ws["I3"] = "=D3-H3"
如果希望用戶打開 Excel 后能看到公式，可以使用 Excel 公式；如果希望結(jié)果固定、方便系統(tǒng)導(dǎo)入，可以使用 Python 直接計算。本文采用 Python 直接計算，更適合自動導(dǎo)出報表。
十五、總結(jié)
本文使用 Python + openpyxl 實現(xiàn)了一個“自動生成工資表與員工考勤統(tǒng)計”的完整案例，覆蓋了辦公自動化中非常常見的 Excel 報表處理流程。
你已經(jīng)學(xué)習(xí)了：
- openpyxl 庫的基本用途
- 如何創(chuàng)建 Excel 工作簿
- 如何自動合并標(biāo)題單元格
- 如何設(shè)置表頭樣式
- 如何寫入員工數(shù)據(jù)
- 如何統(tǒng)計遲到次數(shù)
- 如何計算工資扣款和實發(fā)工資
- 如何對異常員工進(jìn)行顏色高亮
- 如何導(dǎo)出完整工資統(tǒng)計表
- 如何把案例擴展到企業(yè)真實辦公場景
對于 Python 辦公自動化初學(xué)者來說，掌握這個案例后，就可以繼續(xù)擴展到更多 Excel 自動化任務(wù)，例如批量生成報表、合并多份表格、自動整理數(shù)據(jù)、自動發(fā)送郵件等。
只要你的工作中存在大量重復(fù)性的 Excel 操作，就可以考慮用 Python 把它自動化。
以上就是Python+openpyxl自動生成工資表與員工考勤統(tǒng)計的詳細(xì)內(nèi)容，更多關(guān)于Python openpyxl自動生成表的資料請關(guān)注腳本之家其它相關(guān)文章！
相關(guān)文章
keras 自定義loss model.add_loss的使用詳解
這篇文章主要介紹了keras 自定義loss model.add_loss的使用詳解，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-06-06
這篇文章主要為大家介紹了一款強大的端到端測試工具Playwright介紹，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步，早日升職加薪2023-01-01
Python+Empyrical實現(xiàn)計算風(fēng)險指標(biāo)
Empyrical 是一個知名的金融風(fēng)險指標(biāo)庫。它能夠用于計算年平均回報、最大回撤、Alpha值等。下面就教你如何使用 Empyrical 這個風(fēng)險指標(biāo)計算神器2022-05-05
在?Python?中,斷點是一種在代碼中設(shè)置的標(biāo)記,用于在程序執(zhí)行過程中停止或中斷程序的執(zhí)行,本文將詳細(xì)介紹?Python?中的斷點類型,感興趣的可以了解下2024-02-02
在jupyter notebook中使用pytorch的方法
這篇文章主要介紹了在jupyter notebook中使用pytorch的方法,本文給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下2022-09-09
這篇文章主要給大家介紹了關(guān)于numpy基礎(chǔ)教程之np.linalg的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧2019-02-02
Python第三方庫導(dǎo)出與批量安裝的詳細(xì)教程
本文詳細(xì)介紹了如何一鍵導(dǎo)出Python項目的第三方依賴包版本,包括導(dǎo)出環(huán)境中的所有第三方包和導(dǎo)出指定目錄下所有Python腳本依賴的第三方包,并提供了詳細(xì)的步驟和注意事項,需要的朋友可以參考下2026-01-01
這篇文章主要介紹了Pycharm使用Gitee解讀，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2023-05-05
這篇文章主要介紹了python爬蟲之爬取百度翻譯,文中有非常詳細(xì)的代碼示例,對正在學(xué)習(xí)python的小伙伴們喲喲非常好的幫助,需要的朋友可以參考下2021-04-04
在Python中,輸出九九乘法表是一道非常基礎(chǔ)的題目,但對于初學(xué)者來說,也是一個很好的練習(xí)和鞏固基礎(chǔ)知識的機會,這篇文章主要給大家介紹了關(guān)于Python九九乘法表的七種實現(xiàn)方式,需要的朋友可以參考下2023-09-09