首页 > 其他分享 >通用导出excel服务

通用导出excel服务

时间:2022-08-25 14:00:18浏览次数:68  
标签:通用 String 导出 excel List private new return data

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<*>>结构");
    }

 

标签:通用,String,导出,excel,List,private,new,return,data
From: https://www.cnblogs.com/zhshlimi/p/16624075.html

相关文章

  • NPOI导出Excel添加条件格式
    privatevoidSetRule(XSSFSheetsheet){varconditionFormatting=sheet.SheetConditionalFormatting;AddConditionalFormatting(cond......
  • vue使用rem (手机端PC端通用)
    只有PC端时main.js newVue({ router, store, render:h=>h(App),created(){//实例创建完成后被立即调用rem换算方法,解决内容闪现问题......
  • PE结构之导入表与导出表(5)
    导出表与导入表通常来讲exe文件只有导入表而没有导出表,而dll文件既有导入表也有导出表导出表什么是导出表代码重用机制提供了重用代码的动态链接库,它会向调用者说明......
  • 将excel表格转化为建表语句
    将设计方案中的excel表格格式的建表描述,直接转化为建表语句excel格式法律模块:XX_legal 列名 字段类型 注释 是否可为空legal_id bigint 主键 否parent_id bigint......
  • 【Android端】软件脱壳简单通用思路
    Android逆向之脱壳脱壳一般指去除加固包。已知脱壳有三种手段:Xposed:例反射大师VM:例blackdexFrida每个手段都有不同的用法。一般步骤去除签名验证(大部分加壳都......
  • Redis(6)------通用命令
    Key相关命令在redis中无论什么数据类型,在数据库中都是以Key-value形式保存,通过进行对Redis-Key的操作来完成对数据库中数据的操作。常用命令keys*:查看当前数据库中的所......
  • SpringBoot Excel导入导出
    一、引入pom.xml依赖<!--lombok--><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>1.18.24</version><......
  • Sqlserver导入、导出Excel
    1.将数据库查询出的数据以Excel保存到本地数据左上角,点击全选,右键选择另存为(Excel)2.打开Excel,编辑下数据格式(很重要)2.1.日期格式的数据处理,选中此列,右键→设置......
  • redis命令操作set&sortedset、redis命令操作通用命令
    redis命令操作set&sortedset集合类型:set不允许重复元素存储:saddkeyvalue获取:smemberskey:获取set元素中所有元素删除:sremkeyvalue:删除set集合中某......
  • excel 公式 函数
    公式手写函数文字要用双引号定义名称修改函数点击文字点击fx就会弹出对话框countifs查数datedify求两个日期间年数m月数d天数也可以用year函数代替i......