Python Scripts for Automating HR Recruitment Processes with Excel and Email

Introduction : HR recruiters often handle large volumes of resumes, interview scheduling, and candidate evaluation; Python provides powerful tools to automate these processes and improve efficiency.

1. Create example data (candidates.xlsx) : Use pandas to generate a sample Excel file with candidate information.

import pandas as pd
# 创建示例数据
data = {
    '姓名': ['张三', '李四', '王五', '赵六', '孙七'],
    '联系方式': ['13800000000', '13900000000', '13700000000', '13600000000', '13500000000'],
    '邮箱': ['[email protected]', '[email protected]', '[email protected]', '[email protected]', '[email protected]'],
    '应聘职位': ['软件工程师', '产品经理', 'UI设计师', '测试工程师', '项目经理'],
    '工作经验（年）': [3, 5, 2, 4, 6],
    '教育背景': ['本科', '硕士', '本科', '本科', '硕士'],
    '技能': ['Python, Java, SQL', '项目管理, 产品设计', 'Photoshop, Sketch', 'Selenium, JUnit', '敏捷开发, Scrum']
}

df = pd.DataFrame(data)
df.to_excel('candidates.xlsx', index=False)

2. Read Excel file : Load candidate data with pandas.read_excel .

import pandas as pd
# 读取Excel文件
df = pd.read_excel('candidates.xlsx')
print(df.head())

3. Write Excel file : Save processed data to a new file.

import pandas as pd
# 读取现有数据
df = pd.read_excel('candidates.xlsx')
# 写入新的Excel文件
df.to_excel('processed_candidates.xlsx', index=False)

4. Update specific cell : Modify a candidate's contact information.

import pandas as pd
# 读取现有数据
df = pd.read_excel('candidates.xlsx')
# 更新第一个候选人的联系方式
df.at[0, '联系方式'] = '13811111111'
# 保存更新后的数据
df.to_excel('updated_candidates.xlsx', index=False)

5. Add new worksheet : Use openpyxl to create an additional sheet.

from openpyxl import load_workbook
wb = load_workbook('candidates.xlsx')
ws = wb.create_sheet(title="新工作表")
wb.save('candidates_with_new_sheet.xlsx')

6. Delete worksheet : Remove a specified sheet.

from openpyxl import load_workbook
wb = load_workbook('candidates.xlsx')
if '新工作表' in wb.sheetnames:
    del wb['新工作表']
wb.save('candidates_deleted_sheet.xlsx')

7. Copy worksheet : Duplicate an existing sheet.

from openpyxl import load_workbook
wb = load_workbook('candidates.xlsx')
source = wb['Sheet1']
target = wb.copy_worksheet(source)
target.title = "复制的工作表"
wb.save('candidates_copied_sheet.xlsx')

8. Rename worksheet : Change a sheet's title.

from openpyxl import load_workbook
wb = load_workbook('candidates.xlsx')
sheet = wb['Sheet1']
sheet.title = "重命名的工作表"
wb.save('candidates_renamed_sheet.xlsx')

9. Find specific value : Locate rows matching a given name.

import pandas as pd
df = pd.read_excel('candidates.xlsx')
result = df[df['姓名'] == '张三']
print(result)

10. Filter data : Select candidates with at least three years of experience.

import pandas as pd
df = pd.read_excel('candidates.xlsx')
filtered_df = df[df['工作经验（年）'] >= 3]
print(filtered_df)

11. Sort data : Order candidates by experience descending.

import pandas as pd
df = pd.read_excel('candidates.xlsx')
sorted_df = df.sort_values(by='工作经验（年）', ascending=False)
print(sorted_df)

12. Group and summarize : Count candidates per applied position.

import pandas as pd
df = pd.read_excel('candidates.xlsx')
grouped_df = df.groupby('应聘职位').size()
print(grouped_df)

13. Merge cells : Combine a range of cells using openpyxl .

