在pycharm中对excel表基本操作并通过浏览选取.xlsx、.csv文件

最新推荐文章于 2024-03-28 10:31:21 发布

⁢⁢⁢⁢回家的诱惑

最新推荐文章于 2024-03-28 10:31:21 发布

阅读量1.1k

点赞数

分类专栏： pycharm 文章标签： pycharm excel ide

本文链接：https://blog.csdn.net/qdacm_lxg/article/details/132617294

版权

pycharm 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

1.提取日期时间列中的时间、日期列

在这里插入图片描述

 		# 读取Excel文件
        df = pd.read_excel(x)
          # 将日期时间列拆分为日期和时间列
        df['日期'] = pd.to_datetime(df['考勤时间']).dt.date
        df['时间'] = pd.to_datetime(df['考勤时间']).dt.time

1.1要达成效果？

在这里插入图片描述
在每个人月底加一条合计记录，统计出勤天数

1.2.csv转.xlsx文件

 # 进行相关操作
        if x.endswith('.csv'):
            # 转换为xlsx文件
            new_file = x.replace('.csv', '.xlsx')
            print(new_file)

            # 读取CSV文件
            df_cv = pd.read_csv(x, encoding='GBK')

            # 将数据保存为xlsx格式
            df_cv.to_excel(new_file, index=False)
            x = new_file

2.界面设计

在这里插入图片描述

代码：

import tkinter as tk
from tkinter import filedialog, messagebox

# 创建Tkinter窗口
window = tk.Tk()
window.title('文件选择界面')

