C/C++教程

通用导出excel服务

本文主要是介绍通用导出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<*>>结构");
    }

 

这篇关于通用导出excel服务的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!