通用导出excel服务
1. 一般的后管在分页列表管理里都有导出功能
我们的项目是先有分页管理，后面需求要求所有的分页再增加导出功用，所以后端采用统一导出的策略
根据不同业务场景，多次调用对应的分页接口，然后统一输出到excel里
2. 对应的配置类(后期将配置转化到数据库里)
import cn.hutool.core.collection.CollectionUtil; import cn.hutool.core.lang.Pair; import com.google.common.base.Strings; import lombok.Data; import org.springframework.boot.context.properties.ConfigurationProperties; import reactor.util.function.Tuple2; import reactor.util.function.Tuples; import javax.annotation.PostConstruct; import java.util.*; @Data @ConfigurationProperties(prefix = "export.excel.manager") public class ExportExcelManagerProperty { private Map<String, ExportExcelManagerTemplate> templates; @Data public static class ExportExcelManagerTemplate { /** * 导出的文件名 */ private String fileName; /** * 分页列表接口对应的bean名称 */ private String beanName; /** * 分页列表接口对应的method名称 */ private String methodName; /** * 分页接口对应的请求类 */ private String requestBodyClassName; /** * 分页接口对应的返回类(必须是PageInfo<?>类型) */ private String responseBodyClassName; /** * 动态配置的header头(由于不同国家输出表格的header头不一样,所以不能直接在dto上打 @ExcelProperty来解决) */ private List<Pair<String, String>> headerFiledMap; private String headerFiled; /** * 动态配置的输出列及转换公式 */ private Map<String, String> fieldValueMapping; private Map<String, Map<String, String>> fieldValueMappingFunction; }
@PostConstruct private void init() { // templates.entrySet().stream() .filter(p -> !Strings.isNullOrEmpty(p.getValue().getHeaderFiled())) .forEach(p -> { System.out.println(p.getValue().getHeaderFiled()); p.getValue().headerFiledMap = toListTuple(p.getValue().getHeaderFiled()); }); for (Map.Entry<String, ExportExcelManagerTemplate> entry : templates.entrySet()) { Map<String, Map<String, String>> rs = new HashMap<>(); for (Map.Entry<String, String> entry2 : entry.getValue().getFieldValueMapping().entrySet()) { if (!Strings.isNullOrEmpty(entry2.getValue())) { rs.put(entry2.getKey(), toMap(entry2.getValue())); } } entry.getValue().fieldValueMappingFunction = rs; } System.out.println(this); } private static List<Pair<String, String>> toListTuple(String str) { return toListTuple(str, "&", "="); } private static List<Pair<String, String>> toListTuple(String str, String itemSpliter, String keyValueSpliter) { List<Pair<String, String>> result = new ArrayList<>(); Arrays.stream(str.split(itemSpliter)).forEach(p -> { String[] split = p.split(keyValueSpliter); result.add(Pair.of(split[0], split[1])); }); return result; } private static Map<String, String> toMap(String str) { return toMap(str, "&", "="); } private static Map<String, String> toMap(String str, String itemSpliter, String keyValueSpliter) { Map<String, String> result = new TreeMap<>(); Arrays.stream(str.split(itemSpliter)).forEach(p -> { String[] split = p.split(keyValueSpliter); result.put(split[0], split[1]); }); return result; } }
2. 服务层
@Autowired private ExportExcelManagerProperty exportExcelManagerProperty; private static final Map<String, Class> CLASS_MAP = new ConcurrentHashMap<>(32); /** * 增加导出记录 * * @return */ @Override public Pair<String, byte[]> exportExcel(String type, JSONObject req) { ExportExcelManagerProperty.ExportExcelManagerTemplate template = exportExcelManagerProperty .getTemplates().get(type); Assert.notNull(template, "不支持的导出类型,请联系后端开发配置该类型"); Class requestBodyClassClass = getClass(template.getRequestBodyClassName()); Class responseBodyClassClass = getClass(template.getResponseBodyClassName());
// 如果当前的response类已存在 @ExcelProperty 注解,则直接使用 boolean existEasyExcelProperty = hasEasyExcelProperty(responseBodyClassClass); Object pageDto = JSONObject.parseObject(req.toJSONString(), requestBodyClassClass); int i = 0; List<String> header = template.getHeaderFiledMap().stream() .map(p -> p.getKey()).collect(Collectors.toList()); List<String> fields = template.getHeaderFiledMap().stream() .map(p -> p.getValue()).collect(Collectors.toList()); List<Object> data = new LinkedList<>(); do { if (pageDto instanceof BasePageDto) { ((BasePageDto) pageDto).setPageNum(++i); ((BasePageDto) pageDto).setPageSize(1000); } else break; String sourceBeanName = template.getBeanName(); Object sourceBean = sourceBeanName.contains(".") ? SpringBeanUtil.getBean(getClass(sourceBeanName)) : SpringBeanUtil.getBean(sourceBeanName); if (null == sourceBean) { throw new RuntimeException("配置的bean不合法"); } Object result = InvokeMethodUtil.invokeMethod(sourceBean, template.getMethodName(), pageDto); if (null == result) { log.warn("调用bean方法异常! type:{}, req:{}", type, req); throw new RuntimeException("配置的bean方法不合法"); } PageInfo<Object> pageData = getRestResponseBody(result); // 这里还需要根据header头做映射 if (existEasyExcelProperty) { // 实际结构为 List<Object> ,Object为java类实例 data.addAll(pageData.getList()); } else { // 实际结构为 List<List<Object>>,Object的原始类型 data.addAll(handleData(pageData.getList(), responseBodyClassClass, fields, template.getFieldValueMappingFunction())); } if (!pageData.isHasNextPage()) { break; } } while (i < 1000);
if (existEasyExcelProperty) { return Pair.of(template.getFileName(), writeFile(responseBodyClassClass, data)); } else { return Pair.of(template.getFileName(), writeFile(header, data)); } } private static Class getClass(String className) { Class tClass = CLASS_MAP.get(className); if (tClass != null) { return tClass; } tClass = getClassImpl(className); CLASS_MAP.put(className, tClass); return tClass; } private static Class getClassImpl(String className) { Class<?> paramClass = null; try { paramClass = Class.forName(className); return paramClass; } catch (ClassNotFoundException e) { throw new RuntimeException("配置的ParamClassName无法识别"); } } private boolean hasEasyExcelProperty(Class responseBodyClassClass) { Map<Field, ExcelProperty> allField = AnnotationExtentUtil.getAllField(responseBodyClassClass, ExcelProperty.class); return allField.size() > 0; } private static byte[] writeFile(Class responseBodyClassClass, List<Object> data) { try (ByteArrayOutputStream os = new ByteArrayOutputStream()) { ExcelWriter excelWriter = EasyExcelFactory.write().head(responseBodyClassClass).file(os).build(); WriteSheet writeSheet = EasyExcelFactory.writerSheet(0).build(); excelWriter.write(data, writeSheet); excelWriter.finish(); return os.toByteArray(); } catch (IOException e) { //e.printStackTrace(); log.error("生成 excel 异常!", e); throw new RuntimeException("生成 excel 异常!"); } }
private static byte[] writeFile(List<String> headerName, List<Object> data) { List<List<String>> header = headerName.stream() .map(p -> Collections.singletonList(p)) .collect(Collectors.toList()); try (ByteArrayOutputStream os = new ByteArrayOutputStream()) { ExcelWriter excelWriter = EasyExcelFactory.write().head(header).file(os).build(); WriteSheet writeSheet = EasyExcelFactory.writerSheet(0).build(); excelWriter.write(data, writeSheet); excelWriter.finish(); return os.toByteArray(); } catch (IOException e) { //e.printStackTrace(); log.error("生成 excel 异常!", e); throw new RuntimeException("生成 excel 异常!"); } } /** * 对RestResponse做特殊处理，只取其data */ public List<List<Object>> handleData(List<Object> data, Class responseBodyClass, List<String> fields, Map<String, Map<String, String>> functionMap) { List<List<Object>> result = new ArrayList<>(data.size()); for (Object obj : data) { List<Object> item = new ArrayList<>(fields.size()); for (String filed : fields) { Object fieldValue = ReflectionUtils.getFieldValue(obj, filed); Map<String, String> function = functionMap.get(filed); item.add(convertValue(fieldValue, function)); } result.add(item); } return result; } private static Object convertValue(Object v, Map<String, String> function) { if (v == null) { return null; } if (function == null) { if (v instanceof List) { return String.join(",", (List) v); } else { return v; } } if (v instanceof List) { return ((List<?>) v).stream() .map(p -> function.containsKey(p.toString()) ? function.get(p.toString()) : p.toString() ) .collect(Collectors.joining(",")); } else { return function.containsKey(v.toString()) ? function.get(v.toString()) : v; } }
/** * 对RestResponse做特殊处理，只取其data */ public PageInfo<Object> getRestResponseBody(Object result) { if (!(result instanceof RestResponse)) { throw new RuntimeException("返回类型必须是RestResponse结构"); } RestResponse<?> restResponse = (RestResponse<?>) result; if (!RestResponse.SUCC_CODE.equalsIgnoreCase(restResponse.getResultCode())) { log.error("ApiOperationLogAspect.handleRestResponse resultCode not successful :{}", JSONObject.toJSONString(restResponse)); return null; } Object data = restResponse.getData(); if (null == data) { log.error("ApiOperationLogAspect.handleRestResponse RestResponse data is empty"); } if (data instanceof PageInfo) { return (PageInfo) (data); } throw new RuntimeException("返回类型必须是RestResponse<PageInfo<*>>结构"); }
