bwkyd 工资条/薪资表生成器
薪资数据 → 个税计算 → 规范工资条 → 批量生成。HR和财务的必备工具。
When to Use
- 每月生成员工工资条
- 需要计算个税和五险一金
- 批量生成可打印的工资条
- 用户说"帮我做工资条""算个税"
When NOT to Use
- 财务报表分析 → 使用
wps-financial-report - 考勤统计 → 使用
wps-attendance
个税税率表（2024年累计预扣法）
累计应纳税所得额 税率 速算扣除数
≤36,000 3% 0
36,000-144,000 10% 2,520
144,000-300,000 20% 16,920
300,000-420,000 25% 31,920
420,000-660,000 30% 52,920
660,000-960,000 35% 85,920
>960,000 45% 181,920
起征点：5,000元/月
专项扣除：五险一金个人部分
专项附加扣除：子女教育1000/月、继续教育400/月、
大病医疗据实、住房贷款1000/月、住房租金800-1500/月、
赡养老人2000/月（独生）
工作流程
Step 1: 确认薪资结构
标准工资条结构：
┌─────────────────────────────────────────┐
│ XX公司工资条 2026年3月 │
├─────────┬───────────┬──────────────────┤
│ 姓名 │ 张三 │ 部门：技术部 │
│ 工号 │ EMP001 │ 岗位：高级工程师 │
├─────────┴───────────┴──────────────────┤
│ 【应发项目】 │
│ 基本工资 岗位工资 绩效奖金 加班费│
│ 8,000 4,000 2,000 500 │
│ 应发合计：14,500 │
├────────────────────────────────────────┤
│ 【扣除项目】 │
│ 养老8% 医疗2% 失业0.5% 公积金12% │
│ 1,160 290 72.5 1,740 │
│ 个人所得税：145.88 │
│ 扣除合计：3,408.38 │
├────────────────────────────────────────┤
│ 实发工资：11,091.62 │
└────────────────────────────────────────┘
Step 2: 计算引擎
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, Alignment, Border, Side, PatternFill
from datetime import datetime
import os
class SalaryCalculator:
"""薪资计算器"""
# 个税累进税率
TAX_BRACKETS = [
(36000, 0.03, 0),
(144000, 0.10, 2520),
(300000, 0.20, 16920),
(420000, 0.25, 31920),
(660000, 0.30, 52920),
(960000, 0.35, 85920),
(float('inf'), 0.45, 181920),
]
# 五险一金比例（个人部分，各地不同，这是通用参考）
INSURANCE_RATES = {
'养老': 0.08,
'医疗': 0.02,
'失业': 0.005,
'公积金': 0.12,
}
def __init__(self, base=5000, special_deduction=0):
self.threshold = base # 起征点
self.special_deduction = special_deduction # 专项附加扣除
def calc_insurance(self, base_salary):
"""计算五险一金个人部分"""
result = {}
for name, rate in self.INSURANCE_RATES.items():
result[name] = round(base_salary * rate, 2)
result['合计'] = round(sum(result.values()), 2)
return result
def calc_tax(self, taxable_income_ytd, taxable_income_prev_ytd=0):
"""累计预扣法计算个税"""
def _calc(income):
for limit, rate, deduction in self.TAX_BRACKETS:
if income <= limit:
return round(income * rate - deduction, 2)
return 0
tax_ytd = _calc(taxable_income_ytd)
tax_prev = _calc(taxable_income_prev_ytd)
return max(round(tax_ytd - tax_prev, 2), 0)
def calc_monthly(self, gross, insurance_base, month, prev_taxable_ytd=0):
"""计算单月薪资"""
insurance = self.calc_insurance(insurance_base)
taxable = gross - insurance['合计'] - self.threshold - self.special_deduction
taxable = max(taxable, 0)
taxable_ytd = prev_taxable_ytd + taxable
tax = self.calc_tax(taxable_ytd, prev_taxable_ytd)
net = round(gross - insurance['合计'] - tax, 2)
return {
'应发合计': gross,
'五险一金': insurance,
'应纳税所得额': taxable,
'个税': tax,
'扣除合计': round(insurance['合计'] + tax, 2),
'实发工资': net,
}
def generate_salary_slips(data_path, output_path, year_month):
"""从Excel数据生成工资条"""
wb_data = load_workbook(data_path)
ws_data = wb_data.active
wb_out = Workbook()
ws = wb_out.active
ws.title = f"工资条{year_month}"
calc = SalaryCalculator()
row = 1
# 读取表头
headers = [cell.value for cell in ws_data[1]]
for data_row in ws_data.iter_rows(min_row=2, values_only=True):
emp = dict(zip(headers, data_row))
gross = float(emp.get('应发合计', 0) or 0)
base = float(emp.get('社保基数', gross) or gross)
result = calc.calc_monthly(gross, base, int(year_month[-2:]))
# 写入工资条格式
_write_slip(ws, row, emp, result, year_month)
row += 8 # 每个工资条占7行 + 1行空行
wb_out.save(output_path)
return os.path.abspath(output_path)
def _write_slip(ws, start_row, emp, result, year_month):
"""写入单个工资条"""
thin = Side(style='thin')
border = Border(left=thin, right=thin, top=thin, bottom=thin)
header_fill = PatternFill('solid', fgColor='4472C4')
header_font = Font(name='微软雅黑', size=11, bold=True, color='FFFFFF')
body_font = Font(name='微软雅黑', size=10)
r = start_row
# 标题行
ws.merge_cells(start_row=r, start_column=1, end_row=r, end_column=8)
cell = ws.cell(row=r, column=1,
value=f"工资条 {year_month} 姓名：{emp.get('姓名','')} "
f"部门：{emp.get('部门','')}")
cell.font = header_font
cell.fill = header_fill
cell.alignment = Alignment(horizontal='center')
r += 1
items = [
('基本工资', emp.get('基本工资', '')),
('岗位工资', emp.get('岗位工资', '')),
('绩效', emp.get('绩效', '')),
('应发合计', result['应发合计']),
('五险一金', result['五险一金']['合计']),
('个税', result['个税']),
('扣除合计', result['扣除合计']),
('实发工资', result['实发工资']),
]
for col, (label, val) in enumerate(items, 1):
ws.cell(row=r, column=col, value=label).font = Font(
name='微软雅黑', size=9, bold=True)
ws.cell(row=r, column=col).border = border
ws.cell(row=r+1, column=col, value=val).font = body_font
ws.cell(row=r+1, column=col).border = border
ws.cell(row=r+1, column=col).number_format = '#,##0.00'
Step 3: 交付
- 生成工资条Excel文件（含格式）
- 或生成可打印的工资条（每页N条，带裁切线）
- 提供个税验证说明
JSA宏：在WPS中直接生成工资条
// JSA: 薪资表→工资条转换
function SalaryToSlips() {
var ws = Application.ActiveSheet;
var lastRow = ws.Cells(ws.Rows.Count, 1).End(-4162).Row;
var lastCol = ws.Cells(1, ws.Columns.Count).End(-4159).Column;
var newWs = Application.Sheets.Add();
newWs.Name = "工资条";
var outRow = 1;
for (var i = 2; i <= lastRow; i++) {
// 复制表头
for (var j = 1; j <= lastCol; j++) {
newWs.Cells(outRow, j).Value2 = ws.Cells(1, j).Value2;
newWs.Cells(outRow, j).Font.Bold = true;
}
outRow++;
// 复制数据
for (var j = 1; j <= lastCol; j++) {
newWs.Cells(outRow, j).Value2 = ws.Cells(i, j).Value2;
}
outRow++;
outRow++; // 空行（便于裁切）
}
Application.alert("已生成 " + (lastRow - 1) + " 条工资条");
}
示例
# 生成工资条
/wps-salary 帮我把这个薪资表生成工资条格式，方便打印发给员工
# 计算个税
/wps-salary 月薪15000，社保基数12000，公积金12%，算一下到手多少
# 批量生成
/wps-salary 用salary_data.xlsx生成每个人独立的工资条