前言
在真实的开发者场景中,经常会使用excel作为数据的载体,进行数据导入和导出的操作,使用excel的导入和导出有很多种解决方案,用到了导出多sheet到一个excel文件,锁定、解锁、设置字体、自动换行、设置指定单元格背景色、字典转换值、导出下拉框等功能,本篇记录一下EasyExcel的使用。
一、EasyExcel是什么?
EasyExcel是一个开源的项目,是阿里开发的。EasyExcel可以简化Excel表格的导入和导出操作,使用起来简单快捷,易上手。
二、使用步骤
1.导入依赖
在pom.xml中导入我们需要使用的依赖
<!-- easy excel依赖 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.3.2</version>
</dependency>
2.控制层
控制层负责接收前端传过来的文件流,然后使用EasyExcel来对导入或者导出的数据进行进一步的处理,并把处理结果返回,需要注意的是,对于导出操作,该方法并没有返回值,因此我们不需要返回值,而对于导入方法,此处我使用的是layui的前端,所以返回的数据格式是layui可以接收的工具类。
/**
* 导入Excel
*/
@RequestMapping("/importExcel.do")
@ResponseBody
public Message importExcel(@RequestParam(value = "file") MultipartFile file) throws IOException{
EasyExcel.read(file.getInputStream(), SjhyCwrzbVo.class, new ExcelListener(sjhyCwrzService,sjhyZgjlService,xzqhService,cjsjhyRedisUtil)).sheet("核验错误结果表").doRead();
return new Message<>(true, "导入成功");
}
/**
* description: 前置机核验错误日志列表导出
*
* @return
*/
@ApiOperation(value = "核验错误日志列表导出", notes = "错误日志列表导出")
@GetMapping("/exportCwrzList.do")
public void exportCwrzList(
@ApiParam(name = "xzqhbm", value = "行政区划编码") @RequestParam(value = "xzqhbm", required = false) String xzqhbm,
@ApiParam(name = "name", value = "主分类名称") @RequestParam(value = "name", required = false) String name,
@ApiParam(name = "ywlx", value = "业务类型") @RequestParam(value = "ywlx", required = false) String ywlx,
@ApiParam(name = "jybm", value = "校验表名") @RequestParam(value = "jybm", required = false) String jybm,
@ApiParam(name = "jyyw", value = "校验业务") @RequestParam(value = "jyyw", required = false) String jyyw,
@ApiParam(name = "cwms", value = "错误描述") @RequestParam(value = "cwms", required = false) String cwms,
@ApiParam(name = "wttsMin", value = "问题条数MIN") @RequestParam(value = "wttsMin", required = false) String wttsMin,
@ApiParam(name = "wttsMax", value = "问题条数Max") @RequestParam(value = "wttsMax", required = false) String wttsMax,
@ApiParam(name = "jysjq", value = "校验时间起") @RequestParam(value = "jysjq", required = false) String jysjq,
@ApiParam(name = "jysjz", value = "校验时间止") @RequestParam(value = "jysjz", required = false) String jysjz,
@ApiParam(name = "zxwttsMin", value = "最新问题条数-min") @RequestParam(value = "zxwttsMin", required = false) String zxwttsMin,
@ApiParam(name = "zxwttsMax", value = "最新问题条数-max") @RequestParam(value = "zxwttsMax", required = false) String zxwttsMax,
@ApiParam(name = "zxjdMin", value = "最新进度-max") @RequestParam(value = "zxjdMin", required = false) String zxjdMin,
@ApiParam(name = "zxjdMax", value = "最新进度-max") @RequestParam(value = "zxjdMax", required = false) String zxjdMax,
@ApiParam(name = "sfzg", value = "是否整改:1 是, 0否") @RequestParam(value = "sfzg", required = false) String sfzg,
@ApiParam(name = "sytsMin", value = "预计完成剩余天数-min") @RequestParam(value = "sytsMin", required = false) String sytsMin,
@ApiParam(name = "sytsMax", value = "预计完成剩余天数-max") @RequestParam(value = "sytsMax", required = false) String sytsMax,
@ApiParam(name = "zxjdOb", value = "最新进度-排序: asc 顺序, desc 倒序") @RequestParam(value = "zxjdOb", required = false) String zxjdOb,
@ApiParam(name = "sytsOb", value = "剩余天数-排序: asc 顺序, desc 倒序") @RequestParam(value = "sytsOb", required = false) String sytsOb,
@ApiParam(name = "jdbh", value = "进度变化") @RequestParam(value = "jdbh", required = false) String jdbh,
HttpServletResponse response) {
try {
Pagination<SjhyCwrzbVo> result = sjhyCwrzService.getCwrzListByPage(xzqhbm, name, ywlx, jybm, jyyw, cwms, wttsMin, wttsMax, jysjq, jysjz, 1, Integer.MAX_VALUE, zxwttsMin, zxwttsMax, zxjdMin, zxjdMax, sfzg, sytsMin, sytsMax, zxjdOb, sytsOb, jdbh);
List<SjhyCwrzbVo> list = result.getList();
if (CollectionUtils.isEmpty(list)) {
throw new BusinessException("未查询到相关数据");
}
// 获取本周一和周日时间
LocalDate mondayDate = LocalDate.now().with(TemporalAdjusters.previousOrSame(DayOfWeek.MONDAY));
LocalDate sundayDate = mondayDate.plusDays(6);
// 查询整改情况汇总
CwzgZbQueryDto cwzgZbQueryDto = new CwzgZbQueryDto();
cwzgZbQueryDto.setXzqhbm(xzqhbm);
cwzgZbQueryDto.setYwlx(ywlx);
// 插入本周时间检索
cwzgZbQueryDto.setSjfwq(mondayDate.toString());
cwzgZbQueryDto.setSjfwz(sundayDate.toString());
RequestPageDto<CwzgZbQueryDto> cwzgZbQueryDtoRequestPageDto = new RequestPageDto<>();
cwzgZbQueryDtoRequestPageDto.setDto(cwzgZbQueryDto);
cwzgZbQueryDtoRequestPageDto.setPageNo(1);
cwzgZbQueryDtoRequestPageDto.setPageSize(Integer.MAX_VALUE);
Pagination<CwzgZbVo> cwzgZbPage = sjhyCwrzService.getCwzgZbPage(cwzgZbQueryDtoRequestPageDto);
List<CwzgZbVo> list1 = cwzgZbPage.getList();
// 查询整改情况分析
CwzgQksmQueryDto cwzgQksmQueryDto = new CwzgQksmQueryDto();
cwzgQksmQueryDto.setXzqhbm(xzqhbm);
cwzgQksmQueryDto.setYwlx(ywlx);
// 插入本周时间检索
cwzgQksmQueryDto.setSjfwq(mondayDate.toString());
cwzgQksmQueryDto.setSjfwz(sundayDate.toString());
RequestPageDto<CwzgQksmQueryDto> cwzgQksmQueryDtoRequestPageDto = new RequestPageDto<>();
cwzgQksmQueryDtoRequestPageDto.setDto(cwzgQksmQueryDto);
cwzgQksmQueryDtoRequestPageDto.setPageNo(1);
cwzgQksmQueryDtoRequestPageDto.setPageSize(Integer.MAX_VALUE);
Pagination<CwzgQksmVo> cwzgQksmPage = sjhyCwrzService.getCwzgQksmPage(cwzgQksmQueryDtoRequestPageDto);
List<CwzgQksmVo> list2 = cwzgQksmPage.getList();
String xzqh="";
if (StringUtils.isNotBlank(xzqhbm)) {
xzqh = list.get(0).getXzqh();
}
ExcelUtil.exportExcel(response, list, list1, list2, xzqh + "核验结果错误表" + DateUtils.dateFormat(new Date(), "yyyyMMdd"));
System.out.println(response);
} catch (Exception e) {
if (e instanceof BusinessException) {
throw (BusinessException) e;
}
log.error("核验错误日志列表导出"+e);
throw new BusinessException("【导出失败】" + e.getMessage());
}
}
3.配置表头对应实体类
dto1:
package com.dhcc.cjsjhy.dto;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.format.NumberFormat;
import com.alibaba.excel.annotation.write.style.*;
import com.alibaba.excel.converters.date.DateDateConverter;
import com.alibaba.excel.enums.BooleanEnum;
import com.alibaba.excel.enums.poi.BorderStyleEnum;
import com.alibaba.excel.enums.poi.FillPatternTypeEnum;
import com.alibaba.excel.enums.poi.HorizontalAlignmentEnum;
import com.alibaba.excel.enums.poi.VerticalAlignmentEnum;
import com.dhcc.cjsjhy.util.UnLockCell;
import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import org.apache.poi.ss.usermodel.IndexedColors;
import java.util.Date;
/**
* @Created by Mr.Zhang
* @Date 2023/7/19
* @Description XXXXX表 前端展示vo
*/
@Data
@HeadRowHeight(value = 40)
//@ContentRowHeight(value = 30)
@ColumnWidth(value = 18)
@ApiModel(description = "XXXXX表 前端展示vo")
@ExcelIgnoreUnannotated
public class SjhyCwrzbVo {
@ExcelIgnore
@ApiModelProperty(value = "行政区划", name = "xzqhbm", required = false)
String xzqhbm;
@ColumnWidth(value = 15)
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
@ExcelProperty(value = "行政区划", index = -1, order = 1)
@ApiModelProperty(value = "区划名称", name = "xzqh", required = false)
String xzqh;
@ApiModelProperty(value = "批次", name = "pc", required = false)
String pc;
@ApiModelProperty(value = "业务类型", name = "ywlx", required = false)
String ywlx;
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
@ExcelProperty(value = "主分类", index = -1, order = 2)
@ApiModelProperty(value = "业务类型名称", name = "name", required = false)
String name;
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
@ColumnWidth(value = 30)
@ExcelProperty(value = "校验业务", index = -1, order = 3)
@ApiModelProperty(value = "校验业务", name = "jyyw", required = false)
String jyyw;
@ColumnWidth(value = 30)
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
@ExcelProperty(value = "错误描述", index = -1, order = 4)
@ApiModelProperty(value = "错误描述", name = "cwms", required = false)
String cwms;
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
@ColumnWidth(value = 35)
@ExcelProperty(value = "校验表名", index = -1, order = 5)
@ApiModelProperty(value = "校验表名", name = "jybm", required = false)
String jybm;
@ApiModelProperty(value = "问题条数", name = "wtts", required = false)
Integer wtts;
@ColumnWidth(value = 25)
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
@ExcelProperty(value = "校验时间", index = -1, order = 7)
@ApiModelProperty(value = "校验时间", name = "jysj", required = false)
@DateTimeFormat(value = "yyyy-MM-dd HH:mm:ss")
Date jysj;
@ColumnWidth(value = 25)
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
@ExcelProperty(value = "预计完成时间", index = -1, order = 8)
@ApiModelProperty(value = "预计完成时间", name = "yjwcsj", required = false)
@DateTimeFormat(value = "yyyy-MM-dd HH:mm:ss")
Date yjwcsj;
@ColumnWidth(value = 10)
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
@ExcelProperty(value = "最新问题条数", index = -1, order = 6)
@ApiModelProperty(value = "最新问题条数", name = "zxwtts", required = false)
Integer zxwtts;
@ApiModelProperty(value = "进度变化", name = "jdbh", required = false)
String jdbh;
/**
* 上次进度
*/
@ApiModelProperty(value = "上次进度", name = "scjd", required = false)
Integer scjd;
/**
* 最新进度
*/
@ApiModelProperty(value = "最新进度", name = "zxjd", required = false)
Integer zxjd;
/**
* 最新进度
*/
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
@ExcelProperty(value = "截至校验时间完成进度", index = -1, order = 12)
@ApiModelProperty(value = "最新进度百分比", name = "zxjdbfb", required = false)
String zxjdbfb;
/**
* 是否整改(地市提供)
*/
@UnLockCell
@ColumnWidth(value = 17)
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
@ExcelProperty(value = "是否整改(地市提供)", index = -1, order = 13)
@ApiModelProperty(value = "是否整改(地市提供)", name = "sfzgdstg", required = false)
String sfzgdstg;
/**
* 最新预计完成时间(地市提供)
*/
@ColumnWidth(value = 23)
@UnLockCell
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN, dataFormat = 14)
@ContentFontStyle(fontName = "宋体")
@ExcelProperty(value = "最新预计完成时间(地市提供)", index = -1, order = 14)
@ApiModelProperty(value = "最新预计完成时间(地市提供)", name = "zxyjwcsjdstg", required = false)
@DateTimeFormat(value = "yyyy-MM-dd")
String zxyjwcsjdstg;
Date zxyjwcsjdstgDate;
/**
* 备注(地市提供:不能进行整改的注明原因)
*/
@ColumnWidth(value = 30)
@UnLockCell
@ContentStyle(fillBackgroundColor = 1,
verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
@ExcelProperty(value = "备注(地市提供:不能进行整改的注明原因)", index = -1, order = 15)
@ApiModelProperty(value = "备注(地市提供:不能进行整改的注明原因)", name = "bz", required = false)
String bz;
/**
* 是否整改
*/
@ColumnWidth(value = 14)
@ExcelProperty(value = "是否整改", index = -1, order = 9)
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
@ApiModelProperty(value = "是否整改", name = "sfzg", required = false)
String sfzg;
/**
* 是否整改
*/
@ColumnWidth(value = 25)
@ExcelProperty(value = "整改情况说明", index = -1, order = 10)
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
@ApiModelProperty(value = "整改情况说明", name = "zgqksm", required = false)
String zgqksm;
/**
* 预计完成剩余天数
*/
@ApiModelProperty(value = "预计完成剩余天数", name = "yjwcsyts", required = false)
String yjwcsyts;
/**
* 周一进度
*/
@ColumnWidth(value = 14)
@ExcelProperty(value = "本周一完成进度", index = -1, order = 11)
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
@ApiModelProperty(value = "周一进度", name = "zyjd", required = false)
String zyjd;
}
dto2:
package com.dhcc.cjsjhy.dto;
import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.*;
import com.alibaba.excel.enums.BooleanEnum;
import com.alibaba.excel.enums.poi.BorderStyleEnum;
import com.alibaba.excel.enums.poi.HorizontalAlignmentEnum;
import com.alibaba.excel.enums.poi.VerticalAlignmentEnum;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
/**
* @Created by liuguodong
* @Date 2023/6/25
* @Description 错误整改情况说明
*/
@Data
@ExcelIgnoreUnannotated
@HeadRowHeight(value = 25)
@ContentRowHeight(value = 25)
@ColumnWidth(value = 18)
@ApiModel(description = "整改情况分析,前端展示vo")
public class CwzgQksmVo {
@ApiModelProperty(value = "id", name = "id", required = false)
String id;
@ColumnWidth(value = 15)
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
@ExcelProperty(value = "行政区划", index = -1, order = 1)
@ApiModelProperty(value = "行政区划名称", name = "xzqhName", required = false)
String xzqhName;
@ApiModelProperty(value = "行政区划编码", name = "xzqhbm", required = false)
String xzqhbm;
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
@ExcelProperty(value = "业务类型", index = -1, order = 2)
@ApiModelProperty(value = "业务类型", name = "ywlx", required = false)
String ywlx;
@ColumnWidth(value = 30)
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
@ExcelProperty(value = "校验业务", index = -1, order = 3)
@ApiModelProperty(value = "校验业务", name = "bmName", required = false)
String bmName;
@ColumnWidth(value = 30)
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
@ExcelProperty(value = "错误描述", index = -1, order = 4)
@ApiModelProperty(value = "错误描述", name = "cwms", required = false)
String cwms;
@ColumnWidth(value = 35)
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
@ExcelProperty(value = "校验表名", index = -1, order = 5)
@ApiModelProperty(value = "校验表名", name = "bm", required = false)
String bm;
@ColumnWidth(value = 15)
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
@ExcelProperty(value = "日期", index = -1, order = 6)
@ApiModelProperty(value = "日期", name = "rq", required = false)
String rq;
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
@ExcelProperty(value = "整改天数", index = -1, order = 7)
@ApiModelProperty(value = "整改天数", name = "zgts", required = false)
String zgts;
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
@ExcelProperty(value = "最新问题条数", index = -1, order = 9)
@ApiModelProperty(value = "最新问题条数", name = "zxts", required = false)
String zxts;
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
@ExcelProperty(value = "问题最大条数", index = -1, order = 8)
@ApiModelProperty(value = "问题最大条数", name = "zdwtts", required = false)
String zdts;
@ColumnWidth(value = 12)
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
@ExcelProperty(value = {"周进度", "上周日"}, index = -1, order = 10)
// @ExcelProperty(value = "上周日进展", index = -1, order = 10)
@ApiModelProperty(value = "上周日进展", name = "szrjz", required = false)
String szrjz;
@ApiModelProperty(value = "趋势变化", name = "qsbh", required = false)
String qsbh;
@ColumnWidth(value = 12)
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
@ExcelProperty(value = {"周进度", "周一"}, index = -1, order = 11)
// @ExcelProperty(value = "周一进展", index = -1, order = 11)
@ApiModelProperty(value = "周一进展", name = "w1", required = false)
String w1;
@ColumnWidth(value = 12)
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
@ExcelProperty(value = {"周进度", "周二"}, index = -1, order = 12)
// @ExcelProperty(value = "周二进展", index = -1, order = 12)
@ApiModelProperty(value = "周二进展", name = "w2", required = false)
String w2;
@ColumnWidth(value = 12)
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
@ExcelProperty(value = {"周进度", "周三"}, index = -1, order = 13)
// @ExcelProperty(value = "周三进展", index = -1, order = 13)
@ApiModelProperty(value = "周三进展", name = "w3", required = false)
String w3;
@ColumnWidth(value = 12)
@ExcelProperty(value = {"周进度", "周四"}, index = -1, order = 14)
// @ExcelProperty(value = "周四进展", index = -1, order = 14)
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
@ApiModelProperty(value = "周四进展", name = "w4", required = false)
String w4;
@ColumnWidth(value = 12)
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
@ExcelProperty(value = {"周进度", "周五"}, index = -1, order = 15)
// @ExcelProperty(value = "周五进展", index = -1, order = 15)
@ApiModelProperty(value = "周五进展", name = "w5", required = false)
String w5;
@ColumnWidth(value = 12)
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
@ExcelProperty(value = {"周进度", "周六"}, index = -1, order = 16)
// @ExcelProperty(value = "周六进展", index = -1, order = 16)
@ApiModelProperty(value = "周六进展", name = "w6", required = false)
String w6;
@ColumnWidth(value = 12)
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
@ExcelProperty(value = {"周进度", "周日"}, index = -1, order = 17)
// @ExcelProperty(value = "周日进展", index = -1, order = 17)
@ApiModelProperty(value = "周日进展", name = "w7", required = false)
String w7;
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
@ExcelProperty(value = {"进展趋势", "本周进展"}, index = -1, order = 18)
// @ExcelProperty(value = "本周进展", index = -1, order = 18)
@ApiModelProperty(value = "本周进展", name = "bzjz", required = false)
String bzjz;
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
@ExcelProperty(value = {"进展趋势", "较上周进展"}, index = -1, order = 19)
// @ExcelProperty(value = "较上周进展", index = -1, order = 19)
@ApiModelProperty(value = "较上周进展", name = "jszjz", required = false)
String jszjz;
// @ApiModelProperty(value = "本周进展", name = "bzzj", required = false)
// String bzzj;
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
@ExcelProperty(value = "是否能整改", index = -1, order = 20)
@ApiModelProperty(value = "是否能整改", name = "sfnzg", required = false)
String sfnzg;
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
@ExcelProperty(value = "预计完成天数", index = -1, order = 21)
@ApiModelProperty(value = "预计完成天数", name = "yjwcts", required = false)
String yjwcts;
@ExcelProperty(value = "整改完成", index = -1, order = 22)
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
@ApiModelProperty(value = "整改完成:1 是,0 否", name = "zgwc", required = false)
String zgwc;
@ColumnWidth(value = 35)
@ExcelProperty(value = "整改情况说明", index = -1, order = 23)
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
@ApiModelProperty(value = "整改情况说明", name = "zgqksm", required = false)
String zgqksm;
}
dto3:
package com.dhcc.cjsjhy.dto;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.*;
import com.alibaba.excel.enums.BooleanEnum;
import com.alibaba.excel.enums.poi.BorderStyleEnum;
import com.alibaba.excel.enums.poi.HorizontalAlignmentEnum;
import com.alibaba.excel.enums.poi.VerticalAlignmentEnum;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
/**
* @Created by liuguodong
* @Date 2023/6/25
* @Description 错误整改周报vo
*/
@Data
@HeadRowHeight(value = 25)
@ContentRowHeight(value = 25)
@ColumnWidth(value = 18)
@ApiModel(description = "整改情况汇总vo")
@ExcelIgnoreUnannotated
public class CwzgZbVo {
@ColumnWidth(value = 15)
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
@ExcelProperty(value = "行政区划", index = -1, order = 1)
@ApiModelProperty(value = "行政区划名称", name = "xzqhName", required = false)
String xzqhName;
@ApiModelProperty(value = "行政区划编码", name = "xzqhbm", required = false)
String xzqhbm;
@ColumnWidth(value = 15)
@ExcelProperty(value = "业务类型", index = -1, order = 2)
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
@ApiModelProperty(value = "业务类型", name = "ywlx", required = false)
String ywlx;
@ColumnWidth(value = 15)
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
@ExcelProperty(value = "日期", index = -1, order = 3)
@ApiModelProperty(value = "日期", name = "rq", required = false)
String rq;
@ColumnWidth(value = 12)
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
@ExcelProperty(value = "存在问题表个数", index = -1, order = 4)
@ApiModelProperty(value = "存在问题表个数", name = "wtbgs", required = false)
String wtbgs;
@ColumnWidth(value = 12)
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
@ExcelProperty(value = "问题最大条数", index = -1, order = 5)
@ApiModelProperty(value = "问题最大条数总量", name = "zdts", required = false)
String zdts;
@ColumnWidth(value = 12)
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
@ExcelProperty(value = "最新问题条数", index = -1, order = 6)
@ApiModelProperty(value = "最新问题条数总量", name = "zxts", required = false)
String zxts;
@ColumnWidth(value = 20)
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
@ExcelProperty(value = "整改中最新问题条数", index = -1, order = 7)
@ApiModelProperty(value = "整改中最新问题条数总量", name = "zgzzxts", required = false)
String zgzzxts;
@ExcelProperty(value = "整体进度", index = -1, order = 8)
@ColumnWidth(value = 15)
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
@ApiModelProperty(value = "整体进度-字符", name = "jd", required = false)
String jd;
@ApiModelProperty(value = "整体进度- 数值", name = "jdnum", required = false)
Integer jdnum;
@ColumnWidth(value = 15)
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
@ExcelProperty(value = "较上周趋势变化", index = -1, order = 9)
@ApiModelProperty(value = "较上周趋势变化", name = "qsbh", required = false)
String qsbh;
@ColumnWidth(value = 15)
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
// @ExcelProperty(value = "预计完成两周以上表数", index = -1, order = 22)
@ExcelProperty(value = {"预计时间完成情况(表个数)", "两周以上"}, index = -1, order = 22)
@ApiModelProperty(value = "预计两周以上完成的表的个数", name = "yjlzysgs")
String yjlzysgs;
@ColumnWidth(value = 15)
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
// @ExcelProperty(value = "预计一周到两周内完成表个数", index = -1, order = 21)
@ExcelProperty(value = {"预计时间完成情况(表个数)", "一到两周"}, index = -1, order = 21)
@ApiModelProperty(value = "预计一周到两周内完成的表的个数", name = "yjlzwcgs")
String yjlzwcgs;
@ColumnWidth(value = 15)
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
// @ExcelProperty(value = "预计一周内完成表个数", index = -1, order = 20)
@ExcelProperty(value = {"预计时间完成情况(表个数)", "一周内"}, index = -1, order = 20)
@ApiModelProperty(value = "预计一周内完成的表的个数", name = "yjyzwcgs")
String yjyzwcgs;
@ColumnWidth(value = 15)
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
// @ExcelProperty(value = "预计完成超时表数", index = -1, order = 19)
@ExcelProperty(value = {"预计时间完成情况(表个数)", "超时表数"}, index = -1, order = 19)
@ApiModelProperty(value = "预计完成超时表的个数", name = "yjcsgs")
String yjcsgs;
@ColumnWidth(value = 12)
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
// @ExcelProperty(value = "本周进度上升表数", index = -1, order = 13)
@ExcelProperty(value = {"本周进度情况(表个数)", "上升"}, index = -1, order = 13)
@ApiModelProperty(value = "本周进度上升表个数", name = "bzjdssgs")
String bzjdssgs;
@ColumnWidth(value = 12)
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
// @ExcelProperty(value = "本周进度无变化表数", index = -1, order = 15)
@ExcelProperty(value = {"本周进度情况(表个数)", "无变化"}, index = -1, order = 15)
@ApiModelProperty(value = "本周进度(较周一)无变化表个数", name = "bzwbhgs")
String bzwbhgs;
@ColumnWidth(value = 12)
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
// @ExcelProperty(value = "本周进度下降表数", index = -1, order = 14)
@ExcelProperty(value = {"本周进度情况(表个数)", "下降"}, index = -1, order = 14)
@ApiModelProperty(value = "本周进度(较周一)下降表个数", name = "bzjdxjgs")
String bzjdxjgs;
@ColumnWidth(value = 15)
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
// @ExcelProperty(value = "整改中的表个数", index = -1, order = 12)
@ExcelProperty(value = {"整改情况(表个数)", "整改中"}, index = -1, order = 12)
@ApiModelProperty(value = "当前整改中的表个数", name = "zgzgs")
String zgzgs;
@ColumnWidth(value = 15)
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
// @ExcelProperty(value = "不能整改表个数", index = -1, order = 11)
@ExcelProperty(value = {"整改情况(表个数)", "不能整改"}, index = -1, order = 11)
@ApiModelProperty(value = "不能整改表个数 (当前所有不能整改表个数)", name = "bnzggs")
String bnzggs;
@ColumnWidth(value = 15)
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
// @ExcelProperty(value = "整改完成表个数", index = -1, order = 10)
@ExcelProperty(value = {"整改情况(表个数)", "整改完成"}, index = -1, order = 10)
@ApiModelProperty(value = "整改完成表个数 (本周)", name = "bzzgwcgs")
String bzzgwcgs;
@ColumnWidth(value = 12)
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
// @ExcelProperty(value = "较上周进度下降表个数", index = -1, order = 17)
@ExcelProperty(value = {"较上周进度变化(表个数)", "下降"}, index = -1, order = 17)
@ApiModelProperty(value = "较上周进度下降表个数", name = "jszjdxjgs")
String jszjdxjgs;
@ColumnWidth(value = 12)
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
// @ExcelProperty(value = "较上周进度上升表个数", index = -1, order = 16)
@ExcelProperty(value = {"较上周进度变化(表个数)", "上升"}, index = -1, order = 16)
@ApiModelProperty(value = "本周进度(较周一)上升表个数 ", name = "bzjdjzyssgs")
String bzjdjzyssgs;
@ColumnWidth(value = 12)
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ContentFontStyle(fontName = "宋体")
// @ExcelProperty(value = "较上周进度无变化表个数", index = -1, order = 18)
@ExcelProperty(value = {"较上周进度变化(表个数)", "无变化"}, index = -1, order = 18)
@ApiModelProperty(value = "较上周进度无变化表个数", name = "jszjdwbhgs")
String jszjdwbhgs;
@ApiModelProperty(value = "是否空数据、补全数据:1 是, 0 否", name = "sfksj", required = false)
String sfksj;
}
4.导出工具类编写
package com.dhcc.cjsjhy.util;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.handler.WriteHandler;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.dhcc.basic.util.DateUtils;
import com.dhcc.cjsjhy.dto.CwzgQksmVo;
import com.dhcc.cjsjhy.dto.CwzgZbVo;
import com.dhcc.cjsjhy.dto.SjhyCwrzbVo;
import org.apache.commons.collections4.CollectionUtils;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.*;
/**
* excel工具类
*/
public class ExcelUtil {
public static void exportExcel(HttpServletResponse response, List<SjhyCwrzbVo> dtos, List<CwzgZbVo> list1, List<CwzgQksmVo> list2, String fileName) throws IOException {
try {
response.setCharacterEncoding("UTF-8");
//使用URLEncoder来防止文件名乱码或者读取错误
//Access-Control-Expose-Headers 将fileName暴漏在请求头里,不然只能看到前端获取不到
response.setHeader("Access-Control-Expose-Headers", "filename");
response.setHeader("Access-Control-Expose-Headers", "Content-disposition");
response.setHeader("filename", fileName + ".xlsx");
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
/* EasyExcel.write(response.getOutputStream(), SjhyCwrzbVo.class)
// .head(header)
.registerWriteHandler(new CellHandler())
.registerWriteHandler(new StyleWriteHandler())
.sheet("核验错误结果表")
.doWrite(dtos);*/
ServletOutputStream outputStream = response.getOutputStream();
ExcelWriter excelWriter = EasyExcel.write(outputStream).build();
//写入核验错误日志 sheet
WriteSheet writeSheet = EasyExcel.writerSheet(0, "核验错误结果表")
.head(SjhyCwrzbVo.class)
.registerWriteHandler(new LockSheetWriteHandler())
.registerWriteHandler(new UnLockCellHandler())
.registerWriteHandler(new CompleteDateHandler())
.registerWriteHandler(new CellRangeHandler()).build();
excelWriter.write(dtos, writeSheet);
if (CollectionUtils.isNotEmpty(list2)) {
//写入整改情况分析 sheet
WriteSheet writeSheet2 = EasyExcel.writerSheet(2, "整改情况分析")
.head(CwzgQksmVo.class)
.registerWriteHandler(new LockSheetWriteHandler())
.registerWriteHandler(new UnLockCellHandler())
.registerWriteHandler(new ZgqkUpDownHandler()).build();
excelWriter.write(list2, writeSheet2);
}
if (CollectionUtils.isNotEmpty(list1)) {
//写入整改情况汇总 sheet
WriteSheet writeSheet1 = EasyExcel.writerSheet(1, "整改情况汇总")
.head(CwzgZbVo.class)
.registerWriteHandler(new LockSheetWriteHandler())
.registerWriteHandler(new UnLockCellHandler())
.registerWriteHandler(new WtclqkUpDownHandler())
.build();
excelWriter.write(list1, writeSheet1);
}
//关闭流
excelWriter.finish();
outputStream.flush();
} catch (Exception e) {
throw e;
}
}
// @Transactional
// public static void importExcel(MultipartFile file) throws Exception{
//
// }
public static void main(String[] args) {
String pathname = "D:/2.xlsx";
List<List<Object>> dataList = new ArrayList<>();
dataList.add(new ArrayList<>(Arrays.asList(new String[]{"1", "表头2", "表头3", "表头4", "表头4", "表头4", "表头4", "表头4", "表头4", "表头4", "表头4", "表头4", "表头4", " "})));
dataList.add(new ArrayList<>(Arrays.asList(new String[]{"2", "表头2", "表头3", "表头4"})));
dataList.add(new ArrayList<>(Arrays.asList(new String[]{"3", "表头2", "表头3", "表头4"})));
dataList.add(new ArrayList<>(Arrays.asList(new String[]{"4", "表头2", "表头3", "表头4"})));
dataList.add(new ArrayList<>(Arrays.asList(new String[]{"5", "表头2", "表头3", "表头4"})));
dataList.add(new ArrayList<>(Arrays.asList(new String[]{"6", "表头2", "表头3", "表头4"})));
dataList.add(new ArrayList<>(Arrays.asList(new String[]{"7", "表头2", "表头3", "表头4"})));
dataList.add(new ArrayList<>(Arrays.asList(new String[]{"8", "表头2", "表头3", "表头4"})));
dataList.add(new ArrayList<>(Arrays.asList(new String[]{"9", "表头2", "表头3", "表头4"})));
dataList.add(new ArrayList<>(Arrays.asList(new String[]{"10", "表头2", "表头3", "表头4"})));
dataList.add(new ArrayList<>(Arrays.asList(new String[]{"11", "表头2", "表头3", "表头4"})));
dataList.add(new ArrayList<>(Arrays.asList(new String[]{"12", "表头2", "表头3", "表头4"})));
dataList.add(new ArrayList<>(Arrays.asList(new String[]{"13", "表头2", "表头3", "表头4"})));
dataList.add(new ArrayList<>(Arrays.asList(new String[]{"14", "表头2", "表头3", "表头4"})));
List<List<String>> header = new ArrayList<>(10);
header.add(new ArrayList<>(Arrays.asList(new String[]{"区划"})));
header.add(new ArrayList<>(Arrays.asList(new String[]{"批次"})));
header.add(new ArrayList<>(Arrays.asList(new String[]{"数据集名称"})));
header.add(new ArrayList<>(Arrays.asList(new String[]{"表名称"})));
header.add(new ArrayList<>(Arrays.asList(new String[]{"表名"})));
header.add(new ArrayList<>(Arrays.asList(new String[]{"问题描述"})));
header.add(new ArrayList<>(Arrays.asList(new String[]{"问题数量"})));
header.add(new ArrayList<>(Arrays.asList(new String[]{"校验时间"})));
header.add(new ArrayList<>(Arrays.asList(new String[]{"是否整改"})));
header.add(new ArrayList<>(Arrays.asList(new String[]{"本周一完成进度"})));
header.add(new ArrayList<>(Arrays.asList(new String[]{"截至校验时间完成进度"})));
header.add(new ArrayList<>(Arrays.asList(new String[]{"是否整改(地市提供)"})));
header.add(new ArrayList<>(Arrays.asList(new String[]{"最新预计完成时间(地市提供)"})));
header.add(new ArrayList<>(Arrays.asList(new String[]{"备注(地市提供:不能进行整改的注明原因)"})));
EasyExcel.write(new File(pathname))
.head(header)
.registerWriteHandler(new CellHandler())
.registerWriteHandler(new StyleWriteHandler())
.sheet("Sheet1")
.doWrite(dataList);
}
}
5.导出Handler编写
使用EasyExcel导出,需求是对导出的excel进行锁定,并实现对指定列解锁。
新建一个handler包,并在该包下新建一个LockSheetWriteHandler类,继承SheetWriteHandler类,泛型设置为当前需要导出的类:
1、编写UnLockCell注解
作用于需要解锁的实体类字段上
package com.dhcc.cjsjhy.util;
/**
* @ClassName UnLockCell
* @Description TODO
* @Author Mr.Zhang
* @Date 2023/7/20 23:03
* @Version 1.0
*/
import java.lang.annotation.*;
/**
* 用于标记锁定哪些列不需要锁定
*/
@Target(value = {ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface UnLockCell {
}
2、LockSheetWriteHandler
package com.dhcc.cjsjhy.util;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFSheet;
/**
* @ClassName LockSheetWriteHandler
* @Description 锁定文档handler
* @Author Mr.Zhang
* @Date 2023/7/20 23:11
* @Version 1.0
*/
public class LockSheetWriteHandler implements SheetWriteHandler {
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
Sheet sheet = writeSheetHolder.getSheet();
//锁定工作簿,设置保护密码
sheet.protectSheet("1qaz!QAZ");
// 锁定单元格不可选中(防止别人直接复制内容到其他excel修改)
((SXSSFSheet) writeSheetHolder.getSheet()).lockSelectLockedCells(true);
}
}
3、UnLockCellHandler
package com.dhcc.cjsjhy.util;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.google.common.collect.Lists;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellUtil;
import org.elasticsearch.common.collect.HppcMaps;
import java.lang.reflect.Field;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @ClassName UnLockCellHandler
* @Description 解锁用了@UnLockCell注解的字段,handler
* @Author Mr.Zhang
* @Date 2023/7/20 23:00
* @Version 1.0
*/
public class UnLockCellHandler implements CellWriteHandler {
private static final String PASSWORD = "1qaz!QAZ";
/**
* 在创建单元格之前调用
* The maximum number of Cell Styles was exceeded. You can define up to 64000 style in a .xlsx Workbook
*
* @param writeSheetHolder
* @param writeTableHolder
* @param row
* @param head
* @param columnIndex
* @param relativeRowIndex
* @param isHead
*/
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
}
/**
* 在创建单元格后调用
*
* @param writeSheetHolder
* @param writeTableHolder
* @param cell * @param head
* @param relativeRowIndex
* @param isHead
*/
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
}
/**
* 在完成对单元格的所有操作后调用
*
* @param writeSheetHolder
* @param writeTableHolder
* @param cellDataList
* @param cell
* @param head
* @param relativeRowIndex
* @param isHead
*/
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
String fieldName = head.getFieldName();
Class clazz = writeSheetHolder.getClazz();
Field[] fields = clazz.getDeclaredFields();
Lists.newArrayList(fields).stream().filter(field -> field.isAnnotationPresent(UnLockCell.class)).forEach(field -> {
if (field.getName().equals(fieldName)) {
if (field.isAnnotationPresent(UnLockCell.class) && field.isAnnotationPresent(ExcelProperty.class)) {
/* //可以写的模式
CellStyle unLockCellStyle = cell.getSheet().getWorkbook().createCellStyle();
unLockCellStyle.setLocked(false);
cell.setCellStyle(unLockCellStyle);*/
Map<String, Object> properties = new HashMap<>(1);
properties.put(CellUtil.LOCKED, false);
CellUtil.setCellStyleProperties(cell, properties);
}
}
});
/*for (Field field : fields) {
if (field.isAnnotationPresent(ExcelIgnore.class)){
continue;
}
if (field.getName().equals(fieldName)) {
if (field.isAnnotationPresent(UnLockCell.class) && field.isAnnotationPresent(ExcelProperty.class)) {
Map<String, Object> properties = new HashMap<>(1);
properties.put(CellUtil.LOCKED, false);
CellUtil.setCellStyleProperties(cell, properties);
}
}
}*/
}
@Override
public int order() {
return Integer.MAX_VALUE;
}
}
4、CompleteDateHandler
package com.dhcc.cjsjhy.util;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import org.apache.commons.lang.time.DateUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.util.Date;
import java.util.List;
/**
* @ClassName CompleteDateHandler
* @Description 对导出的指定列进行处理,设置颜色,填充值等
* @Author Mr.Zhang
* @Date 2023/7/22 22:56
* @Version 1.0
*/
public class CompleteDateHandler implements CellWriteHandler {
private static final Logger log = LoggerFactory.getLogger(CompleteDateHandler.class);
private static final SimpleDateFormat SDF = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
private static boolean isYjwcsjHeadAppeared = false;
/*
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, WriteCellData<?> cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
if ("yjwcsj".equals(head.getFieldName())) {
LocalDateTime yjwcsjLocalDateTime = cellData.getDateValue();
if (null != yjwcsjLocalDateTime) {
boolean after = LocalDateTime.now().plusDays(5).isAfter(yjwcsjLocalDateTime);
if (after) {
CellStyle cellStyle = cell.getCellStyle();
XSSFFont xssfFont = new XSSFFont();
xssfFont.setColor(IndexedColors.RED.getIndex());
cellStyle.setFont(xssfFont);
cell.setCellStyle(cellStyle);
}
}
}
}*/
@Override
public void afterCellDataConverted(CellWriteHandlerContext context) {
Head head = context.getHeadData();
List<WriteCellData<?>> cellDataList = context.getCellDataList();
Cell cell = context.getCell();
if ("yjwcsj".equals(head.getFieldName())) {
int rowIndex = cell.getRowIndex();
if (rowIndex > 0) {
WriteCellData<?> cellData = cellDataList.get(0);
LocalDateTime yjwcsjLocalDateTime = cellData.getDateValue();
if (null != yjwcsjLocalDateTime) {
boolean after = LocalDateTime.now().plusDays(5).isAfter(yjwcsjLocalDateTime);
if (after) {
// 设置字体颜色
WriteFont writeFont = new WriteFont();
writeFont.setColor(IndexedColors.WHITE.getIndex());
WriteCellStyle writeCellStyle = cellData.getWriteCellStyle();
writeCellStyle.setWriteFont(writeFont);
// 设置背景颜色
writeCellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
//必须设置 否则背景色不生效
writeCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
cellData.setWriteCellStyle(writeCellStyle);
}
}
}
}
else if ("sfzg".equals(head.getFieldName())) {
int rowIndex = cell.getRowIndex();
if (rowIndex > 0) {
WriteCellData<?> cellData = cellDataList.get(0);
String stringValue = cellData.getStringValue();
if (StringUtils.isNotBlank(stringValue) ) {
if ("否".equals(stringValue)){
// 设置字体颜色
WriteFont writeFont = new WriteFont();
writeFont.setColor(IndexedColors.WHITE.getIndex());
WriteCellStyle writeCellStyle = new WriteCellStyle();
writeCellStyle.setWriteFont(writeFont);
// 设置背景颜色
writeCellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
//必须设置 否则背景色不生效
writeCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
cellData.setWriteCellStyle(writeCellStyle);
}
}
}
}
}
}
5、CellRangeHandler
package com.dhcc.cjsjhy.util;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
import com.google.common.collect.Lists;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.util.CellRangeAddressList;
import java.util.Arrays;
import java.util.Optional;
/**
* @ClassName CellRangeHandler
* @Description 设置指定列导出值为选择下拉
* @Author Mr.Zhang
* @Date 2023/7/23 1:12
* @Version 1.0
*/
public class CellRangeHandler implements CellWriteHandler {
@Override
public void afterCellDataConverted(CellWriteHandlerContext context) {
Integer rowIndex = Optional.ofNullable(context.getCell()).map(Cell::getRowIndex).orElse(null);
if (null != rowIndex) {
CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(rowIndex, rowIndex, 12, 12);
DataValidationHelper helper = context.getWriteSheetHolder().getSheet().getDataValidationHelper();
DataValidationConstraint constraint = helper.createExplicitListConstraint(new String[]{"是", "否"});
context.getWriteSheetHolder().getSheet().addValidationData(helper.createValidation(constraint, cellRangeAddressList));
}
}
}
6、ZgqkUpDownHandler
package com.dhcc.cjsjhy.util;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.text.SimpleDateFormat;
import java.util.List;
/**
* @ClassName CompleteDateHandler
* @Description TODO 整改情况 本周进展和较上周进展根据值设置 上升、下降、无变化
* @Author Mr.Zhang
* @Date 2023/7/22 22:56
* @Version 1.0
*/
public class ZgqkUpDownHandler implements CellWriteHandler {
private static final Logger log = LoggerFactory.getLogger(ZgqkUpDownHandler.class);
private static final SimpleDateFormat SDF = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
private static boolean isYjwcsjHeadAppeared = false;
/*
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, WriteCellData<?> cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
if ("yjwcsj".equals(head.getFieldName())) {
LocalDateTime yjwcsjLocalDateTime = cellData.getDateValue();
if (null != yjwcsjLocalDateTime) {
boolean after = LocalDateTime.now().plusDays(5).isAfter(yjwcsjLocalDateTime);
if (after) {
CellStyle cellStyle = cell.getCellStyle();
XSSFFont xssfFont = new XSSFFont();
xssfFont.setColor(IndexedColors.RED.getIndex());
cellStyle.setFont(xssfFont);
cell.setCellStyle(cellStyle);
}
}
}
}*/
@Override
public void afterCellDataConverted(CellWriteHandlerContext context) {
Head head = context.getHeadData();
List<WriteCellData<?>> cellDataList = context.getCellDataList();
Cell cell = context.getCell();
if ("bzjz".equals(head.getFieldName()) || "jszjz".equals(head.getFieldName())) {
int rowIndex = cell.getRowIndex();
if (rowIndex > 0) {
WriteCellData<?> cellData = cellDataList.get(0);
String stringValue = cellData.getStringValue();
if (StringUtils.isNotBlank(stringValue)) {
switch (stringValue) {
case "1":
cellData.setStringValue("无变化");
break;
case "2":
cellData.setStringValue("上升");
break;
case "3":
cellData.setStringValue("下降");
break;
}
}
}
}
}
}
7、WtclqkUpDownHandler
package com.dhcc.cjsjhy.util;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.text.SimpleDateFormat;
import java.util.List;
/**
* @ClassName CompleteDateHandler
* @Description TODO 整改情况 本周进展和较上周进展根据值设置 上升、下降、无变化
* @Author Mr.Zhang
* @Date 2023/7/22 22:56
* @Version 1.0
*/
public class WtclqkUpDownHandler implements CellWriteHandler {
private static final Logger log = LoggerFactory.getLogger(WtclqkUpDownHandler.class);
private static final SimpleDateFormat SDF = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
private static boolean isYjwcsjHeadAppeared = false;
/*
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, WriteCellData<?> cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
if ("yjwcsj".equals(head.getFieldName())) {
LocalDateTime yjwcsjLocalDateTime = cellData.getDateValue();
if (null != yjwcsjLocalDateTime) {
boolean after = LocalDateTime.now().plusDays(5).isAfter(yjwcsjLocalDateTime);
if (after) {
CellStyle cellStyle = cell.getCellStyle();
XSSFFont xssfFont = new XSSFFont();
xssfFont.setColor(IndexedColors.RED.getIndex());
cellStyle.setFont(xssfFont);
cell.setCellStyle(cellStyle);
}
}
}
}*/
@Override
public void afterCellDataConverted(CellWriteHandlerContext context) {
Head head = context.getHeadData();
List<WriteCellData<?>> cellDataList = context.getCellDataList();
Cell cell = context.getCell();
if ("qsbh".equals(head.getFieldName())) {
int rowIndex = cell.getRowIndex();
if (rowIndex > 0) {
WriteCellData<?> cellData = cellDataList.get(0);
String stringValue = cellData.getStringValue();
if (StringUtils.isNotBlank(stringValue)) {
switch (stringValue) {
case "1":
cellData.setStringValue("无变化");
break;
case "2":
cellData.setStringValue("上升");
break;
case "3":
cellData.setStringValue("下降");
break;
}
}
}
}
}
}
6、导入监听器编写
使用EasyExcel需要进行全局监听配置
新建一个listener包,并在该包下新建一个ExcelListener类,继承AnalysisEventListener类,泛型设置为当前需要导出的类,我这里需要导出的是XX数据,因此我将泛型设置为XX类,整体代码如下:
package com.dhcc.cjsjhy.util;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.dhcc.basic.dao.query.Operator;
import com.dhcc.basic.dao.query.SimpleCondition;
import com.dhcc.basic.exception.BusinessException;
import com.dhcc.basic.util.DateUtils;
import com.dhcc.basic.util.Uuid;
import com.dhcc.cjsjhy.constants.SysConstants;
import com.dhcc.cjsjhy.dto.SjhyCwrzbVo;
import com.dhcc.cjsjhy.enums.SjhyZgjlLsyyEnum;
import com.dhcc.cjsjhy.model.SjhyZgjl;
import com.dhcc.cjsjhy.model.Xzqh;
import com.dhcc.cjsjhy.service.SjhyCwrzService;
import com.dhcc.cjsjhy.service.SjhyZgjlService;
import com.dhcc.cjsjhy.service.XzqhService;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.BeanUtils;
import org.springframework.transaction.annotation.Transactional;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
* @ClassName ExcelListener
* @Description 导入对数据进行处理
* @Author Mr.Zhang
* @Date 2023/7/28 9:28
* @Version 1.0
*/
// 有个很重要的点 ExcelListener 不能被spring管理,要每次读取excel都要new,然后里面用到spring可以构造方法传进去
public class ExcelListener extends AnalysisEventListener<SjhyCwrzbVo> {
private List<SjhyCwrzbVo> list = new ArrayList<>();
/**
* 每隔5条存储数据库,实际使用中可以3000条,然后清理list ,方便内存回收
*/
private static final int BATCH_COUNT = 1000;
/**
* 假设这个是一个DAO,当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
*/
private SjhyCwrzService sjhyCwrzService;
private SjhyZgjlService sjhyZgjlService;
private XzqhService xzqhService;
private CJsjhyRedisUtil cjsjhyRedisUtil;
/**
* 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
*/
public ExcelListener(SjhyCwrzService sjhyCwrzService, SjhyZgjlService sjhyZgjlService, XzqhService xzqhService, CJsjhyRedisUtil cjsjhyRedisUtil) {
this.sjhyCwrzService = sjhyCwrzService;
this.sjhyZgjlService = sjhyZgjlService;
this.xzqhService = xzqhService;
this.cjsjhyRedisUtil = cjsjhyRedisUtil;
}
int i = 2;
/**
* 这个每一条数据解析都会来调用
*/
@Override
@Transactional
public void invoke(SjhyCwrzbVo sjhyCwrzbVo, AnalysisContext analysisContext) {
System.out.println("解析到第" + i + "条数据:========================" + sjhyCwrzbVo.toString());
// 数据存储到datas,供批量处理,或后续自己业务逻辑处理。
if (null != sjhyCwrzbVo.getSfzgdstg() || null != sjhyCwrzbVo.getZxyjwcsjdstg() || null != sjhyCwrzbVo.getBz()) {
if (null == sjhyCwrzbVo.getSfzgdstg() || !("否".equals(sjhyCwrzbVo.getSfzgdstg()) || "是".equals(sjhyCwrzbVo.getSfzgdstg()))) {
throw new BusinessException("第" + i + "行数据,是否整改(地市提供)填写有问题,不能为空,且只能填写 “是” 或 “否”,请检查。");
}
}
if (null != sjhyCwrzbVo.getSfzgdstg()) {
if ("是".equals(sjhyCwrzbVo.getSfzgdstg())) {
if (null == sjhyCwrzbVo.getZxyjwcsjdstg() || !sjhyCwrzbVo.getZxyjwcsjdstg().contains("-")) {
throw new BusinessException("第" + i + "行数据,最新预计完成时间(地市提供)不能为空或者格式错误,格式为:yyyy/MM/dd,请检查。");
}
Date parseDate = TimeUtils.stringToDate(sjhyCwrzbVo.getZxyjwcsjdstg(), TimeUtils.DateFormat.ONLY_DAY);
sjhyCwrzbVo.setZxyjwcsjdstgDate(parseDate);
if (sjhyCwrzbVo.getZxyjwcsjdstgDate().before(new Date())) {
throw new BusinessException("第" + i + "行数据,最新预计完成时间(地市提供)不能小于当前时间,格式为:yyyy/MM/dd,请检查。");
}
list.add(sjhyCwrzbVo);
} else if ("否".equals(sjhyCwrzbVo.getSfzgdstg())) {
if (StringUtils.isBlank(sjhyCwrzbVo.getBz())) {
throw new BusinessException("第" + i + "行数据,备注(地市提供)不能为空,请检查。");
}
list.add(sjhyCwrzbVo);
}
// list.add(sjhyCwrzbVo);
}
// 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM
if (list.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理datas
list.clear();
}
i++;
}
/**
* 所有数据解析完成了 都会来调用
*/
@Override
@Transactional
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
saveData();//确保所有数据都能入库
}
/**
* 加上存储数据库
*/
@Transactional
public void saveData() {
System.out.println("==============================" + list.size() + "条数据,开始存储到数据库");
for (SjhyCwrzbVo sjhyCwrzbVo : list) {
// 数据存储到datas,供批量处理,或后续自己业务逻辑处理。
if (null != sjhyCwrzbVo.getSfzgdstg()) {
if ("是".equals(sjhyCwrzbVo.getSfzgdstg())) {
Xzqh xzqh = xzqhService.findCodeByName(sjhyCwrzbVo.getXzqh());
SjhyZgjl one = sjhyZgjlService.findOne(new SimpleCondition()
.addParm("bm", sjhyCwrzbVo.getJybm())
.addParm("XZQHBM", xzqh.getCode())
.begin()
.addParm("lSYY", Operator.notEqual, SjhyZgjlLsyyEnum.SC.getValue())
.or()
.addParm("lSYY", Operator.isNull, null)
.end().setOrderBy("CJSJ desc"));
if (null != one && null == one.getSjwcsj()) {
if (SysConstants.LSBZ_F.equals(one.getLsbz())) {
// 修改以前的整改记录
one.setLsbz(SysConstants.LSBZ_S);
one.setLsyy(SjhyZgjlLsyyEnum.GX.getValue());
one.setXgsj(new Date());
sjhyZgjlService.update(one);
}
// 新增一条整改记录
// 新建一个对象
SjhyZgjl sjhyZgjl = new SjhyZgjl();
BeanUtils.copyProperties(one, sjhyZgjl);
sjhyZgjl.setId(Uuid.GenUuid());
sjhyZgjl.setSfnzg(SysConstants.SFYX_S);
sjhyZgjl.setLsbz(SysConstants.LSBZ_F);
sjhyZgjl.setLsyy(null);
sjhyZgjl.setYjkssj(null == one.getYjkssj() ? new Date() : one.getYjkssj());
sjhyZgjl.setYjwcsj(sjhyCwrzbVo.getZxyjwcsjdstgDate());
sjhyZgjl.setCjsj(new Date());
sjhyZgjl.setXgsj(new Date());
if (null != sjhyCwrzbVo.getBz()) {
sjhyZgjl.setQksm(sjhyCwrzbVo.getBz());
}
sjhyZgjlService.save(sjhyZgjl);
} else {
// 如果没有以前的整改记录则直接新增一条整改记录
SjhyZgjl sjhyZgjl = new SjhyZgjl();
sjhyZgjl.setId(Uuid.GenUuid());
sjhyZgjl.setBm(sjhyCwrzbVo.getJybm());
// 周期code
sjhyZgjl.setCode(cjsjhyRedisUtil.getCurrentDayNo(SysConstants.SJHY_ZGJL_CODE_KEY, DateUtils.dateFormat(new Date(), "yyyyMMdd")));
sjhyZgjl.setXzqhbm(xzqh.getCode());
sjhyZgjl.setSfnzg(SysConstants.SFYX_S);
sjhyZgjl.setYjkssj(new Date());
sjhyZgjl.setYjwcsj(sjhyCwrzbVo.getZxyjwcsjdstgDate());
sjhyZgjl.setCjsj(new Date());
sjhyZgjl.setXgsj(new Date());
if (null != sjhyCwrzbVo.getBz()) {
sjhyZgjl.setQksm(sjhyCwrzbVo.getBz());
}
sjhyZgjl.setLsbz(SysConstants.LSBZ_F);
sjhyZgjlService.save(sjhyZgjl);
}
} else if ("否".equals(sjhyCwrzbVo.getSfzgdstg())) {
Xzqh xzqh = xzqhService.findCodeByName(sjhyCwrzbVo.getXzqh());
SjhyZgjl one = sjhyZgjlService.findOne(new SimpleCondition()
.addParm("bm", sjhyCwrzbVo.getJybm())
.addParm("XZQHBM", xzqh.getCode())
.begin()
.addParm("lSYY", Operator.notEqual, SjhyZgjlLsyyEnum.SC.getValue())
.or()
.addParm("lSYY", Operator.isNull, null)
.end()
.setOrderBy("CJSJ desc"));
if (null != one && null == one.getSjwcsj()) {
if (SysConstants.LSBZ_F.equals(one.getLsbz())) {
// 修改以前的整改记录
one.setLsbz(SysConstants.LSBZ_S);
one.setLsyy(SjhyZgjlLsyyEnum.GX.getValue());
one.setXgsj(new Date());
sjhyZgjlService.update(one);
}
// 新增一条整改记录
// 新建一个对象
SjhyZgjl sjhyZgjl = new SjhyZgjl();
BeanUtils.copyProperties(one, sjhyZgjl);
sjhyZgjl.setId(Uuid.GenUuid());
sjhyZgjl.setSfnzg(SysConstants.SFYX_F);
sjhyZgjl.setLsbz(SysConstants.LSBZ_S);
sjhyZgjl.setLsyy(SjhyZgjlLsyyEnum.BZG.getValue());
sjhyZgjl.setQksm(sjhyCwrzbVo.getBz());
sjhyZgjl.setCjsj(new Date());
sjhyZgjl.setXgsj(new Date());
sjhyZgjlService.save(sjhyZgjl);
} else {
// 如果没有以前的整改记录则直接新增一条整改记录
SjhyZgjl sjhyZgjl = new SjhyZgjl();
sjhyZgjl.setId(Uuid.GenUuid());
sjhyZgjl.setBm(sjhyCwrzbVo.getJybm());
// 周期code
sjhyZgjl.setCode(cjsjhyRedisUtil.getCurrentDayNo(SysConstants.SJHY_ZGJL_CODE_KEY, DateUtils.dateFormat(new Date(), "yyyyMMdd")));
sjhyZgjl.setXzqhbm(xzqh.getCode());
sjhyZgjl.setSfnzg(SysConstants.SFYX_F);
sjhyZgjl.setCjsj(new Date());
sjhyZgjl.setXgsj(new Date());
sjhyZgjl.setQksm(sjhyCwrzbVo.getBz());
sjhyZgjl.setLsbz(SysConstants.LSBZ_S);
sjhyZgjl.setLsyy(SjhyZgjlLsyyEnum.BZG.getValue());
sjhyZgjlService.save(sjhyZgjl);
}
}
}
}
}
}
总结
EasyExcel是一个excel导入导出的解决方案,非常好用,其它的功能查看官方API文档。