easyExcel的一些操作

首先引入依赖

<dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>easyexcel</artifactId>
      <version>3.1.1</version>
</dependency>

创建实体类

@Data
@ContentRowHeight(25) //文本行高度
@HeadRowHeight(30)    //标题高度
@ColumnWidth(25)      //默认列宽度
public class JdkhDto {
    @ExcelProperty({"1", "序号"})
    private int seriaNumber;
    @ExcelProperty({"1", "处室"})
    @ColumnWidth(40)
    private String cs;
    @ExcelProperty({"1", "人员"})
    private String person;
    @ExcelProperty({"1", "考核结果"})
    private String rq;
}

这样的话可以有两个表头

 因为要对第一行进行动态转换，所以特意留了一行（其实动态的在这里写没有用，要代码实现）

为了方便，没有写三层架构

 第一列序号自增，其实很简单，实体类第一个加序号字段，从后端取出来之后放到集合里的同时，自增放进去,实现：

public List<JdkhDto> download(String entrycode) {
        List<JdkhDto> jdkhDtoList = new ArrayList<>();

        List<UserIdDto> attribute = jdkhDownloadMapper.getAttribute(entrycode);
        List<String> personId = new ArrayList<>();
        for (UserIdDto userId : attribute) {
            List<String> lists = Arrays.asList(userId.getAttribute1().split(","));
            personId.add(userId.getFullname().substring(0, 10));
            for (String list : lists) {
                personId.add(list);
            }
        }

        //前面代码不用看，从这里开始看，定义一个开始变量 i = 1
        Integer i = 1;
        for (String bid : personId) {
            String score = "";
            score = jdkhDownloadMapper.getScore(entrycode, bid);
            if ("1".equals(score)) {
                score = "好";
            } else if ("2".equals(score)) {
                score = "较好";
            } else if ("3".equals(score)) {
                score = "一般";
            } else if ("4".equals(score)) {
                score = "较差";
            } else if ("0".equals(score)) {
                score = "不定等次";
            }
            String personName = jdkhDownloadMapper.getPersonName(bid);
            String organName = jdkhDownloadMapper.getOrganname(entrycode, bid);
            if ("1".equals(organName) || "".equals(organName) || null == organName) {
                organName = jdkhDownloadMapper.getNewOrganname(entrycode, bid);
            }
            JdkhDto jdkhDto = new JdkhDto();
            jdkhDto.setRq(score);
            jdkhDto.setCs(organName);
            jdkhDto.setPerson(personName);
            //在这里把i给加到对象里面
            jdkhDto.setSeriaNumber(i);
            jdkhDtoList.add(jdkhDto);
            //每循环一次i自增
            i++;
        }
        JdkhDto jdkhDto = new JdkhDto();
        //最后一行要靠右，不想单独设置就加了很多空格让“签字”靠右
        jdkhDto.setSeriaNumber("                                                                            签字 ：");
        jdkhDtoList.add(jdkhDto);
        return jdkhDtoList;
    }

写进excel操作：

@RestController
@RequestMapping("/download")
public class JdkhDownloadController {

    @Autowired
    private JdkhDownloadService jdkhDownloadService;

    @GetMapping("/all")
    public HttpResult download(@RequestParam("year") String year, @RequestParam("quarter") String quarter) {
        String entrycode = year + "Q" + quarter;
        //D盘下的myexcel目录下
        String fileName = "D:/myexcel/" + "省自然资源厅" + year + "第" + quarter + "季度考核结果" + ".xlsx";

        List<List<String>> list = new ArrayList<>();
        //内容样式策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        //垂直居中,水平居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);


        // 字体策略
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short) 12);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        //头策略使用默认 设置字体大小
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 12);
        headWriteCellStyle.setWriteFont(headWriteFont);
        
        //这里实现动态头，和注解加两个表头一个原理
        List<String> headList = new ArrayList<>();
        headList.add("序号");
        headList.add("处室");
        headList.add("人员");
        headList.add("考核结果");
        headList.forEach(i -> {
            List<String> head = new ArrayList<>();
            head.add("省自然资源厅" + year + "第" + quarter + "季度考核结果");
            head.add(i);
            list.add(head);
        });
        List<JdkhDto> download = jdkhDownloadService.download(entrycode);
        if (download.size() == 1) {
            return HttpResult.error("选择的日期暂时没有考核结果");
        }
//        LoopMergeStrategy loopMergeStrategy = new LoopMergeStrategy(3, 4,0);
        OnceAbsoluteMergeStrategy onceAbsoluteMergeStrategy = new OnceAbsoluteMergeStrategy(download.size() + 1, download.size() + 1, 0, 3);
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为季度考核表 然后文件流会自动关闭
        EasyExcel.write(fileName, JdkhDto.class).head(list)
                .registerWriteHandler(new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle))
                .registerWriteHandler(onceAbsoluteMergeStrategy)
                .sheet(quarter + "季度考核表").doWrite(download);
        return HttpResult.ok();
    }
}

最后效果：