# 官方网站：https://easyexcel.opensource.alibaba.com/
# 导入依赖
<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.2.1</version>
</dependency>
# 常用实体注解
// 忽略该字段
@ExcelIgnore
// 表头名称
@ExcelProperty("采购名称")
// Date类型的日期格式
@com.alibaba.excel.annotation.format.DateTimeFormat("yyyy-MM-dd")
# 下载导出的文件
@RequestMapping(value = "/exportXls")
public void exportXls(HttpServletRequest request, BusProjectImplementPlan busProjectImplementPlan, HttpServletResponse response) {
// 通过response进行返回
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder.encode("项目实施计划", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
// 写入response输出流
EasyExcel.write(response.getOutputStream(), BusProjectImplementPlan.class).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).registerWriteHandler(new CellWriteHandler() {
@Override
public void afterCellDispose(CellWriteHandlerContext context) {
if (BooleanUtils.isNotTrue(context.getHead())) {
String[] dateArrs = {"technicalSolutionPlanDate",
"technicalSolutionDepartCheckPlanDate",
"technicalSolutionsReportPlanDate",
"technicalSolutionsCenterCheckPlanDate",
"technicalSolutionsCompanyCheckPlanDate",
"reviewMeetingCheckPlanDate",
"procurementProjectCheckPlanDate",
"procurementFileCheckPlanDate",
"procurementFileCenterCheckPlanDate",
"procurementFileCompanyCheckPlanDate",
"procurementFileReleaseCheckPlanDate",
"procurementCheckPlanDate",
"decisionPlanDate"};
String[] jszbDateArrs = {"technicalStandardPlanDate",
"technicalStandardDepartCheckPlanDate",
"technicalStandardReportPlanDate",
"technicalStandardCenterCheckPlanDate",
"procurementApplyCheckPlanDate"};
List<String> dataArrList = Arrays.asList(dateArrs);
List<String> jszbDateArrList = Arrays.asList(jszbDateArrs);
String fieldName = context.getHeadData().getFieldName();
int indexOf = -1;
if ("4".equals(projectType)) {
indexOf = jszbDateArrList.indexOf(fieldName);
} else {
indexOf = dataArrList.indexOf(fieldName);
}
// 获取当前单元格的样式 关键代码 start
WriteCellData<?> cellData = context.getFirstCellData();
WriteCellStyle writeCellStyle = cellData.getOrCreateStyle();
// 关键代码 end
if (indexOf > -1) {
// context.getRowIndex()从1开始，记得要-1
BusProjectImplementPlan projectImplementPlan = pageList.get(context.getRowIndex() - 1);
// 通过Hutool的BeanUtil把实体转为map，免得需要通过反射来获取实体的值
Map<String, Object> projectImplementPlanMap = BeanUtil.beanToMap(projectImplementPlan);
Date planDate = (Date) projectImplementPlanMap.get(dataArrList.get(indexOf));
Date actualDate = (Date) projectImplementPlanMap.get(dataArrList.get(indexOf).replace("Plan", "Actual"));
if (planDate != null) {
if (indexOf == 0) {
// 第一个时间点，完成为绿，未完成，若距离节点小于等于7天，则橙色，超时红色
if (actualDate == null) {
long between = DateUtil.between(new Date(), planDate, DateUnit.DAY, false);
if (between < 0) {
writeCellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
writeCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
} else if (between <= 7) {
writeCellStyle.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
writeCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
}
} else {
writeCellStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
writeCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
}
} else {
// 如果不是第一个时间点，则判断是否过期，如果未过期，则判断上一个节点是否完成，如果已完成，则判断两个时间点的关系，当前时间超过两节点的1/2且距离下一节点小于7天标橙
if (actualDate == null) {
// 如果没有过期
Date lastPlanDate = (Date) projectImplementPlanMap.get(dataArrList.get(indexOf - 1));
Date lastActualDate = (Date) projectImplementPlanMap.get(dataArrList.get(indexOf).replace("Plan", "Actual"));
if (lastActualDate != null) {
long between = DateUtil.between(new Date(), planDate, DateUnit.DAY, false);
if (between < 0) {
writeCellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
writeCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
} else if (between <= 7) {
long betweenNext = DateUtil.between(planDate, lastPlanDate, DateUnit.DAY, false);
if (Math.floor(betweenNext / 2) <= between) {
writeCellStyle.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
writeCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
}
}
}
} else {
writeCellStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
writeCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
}
}
}
}
}
}
}).autoCloseStream(Boolean.FALSE).sheet().doWrite(pageList);
}
# 另一种，根据数据内容判断
// 这里需要设置不关闭流
EasyExcel.write(response.getOutputStream()).head(headList).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).registerWriteHandler(new CellWriteHandler() {
@Override
public void afterCellDispose(CellWriteHandlerContext context) {
if (BooleanUtils.isNotTrue(context.getHead())) {
if (!defaultHeaderList.contains(context.getHeadData().getHeadNameList().get(0))) {
// 不包含说明是日期列
YxfsYearPlanDetail yearPlanDetail = yearPlanDetailList.get(context.getRowIndex() - 1);
Date startDate = yearPlanDetail.getStartDate();
Date endDate = yearPlanDetail.getEndDate();
// 根据月份判断是当年还是次年
String[] split = context.getHeadData().getHeadNameList().get(0).split("-");
int nowYear = year;
if (Integer.parseInt(split[0]) < 9) {
nowYear = year + 1;
}
// 获取年度计划详情对应的日期，并和当前日期做对比
DateTime nowDate = DateUtil.parse(nowYear + "-" + context.getHeadData().getHeadNameList().get(0));
if (nowDate.getTime() >= startDate.getTime() && nowDate.getTime() <= endDate.getTime()) {
WriteCellData<?> cellData = context.getFirstCellData();
WriteCellStyle writeCellStyle = cellData.getOrCreateStyle();
writeCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
// DateTime fakeNow = DateUtil.parse("2023-09-15");
if (nowDate.getTime() <= System.currentTimeMillis()) {
// 如果已发生，则涂为绿色
writeCellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
} else {
writeCellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
}
}
}
}
}
}).autoCloseStream(Boolean.FALSE).sheet().doWrite(dataList);
# 前端代码
handleExportXls(fileName) {
if (!fileName || typeof fileName !== 'string') {
fileName = '导出文件'
}
let param = {
projectType: this.index
}
let link = document.createElement('a')
link.style.display = 'none'
link.href = process.env.VUE_APP_API_BASE_URL + '/' + this.url.exportXlsUrl + '?delFlag=0&projectType=' + this.index
link.setAttribute('download', fileName + '.xls')
document.body.appendChild(link)
link.click()
document.body.removeChild(link)
}