欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

荐 EasyPoi实现动态模板导入导出

程序员文章站 2022-04-15 23:41:23
导入jar包: cn.afterturn easypoi-spring-boot-starter 4.1.3 动态导入思路:通过先生成临时模...
导入jar包:
  <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-spring-boot-starter</artifactId>
            <version>4.1.3</version>
        </dependency>
动态导入思路:

通过先生成临时模板 再解析临时模板 最后删除临时模板 来实现动态导出功能

动态导出思路:

通过先生成动态实体类 再将Excel中数据以实体类解析 最终获取Excel 表中的数据

package ev;


import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import com.alibaba.fastjson.JSON;
import com.ev.common.vo.CustomizedColumnVO;
import com.ev.framework.config.MyExcelExportStyleConfig;
import com.ev.framework.utils.EasyPoiDynamicGenerationClassUtil;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import javassist.CannotCompileException;
import javassist.NotFoundException;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Workbook;

import java.io.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.UUID;

/**
 * 文件名称: ev.EasyPoiTest.java</br>
 * 初始作者: AirOrangeWorkSpace</br>
 * 创建日期: 2020/7/15 9:22</br>
 * 功能说明: 测试EasyPoi <br/>
 * =================================================<br/>
 * 修改记录:<br/>
 * 修改作者        日期       修改内容<br/>
 * ================================================<br/>
 * Copyright (c) 2020-2021 .All rights reserved.<br/>
 */
@Slf4j
public class EasyPoiTest {
    @Data
    static class ClientData implements Serializable {
        private static final long serialVersionUID = 1L;
        // 编号
        private String code;
        // 客户名
        private String clientName;
        // 联系电话
        private String phone;
    }

    public static void main(String[] args) throws IOException, CannotCompileException, NotFoundException {
        String customizedColumnVO = "[\n" +
                "    {\n" +
                "        \"name\":\"编号\",\n" +
                "        \"property\":\"code\"\n" +
                "    },\n" +
                "    {\n" +
                "        \"name\":\"客户名\",\n" +
                "        \"property\":\"clientName\"\n" +
                "    },\n" +
                "    {\n" +
                "        \"name\":\"联系电话\",\n" +
                "        \"property\":\"phone\"\n" +
                "    }\n" +
                "]";

        String data = "[\n" +
                "    {\n" +
                "        \"code\":\"123\",\n" +
                "        \"clientName\":\"阿里\",\n" +
                "        \"phone\":\"400\"\n" +
                "    },\n" +
                "    {\n" +
                "        \"code\":\"321\",\n" +
                "        \"clientName\":\"腾讯\",\n" +
                "        \"phone\":\"410\"\n" +
                "    },\n" +
                "    {\n" +
                "        \"code\":\"213\",\n" +
                "        \"clientName\":\"百度\",\n" +
                "        \"phone\":\"420\"\n" +
                "    }\n" +
                "]";
        // 解析动态表头 数据
        List<CustomizedColumnVO> customizedColumn = JSON.parseArray(customizedColumnVO, CustomizedColumnVO.class);
        // 获取业务数据
        List<ClientData> clientData = JSON.parseArray(data, ClientData.class);
        // 动态导出
        downLoadExcel(customizedColumn,clientData);
        // 动态导入(拿导出的文件进行导入)
        File file  = new File("D:/客户信息.xls");
        exportExcel(customizedColumn,file);
    }

    private static void downLoadExcel(List<CustomizedColumnVO> customizedColumn, List<ClientData> data) throws IOException {
        List<ExcelExportEntity> colList = new ArrayList<>();
        ExcelExportEntity colEntity;
        // 动态表的单行
        Map<String, Object> valMap = Maps.newHashMap();
        // 动态表的内容行
        List<Map<String, Object>> list = Lists.newArrayList();
        int size = customizedColumn.size();
        for (int i = 0; i < size; i++) {
            // 设置动态Excel表头
            CustomizedColumnVO customizedColumnClass = customizedColumn.get(i);
            String name = customizedColumnClass.getName();
            String property = customizedColumnClass.getProperty();
            colEntity = new ExcelExportEntity(name, property);
            colEntity.setNeedMerge(true);
            colList.add(colEntity);
            // 将EasyPoi指令放入第一行数据
            if (i == 0) {
                valMap.put(property, "{{$fe:list t." + property);
                continue;
            }
            if (i == size - 1) {
                valMap.put(property, "t." + property +"}}");
                continue;
            }
            valMap.put(property, "t." + property);
        }

        list.add(valMap);
        ExportParams exportParams = new ExportParams();
        exportParams.setSheetName("数据");
        exportParams.setStyle(MyExcelExportStyleConfig.class);
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, colList,
                list);
        // 设置临时地址
        String path = "D:/";
        // 创建临时模板
        String fileName = path + UUID.randomUUID().toString() + ".xls";
        log.info("临时文件地址: " + fileName);
        FileOutputStream fos = new FileOutputStream(fileName);
        workbook.write(fos);
        fos.close();

        File deleteFile = new File(fileName);
        TemplateExportParams params = new TemplateExportParams(fileName);
        Map<String, Object> map = Maps.newHashMap();
        map.put("list", data);
        Workbook book = ExcelExportUtil.exportExcel(params, map);

        // 创建导出文件
        FileOutputStream fo = new FileOutputStream("D:/客户信息.xls");
        book.write(fo);
        fo.close();

