C#加班统计次数

C#加班统计次数

运行环境：vs2022 .net 8.0 社区版
1、用C#语言；2、有界面上传Excel文件; 3、对Excel列（部门、人员姓名、人员编号、考勤时间 ）处理：（1）按人员编号、考勤日期分组且保留原来字段，保留唯一最晚考勤时间记录，（2）按人员编号分组，统计分组员工加班次数：判断条件（1）周一至周五（2）打卡时间超过17：30 4、输出Excel在另一个sheet标签统计。

using System.Windows.Forms;
using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;

namespace WinFormsApp1
{
    public partial class Form : System.Windows.Forms.Form
    {
        
        public Form()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            OpenFileDialog openFileDialog = new OpenFileDialog();
            openFileDialog.Filter = "Excel Files|*.xls;*.xlsx;*.xlsm";

            if (openFileDialog.ShowDialog() == DialogResult.OK)
            {
                // 用户选择了一个文件
                string selectedFilePath = openFileDialog.FileName;

                // 在这里可以编写上传文件的逻辑，例如将文件复制到指定位置、读取文件内容等
                // 这里只是简单的演示，可以根据实际需求进行扩展
                //MessageBox.Show($"已选择文件：{selectedFilePath}");
                txtUpload.Text = selectedFilePath;
                ProcessExcelFile(selectedFilePath);
                
            }
        }
      
        public void ProcessExcelFile(string filePath)
        {
            FileInfo fileInfo = new FileInfo(filePath);
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            using (ExcelPackage package = new ExcelPackage(fileInfo))
            {
                ExcelWorksheet worksheet = package.Workbook.Worksheets[0]; // 假设数据在第一个sheet中

                int rowCount = worksheet.Dimension.Rows;
                int colCount = worksheet.Dimension.Columns;

                // 数据处理
                List<EmployeeAttendance> attendanceList = new List<EmployeeAttendance>();
                try
                {
                    for (int row = 3; row <= rowCount; row++) // 假设第一行是标题行
                    {
                        string department = worksheet.Cells[row, 2].Value?.ToString();
                        string employeeName = worksheet.Cells[row, 3].Value?.ToString();
                        int employeeId = Convert.ToInt32(worksheet.Cells[row, 4].Value);
                        DateTime attendanceTime = DateTime.Parse(worksheet.Cells[row, 7].Value?.ToString());

                        attendanceList.Add(new EmployeeAttendance
                        {
                            Department = department,
                            EmployeeName = employeeName,
                            EmployeeId = employeeId,
                            AttendanceTime = attendanceTime
                        });
                    }
                }
                catch (Exception e)
                {
                    MessageBox.Show("提示：Excel文件格式不对！请重新选择\n错误信息：" + e.Message);
                    throw e;
                }


                // // 使用 LINQ 查询，按照人员编号、人员姓名、部门、考勤日期分组并保留最晚考勤时间记录,条件是在周一至周五
                var latestAttendanceRecords = attendanceList
                    .Where(a => a.AttendanceTime.DayOfWeek >= DayOfWeek.Monday && a.AttendanceTime.DayOfWeek <= DayOfWeek.Friday)
                    .GroupBy(a => new { a.EmployeeId,a.EmployeeName,a.Department, a.AttendanceTime.Date })
                    .Select(g => g.OrderByDescending(a => a.AttendanceTime).First())
                    .ToList();
                try
                {
                    Random random = new Random();
                    int randomNumber = random.Next(1000, 10000); // 生成1000到9999之间的随机整数
                                                                 // 输出统计结果到另一个sheet
                    ExcelWorksheet resultSheet = package.Workbook.Worksheets.Add($"最晚打卡记录{randomNumber}");

                    // 写入标题
                    resultSheet.Cells[1, 1].Value = "员工编号";
                    resultSheet.Cells[1, 2].Value = "员工姓名";
                    resultSheet.Cells[1, 3].Value = "员工部门";
                    resultSheet.Cells[1, 4].Value = "考勤时间";

                    // 写入数据
                    int rowIndex = 2;
                    foreach (var kvp in latestAttendanceRecords)
                    {
                        resultSheet.Cells[rowIndex, 1].Value = kvp.EmployeeId;
                        resultSheet.Cells[rowIndex, 2].Value = kvp.EmployeeName;
                        resultSheet.Cells[rowIndex, 3].Value = kvp.Department;
                        resultSheet.Cells[rowIndex, 4].Value = kvp.AttendanceTime;
                        rowIndex++;
                    }

                    // 保存文件
                    package.Save();
                    //MessageBox.Show($"统计结果输出成功");
                }
                catch (Exception)
                {

                    throw;
                }

                // 按照人员编号、人员姓名和部门分组，统计每个人的加班次数，并且考勤时间超过17点
                var overtimeCountByEmployee = latestAttendanceRecords.Where(a =>(a.AttendanceTime.Hour == 17 && a.AttendanceTime.Minute >= 30)||(a.AttendanceTime.Hour > 17 ))
                    .GroupBy(a => new { a.EmployeeId, a.EmployeeName, a.Department })
                    .Select(g => new
                    {
                        EmployeeId = g.Key.EmployeeId,
                        EmployeeName = g.Key.EmployeeName,
                        Department = g.Key.Department,
                        OvertimeCount = g.Count()
                    })
                    .ToList();
                try
                {
                    Random random = new Random();
                    int randomNumber = random.Next(1000, 10000); // 生成1000到9999之间的随机整数
                                                                 // 输出统计结果到另一个sheet
                    ExcelWorksheet resultSheet = package.Workbook.Worksheets.Add($"统计结果{randomNumber}");

                    // 写入标题
                    resultSheet.Cells[1, 1].Value = "员工编号";
                    resultSheet.Cells[1, 2].Value = "员工姓名";
                    resultSheet.Cells[1, 3].Value = "员工部门";
                    resultSheet.Cells[1, 4].Value = "加班次数";

                    // 写入数据
                    int rowIndex = 2;
                    foreach (var kvp in overtimeCountByEmployee)
                    {
                        resultSheet.Cells[rowIndex, 1].Value = kvp.EmployeeId;
                        resultSheet.Cells[rowIndex, 2].Value = kvp.EmployeeName;
                        resultSheet.Cells[rowIndex, 3].Value = kvp.Department;
                        resultSheet.Cells[rowIndex, 4].Value = kvp.OvertimeCount;
                        rowIndex++;
                    }

                    // 保存文件
                    package.Save();
                    MessageBox.Show($"统计结果输出成功");
                    this.Close();

                }
                catch (Exception e)
                {
                    MessageBox.Show("提示：输出失败，该文件被打开，请关闭文件后重试！\n错误信息：" + e.Message);
                    throw e;
                }

              
            }
        }
    

    }
    public class EmployeeAttendance
    {
        public string Department { get; set; }
        public string EmployeeName { get; set; }
        public int EmployeeId { get; set; }
        public DateTime AttendanceTime { get; set; }
    }
}

界面：