EasyPoi导出导入多sheet页+动态列+单元格下拉选项

EasyPoi版本要求

因为需要使用动态的下拉选项 easypoi 版本我使用的是4.3

项目需求说明

动态表单功能，表单中有个TAB页面，每个TAB页面都是一个表格 ，表格的列是动态配置生成的，项目要求需要导出这些表格并且支持导入多sheet页Excel；并且导出的Excel做修改后重新导入读取数据。

多Sheet页动态列导出

 /**
     * 导出Excel
     *
     * @param response
     * @param templateCode 模板编码
     */
    @GetMapping("/exportExcel")
    public void exportExcel(HttpServletResponse response, String templateCode, String planName, String planCode, String functionType) throws Exception {
   		//查询有多少个维度tab，每个维度下有当前维度表格的动态列 
        List<TemplateDimensionDto> templateDimensions = getTemplateDimensionDtos(templateCode, planCode);
        //查询维度下每个表格的数据
        Map<String, List<DynamicSearchAdminData>> adminDataMap = getAdminData(templateCode, planCode);
		//官方demo中只提供了一个sheet页的导出，这个集合用于存储每个sheet页的标题、数据、动态列数据
        List<Map<String, Object>> sheetsList = new ArrayList<>();
        for (TemplateDimensionDto t : templateDimensions) {
            List<Map<String, String>> data = getDimensionRowData(adminDataMap, t.getDimensionCode());
            String dimensionName = t.getDimensionName();
            List<ExcelExportEntity> dynamicExcelExportEntitys = getDynamicExcelExportEntity(t.getDynamicFields());
            HashMap<String, Object> map = new HashMap<>();
            ExportParams params = new ExportParams();
            params.setSheetName(dimensionName);
            map.put("title", params);
            map.put("entityList", dynamicExcelExportEntitys);
            map.put("data", data);
            sheetsList.add(map);
        }

        ExcelUtil.defaultMultiExport(sheetsList, planName + ".xls", response);
    }

处理每个sheet页的动态列，easypoi中ExcelExportEntity 就是@Excel注解的Java实体类的变体

    /**
     * 生成sheet页面 动态的列表
     *
     * @param dynamicFields 每个维度的动态列
     * @return
     */
    private List<ExcelExportEntity> getDynamicExcelExportEntity(List<TemplateDimensionDto.DynamicField> dynamicFields) {
        return dynamicFields.stream().map(t -> {
            String jsonData = t.getJsonData();
            //参数1 Excel中的列名称，参数2 字段名
            ExcelExportEntity entity = new ExcelExportEntity(t.getFieldName(), t.getFieldCode());
            //设置Excel列的宽度
            entity.setWidth(15);
            if (StringUtils.isNotBlank(jsonData)) {
            	//设置TRUE 单元才能有下拉选择并且要设置下拉的数据源 可以通过setReplace也可以通过easypoi的字典形式
                entity.setAddressList(true);
                JSONArray jsonArray = JSON.parseArray(jsonData);
                String[] arr = new String[jsonArray.size()];
                for (int i = 0; i < jsonArray.size(); i++) {
                    JSONObject jsonObject = jsonArray.getJSONObject(i);
                    String selectValue = jsonObject.getString("selectValue");
                    arr[i] = selectValue + "_" + selectValue;
                }
                //数据源格式参考@Excel中replace [name_1]
                entity.setReplace(arr);
            }
            return entity;
        }).collect(Collectors.toList());
    }

    public static void defaultMultiExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws Exception {
        Workbook workbook = exportMultiSheetExcel(list);
        if (workbook != null) {
            downLoadExcel(fileName, response, workbook);
        }
    }

    /**
     * 动态列 多个sheet页导出
     * param list
     * @return
     */
    private static Workbook exportMultiSheetExcel(List<Map<String, Object>> list) {
        Workbook workbook = new HSSFWorkbook();
        for (Map<String, Object> map : list) {
            MyExcelExportService service = new MyExcelExportService();
            service.createSheetWithList(workbook, (ExportParams) map.get("title"), ExportParams.class,
                    (List<ExcelExportEntity>) map.get("entityList"), (Collection<?>) map.get("data"));
        }
        return workbook;
    }

