pom
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>org.example</groupId>
<artifactId>mavenexe</artifactId>
<version>1.0-SNAPSHOT</version>
<packaging>jar</packaging>
<name>mavenexe</name>
<url>http://maven.apache.org</url>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<dependencies>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.7.12</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.3</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.20</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<configuration>
<source>8</source>
<target>8</target>
</configuration>
</plugin>
<plugin>
<artifactId>maven-assembly-plugin</artifactId>
<version>3.3.0</version>
<configuration>
<archive>
<manifest>
<mainClass>org.example.ReportAPP</mainClass>
</manifest>
</archive>
<descriptorRefs>
<descriptorRef>jar-with-dependencies</descriptorRef>
</descriptorRefs>
</configuration>
<executions>
<execution>
<id>make-assembly</id> <!-- this is used for inheritance merges -->
<phase>package</phase> <!-- bind to the packaging phase -->
<goals>
<goal>single</goal>
</goals>
</execution>
</executions>
</plugin>
</plugins>
</build>
</project>
domain对象
package org.example.domain;
import lombok.Data;
import java.time.LocalTime;
import java.util.TreeSet;
@Data
public class EmployeeDayPunch {
// 姓名
private String name;
// 部门
private String department;
// 根据时间进行排序
private TreeSet<LocalTime> punchTimeSet = new TreeSet<>(LocalTime::compareTo);
}
package org.example.domain;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ContentLoopMerge;
import lombok.Data;
@Data
public class EmployeeExcelExport {
// 部门
@ContentLoopMerge(eachRow = 2)
@ExcelProperty("部门")
String department;
// 姓名
@ContentLoopMerge(eachRow = 2)
@ExcelProperty("姓名")
String name;
// 正常出勤次数
@ContentLoopMerge(eachRow = 2)
@ExcelProperty("正常出勤次数")
private Integer normalCount = 0;
// 迟到或早退半小时以内次数
@ExcelProperty("迟到或早退半小时以内次数")
private Integer inHalfHourCount = 0;
// 迟到或早退半小时以上次数
@ExcelProperty("迟到或早退半小时以上次数")
private Integer outHalfHourCount = 0;
// 旷工半天次数
@ExcelProperty("旷工半天次数")
private Integer outHalfWorkCount = 0;
// 旷工全天次数
@ExcelProperty("旷工全天次数")
private Integer outAllWorkCount = 0;
// 上午|下午
@ExcelProperty("日期")
private String timeType;
@ExcelProperty("1")
private String no1;
@ExcelProperty("2")
private String no2;
@ExcelProperty("3")
private String no3;
@ExcelProperty("4")
private String no4;
@ExcelProperty("5")
private String no5;
@ExcelProperty("6")
private String no6;
@ExcelProperty("7")
private String no7;
@ExcelProperty("8")
private String no8;
@ExcelProperty("9")
private String no9;
@ExcelProperty("10")
private String no10;
@ExcelProperty("11")
private String no11;
@ExcelProperty("12")
private String no12;
@ExcelProperty("13")
private String no13;
@ExcelProperty("14")
private String no14;
@ExcelProperty("15")
private String no15;
@ExcelProperty("16")
private String no16;
@ExcelProperty("17")
private String no17;
@ExcelProperty("18")
private String no18;
@ExcelProperty("19")
private String no19;
@ExcelProperty("20")
private String no20;
@ExcelProperty("21")
private String no21;
@ExcelProperty("22")
private String no22;
@ExcelProperty("23")
private String no23;
@ExcelProperty("24")
private String no24;
@ExcelProperty("25")
private String no25;
@ExcelProperty("26")
private String no26;
@ExcelProperty("27")
private String no27;
@ExcelProperty("28")
private String no28;
@ExcelProperty("29")
private String no29;
@ExcelProperty("30")
private String no30;
@ExcelProperty("31")
private String no31;
}
package org.example.domain;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
import java.time.LocalDateTime;
@Data
public class ExcelPunchQO {
// 姓名
@ExcelProperty("name")
private String name;
// 部门名称
@ExcelProperty("department")
private String department;
// 打卡时间
@ExcelProperty("punchTime")
private LocalDateTime punchTime;
}
具体的工具
package org.example.util;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.read.listener.PageReadListener;
import org.example.domain.EmployeeDayPunch;
import org.example.domain.EmployeeExcelExport;
import org.example.domain.ExcelPunchQO;
import java.io.File;
import java.lang.reflect.Field;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
public class ExcelUtil {
private static LocalTime time1 = LocalTime.of(8, 30);
private static LocalTime time2 = LocalTime.of(9, 0 );
private static LocalTime time3 = LocalTime.of(10, 0);
private static LocalTime time4 = LocalTime.of(16, 59, 59);
private static LocalTime time5 = LocalTime.of(17, 30);
private static LocalTime time6 = LocalTime.of(18, 0);
private static final String MORNING = "上午";
private static final String AFTERNOON = "下午";
public static void simpleWrite(File file) {
System.out.println("开始创建文件夹,读取文件......");
EasyExcel.write(createExportDir(file), EmployeeExcelExport.class)
.sheet("模板")
.doWrite(() -> {
// 分页查询数据
try {
return getData(file);
} catch (NoSuchFieldException e) {
throw new RuntimeException(e);
} catch (IllegalAccessException e) {
throw new RuntimeException(e);
}
});
}
private static File createExportDir(File file) {
LocalDateTime now = LocalDateTime.now();
DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
String timeDirName = now.format(formatter);
File newFile = new File(file.getParentFile().getParentFile().getPath() + "/" + timeDirName + "/" + System.currentTimeMillis() + ".xls");
if (!newFile.getParentFile().exists()) {
newFile.getParentFile().mkdirs();
}
System.out.println("创建文件夹成功!!!");
return newFile;
}
private static List<EmployeeExcelExport> getData(File file) throws NoSuchFieldException, IllegalAccessException {
// 读取数据
List<ExcelPunchQO> excelList = readFromExcel(file);
// 处理数据
List<EmployeeExcelExport> exportList = handleData(excelList);
return exportList;
}
private static List<ExcelPunchQO> readFromExcel(File file) {
System.out.println("开始读取excel中的数据......");
// 读取excel数据
List<ExcelPunchQO> readList = new ArrayList<>();
EasyExcel.read(file, ExcelPunchQO.class, new PageReadListener<ExcelPunchQO>(readList::addAll)).sheet().doRead();
System.out.println("excel数据读取结束, 总条数:" + readList.size());
return readList;
}
private static List<EmployeeExcelExport> handleData(List<ExcelPunchQO> result) throws NoSuchFieldException, IllegalAccessException {
System.out.println("开始处理所有打卡数据.....");
// 通过name分组,将一个人一个月的打卡数据分在一起
Map<String, List<ExcelPunchQO>> nameMap = result.stream().collect(Collectors.groupingBy(ExcelPunchQO::getName));
// 循环处理
List<EmployeeExcelExport> totalList = new ArrayList<>();
for (Map.Entry<String, List<ExcelPunchQO>> entry : nameMap.entrySet()) {
// 处理每个人的数据
List<EmployeeExcelExport> oneList = handleOne(entry.getValue());
totalList.addAll(oneList);
}
System.out.println("所有打卡数据处理结束,返回所有人最终的打卡数据!!!");
return totalList;
}
还有
private static List<EmployeeExcelExport> handleOne(List<ExcelPunchQO> punchList) throws NoSuchFieldException, IllegalAccessException {
EmployeeDayPunch[] monthArray = new EmployeeDayPunch[32];
String userName = punchList.get(0).getName();
String department = punchList.get(0).getDepartment();
for (int i = 1; i < monthArray.length; i++) {
EmployeeDayPunch employ = new EmployeeDayPunch();
employ.setName(userName);
employ.setDepartment(department);
monthArray[i] = employ;
}
EmployeeExcelExport morningPunch = new EmployeeExcelExport();
EmployeeExcelExport afternoonPunch = new EmployeeExcelExport();
morningPunch.setName(userName);
morningPunch.setDepartment(department);
afternoonPunch.setName(userName);
afternoonPunch.setDepartment(department);
for (ExcelPunchQO userInfo : punchList) {
LocalDateTime localDateTime = userInfo.getPunchTime();
int day = localDateTime.getDayOfMonth();
monthArray[day].getPunchTimeSet().add(localDateTime.toLocalTime());
}
List<EmployeeExcelExport> result = new ArrayList<>();
Integer normalCount = 0;
Integer inHalfHourCount = 0;
Integer outHalfHourCount = 0;
Integer outHalfWorkCount = 0;
Integer outAllWorkCount = 0;
morningPunch.setTimeType(MORNING);
afternoonPunch.setTimeType(AFTERNOON);
for (int i = 1; i < monthArray.length; i++) {
Field field = EmployeeExcelExport.class.getDeclaredField("no" + i);
field.setAccessible(true);
LocalTime firstTime = monthArray[i].getPunchTimeSet().lower(time3);
boolean error = false;
boolean morningOutWorkError = false;
if (firstTime == null) {
field.set(morningPunch, "×");
morningOutWorkError = true;
error = true;
} else if (firstTime.compareTo(time1) == -1){
field.set(morningPunch, "√");
} else if (firstTime.compareTo(time2) == -1){
field.set(morningPunch, "☆");
inHalfHourCount++;
error = true;
} else if (firstTime.compareTo(time3) == -1){
field.set(morningPunch, "★");
outHalfHourCount++;
error = true;
}
LocalTime lastTime = monthArray[i].getPunchTimeSet().higher(time4);
boolean afterOutWorkError = false;
if (lastTime == null) {
field.set(afternoonPunch, "×");
afterOutWorkError = true;
error = true;
} else if (lastTime.compareTo(time6) >= 0){
field.set(afternoonPunch, "√");
} else if (lastTime.compareTo(time5) == -1){
field.set(afternoonPunch, "☆");
inHalfHourCount++;
error = true;
} else if (lastTime.compareTo(time6) == -1){
field.set(afternoonPunch, "★");
outHalfHourCount++;
error = true;
}
main函数
package org.example;
import org.example.util.ExcelUtil;
import java.io.File;
import java.nio.file.Path;
import java.nio.file.Paths;
public class ReportAPP {
public static void main(String[] args) {
Path path = Paths.get("template","data.xls");
File dataFile = path.toFile().getAbsoluteFile();
ExcelUtil.simpleWrite(dataFile);
}
}
exe4j
L-g782dn2d-1f1yqxx1rv1sqd