import pandas as pd
import numpy as np
from faker import Faker


# 初始化Faker，支持中文
fake = Faker("zh_CN")


# 生成100條員工數(shù)據(jù)
data = {
    "員工編號(hào)": range(1, 101),
    "姓名": [fake.name() for _ in range(100)],
    "部門": np.random.choice(["人力資源部", "財(cái)務(wù)部", "信息技術(shù)部", "銷售部", "市場(chǎng)部"], 100),
    "工資": np.random.randint(3000, 10000, 100),
    "入職日期": [fake.date_between(start_date="-5y", end_date="today") for _ in range(100)],
    "郵箱": [fake.email() for _ in range(100)],
}


# 創(chuàng)建DataFrame
df = pd.DataFrame(data)


# 保存為Excel文件
df.to_excel("員工數(shù)據(jù).xlsx", index=False)

import pandas as pd


# 讀取數(shù)據(jù)
df = pd.read_excel("員工數(shù)據(jù).xlsx")


# 格式化日期
df["入職日期"] = pd.to_datetime(df["入職日期"]).dt.strftime("%Y-%m-%d")


# 刪除工資低于5000的記錄
df = df[df["工資"] >= 5000]


# 保存清洗后的數(shù)據(jù)
df.to_excel("清洗后的員工數(shù)據(jù).xlsx", index=False)

import pandas as pd


df = pd.read_excel("員工數(shù)據(jù).xlsx")
departments = df["部門"].unique()


with pd.ExcelWriter("按部門拆分的員工數(shù)據(jù).xlsx") as writer:
    for dept in departments:
        df_dept = df[df["部門"] == dept]
        df_dept.to_excel(writer, sheet_name=dept, index=False)

import pandas as pd


df = pd.read_excel("員工數(shù)據(jù).xlsx")
report = df.groupby("部門").agg({"工資": ["mean", "sum"]})
report.columns = ["平均工資", "總工資"]
report.to_excel("工資統(tǒng)計(jì)報(bào)表.xlsx")

import pandas as pd
import smtplib
from email.mime.text import MIMEText


df = pd.read_excel("員工數(shù)據(jù).xlsx")


# 郵件配置
smtp_server = "smtp.example.com"
port = 587
sender_email = "hr@company.com"
password = "your_password"


for index, row in df.iterrows():
    msg = MIMEText(f"尊敬的{row['姓名']}，您的本月工資為{row['工資']}元。")
    msg["Subject"] = "您的工資條"
    msg["From"] = sender_email
    msg["To"] = row["郵箱"]


    with smtplib.SMTP(smtp_server, port) as server:
        server.starttls()
        server.login(sender_email, password)
        server.send_message(msg)

from openpyxl import load_workbook
from openpyxl.styles import PatternFill


wb = load_workbook("員工數(shù)據(jù).xlsx")
ws = wb.active


# 定義綠色填充
green_fill = PatternFill(start_color="00FF00", end_color="00FF00", fill_type="solid")


for row in ws.iter_rows(min_row=2, max_col=4, max_row=ws.max_row):
    if row[3].value > 8000:  # 假設(shè)工資在第4列
        for cell in row:
            cell.fill = green_fill


wb.save("高亮顯示員工數(shù)據(jù).xlsx")

import pandas as pd
from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference


df = pd.read_excel("員工數(shù)據(jù).xlsx")
df["入職年份"] = pd.to_datetime(df["入職日期"]).dt.year
year_distribution = df["入職年份"].value_counts().sort_index()


# 創(chuàng)建Excel圖表
wb = Workbook()
ws = wb.active
ws.append(["年份", "人數(shù)"])
for year, count in year_distribution.items():
    ws.append([year, count])


chart = BarChart()
chart.title = "員工入職年份分布"
data_range = Reference(ws, min_col=2, min_row=1, max_row=len(year_distribution)+1, max_col=2)
categories = Reference(ws, min_col=1, min_row=2, max_row=len(year_distribution)+1)
chart.add_data(data_range, titles_from_data=True)
chart.set_categories(categories)
ws.add_chart(chart, "D2")


wb.save("員工入職年份分布.xlsx")

import pandas as pd


df = pd.read_excel("員工數(shù)據(jù).xlsx")
df.columns = ["employee_id", "name", "department", "salary", "join_date", "email"]
df.to_excel("重命名后的員工數(shù)據(jù).xlsx", index=False)

import pandas as pd


df = pd.read_excel("員工數(shù)據(jù).xlsx")
df["郵箱"].fillna("無郵箱@公司.com", inplace=True)
df.to_excel("填充空值后的員工數(shù)據(jù).xlsx", index=False)

import pandas as pd
import numpy as np


df = pd.read_excel("員工數(shù)據(jù).xlsx")
df["績效評(píng)分"] = np.random.randint(1, 6, size=len(df))
df.to_excel("帶績效評(píng)分的員工數(shù)據(jù).xlsx", index=False)

from openpyxl import load_workbook
from openpyxl.worksheet.datavalidation import DataValidation


wb = load_workbook("員工數(shù)據(jù).xlsx")
ws = wb.active


# 添加數(shù)據(jù)驗(yàn)證
dv = DataValidation(type="list", formula1='"人力資源部,財(cái)務(wù)部,信息技術(shù)部,銷售部,市場(chǎng)部"')
dv.add("C2:C100")  # 假設(shè)部門列在C列
ws.add_data_validation(dv)


wb.save("帶數(shù)據(jù)驗(yàn)證的員工數(shù)據(jù).xlsx")