        // 删除临时文件
        if (deleteFile.exists()) {
            log.info(deleteFile.delete() ? "临时文件已删除" : "临时文件删除失败");
        } else {
            log.error("无临时文件");
        }

    }

    private static void exportExcel(List<CustomizedColumnVO> customizedColumn, File file) throws IOException, CannotCompileException, NotFoundException {
        if (!file.exists()) {
            log.error("文件不存在");
            return;
        }

        ImportParams params = new ImportParams();
        params.setTitleRows(0);
        params.setHeadRows(1);

        // 检验表的是否合法
        String[] importFields = customizedColumn
                .stream()
                .map(CustomizedColumnVO::getName).toArray(String[]::new);
        params.setImportFields(importFields);

        // 生成动态的实体类(核心代码)
        Class<?> salaryArchivesClass = EasyPoiDynamicGenerationClassUtil.generatePrototypeClass(customizedColumn);

        List<?> clientDataList;
        try {
            FileInputStream fileInputStream = new FileInputStream(file);
            clientDataList = ExcelImportUtil.importExcel(fileInputStream, salaryArchivesClass, params);
            log.info(JSON.toJSONString(clientDataList));
        } catch (Exception e) {
            log.error("解析失败");
        }
        // TODO 根据数据处理逻辑

    }

}

动态字段实体类:
package com.ev.common.vo;

import lombok.Data;

/**
 * 文件名称: com.ev.test.vo.CustomizedAliasJsonObject.java</br>
 * 初始作者: AirOrangeWorkSpace</br>
 * 创建日期: 2020/5/15 14:54</br>
 * 功能说明: 动态实体类 <br/>
 * =================================================<br/>
 * 修改记录:<br/>
 * 修改作者        日期       修改内容<br/>
 * ================================================<br/>
 * Copyright (c) 2020-2021 .All rights reserved.<br/>
 */
@Data
public class CustomizedColumnVO {
    // 列名
    private String property;
    // 属性名
    private String name;

}
动态生成带注解的数据实体类:
package com.ev.framework.utils;

import com.ev.common.vo.CustomizedColumnVO;
import javassist.*;
import javassist.bytecode.AnnotationsAttribute;
import javassist.bytecode.ClassFile;
import javassist.bytecode.ConstPool;
import javassist.bytecode.annotation.Annotation;
import javassist.bytecode.annotation.StringMemberValue;

import java.io.IOException;
import java.util.List;
import java.util.UUID;

/**
 * 类描述: [动态生成EasyPoi解析实体类]</br>
 * 初始作者:  AirOrangeWorkSpace </br>
 * 创建日期: 2020/5/15 16:28<br/>
 * 开始版本: 1.0.0<br/>
 * =================================================<br/>
 * 修改记录:<br/>
 * 修改作者 日期 修改内容<br/>
 * ================================================<br/>
*/
public class EasyPoiDynamicGenerationClassUtil {
    public static final String CLASS_NAME_PREFIX = "com.ev.common.vo.EasyPoiExcelVO@";
    public static final String ANNOTATION_PACKAGE_NAME = "cn.afterturn.easypoi.excel.annotation.Excel";
    public static final String STRING_PACKAGE_NAME = "java.lang.String";
    /**
     * 方法描述: [获取动态生成EasyPoi实体VO]</br>
     * 初始作者:  AirOrangeWorkSpace </br>
     * 创建日期: 2020/5/15 16:38<br/>
     * 开始版本: 1.0.0<br/>
     * =================================================<br/>
     * 修改记录:<br/>
     * 修改作者 日期 修改内容<br/>
     * ================================================<br/>
     * @param  list:获取用户自定义表头
     * @Return : 动态生成的EasyPoi实体类
    */
    public static Class<?> generatePrototypeClass(List<CustomizedColumnVO> list) throws NotFoundException, CannotCompileException, IOException {

        String className = CLASS_NAME_PREFIX + UUID.randomUUID().toString();

        ClassPool pool = ClassPool.getDefault();
        CtClass clazz = pool.makeClass(className);
        ClassFile ccFile = clazz.getClassFile();
        ConstPool constpool = ccFile.getConstPool();
        //添加fields
        addExpressField(pool, clazz, constpool,list);

        return clazz.toClass();
    }

    private static void addExpressField(ClassPool pool, CtClass clazz, ConstPool constpool,List<CustomizedColumnVO> list) throws CannotCompileException, NotFoundException {
        // 将数据库查出动态附上property 属性
        for (CustomizedColumnVO customizedColumnVO : list) {
            addFieldAndAnnotation(pool, clazz, constpool, customizedColumnVO.getName(), customizedColumnVO.getProperty());
        }
    }


    private static void addFieldAndAnnotation(ClassPool pool, CtClass clazz, ConstPool constpool, String titleName, String fieldName) throws NotFoundException, CannotCompileException {

        //生成field
        CtField field = new CtField(pool.get(STRING_PACKAGE_NAME), fieldName, clazz);
        field.setModifiers(Modifier.PUBLIC);

        //添加easypoi的注解
        AnnotationsAttribute fieldAttr = new AnnotationsAttribute(constpool, AnnotationsAttribute.visibleTag);
        Annotation annotation = new Annotation(ANNOTATION_PACKAGE_NAME, constpool);
        annotation.addMemberValue("name", new StringMemberValue(titleName, constpool));
        fieldAttr.addAnnotation(annotation);
        field.getFieldInfo().addAttribute(fieldAttr);

        //生成get,set方法
        clazz.addMethod(CtNewMethod.getter("get" + upperFirstLatter(fieldName), field));
        clazz.addMethod(CtNewMethod.setter("set" + upperFirstLatter(fieldName), field));

        clazz.addField(field);
    }

    private static String upperFirstLatter(String letter) {
        return letter.substring(0, 1).toUpperCase() + letter.substring(1);
    }


}

实现效果:

EXCEL:
荐
                                                        EasyPoi实现动态模板导入导出

控制台:
荐
                                                        EasyPoi实现动态模板导入导出

本文地址:https://blog.csdn.net/AirOrange_qi/article/details/107355529

相关标签: poi java