人力资源管理工作的其他Python运用
相信大家工作中,会时常有员工向我们所要离职证明或者实习协议,遇见我们人事工作手头很紧,忙不过来,那么自动生成神奇你值得拥有,其实逻辑跟上面那个文章差不多,大家看一下就懂了,就可以发挥各种想象去开发了!
脚本逻辑:
1.我们公司会有自己制作的花名册excel,里面有很多sheet,有个“离职”sheet
2.请确认列数对应与脚本一致,不一致修改即可
准备模板文件(命名:“离职证明【模板勿动】.docx”),请务必替换字符要带下划线;
XXX有限公司
离职证明
兹有 礥 ,身份证号码为 懿 ,于 奰 年 躄 月 罍与我司签订劳动合同,离职时工作岗位为 鰘 。
经协商一致,我司同意该员工于 颣 年 薐 月 豳 日解除劳动合同。
特此证明。
本人签字:
XXX有限公司
颣 年 薐 月 豳 日
代码一(无UI):
from docx import Document
from openpyxl import load_workbook
import os
import datetime
import time
# 结合路径判断生成文件夹,规避程序报错而终止的风险
if not os.path.exists('./' + '离职证明导出'):
os.mkdir('./' + '离职证明导出')
print("创建目录成功")
import tkinter as tk
from tkinter import filedialog
print("请选择花名册文件")
try:
root = tk.Tk()
root.withdraw()
Filepath = filedialog.askopenfilename()
workbook = load_workbook(Filepath)
except:
print("未选择文件,请重新选择!")
root = tk.Tk()
root.withdraw()
Filepath = filedialog.askopenfilename()
workbook = load_workbook(Filepath)
sheet = workbook["离职"]
numbers = int(sheet.max_row)-1
who = input("请输入姓名:")
summit = 0
for table_row in range(2, sheet.max_row + 1):
name = str(sheet.cell(row=table_row, column=6).value)
if name == who:
print("找到1个符合条件")
try:
try:
print("=====入职日期=====")
date_kaishi = sheet['I{}'.format(table_row)].value
try:
date_kaishi_year = "20"+str(date_kaishi.strftime('%y'))
date_kaishi_month = str(date_kaishi.strftime('%m'))
date_kaishi_day = str(date_kaishi.strftime('%d'))
except:
date_kaishi_year = datetime.datetime.strptime(date_kaishi,'%Y-%m-%d').year
date_kaishi_month = datetime.datetime.strptime(date_kaishi,'%Y-%m-%d').month
date_kaishi_day = datetime.datetime.strptime(date_kaishi,'%Y-%m-%d').day
print(date_kaishi_year)
print(date_kaishi_month)
print(date_kaishi_day)
print("=====离职日期=====")
date_zhongzhi = sheet['J{}'.format(table_row)].value
try:
date_zhongzhi_year = "20"+str(date_zhongzhi.strftime('%y'))
date_zhongzhi_month = str(date_zhongzhi.strftime('%m'))
date_zhongzhi_day = str(date_zhongzhi.strftime('%d'))
except:
date_zhongzhi_year = datetime.datetime.strptime(date_zhongzhi,'%Y-%m-%d').year
date_zhongzhi_month = datetime.datetime.strptime(date_zhongzhi,'%Y-%m-%d').month
date_zhongzhi_day = datetime.datetime.strptime(date_zhongzhi,'%Y-%m-%d').day
print(date_zhongzhi_year)
print(date_zhongzhi_month)
print(date_zhongzhi_day)
print("=====身份证=====")
date_ID = sheet['H{}'.format(table_row)].value
print(date_ID)
print("=====岗位=====")
GW = sheet['E{}'.format(table_row)].value
print(GW)
wordfile = Document('./' + '离职证明【模板勿动】.docx')
all_paragraphs = wordfile.paragraphs
for paragraph in all_paragraphs:
for run in paragraph.runs:
try:
if "奰" in run.text:
run.text = run.text.replace("奰", date_kaishi_year)
elif "躄" in run.text:
run.text = run.text.replace("躄", date_kaishi_month)
elif "罍" in run.text:
run.text = run.text.replace("罍", date_kaishi_day)
elif "颣" in run.text:
run.text = run.text.replace("颣", date_zhongzhi_year)
elif "薐" in run.text:
run.text = run.text.replace("薐", date_zhongzhi_month)
elif "豳" in run.text:
run.text = run.text.replace("豳", date_zhongzhi_day)
elif "懿" in run.text:
run.text = run.text.replace("懿", str(date_ID))
elif "鰘" in run.text:
run.text = run.text.replace("鰘", str(GW))
elif "礥" in run.text:
run.text = run.text.replace("礥", str(who))
except Exception as e:
print("替换文本出错:"+str(e))
wordfile.save('./' + f'离职证明导出/{table_row}_{name}_离职证明.docx')
print(f"{table_row}_{name}_离职证明.docx | 另存成功")
summit += 1
except Exception as e:
print("内出错:"+str(e))
except Exception as e:
print("外出错:"+str(e))
if summit == 0:
print(f"未查到 {who}!!!!!\n")
elif summit == 1:
print("导出一份,结束!\n")
elif summit >= 2:
print("结束!\n")
print(f"本次共导出 {summit} 个 {who}的离职文件,请注意筛选!!!!!\n")
input ("Please Enter to close this exe:")
代码一(有UI,用的wxpython框架):
# -*- coding: utf-8 -*-
import wx
import time
import tkinter as tk
from tkinter import filedialog
from docx import Document
from openpyxl import load_workbook
import os
import datetime
def lzzm(Filepath,who):
if not os.path.exists('./' + '离职证明导出'):
os.mkdir('./' + '离职证明导出')
contents.AppendText("创建目录成功\n")
workbook = load_workbook(Filepath)
sheet = workbook["离职"]
summit = 0
for table_row in range(2, sheet.max_row + 1):
name = str(sheet.cell(row=table_row, column=6).value)
if name == who:
contents.AppendText("查到一个符合条件\n")
try:
try:
contents.AppendText("=====入职日期=====\n")
date_kaishi = sheet['I{}'.format(table_row)].value
try:
date_kaishi_year = "20"+str(date_kaishi.strftime('%y'))
date_kaishi_month = str(date_kaishi.strftime('%m'))
date_kaishi_day = str(date_kaishi.strftime('%d'))
except:
date_kaishi_year = datetime.datetime.strptime(date_kaishi,'%Y-%m-%d').year
date_kaishi_month = datetime.datetime.strptime(date_kaishi,'%Y-%m-%d').month
date_kaishi_day = datetime.datetime.strptime(date_kaishi,'%Y-%m-%d').day
contents.AppendText(date_kaishi_year)
contents.AppendText(date_kaishi_month)
contents.AppendText(date_kaishi_day+"\n")
contents.AppendText("=====离职日期=====\n")
date_zhongzhi = sheet['J{}'.format(table_row)].value
try:
date_zhongzhi_year = "20"+str(date_zhongzhi.strftime('%y'))
date_zhongzhi_month = str(date_zhongzhi.strftime('%m'))
date_zhongzhi_day = str(date_zhongzhi.strftime('%d'))
except:
date_zhongzhi_year = datetime.datetime.strptime(date_zhongzhi,'%Y-%m-%d').year
date_zhongzhi_month = datetime.datetime.strptime(date_zhongzhi,'%Y-%m-%d').month
date_zhongzhi_day = datetime.datetime.strptime(date_zhongzhi,'%Y-%m-%d').day
contents.AppendText(date_zhongzhi_year)
contents.AppendText(date_zhongzhi_month)
contents.AppendText(date_zhongzhi_day+"\n")
contents.AppendText("=====身份证=====\n")
date_ID = sheet['H{}'.format(table_row)].value
contents.AppendText(str(date_ID)+"\n")
contents.AppendText("=====岗位=====\n")
GW = sheet['E{}'.format(table_row)].value
contents.AppendText(str(GW)+"\n")
contents.AppendText("即将生成文件...\n")
wordfile = Document('./' + '离职证明【模板勿动】.docx')
all_paragraphs = wordfile.paragraphs
for paragraph in all_paragraphs:
for run in paragraph.runs:
try:
if "奰" in run.text:
run.text = run.text.replace("奰", date_kaishi_year)
elif "躄" in run.text:
run.text = run.text.replace("躄", date_kaishi_month)
elif "罍" in run.text:
run.text = run.text.replace("罍", date_kaishi_day)
elif "颣" in run.text:
run.text = run.text.replace("颣", date_zhongzhi_year)
elif "薐" in run.text:
run.text = run.text.replace("薐", date_zhongzhi_month)
elif "豳" in run.text:
run.text = run.text.replace("豳", date_zhongzhi_day)
elif "懿" in run.text:
run.text = run.text.replace("懿", str(date_ID))
elif "鰘" in run.text:
run.text = run.text.replace("鰘", str(GW))
elif "礥" in run.text:
run.text = run.text.replace("礥", str(who))
except Exception as e:
print("替换文本出错:"+str(e))
wordfile.save('./' + f'离职证明导出/{table_row}_{name}_离职证明.docx')
contents.AppendText(f"{table_row}_{name}_离职证明.docx | 另存成功\n")
summit += 1
except Exception as e:
contents.AppendText("内出错:"+str(e)+"\n")
except Exception as e:
contents.AppendText("外出错:"+str(e)+"\n")
if summit == 0:
contents.AppendText(f"未查到 {who}!!!!!\n")
elif summit == 1:
contents.AppendText("导出一份,结束!\n")
elif summit >= 2:
contents.AppendText("结束!\n")
contents.AppendText(f"本次共导出 {summit} 个 {who}的离职文件,请注意筛选!!!!!\n")
def choice(event):
print("请选择文件")
root = tk.Tk()
root.withdraw()
Filepath = filedialog.askopenfilename() #获得选择好的文件
print('Filepath:',Filepath)
filename.SetValue(Filepath)
def Go(event):
if filename.GetValue() == "" or filename.GetValue() == "未选择文件":
contents.AppendText("未选择文件,请选择后运行\n")
elif filename1.GetValue() == "请输入姓名" or filename1.GetValue() == "":
contents.AppendText("未输入姓名,请输入后运行\n")
else:
try:
lzzm(Filepath=filename.GetValue(),who=filename1.GetValue())
except Exception as e:
contents.AppendText(f"{e}:运行失败!\n")
app = wx.App()
win = wx.Frame(None,title = "离职证明快速生成小助手", size=(535,520))
bkg = wx.Panel(win)
#设置icon
##icon = wx.Icon(r'logo.ico')
##win.SetIcon(icon)
#设置透明度
win.SetTransparent(230)
loadButton = wx.Button(bkg, label = '选择文件')
loadButton.Bind(wx.EVT_BUTTON,choice)
saveButton = wx.Button(bkg, label = '开始运行')
saveButton.Bind(wx.EVT_BUTTON,Go)
filename = wx.TextCtrl(bkg,value = "未选择文件", style = wx.TE_READONLY)
filename1 = wx.TextCtrl(bkg,value = "请输入姓名")
contents = wx.TextCtrl(bkg,value = "程序指南:\n1.选择文件选择花名册后\n2.请输入需要制作离职证明的姓名\n3.点击开始运行,如果在离职的sheet里面存在此人会导出,反之不存在!\n ====================\n", style = wx.TE_MULTILINE | wx.HSCROLL | wx.TE_READONLY)
hbox = wx.BoxSizer()
hbox.Add(filename, proportion =1, flag = wx.EXPAND)
hbox.Add(loadButton, proportion =0,flag = wx.LEFT, border = 5)
pbox = wx.BoxSizer()
pbox.Add(filename1, proportion =1, flag = wx.EXPAND)
pbox.Add(saveButton, proportion =0,flag = wx.LEFT, border = 5)
vbox = wx.BoxSizer(wx.VERTICAL)
vbox.Add(hbox,proportion = 0,flag = wx.EXPAND | wx.ALL, border = 5)
vbox.Add(pbox,proportion = 0,flag = wx.EXPAND | wx.ALL, border = 5)
vbox.Add(contents, proportion = 1,flag=wx.EXPAND | wx.LEFT | wx.BOTTOM | wx.RIGHT, border = 5)
bkg.SetSizer(vbox)
win.Show()
app.MainLoop()
测试:
2023年2月27如升级版:自动根据标题处理
import xlrd
import time
import tkinter as tk
from tkinter import filedialog
from docx import Document
def info_update(doc, old_info, new_info):
for para in doc.paragraphs:
for run in para.runs:
run.text = run.text.replace(old_info, new_info)
for table in doc.tables:
for row in table.rows:
for cell in row.cells:
cell.text = cell.text.replace(old_info, new_info)
print('''
注意:请确保花名册excel存在“离职”sheet,且标题行存在“姓名”“岗位”“身份证号”“入职日期”“离职日期”
''')
print("="*30)
first = 0
while first == 0:
print("请选择花名册文件")
root = tk.Tk()
root.withdraw()
try:
Filepath = filedialog.askopenfilename()
print(Filepath)
workbook = xlrd.open_workbook(Filepath)
print("花名册读取成功...")
first = 1
except Exception as e:
print("出错:"+str(e))
print("一般报错都是你没有选择花名册文件哟")
time.sleep(5)
try:
sheet = workbook.sheet_by_name('离职')
row = sheet.row_values(0)
a = row.index('姓名')
b = row.index('身份证号')
c = row.index('入职日期')
d = row.index('岗位')
f = row.index('离职日期')
except Exception as e:
print("出错:"+str(e))
print("一般报错都是标题行不符合要求哟,请关闭程序核查...")
time.sleep(200)
e = 0
name = input('请输入姓名:')
for i in range(sheet.nrows):
if sheet.cell_value(i, a) == name:
e = i
break
if e == 0:
print(f"在离职sheet里面未找到 {name},脚本停止运行...")
time.sleep(200)
else:
print(name)
print('身份证号:', sheet.cell_value(e, b))
#入职日期
print(sheet.cell_value(e, c))
if type(sheet.cell_value(e, c)).__name__ == 'float':
rz_2 = xlrd.xldate_as_datetime(sheet.cell_value(e, c),0)
rz_1 = str(rz_2).split(" ")[0]
rz = "{}年{}月{}日".format(*rz_1.split("-"))
elif len(sheet.cell_value(e, c)) == 19 and ":" in sheet.cell_value(e, c):
rz_1 = sheet.cell_value(e, c).split(" ")[0]
rz = "{}年{}月{}日".format(*rz_1.split("-"))
else:
rz = sheet.cell_value(e, c)
print('入职日期:', rz)
#离职日期
print(sheet.cell_value(e, f))
if type(sheet.cell_value(e, f)).__name__ == 'float':
lz_2 = xlrd.xldate_as_datetime(sheet.cell_value(e, f),0)
lz_1 = str(lz_2).split(" ")[0]
lz = "{}年{}月{}日".format(*lz_1.split("-"))
elif len(sheet.cell_value(e, f)) == 19 and ":" in sheet.cell_value(e, f):
lz_1 = sheet.cell_value(e, f).split(" ")[0]
lz = "{}年{}月{}日".format(*lz_1.split("-"))
else:
lz = sheet.cell_value(e, f)
print('离职日期:', lz)
print('岗位:', sheet.cell_value(e, d))
id_num = sheet.cell_value(e, b)
sex = eval(id_num[16:18])
if(sex%2==0):
gender ="男"
else:
gender ="女"
print('性别:', gender)
try:
doc = Document('./模板/离职证明(脚本模板).docx')
except Exception as e:
print("找不到./模板/离职h证明(脚本模板).docx 文件,请关闭程序核查...")
time.sleep(200)
info_update(doc, '【姓名】', name)
info_update(doc, '【性别】', gender)
info_update(doc, '【身份证号】', sheet.cell_value(e, b))
info_update(doc, '【入职日期】', rz)
info_update(doc, '【离职日期】', rz)
info_update(doc, '【岗位】', sheet.cell_value(e, d))
print(f'生成 离职证明-{name}.docx')
doc.save(f'离职证明-{name}.docx')
print("="*30)
input("")
离职证明(脚本模板).docx 内容
离职证明
兹证明 【姓名】 (性别: 【性别】 ,身份证号: 【身份证号】 )自 【入职日期】 至 【离职日期】 在我司担任 【岗位】 职务,在此工作期间无不良表现,工作良好,同事关系融洽。经个人申请,公司研究决定同意其离职,已办理离职手续。
特此证明!
XXX有限责任公司
2023年2月27日