# 设置窗口大小和位置
window.geometry('500x300')
window.update_idletasks()
width = window.winfo_width()
height = window.winfo_height()
x = (window.winfo_screenwidth() // 2) - (width // 2)
y = (window.winfo_screenheight() // 2) - (height // 2)
window.geometry('{}x{}+{}+{}'.format(width, height, x, y))

# 设置窗口背景
bg_color = '#212121'  # 可自定义颜色，这里使用暗灰色背景
window.configure(bg=bg_color)

# 设置标题
title_label = tk.Label(window, text='文件选择', font=('Arial', 24), fg='white', bg=bg_color)
title_label.pack(pady=20)

# 定义变量
file_path = tk.StringVar()

# 文本框样式
text_box_style = {
    'bg': '#333333',       # 文本框背景色，可自定义颜色
    'fg': 'white',         # 文本框前景色（字体颜色），可自定义颜色
    'insertbackground': 'white',   # 光标颜色，可自定义颜色
    'highlightbackground': 'white',   # 高亮背景色，可自定义颜色
    'highlightcolor': 'white',   # 高亮前景色，可自定义颜色
    'font': ('Arial', 12)   # 字体样式，可自定义字体和大小
}

# 浏览文件函数
def browse_file():
    file_path.set(filedialog.askopenfilename())

# 提交函数
def submit():
    try:
        x = file_path.get()
        # 执行相关代码操作

        # 运行成功，弹出提示框
        messagebox.showinfo('成功', '运行成功')
    except Exception as e:
        # 运行出错，弹出提示框
        messagebox.showerror('错误', f'运行出错：{str(e)}\n请联系管理人员')

# 文本框和浏览按钮样式
text_box_style = {
    'bg': '#333333',       # 文本框背景色，可自定义颜色
    'fg': 'white',         # 文本框前景色（字体颜色），可自定义颜色
    'insertbackground': 'white',   # 光标颜色，可自定义颜色
    'highlightbackground': 'white',   # 高亮背景色，可自定义颜色
    'highlightcolor': 'white',   # 高亮前景色，可自定义颜色
    'font': ('Arial', 12)   # 字体样式，可自定义字体和大小
}

# 浏览按钮样式
browse_button_style = {
    'font': ('Arial', 14),   # 字体样式，可自定义字体和大小
    'bg': '#4CAF50',        # 按钮背景色，可自定义颜色
    'fg': 'white',          # 按钮前景色（字体颜色），可自定义颜色
    'activebackground': '#45a049',   # 鼠标悬停时按钮背景色，可自定义颜色
    'activeforeground': 'white',   # 鼠标悬停时按钮前景色（字体颜色），可自定义颜色
    'bd': 0,                # 按钮边框宽度，默认为1
    'highlightthickness': 0   # 按钮高亮边框宽度，默认为1
}

# 文本框
text_box = tk.Entry(window, textvariable=file_path, **text_box_style)
text_box.pack(pady=10)

# 浏览按钮
browse_button = tk.Button(window, command=browse_file, text='浏览', **browse_button_style)
browse_button.pack()

# 提交按钮
submit_button = tk.Button(window, text='提交', command=submit, **browse_button_style)
submit_button.pack(pady=20)

# 运行窗口的主事件循环
window.mainloop()

3.第一种思路

加个‘考勤天数’，有记录标为1
每个人按月份排满所有天
没有日期就插入当天日期空记录

一个月完就插条记录考勤总天数

创一个新表
当从Excel表格中读取文件，按名字分组，创一个新表，按姓名，然后通过旧表日期判断该月有多少天，然后给每个人从1号开始插入姓名、日期、周几的字段到月底最后一天，最后将旧表右连接新表，以姓名连接

新表规则：
有原表的姓名，每个人有该月的每一天日期记录，和星期几，一共三个字段（姓名、日期、星期几）
记录规则：
为每个人的最后一天下一行增加一个只有名字，其他字段为空的记录

连接规则：
旧表右连接新表

合并表：
当一条记录只有名字有值，其他字段为空时，在考勤列统计按姓名分组的考勤值为1的记录数

4.第二种思路

前提：先不提时间、日期（少判断原则，连接优先）

第一步，将表按姓名、考勤时间（日期）分组；
第二步，新建新表，字段（姓名、考勤时间）与取最后一条数据记录
第三步，旧表右连接新表（条件：姓名、考勤时间（日期））
第四步，判断（若考勤时间x==考勤时间y）{则x<12,删除y;否则删除x–(NULL)}
第五步，变复杂啦—转为第3.5步：提取（考勤时间x）列中的日期新建列（日期）
第六步，提取考勤时间x、考勤时间y中的时间，并新建列（时间x、时间y）或者覆盖（考勤时间x、考勤时间y）
第七步，新建列（星期几）：值（按日期），新建列（考勤天数）：值（1）
第八步，新建新表2，字段（姓名、日期）日期规则（按旧表该月1号到月底）新建记录
第九部，连接
第十步，加空行，统计合计，调列序

5.1按名字合并相同日期的记录

import pandas as pd
from datetime import time

# 读取Excel文件
df = pd.read_excel('D:\\MyDocuments\\Desktop\\测试样例2.xlsx')


# 将日期时间列拆分为日期和时间列
df['日期'] = pd.to_datetime(df['考勤时间']).dt.date
df['时间'] = pd.to_datetime(df['考勤时间']).dt.time

# 按姓名、日期和时间排序
df.sort_values(by=['姓名', '日期', '时间'], inplace=True)

# 选择第一条记录的时间作为上班打卡时间
first_record = df.groupby(['姓名', '日期']).first().reset_index()
first_record.rename(columns={'时间': '上班打卡时间'}, inplace=True)

# 选择最后一条记录的时间作为下班打卡时间
last_record = df.groupby(['姓名', '日期']).last().reset_index()
last_record.rename(columns={'时间': '下班打卡时间'}, inplace=True)

# 若上班打卡时间大于12点，设置为12点
first_record.loc[first_record['上班打卡时间'] > pd.to_datetime('12:00:00').time(), '上班打卡时间'] = pd.to_datetime('12:00:00').time()

# 若下班打卡时间小于12点，设置为12点
last_record.loc[last_record['下班打卡时间'] < pd.to_datetime('12:00:00').time(), '下班打卡时间'] = pd.to_datetime('12:00:00').time()

# 合并上班打卡时间和下班打卡时间记录到原始数据
merged_df = pd.merge(df, first_record[['姓名', '日期', '上班打卡时间']], on=['姓名', '日期'], how='left')
merged_df = pd.merge(merged_df, last_record[['姓名', '日期', '下班打卡时间']], on=['姓名', '日期'], how='left')

# 若没有上班打卡时间，设置为12点
merged_df['上班打卡时间'].fillna(pd.to_datetime('12:00:00').time(), inplace=True)

# 若没有下班打卡时间，设置为12点
merged_df['下班打卡时间'].fillna(pd.to_datetime('12:00:00').time(), inplace=True)

# 根据姓名和日期合并记录，保留原有字段
merged_df.drop_duplicates(subset=['姓名', '日期'], inplace=True)


# 删除"考勤时间"列和"时间"列
merged_df.drop(['考勤时间', '时间'], axis=1, inplace=True)

# 保存结果到新的Excel文件
merged_df.to_excel('D:\\MyDocuments\\Desktop\\output012.xlsx', index=False)

5.2 第一版

import os
import tkinter as tk
from tkinter import filedialog, messagebox
import pandas as pd
from datetime import datetime, time, timedelta


# 创建Tkinter窗口
window = tk.Tk()
window.title('文件选择界面')

# 设置窗口大小和位置
window.geometry('500x300')
window.update_idletasks()
width = window.winfo_width()
height = window.winfo_height()
x = (window.winfo_screenwidth() // 2) - (width // 2)
y = (window.winfo_screenheight() // 2) - (height // 2)
window.geometry('{}x{}+{}+{}'.format(width, height, x, y))

# 设置窗口背景
bg_color = '#212121'  # 可自定义颜色，这里使用暗灰色背景
window.configure(bg=bg_color)

# 设置标题
title_label = tk.Label(window, text='文件选择', font=('Arial', 24), fg='white', bg=bg_color)
title_label.pack(pady=20)

# 定义变量
file_path = tk.StringVar()

# 文本框样式
text_box_style = {
    'bg': '#333333',       # 文本框背景色，可自定义颜色
    'fg': 'white',         # 文本框前景色（字体颜色），可自定义颜色
    'insertbackground': 'white',   # 光标颜色，可自定义颜色
    'highlightbackground': 'white',   # 高亮背景色，可自定义颜色
    'highlightcolor': 'white',   # 高亮前景色，可自定义颜色
    'font': ('Arial', 12)   # 字体样式，可自定义字体和大小
}

# 浏览文件函数
def browse_file():
    file_path.set(filedialog.askopenfilename())

# 提交函数
def submit():
    try:
        x = file_path.get()
        # 执行相关代码操作
        # =====================================================================================================
        # ===============================================主代码区=================================================

        # 定义文件路径
        # filename_in = 'xlsx_file/input/测试样例2.xlsx'
        # filename_out = 'xlsx_file/out/output205.xlsx'

        # 获取当前日期和时间
        now = datetime.now()

        # 格式化时间为字符串，并提取时、分、秒
        time_str = now.strftime("%H%M%S")
        hour = now.hour
        minute = now.minute
        second = now.second

        # 定义输出路径
        output_directory = 'D:\\MyDocuments\Desktop\\'  # 替换为你的目标输出路径

        # 在输出路径中使用时、分、秒的字符串来命名文件
        file_name = f"分析结果_{hour}_{minute}_{second}.xlsx"
        filename_out = os.path.join(output_directory, file_name)

        # 定义参考上班时间
        reference_time = time(8, 0)
        # 定义参考下班时间
        reference_time2 = time(17, 0)
        # 定义参考加班时间
        reference_time3 = time(17, 30)

        # 读取Excel文件
        df = pd.read_excel(x)

        # 转日期格式
        df['考勤时间'] = pd.to_datetime(df['考勤时间'], errors='coerce')

        # 获取日期的星期几，并添加到DataFrame中
        df['星期几'] = df['考勤时间'].dt.strftime('%A')

        # 将英文周几转换为中文周几
        weekdays = {
            'Monday': '星期一',
            'Tuesday': '星期二',
            'Wednesday': '星期三',
            'Thursday': '星期四',
            'Friday': '星期五',
            'Saturday': '星期六',
            'Sunday': '星期日'
        }

        df['星期几'] = df['星期几'].map(weekdays)

        # 将日期时间列拆分为日期和时间列
        df['日期'] = pd.to_datetime(df['考勤时间']).dt.date
        df['时间'] = pd.to_datetime(df['考勤时间']).dt.time


        # 提取上班打卡时间
        time_column = '考勤时间'  # 请将 '考勤时间' 替换为实际的列名称
        df[time_column] = pd.to_datetime(df[time_column]).dt.time  # 提取时间并转换为 time 对象




        # 计算时间差 》换算为分钟数
        def calculate_late_arrival_time(x, reference_time):
            if x is None:
                return '未打卡'
            temp = ((timedelta(hours=x.hour, minutes=x.minute) - timedelta(hours=reference_time.hour,
                                                                              minutes=reference_time.minute)).total_seconds() / 60)
            if int(temp) <= 0:
                return '未迟到'
            elif int(temp) >= 300:
                return '未打卡'
            else:
                return temp


        def calculate_early_departure_time(x, reference_time2):
            if x is None:
                return '未打卡'
            temp= ((timedelta(hours=reference_time2.hour, minutes=reference_time2.minute) - timedelta(hours=x.hour,
                                                                                                          minutes=x.minute)).total_seconds() / 60)
            if int(temp) <= 0:
                return '未早退'
            elif int(temp) >= 300:
                return '未打卡'
            else:
                return temp

        def calculate_jb_departure_time(x, reference_time3):
            if x is None:
                return '未打卡'
            temp = ((timedelta(hours=x.hour, minutes=x.minute) - timedelta(hours=reference_time3.hour,
                                                                              minutes=reference_time3.minute)).total_seconds() / 60)
            if int(temp) <= 0:
                return '未加班'
            elif int(temp) >= 330:
                return '未打卡'
            else:
                return temp
        # reference_time 和 reference_time2 是用于计算迟到时间和早退时间的参考时间
        df['迟到时间'] = df[time_column].apply(lambda x: calculate_late_arrival_time(x, reference_time))
        df['早退时间'] = df[time_column].apply(lambda x: calculate_early_departure_time(x, reference_time2))
        df['加班时间'] = df[time_column].apply(lambda x: calculate_jb_departure_time(x, reference_time3))

        # 按姓名、日期和时间排序
        df.sort_values(by=['姓名', '日期', '时间'], inplace=True)

        # 选择第一条记录的时间作为上班打卡时间
        first_record = df.groupby(['姓名', '日期']).first().reset_index()
        first_record.rename(columns={'时间': '上班打卡时间'}, inplace=True)

        # 选择最后一条记录的时间作为下班打卡时间
        last_record = df.groupby(['姓名', '日期']).last().reset_index()
        last_record.rename(columns={'时间': '下班打卡时间'}, inplace=True)

        # 若上班打卡时间大于12点，设置为12点
        first_record.loc[first_record['上班打卡时间'] > pd.to_datetime('12:00:00').time(), '上班打卡时间'] = pd.to_datetime('12:00:00').time()

        # 若下班打卡时间小于12点，设置为12点
        last_record.loc[last_record['下班打卡时间'] < pd.to_datetime('12:00:00').time(), '下班打卡时间'] = pd.to_datetime('12:00:00').time()

        # 合并上班打卡时间和下班打卡时间记录到原始数据
        merged_df = pd.merge(df[['姓名', '日期','星期几', '所属组织']], first_record[['姓名', '日期', '上班打卡时间', '迟到时间']], on=['姓名', '日期'])
        merged_df = pd.merge(merged_df[['姓名', '所属组织', '日期', '星期几', '上班打卡时间', '迟到时间']], last_record[['姓名', '日期', '下班打卡时间', '早退时间','加班时间']], on=['姓名', '日期'], how='left')


        # 根据姓名和日期合并记录，保留原有字段(去重）
        merged_df.drop_duplicates(subset=['姓名', '日期'], inplace=True)


        # 删除"考勤时间"列和"时间"列
        # merged_df.drop(['考勤时间', '时间','迟到时间_x', '早退时间_x'], axis=1, inplace=True)

        # 输出
        merged_df.to_excel(filename_out, sheet_name='详细信息', index=False)
        print('文件保存成功！')



        # ===========================================================================================
        # ===========================================================================================

        # 读取Excel表单数据
        df = pd.read_excel(filename_out, sheet_name='详细信息')

        # 为迟到时间、早退时间和加班时间创建相应的布尔列，并判断是否为数字字符串
        df['迟到次数'] = df['迟到时间'].map(lambda x: str(x).isdigit()).astype(int)
        df['早退次数'] = df['早退时间'].map(lambda x: str(x).isdigit()).astype(int)
        df['加班次数'] = df['加班时间'].map(lambda x: str(x).isdigit()).astype(int)

        # 统计迟到未打卡次数和早退未打卡次数
        df['上班未打卡数'] = df['迟到时间'].map(lambda x: 1 if str(x) == '未打卡' else 0)
        df['下班未打卡数'] = df['早退时间'].map(lambda x: 1 if str(x) == '未打卡' else 0)

        # 按姓名和日期分组，计算每个人在每天的迟到次数、早退次数和加班天数
        result = df.groupby(['姓名']).agg({
            '迟到次数': 'sum',
            '早退次数': 'sum',
            '加班次数': 'sum',
            '日期': 'nunique',
            '上班未打卡数': 'sum',
            '下班未打卡数': 'sum'
        }).reset_index()


        # 重命名列名
        result.rename(columns={'日期': '考勤总天数'}, inplace=True)


        # 将结果写入Excel表单的另一sheet
        with pd.ExcelWriter(filename_out, engine='openpyxl', mode='a') as excel_writer:
            result.to_excel(excel_writer, sheet_name='结果统计', index=False)

        # excel_writer.save()
        print('统计结果保存成功')




        # =====================================================================================================
        # =====================================================================================================

        # 运行成功，弹出提示框
        messagebox.showinfo('成功', '运行成功')
    except Exception as e:
        # 运行出错，弹出提示框
        messagebox.showerror('错误', f'运行出错：{str(e)}\n请联系管理人员')

# 文本框和浏览按钮样式
text_box_style = {
    'bg': '#333333',       # 文本框背景色，可自定义颜色
    'fg': 'white',         # 文本框前景色（字体颜色），可自定义颜色
    'insertbackground': 'white',   # 光标颜色，可自定义颜色
    'highlightbackground': 'white',   # 高亮背景色，可自定义颜色
    'highlightcolor': 'white',   # 高亮前景色，可自定义颜色
    'font': ('Arial', 12)   # 字体样式，可自定义字体和大小
}

# 浏览按钮样式
browse_button_style = {
    'font': ('Arial', 14),   # 字体样式，可自定义字体和大小
    'bg': '#4CAF50',        # 按钮背景色，可自定义颜色
    'fg': 'white',          # 按钮前景色（字体颜色），可自定义颜色
    'activebackground': '#45a049',   # 鼠标悬停时按钮背景色，可自定义颜色
    'activeforeground': 'white',   # 鼠标悬停时按钮前景色（字体颜色），可自定义颜色
    'bd': 0,                # 按钮边框宽度，默认为1
    'highlightthickness': 0   # 按钮高亮边框宽度，默认为1
}

# 文本框
text_box = tk.Entry(window, textvariable=file_path, **text_box_style)
text_box.pack(pady=10)

# 浏览按钮
browse_button = tk.Button(window, command=browse_file, text='浏览', **browse_button_style)
browse_button.pack()

# 提交按钮
submit_button = tk.Button(window, text='提交', command=submit, **browse_button_style)
submit_button.pack(pady=20)

# 运行窗口的主事件循环
window.mainloop()