利用easy poi做文件导出

一、引入必要依赖

与spring boot集成

        <!--easy poi-->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-spring-boot-starter</artifactId>
            <version>4.3.0</version>
        </dependency>

不与spring boot 集成

 <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>3.2.0</version>
</dependency>
<dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>3.2.0</version>
</dependency>
<dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>3.2.0</version>
</dependency>
        
<!-- poi校验使用 -->
<dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-validator</artifactId>
            <version>5.3.6.Final</version>
</dependency>

二、创建映射类

@Data
public class PatrolProcessDTO {

    @Excel(name = "设备名称", orderNum = "0", width = 20, mergeVertical = true)
    private String orderNum;

    @Excel(name = "工单名称", orderNum = "1", width = 20, mergeVertical = true)
    private String workOrderName;

    @Excel(name = "巡检路线", orderNum = "2", width = 20, mergeVertical = true)
    private String patrolRoute;

    @Excel(name = "维修技师", orderNum = "3", width = 20, mergeVertical = true)
    private String maintenanceUserName;

    @Excel(name = "打点情况", orderNum = "4", width = 20, mergeVertical = false)
    private String content;

    @Excel(name = "处理结果（工单状态）", orderNum = "5", width = 20, mergeVertical = true)
    private String workOrderSolveStatusName;

    @Excel(name = "维修技师处理时长", orderNum = "6", width = 20, mergeVertical = true)
    private String maintenanceTime;

    @Excel(name = "维修班长确认时长", orderNum = "7", width = 20, mergeVertical = true)
    private String confirmTime;

    @Excel(name = "总用时", orderNum = "8", width = 20, mergeVertical = true)
    private String workOrderSpendTime;
}

//mergeVertical 为true 代表遇相同相邻上下文合并

三、创建设置样式方法

private void setColor(Sheet sheetAt, List<Integer> list, Workbook workbook) {
        if (!CollectionUtils.isEmpty(list)) {
            for (Integer rowIndex : list) {
                Row row = sheetAt.getRow(rowIndex);
                if (ObjectUtil.isNull(row)) {
                    continue;
                }
                //获取单元格对象
                Cell cell2 = row.getCell(4);
                //获取单元格样式对象
                CellStyle cellStyle = workbook.createCellStyle();
                //获取单元格内容对象
                Font font = workbook.createFont();
                //一定要装入 样式中才会生效
                cellStyle.setFont(font);
                //获取当前单元格中的value
                if (ObjectUtil.isNull(cell2)) {
                    continue;
                }
                String value = sheetAt.getRow(rowIndex).getCell(4).toString();
                if (StringUtils.isNotBlank(value) && value.contains("未打卡")) {
                    cellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
                    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                }
                cell2.setCellStyle(cellStyle);
            }
        }
    }

四、导出实现

  List<PatrolProcessDTO> allContentList = new ArrayList<>();
        //根据时间获取工单
        List<PatrolProcessExportDTO> patrolProcessExportDTOList = analysisPatrolProcessExportInfo.analyzeExportProcessInfo(new ExportWorkOrderInfoDTO(startTime, endTime));
        List<Integer> rowIndexList = new LinkedList<>();
        if (!CollectionUtils.isEmpty(patrolProcessExportDTOList)) {
            int i = 1;
            for (PatrolProcessExportDTO patrolProcessExportDTO : patrolProcessExportDTOList) {
                Map<String, List<List<String>>> nfcMap = patrolProcessExportDTO.getNfcMap();
                if (!CollectionUtils.isEmpty(nfcMap)) {
                    int finalI = i;
                    nfcMap.forEach((k, v) -> {
                        PatrolProcessDTO patrolProcessDTO = new PatrolProcessDTO();
                        BeanUtils.copyProperties(patrolProcessExportDTO, patrolProcessDTO);
                        patrolProcessDTO.setOrderNum(finalI + "");
                        //设置打卡点情况
                        if (v.size() == 0) {
                            patrolProcessDTO.setContent(k + "未打卡");
                            rowIndexList.add(allContentList.size());
                        } else {
                            patrolProcessDTO.setContent(getNfcInfo(v, k));
                        }
                        allContentList.add(patrolProcessDTO);

                    });
                } else {
                    PatrolProcessDTO patrolProcessDTO = new PatrolProcessDTO();
                    BeanUtils.copyProperties(patrolProcessExportDTO, patrolProcessDTO);
                    patrolProcessDTO.setOrderNum(i + "");
                    allContentList.add(patrolProcessDTO);
                }
                i++;
            }
        }

        //向会话写入
        try {
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String fileName = URLEncoder.encode("巡检工单", "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            //设置sheet页名称
            ExportParams exportParams = new ExportParams();
            exportParams.setSheetName("巡检工单数据");
            Workbook workbook = ExcelExportUtil.exportExcel(exportParams, PatrolProcessDTO.class, allContentList);
            //设置单元格样式
            Sheet sheetAt = workbook.getSheetAt(0);
            setColor(sheetAt, rowIndexList, workbook);
            workbook.write(response.getOutputStream());
        } catch (Exception e) {
            log.error("导出出错-{}", ExceptionUtils.getStackTrace(e));
            renderString(response, JSON.toJSONString(new ResultInfo().error(SystemError.SYS_10055)));
        }

    }