利用easy poi做文件导出

一、引入必要依赖

与spring boot集成

        
        
            cn.afterturn
            easypoi-spring-boot-starter
            4.3.0
        

不与spring boot 集成

 
            cn.afterturn
            easypoi-base
            3.2.0
            cn.afterturn
            easypoi-web
            3.2.0
            cn.afterturn
            easypoi-annotation
            3.2.0
        
            org.hibernate
            hibernate-validator
            5.3.6.Final

二、创建映射类

@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 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 allContentList = new ArrayList<>();
        //根据时间获取工单
        List patrolProcessExportDTOList = analysisPatrolProcessExportInfo.analyzeExportProcessInfo(new ExportWorkOrderInfoDTO(startTime, endTime));
        List rowIndexList = new LinkedList<>();
        if (!CollectionUtils.isEmpty(patrolProcessExportDTOList)) {
            int i = 1;
            for (PatrolProcessExportDTO patrolProcessExportDTO : patrolProcessExportDTOList) {
                Map>> 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)));
        }
 
    }