使用java代码实现读取考勤Excel文档,封装成对象数据,并对对象数据进行处理并输出Excel文档,如下:
public class EmployeeExcelParser {
private static final String EXCEL_XLS = "xls";
private static final String EXCEL_XLSX = "xlsx";
private static final long EIGHT_HOUSE_TIME = 8 * 60 * 60;//八小时
private static final String START_HOUSE = "09:00:00";//上班
private static final String END_HOUSE = "17:30:00";//下班
public static void main(String[] args) {
SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
HashMap<String, Employee> employeeList = new HashMap<>();
String filePath = "D://employee/微信考勤数据.xlsx";
String finalXlsxPath = "D://employee/BU18工时计算结果.xlsx";
try {
// 同时支持Excel 2003、2007
File excelFile = new File(filePath); // 创建文件对象
checkExcelVaild(excelFile);
Workbook workbook = getWorkbok(excelFile);
int sheetCount = workbook.getNumberOfSheets(); // Sheet的数量,
/**
* 设置当前excel中sheet的下标:0开始
*/
Sheet sheet1 = workbook.getSheetAt(0); // // 获取第一页,工作日
// 为跳过第一行目录设置count
int count = 0;
for (Row row : sheet1) {
// 跳过第一行的目录
if(count == 0){
count++;
continue;
}
// 如果当前行没有数据,跳出循环
if(row.getCell(0).toString().equals("")){
return ;
}
Employee employee;
//封装对象
if(employeeList.containsKey(row.getCell(1).toString())){
employee = employeeList.get(row.getCell(1).toString());
}else{
employee = new Employee();
employee.setName(row.getCell(0).toString());
employee.setSerial_number(row.getCell(1).toString());
employee.setSection(row.getCell(2).toString());
employee.setMonth(row.getCell(3).toString().substring(0,7));
}
employee.setSum_man_day(employee.getSum_man_day() + 1);//加一天
employee.setSum_leave(
employee.getSum_leave() + Double.valueOf(row.getCell(12).toString()));//请假
employee.setSum_absence(
employee.getSum_absence() + Double.valueOf(row.getCell(14).toString()));//缺勤
long workTime = 0;//当天上班时长
//正常计算时长
workTime = workDuration(row.getCell(4), row.getCell(5));//签到时间 he 签退时间 ;
if(workTime < 0){
//System.out.println(workTime);
}
workTime = workTime < 0 ? 0 : workTime;
//判断是否迟到
if (isEmpty(row.getCell(4)) || isEmpty(row.getCell(5)) || workTime < EIGHT_HOUSE_TIME) {//迟到状态,或早退状态
String state = row.getCell(10).toString();
if( "审批通