在EasyPOI -ExcelExportUtil类只提供了如下方法，我们需要更加这个方法创建一个自己的Workbook服务实现。

 /**
     * 根据Map创建对应的Excel(一个excel 创建多个sheet)
     *
     * @param list 多个Map key title 对应表格Title key entity 对应表格对应实体 key data
     *             Collection 数据
     * @return
     */
    public static Workbook exportExcel(List<Map<String, Object>> list, ExcelType type) {
        Workbook workbook = getWorkbook(type, 0);
        for (Map<String, Object> map : list) {
            ExcelExportService service = new ExcelExportService();
            ExportParams params = (ExportParams) map.get("title");
            params.setType(type);
            service.createSheet(workbook,params,
                    (Class<?>) map.get("entity"), (Collection<?>) map.get("data"));
        }
        return workbook;
    }

扩展可以支持导出多个sheet页的ExcelExportService

public class MyExcelExportService extends ExcelExportService {

    public void createSheetWithList(Workbook workbook, ExportParams entity, Class<?> pojoClass, List<ExcelExportEntity> entityList, Collection<?> dataSet) {
        if (LOGGER.isDebugEnabled()) {
            LOGGER.debug("Excel export start ,class is {}", pojoClass);
            LOGGER.debug("Excel version is {}",
                    entity.getType().equals(ExcelType.HSSF) ? "03" : "07");
        }
        if (workbook == null || entity == null || pojoClass == null || dataSet == null) {
            throw new ExcelExportException(ExcelExportEnum.PARAMETER_ERROR);
        }
        try {
            List<ExcelExportEntity> excelParams = entityList;
            // 得到所有字段
            Field[] fileds = PoiPublicUtil.getClassFields(pojoClass);
            ExcelTarget etarget = pojoClass.getAnnotation(ExcelTarget.class);
            String targetId = etarget == null ? null : etarget.value();
            getAllExcelField(entity.getExclusions(), targetId, fileds, excelParams, pojoClass, null, null);
            //获取所有参数后,后面的逻辑判断就一致了
            createSheetForMap(workbook, entity, excelParams, dataSet);
        } catch (Exception e) {
            LOGGER.error(e.getMessage(), e);
            throw new ExcelExportException(ExcelExportEnum.EXPORT_ERROR, e.getCause());
        }
    }
}

到目前为止导出所需要的workbook已经可以构建生成了下面就是通用的导出功能了

    /**
     * 下载
     *
     * @param fileName 文件名称
     * @param response
     * @param workbook excel数据
     */
    private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws Exception {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            throw new Exception(e.getMessage());
        }
    }

EasyPoi多sheet页动态列导入

easypoi提供的动态列导入如下，不太复合我的业务需求。我需要根据导入的动态列Excel自动映射成一个对象

 @Test
    public void mapTest() {

        ImportParams params = new ImportParams();
        params.setTitleRows(1);
        params.setHeadRows(1);
        params.setDataHandler(new MapImportHandler());
        long start = new Date().getTime();
        List<Map<String, Object>> list = ExcelImportUtil.importExcel(
            new File(FileUtilTest.getWebRootPath("import/check.xls")), Map.class, params);
        for (int i = 0; i < list.size(); i++) {
            System.out.println(list.get(i));
        }
        System.out.println(new Date().getTime() - start);
        Assert.assertEquals(20,list.size());


    }

public class MapImportHandler extends ExcelDataHandlerDefaultImpl<Map<String, Object>> {

    @Override
    public void setMapValue(Map<String, Object> map, String originKey, Object value) {
        if (value instanceof Double) {
            map.put(getRealKey(originKey), PoiPublicUtil.doubleToString((Double) value));
        } else {
            map.put(getRealKey(originKey), value != null ? value.toString() : null);
        }
    }

    private String getRealKey(String originKey) {
        if (originKey.equals("交易账户")) {
            return "accountNo";
        }
        if (originKey.equals("姓名")) {
            return "name";
        }
        if (originKey.equals("客户类型")) {
            return "type";
        }
        return originKey;
    }
}

