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之间的随机整数// 输出统计结果到另一个sheetExcelWorksheet 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之间的随机整数// 输出统计结果到另一个sheetExcelWorksheet 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; }}
}

界面：