招聘人员需要用到的20个Python脚本工具

import pandas as pd
# 创建示例数据
data = {
    '姓名': ['张三', '李四', '王五', '赵六', '孙七'],
    '联系方式': ['13800000000', '13900000000', '13700000000', '13600000000', '13500000000'],
    '邮箱': ['zhangsan@example.com', 'lisi@example.com', 'wangwu@example.com', 'zhaoliu@example.com', 'sunqi@example.com'],
    '应聘职位': ['软件工程师', '产品经理', 'UI设计师', '测试工程师', '项目经理'],
    '工作经验（年）': [3, 5, 2, 4, 6],
    '教育背景': ['本科', '硕士', '本科', '本科', '硕士'],
    '技能': ['Python, Java, SQL', '项目管理, 产品设计', 'Photoshop, Sketch', 'Selenium, JUnit', '敏捷开发, Scrum']
}
# 保存到Excel文件
df = pd.DataFrame(data)
df.to_excel('candidates.xlsx', index=False)

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

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

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

from openpyxl import load_workbook
# 加载现有工作簿
wb = load_workbook('candidates.xlsx')
# 创建新的工作表
ws = wb.create_sheet(title="新工作表")
# 保存工作簿
wb.save('candidates_with_new_sheet.xlsx')

from openpyxl import load_workbook
# 加载现有工作簿
wb = load_workbook('candidates.xlsx')
# 删除指定的工作表
if '新工作表' in wb.sheetnames:
    del wb['新工作表']
# 保存工作簿
wb.save('candidates_deleted_sheet.xlsx')

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')

from openpyxl import load_workbook
# 加载现有工作簿
wb = load_workbook('candidates.xlsx')
# 重命名指定的工作表
sheet = wb['Sheet1']
sheet.title = "重命名的工作表"
# 保存工作簿
wb.save('candidates_renamed_sheet.xlsx')

import pandas as pd
# 读取现有数据
df = pd.read_excel('candidates.xlsx')
# 查找特定值
result = df[df['姓名'] == '张三']
print(result)

import pandas as pd
# 读取现有数据
df = pd.read_excel('candidates.xlsx')
# 筛选出工作经验大于等于3年的候选人
filtered_df = df[df['工作经验（年）'] >= 3]
# 打印筛选结果
print(filtered_df)

import pandas as pd
# 读取现有数据
df = pd.read_excel('candidates.xlsx')
# 按工作经验降序排序
sorted_df = df.sort_values(by='工作经验（年）', ascending=False)
# 打印排序结果
print(sorted_df)

import pandas as pd
# 读取现有数据
df = pd.read_excel('candidates.xlsx')
# 按应聘职位分组并计算人数
grouped_df = df.groupby('应聘职位').size()
# 打印分组汇总结果
print(grouped_df)

from openpyxl import load_workbook
# 加载现有工作簿
wb = load_workbook('candidates.xlsx')
ws = wb.active
# 合并单元格A1到C1
ws.merge_cells('A1:C1')
# 保存工作簿
wb.save('candidates_merged_cells.xlsx')

from openpyxl import load_workbook
from openpyxl.styles import Font, Alignment
# 加载现有工作簿
wb = load_workbook('candidates.xlsx')
ws = wb.active
# 设置A1单元格的字体和对齐方式
cell = ws['A1']
cell.font = Font(bold=True, color="FF0000")
cell.alignment = Alignment(horiznotallow='center', vertical='center')
# 保存工作簿
wb.save('candidates_formatted_cell.xlsx')

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')

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} 年")

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')

from openpyxl import load_workbook
# 加载现有工作簿
wb = load_workbook('candidates.xlsx')
ws = wb.active
# 拆分A1到C1的合并单元格
ws.unmerge_cells('A1:C1')
# 保存工作簿
wb.save('candidates_unmerged_cells.xlsx')

from openpyxl import load_workbook
# 加载现有工作簿
wb = load_workbook('candidates.xlsx')
ws = wb.active
# 清除A1单元格的内容
ws['A1'].value = None
# 清除A1单元格的样式
ws['A1'].font = None
ws['A1'].fill = None
ws['A1'].border = None
ws['A1'].alignment = None
ws['A1'].number_format = None
ws['A1'].protection = None
# 保存工作簿
wb.save('candidates_cleared_content_and_style.xlsx')

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')

import smtplib
from email.mime.text import MIMEText
from email.header import Header
# 邮件配置
sender = 'your_email@example.com'
receivers = ['candidate_email@example.com']
smtp_server = 'smtp.example.com'
smtp_port = 587
username = 'your_email@example.com'
password = 'your_password'
# 邮件内容
message = MIMEText('您好，您的面试已安排，请准时参加。', 'plain', 'utf-8')
message['From'] = Header("招聘团队", 'utf-8')
message['To'] = Header("候选人", 'utf-8')
message['Subject'] = Header('面试通知', 'utf-8')
try:
    # 连接到SMTP服务器
    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()