我做作如下处理

    /**
     * 导入
     *
     * @param file
     * @param planCode
     * @param templateCode
     * @param functionType
     * @return
     * @throws Exception
     */
    @RequestMapping("/importRecord")
    @ResponseBody
    public Map<String, List<Map<String, String>>> importRecord(@RequestPart("file") MultipartFile file,
                                                               @RequestParam("planCode") String planCode,
                                                               String templateCode,
                                                               String functionType) throws Exception {
        //业务代码 查询计划 模板下的维度以及维度下的动态列                                                   
        List<TemplateDimensionDto> templateDimensionDtos = getTemplateDimensionDtos(templateCode, planCode);
        Map<String, List<Map<String, String>>> dimensionDataMap = new HashMap<>();
        for (int i = 0; i < templateDimensionDtos.size(); i++) {
            TemplateDimensionDto templateDimensionDto = templateDimensionDtos.get(i);
            //导入参数
            ImportParams params = new ImportParams();
            //读取的sheet开始角标 默认0 
            params.setStartSheetIndex(i);
//            params.setSheetNum(1 + i);
			//处理每个sheet页中的动态列 
            List<DynamicColumnVo> collect = templateDimensionDto.getDynamicFields().stream().map(t -> {
                String fieldCode = t.getFieldCode();
                String fieldName = t.getFieldName();
                DynamicColumnVo dynamicColumnVo = new DynamicColumnVo();
                dynamicColumnVo.setField(fieldCode);
                dynamicColumnVo.setTitle(fieldName);
                return dynamicColumnVo;
            }).collect(Collectors.toList());
			//通过EasyPoiDynamicGenerationClassUtil.generatePrototypeClass(collect) 构建对象
            List<?> result = ExcelImportUtil.importExcel(file.getInputStream(),
                    EasyPoiDynamicGenerationClassUtil.generatePrototypeClass(collect), params);
            List<Map<String, String>> parseResult = EasyPoiDynamicGenerationClassUtil.parseObjectList(result);
            dimensionDataMap.put(templateDimensionDto.getDimensionCode(), parseResult);
        }
        log.info(JSON.toJSONString(dimensionDataMap));

        ShiroUser shiroUser = (ShiroUser) SecurityUtils.getSubject().getPrincipal();
        if (ObjectUtil.isNotNull(shiroUser.getSupplierId())) {
            dimensionDataMap = iDynamicSearchService.checkDimensionDataMap(planCode, templateCode, templateDimensionDtos, dimensionDataMap);
        }
        return dimensionDataMap;
    }
}

动态列的MAP映射对象核心代码

package com.jtech.toa.utils;

import com.beust.jcommander.internal.Lists;
import com.google.common.collect.Maps;
import com.jtech.toa.priceModel.analogy.vo.DynamicColumnVo;
import com.xiaoleilu.hutool.util.CollectionUtil;
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.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.List;
import java.util.Map;
import java.util.UUID;

/**
 * 动态生成excel导入模板类
 */
public class EasyPoiDynamicGenerationClassUtil {
    public static final String CLASS_NAME_PREFIX = "com.jtech.toa.utils.EasyPoiExcelVO@";
    public static final String ANNOTATION_PACKAGE_NAME = "cn.afterturn.easypoi.excel.annotation.Excel";
    public static final String STRING_PACKAGE_NAME = "java.lang.String";

    public static Class<?> generatePrototypeClass(List<DynamicColumnVo> 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<DynamicColumnVo> list) throws CannotCompileException, NotFoundException {
        for (DynamicColumnVo dynamicColumnDto : list) {
            addFieldAndAnnotation(pool, clazz, constpool, dynamicColumnDto.getTitle(), dynamicColumnDto.getField());
        }
    }


    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);
    }

    private static String getFieldValue(String fieldName, Object data) throws Exception{
        Method m =  (Method) data.getClass().getMethod(
                "get" + EasyPoiDynamicGenerationClassUtil.upperFirstLatter(fieldName));
        return (String)m.invoke(data);
    }

    public static List<Map<String, String>> parseObjectList(List<?> result) throws Exception {
        List<Map<String, String>> parseResult = Lists.newArrayList();
        if (CollectionUtil.isNotEmpty(result)) {
            Class<?> clazz = result.get(0).getClass();
            Field[] fields = clazz.getDeclaredFields();
            for(Object data:result){
                Map<String, String> parseDataMap = Maps.newConcurrentMap();
                for (Field field : fields) {
                    String value = getFieldValue(field.getName(), data);
                    parseDataMap.put(field.getName(), value == null ? "" : value);
                }
                parseResult.add(parseDataMap);
            }
        }
        return parseResult;
    }
}