from openpyxl import load_workbook
wb = load_workbook('candidates.xlsx')
ws = wb.active
ws.merge_cells('A1:C1')
wb.save('candidates_merged_cells.xlsx')

14. Set cell format : Apply font style and alignment.

from openpyxl import load_workbook
from openpyxl.styles import Font, Alignment
wb = load_workbook('candidates.xlsx')
ws = wb.active
cell = ws['A1']
cell.font = Font(bold=True, color="FF0000")
cell.alignment = Alignment(horizontal='center', vertical='center')
wb.save('candidates_formatted_cell.xlsx')

15. Insert chart : Add a bar chart showing work‑experience distribution.

import pandas as pd
from openpyxl import load_workbook
from openpyxl.chart import BarChart, Reference
df = pd.read_excel('candidates.xlsx')
df.to_excel('temp_candidates.xlsx', index=False)
wb = load_workbook('temp_candidates.xlsx')
ws = wb.active
chart = BarChart()
data = Reference(ws, min_col=5, min_row=1, max_row=len(df)+1, max_col=5)
categories = Reference(ws, min_col=1, min_row=2, max_row=len(df)+1)
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
chart.title = "工作经验分布"
ws.add_chart(chart, "F1")
wb.save('candidates_with_chart.xlsx')

16. Calculate totals and averages : Compute sum and mean of experience years.

import pandas as pd
df = pd.read_excel('candidates.xlsx')
total_experience = df['工作经验（年）'].sum()
average_experience = df['工作经验（年）'].mean()
print(f"总工作经验: {total_experience} 年")
print(f"平均工作经验: {average_experience:.2f} 年")

17. Conditional formatting : Highlight cells with experience less than three years.

from openpyxl import load_workbook
from openpyxl.formatting.rule import CellIsRule
from openpyxl.styles import PatternFill
wb = load_workbook('candidates.xlsx')
ws = wb.active
red_fill = PatternFill(start_color="FF0000", end_color="FF0000", fill_type="solid")
rule = CellIsRule(operator='lessThan', formula=['3'], fill=red_fill)
ws.conditional_formatting.add('E2:E6', rule)
wb.save('candidates_conditional_format.xlsx')

18. Unmerge cells : Split previously merged cells.

from openpyxl import load_workbook
wb = load_workbook('candidates.xlsx')
ws = wb.active
ws.unmerge_cells('A1:C1')
wb.save('candidates_unmerged_cells.xlsx')

19. Auto‑adjust column widths : Dynamically set column widths based on content length.

from openpyxl import load_workbook
wb = load_workbook('candidates.xlsx')
ws = wb.active
for col in ws.columns:
    max_length = 0
    column = col[0].column_letter
    for cell in col:
        try:
            if len(str(cell.value)) > max_length:
                max_length = len(cell.value)
        except:
            pass
    adjusted_width = (max_length + 2)
    ws.column_dimensions[column].width = adjusted_width
wb.save('candidates_auto_adjusted_columns.xlsx')

20. Send email notification : Use smtplib to email interview invitations to candidates.

import smtplib
from email.mime.text import MIMEText
from email.header import Header
sender = '[email protected]'
receivers = ['[email protected]']
smtp_server = 'smtp.example.com'
smtp_port = 587
username = '[email protected]'
password = 'your_password'
message = MIMEText('您好，您的面试已安排，请准时参加。', 'plain', 'utf-8')
message['From'] = Header('招聘团队', 'utf-8')
message['To'] = Header('候选人', 'utf-8')
message['Subject'] = Header('面试通知', 'utf-8')
try:
    server = smtplib.SMTP(smtp_server, smtp_port)
    server.starttls()
    server.login(username, password)
    server.sendmail(sender, receivers, message.as_string())
    print('邮件发送成功')
except Exception as e:
    print(f'邮件发送失败: {e}')
finally:
    server.quit()

Conclusion : The provided Python snippets cover a wide range of tools that HR recruiters can adapt to automate resume handling, data analysis, and communication, thereby significantly improving recruitment efficiency.