需要效果如图
目前的占位符,生成的签到次数{d3.visitshow}只能生成第一行
@ApiOperation("员工考勤-导出excel")
@PostMapping("empAttendance/export")
@SneakyThrows
public void empAttendanceExport(@RequestBody CustomerVisitPageQuery query, HttpServletResponse response) {
TableDataInfo tableDataInfo = customerVisitService.empAttendance(getLoginUser().getUser(), query);
List<EmpAttendanceVO> rows = (List<EmpAttendanceVO>) tableDataInfo.getRows();
int id = 0;
for (EmpAttendanceVO row : rows) {
id++;
row.setId(id);
}
//第二页表格数据填充
List<EmpAttendanceVO> rowForTwo = new ArrayList<>();
for (EmpAttendanceVO empAttendanceVO : rows) {
EmpAttendanceVO clonedEmpAttendanceVO = new EmpAttendanceVO();
BeanUtils.copyProperties(empAttendanceVO, clonedEmpAttendanceVO);
rowForTwo.add(clonedEmpAttendanceVO);
}
response.setCharacterEncoding("UTF-8");
response.setContentType("application/vnd.ms.excel");
String fileName = URLEncoder.encode("考勤表", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + "xlsx");
ServletOutputStream outputStream = response.getOutputStream();
InputStream resourceAsStream = MmmCrmApplication.class.getClassLoader().getResourceAsStream("visitDetail.xlsx");
// 第一次填充
String tempFilePath = "temp.xlsx";
try {
// 创建 ExcelWriter 对象
ExcelWriter excelWriter = EasyExcel.write(outputStream).withTemplate(resourceAsStream).build();
// 创建 WriteSheet 对象
WriteSheet writeSheet1 = EasyExcel.writerSheet("出勤汇总").build(); // 第一页
WriteSheet writeSheet2 = EasyExcel.writerSheet("出勤详情").build(); // 第二页
// 进行填充操作
excelWriter.fill(rows, writeSheet1);
// 创建 FillConfig 对象
FillConfig build = FillConfig.builder()
//设置填充方向为水平
.direction(WriteDirectionEnum.HORIZONTAL)
.build();
//获取当月天数,并遍历填充
int dayCount = query.getDayCount();
List<DayCount> dayCounts = new ArrayList<>();
for (int i = 1; i <= dayCount; i++) {
DayCount dayC = new DayCount();
dayC.setDay(i);
dayCounts.add(dayC);
}
excelWriter.fill(new FillWrapper("d1",rowForTwo),writeSheet2);
excelWriter.fill(new FillWrapper("d2",dayCounts),build,writeSheet2);
for (EmpAttendanceVO empAttendanceVO : rowForTwo) {
List<VisitCalendar> visitCalendarByMonth = customerVisitService.getVisitCalendarByMonth(query);
for (VisitCalendar visitCalendar : visitCalendarByMonth) {
String visitType = visitCalendar.getVisitType();
if ("正常".equals(visitType)){
visitCalendar.setVisitShow("√");
}
if ("缺勤".equals(visitType)){
visitCalendar.setVisitShow("0");
}
if ("休息".equals(visitType)){
visitCalendar.setVisitShow("休");
}
if ("迟到/早退".equals(visitType)){
visitCalendar.setVisitShow(visitCalendar.getVisitCount()+"");
}
if ("无效出勤".equals(visitType)){
visitCalendar.setVisitShow(visitCalendar.getVisitCount()+"");
}
if ("未出数据".equals(visitType)){
visitCalendar.setVisitShow("\\");
}
}
excelWriter.fill(new FillWrapper("d3",visitCalendarByMonth),build,writeSheet2);
}
// 关闭 ExcelWriter 对象,释放资源
excelWriter.finish();
} finally {
// 删除临时文件
File tempFile = new File(tempFilePath);
tempFile.delete();
}
}
```java
package mmm.crm.business.vo;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import java.util.ArrayList;
import java.util.List;
/**
* @Author: XGY
* @Date: 2023/10/26 10:48
* 员工考勤vo
*/
@Data
public class EmpAttendanceVO {
private int id;
@ApiModelProperty("员工编号")
private Long userId;
@ApiModelProperty("员工姓名")
private String empName;
@ApiModelProperty("员工手机号")
private String phoneNum;
@ApiModelProperty("所属大区")
private String bigRegion;
@ApiModelProperty("本月应出勤天数")
private int requiredDays;
@ApiModelProperty("有效出勤天数")
private int effectiveDays;
@ApiModelProperty("无效出勤天数")
private int invalidDays;
@ApiModelProperty("缺勤天数")
private int absenteeismDays;
@ApiModelProperty("迟到早退天数")
private int lateDays;
@ApiModelProperty("签到次数")
private int visitCount;
@ApiModelProperty("签到情况")
private List<VisitCalendar> visitCalendar;
}
```java
package mmm.crm.business.vo;
import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import java.util.Date;
/**
* @Author: XGY
* @Date: 2023/10/31 18:01
* 签到日历格式
*/
@Data
public class VisitCalendar {
//日期
@ApiModelProperty("对应日期")
@JsonFormat(locale="zh", timezone="GMT+8", pattern="yyyy-M-d")
private Date date;
//签到数量
private int visitCount;
//当日签到类型(正常,无效,缺勤,迟到/早退)
private String visitType;
//excel表签到情况展示
private String visitShow;
}
还需要给每一行根据VisitShow字段的值设置不同的颜色