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

导入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:

控制台:

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