Spring Boot整合EasyExcel，动态导出表头和数据

前端页面设置了列表表头 的动态查询用户可以自己设置那些需要关注的字段为此后端需要保持导出的表头与前端一致。本文介绍如何使用spring booteasyExcel动态导出数据。步骤1.设置实体类DatapublicclassRepairWorkOrderextendsBaseEntity{//自增主键IDprivateIntegerid;//工单号码TableField(conditionSqlCondition.LIKE)privateStringorderNo;//设备SNTableField(conditionSqlCondition.LIKE)privateStringdeviceSn;//设备型号TableField(conditionSqlCondition.LIKE)privateStringdeviceModel;//设备类型privateStringdeviceType;//工单类型0-售后工单、1-自制工单privateIntegertype;}步骤2.设置导出实体SuppressWarnings(serial)DataColumnWidth(20)publicclassRepairWorkOrderExportDTO{ExcelProperty(value工单号)privateStringorderNo;//工单状态0-待定性定责 1-待分配维修工程师 2-待维修测试 3-待交付 4-已完成ExcelProperty(value工单状态)privateStringstatusString;privateIntegerstatus;ExcelProperty(value设备SN)privateStringdeviceSn;//设备型号ExcelProperty(value设备型号)privateStringdeviceModel;//工单类型0-售后工单、1-自制产品维修ExcelProperty(value工单类型)privateStringtypeString;privateIntegertype;}步骤3.加依赖EasyExcel Spring Boot StarterdependencygroupIdcom.alibaba/groupIdartifactIdeasyexcel-spring-boot-starter/artifactIdversion3.3.2/version/dependency步骤4.动态导出工具方法数据列表dataList导出表头列表includeColumnFieldNames这里使用LinkedHashSet保证导出表头的顺序和传入的参数顺序一致。响应responsepublicvoidexportDynamicExcel(ListRepairWorkOrderExportDTOdataList,LinkedHashSetStringincludeColumnFieldNames,HttpServletResponseresponse)throwsIOException{if(ObjectUtil.isNull(dataList)){thrownewValidateException(导出参数不能为空);}if(ObjectUtil.isEmpty(includeColumnFieldNames)){thrownewValidateException(导出字段列表不能为空);}response.setContentType(application/vnd.openxmlformats-officedocument.spreadsheetml.sheet);response.setCharacterEncoding(utf-8);StringfileName维修工单列表_System.currentTimeMillis().xlsx;response.setHeader(Content-Disposition,attachment; filename\java.net.URLEncoder.encode(fileName,UTF-8).replaceAll(\\,%20)\);try(OutputStreamoutresponse.getOutputStream()){ExcelWriterBuilderwriterBuilderEasyExcel.write(out,RepairWorkOrderExportDTO.class).includeColumnFiledNames(includeColumnFieldNames).registerWriteHandler(newLongestMatchColumnWidthStyleStrategy()).registerConverter(LocalDateStringConverter.INSTANCE)//Date日期转化工具如无date类型可以去掉.registerConverter(LocalDateTimeStringConverter.INSTANCE)//DateTime日期转化工具如无dateTime类型可以去掉;writerBuilder.sheet(维修工单列表).doWrite(dataList);}catch(Exceptione){log.error(导出异常,e);thrownewValidateException(导出异常);}}步骤5.状态等字段翻译成文字这里我就使用最简单的方法了遍历然后翻译。privatevoidtranslateStatus(ListRepairWorkOrderExportDTOlist){if(listnull||list.isEmpty()){return;}for(RepairWorkOrderExportDTOdto:list){if(dtonull){continue;}dto.setTypeString(dto.getType()0?Constant.type0:Constant.type1);dto.setStatusString(STATUS_MAP.getOrDefault(dto.getStatus(),未知状态));IntegerwarrantyStatusdto.getWarrantyStatus();StringdeviceTypedto.getDeviceType();if(deviceType!null){if(ServiceOrderEnum.UAV.getDescEN().equals(dto.getDeviceType())){dto.setDeviceType(ServiceOrderEnum.UAV.getDesc());}if(ServiceOrderEnum.LOAD.getDescEN().equals(dto.getDeviceType())){dto.setDeviceType(ServiceOrderEnum.LOAD.getDesc());}if(ServiceOrderEnum.NEST.getDescEN().equals(dto.getDeviceType())){dto.setDeviceType(ServiceOrderEnum.NEST.getDesc());}}}}