福建省建设厅网站劳保核定卡,企业一号wordpress主题,it外包抽成,免费word模板下载哪个网站C#加班统计次数 运行环境#xff1a;vs2022 .net 8.0 社区版 1、用C#语言#xff1b;2、有界面上传Excel文件; 3、对Excel列#xff08;部门、人员姓名、人员编号、考勤时间 #xff09;处理#xff1a;#xff08;1#xff09;按人员编号、考勤日期分组且保留原来字段vs2022 .net 8.0 社区版 1、用C#语言2、有界面上传Excel文件; 3、对Excel列部门、人员姓名、人员编号、考勤时间 处理1按人员编号、考勤日期分组且保留原来字段保留唯一最晚考勤时间记录2按人员编号分组统计分组员工加班次数判断条件1周一至周五2打卡时间超过1730 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;// 数据处理ListEmployeeAttendance attendanceList new ListEmployeeAttendance();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; }}
}
界面
