导入导出带下拉框模版（EasyExcel）

前言

项目进行到新的一个迭代了，赶了1周需求，接口终于处理完了。分享记录下迭代中处理导入、导出、下载模版功能的细节吧。

一、场景

EasyExcel（阿里）实现Excel数据处理
三层表头，第二、三层表头动态
数据根据第二、三层表头动态
导出的模版动态数据区域，下拉选择
导入4的模版，导入业务数据
模版效果：

懂的都懂，没有其他啥好说的，直接上代码吧。

二、使用步骤

1.导出

controller导出方法

/**
     * 导出排班计划
     */
    @ResourceAction(id = "exportShiftPlan", name = "导出排班计划")
    @GetMapping("/exportShiftPlan")
    public void exportShiftPlan(PotAttendShiftPlanQueryRequestVo requestVo, HttpServletResponse response) {
        try {
            // 设置响应头
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String fileName = URLEncoder.encode(DateUtil.format(DateUtil.date(), DatePattern.PURE_DATETIME_PATTERN) + "排班计划导出" + ".xlsx", StandardCharsets.UTF_8).replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName);

            //组织导出数据
            Map<String, Object> exportDataMap = service.exportShiftPlan(requestVo);
            List<List<String>> headList = (List<List<String>>) exportDataMap.get("headList");
            List<List<Object>> dataList = (List<List<Object>>) exportDataMap.get("dataList");

            ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(response.getOutputStream());
            excelWriterBuilder.head(headList).sheet("排班计划").doWrite(dataList);

        } catch (Exception e) {
            throw new RuntimeException("导出排班计划失败");
        }

    }

exportShiftPlan方法实现

@Override
    public Map<String, Object> exportShiftPlan(PotAttendShiftPlanQueryRequestVo requestVo) {
        Map<String, Object> dataMap = new HashMap<>();

        //处理动态列头
        List<List<String>> headList = new ArrayList<>();

        //前3列姓名、部门、员工账号
        List<String> xmHeard = new ArrayList<>();
        xmHeard.add("姓名");
        headList.add(xmHeard);

        List<String> bmHeard = new ArrayList<>();
        bmHeard.add("部门");
        headList.add(bmHeard);

        List<String> yghHeard = new ArrayList<>();
        yghHeard.add("员工账号");
        headList.add(yghHeard);

        //时间处理
        Date startTime = requestVo.getStartTime();
        Date endTime = requestVo.getEndTime();
        List<DateTime> rangeDateList = DateUtil.rangeToList(startTime, endTime, DateField.DAY_OF_MONTH);
        for (DateTime dateTime : rangeDateList) {
            List<String> dateHead = new ArrayList<>();
            //日期
            dateHead.add(DateUtil.format(dateTime, DatePattern.NORM_DATE_PATTERN));
            //星期
            int dayOfWeek = DateUtil.dayOfWeek(dateTime);
            String weekName = AttendConstant.weekDict.get(String.valueOf(dayOfWeek)).toString();
            dateHead.add(weekName);

            headList.add(dateHead);
        }
		//列头
        dataMap.put("headList", headList);

       //do your code，循环处理组织一行数据放到list，再放到map
       //动态列部分数据根据列头时间循环处理 
       dataMap.put("dataList", dataList);
       

        return dataMap;
    }

2.下载模版

controller方法

/**
     * 下载排班计划导入模版
     *
     * @param response 响应
     */
    @ResourceAction(id = "exportShiftPlanTemplate", name = "导出排班计划模版")
    @GetMapping("/exportShiftPlanTemplate")
    public void exportShiftPlanTemplate(PotAttendShiftPlanQueryRequestVo requestVo, HttpServletResponse response) {
        try {
            // 设置响应头
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");

            String fileName = BusinessSerialNoUtil.genDateFmtSeqCode(redisService, "", "", 4, CommonConstant.SHIFT_EXPORT_TEMPLATE, "0", CommonConstant.COMPLET_BEFORE, DatePattern.PURE_DATE_PATTERN) + "排班计划模版.xlsx";

            //防止乱码
            fileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8);

            response.setHeader("Content-disposition", "attachment;filename=" + fileName);

            //组织导出数据
            Map<String, Object> exportDataMap = service.exportShiftPlanTemplate(requestVo);
            List<List<String>> headList = (List<List<String>>) exportDataMap.get("headList");
            List<List<Object>> dataList = (List<List<Object>>) exportDataMap.get("dataList");
            List<PotAttendShiftDTO> shiftList = (List<PotAttendShiftDTO>) exportDataMap.get("shiftList");

            ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(response.getOutputStream());

            //动态设置下拉框
            excelWriterBuilder.registerWriteHandler(new CustomCellWriteHeightStrategy((short) 30, (short) 30));
            excelWriterBuilder.registerWriteHandler(new ShiftSheetWriteHandler(shiftList, headList.size(), dataList.size()));

            ExcelWriterSheetBuilder excelWriterSheetBuilder = excelWriterBuilder.head(headList).sheet("排班计划");

            excelWriterSheetBuilder.doWrite(dataList);

        } catch (Exception e) {
            String errMsg = e.getMessage();
            if (StringUtils.isBlank(errMsg)) {
                errMsg = "导出导入模版失败";
            }
            throw new RuntimeException(errMsg);
        }

    }

实际上就是在导出的基础上增加了一个样式核心拦截、一个数据处理的handler
CustomCellWriteHeightStrategy
自定义单元格高度策略


/**
 * 自定义单元格高度策略
 * @author zwmac
 */
public class CustomCellWriteHeightStrategy extends AbstractRowHeightStyleStrategy {
    /**
     * 默认高度
     */
    private static final Integer DEFAULT_HEIGHT = 30;
    /**
     * 头部行高
     */
    private Short headRowHeight = DEFAULT_HEIGHT.shortValue();
    /**
     * 内容行高
     */
    private Short contentRowHeight = DEFAULT_HEIGHT.shortValue();

    public CustomCellWriteHeightStrategy(Short headRowHeight, Short contentRowHeight) {
        this.headRowHeight = headRowHeight;
        this.contentRowHeight = contentRowHeight;
    }

    @Override
    protected void setHeadColumnHeight(Row row, int relativeRowIndex) {
        int maxHeight = autoCalMaxHeight(row);

        //row.setHeight((short) (maxHeight * DEFAULT_HEIGHT));
        row.setHeightInPoints(maxHeight * DEFAULT_HEIGHT);
    }

    @Override
    protected void setContentColumnHeight(Row row, int relativeRowIndex) {
        int maxHeight = autoCalMaxHeight(row);

        //row.setHeight((short) (maxHeight * DEFAULT_HEIGHT));
        row.setHeightInPoints(maxHeight * DEFAULT_HEIGHT);
    }

    private int autoCalMaxHeight(Row row) {
        Iterator<Cell> cellIterator = row.cellIterator();
        if (!cellIterator.hasNext()) {
            return 1;
        }

        // 默认为 1行高度
        int maxHeight = 1;
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            switch (cell.getCellType()) {
                case STRING:
                    if (cell.getStringCellValue().contains("\n")) {
                        int length = cell.getStringCellValue().split("\n").length;
                        maxHeight = Math.max(maxHeight, length);
                    }
                    break;
                default:
                    break;
            }
        }
        return maxHeight;
    }
}

ShiftSheetWriteHandler
下拉框处理，这里用的是隐藏域方式，之前其实分享过另一种处理方式，也提到了这种方式，下拉框处理


/**
 * 排班sheetHandler
 *
 * @author zwmac
 */
public class ShiftSheetWriteHandler implements SheetWriteHandler {

    /**
     * 一行的列数
     */
    private Integer rowCelNum = 0;
    /**
     * 行数
     */
    private Integer rowNum = 0;
    /**
     * 班次数据
     */
    private List<PotAttendShiftDTO> shiftList;

/*
    public ShiftSheetWriteHandler(List<PotAttendShiftDTO> shiftList, int rowCelNum) {
        this.shiftList = shiftList;
        this.rowCelNum = rowCelNum;
    }

    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        //班次转map
        Map<String, String[]> mapDropDown = new HashMap<>();
        String[] shiftTimeArr = new String[shiftList.size()];
        for (int i = 0; i < shiftList.size(); i++) {
            PotAttendShiftDTO shift = shiftList.get(i);
            String shiftName = shift.getShiftName();
            List<PotAttendShiftTime> shiftTimeList = shift.getAttendShiftTimeList();
            StringBuffer sbf = new StringBuffer();
            if (CollectionUtil.isNotEmpty(shiftTimeList)) {
                for (int j = 0; j < shiftTimeList.size(); j++) {
                    PotAttendShiftTime shiftTime = shiftTimeList.get(j);
                    String time = DateUtil.format(shiftTime.getStartTime(), "HH:mm") + "-" + DateUtil.format(shiftTime.getEndTime(),"HH:mm");
                    sbf.append(time);
                    if (j != shiftTimeList.size() - 1) {
                        sbf.append("跳");
                    }
                }
            }
            //长度估计是长了，报异常
            //shiftTimeArr[i] = shiftName + " " + sbf;
            shiftTimeArr[i] = shiftName;

        }
        mapDropDown.put("1", shiftTimeArr);

        //获取工作簿
        Sheet sheet = writeSheetHolder.getSheet();
        ///开始设置下拉框
        DataValidationHelper helper = sheet.getDataValidationHelper();

        //设置下拉框
        for (Map.Entry<String, String[]> entry : mapDropDown.entrySet()) {
            *//*起始行、终止行、起始列、终止列  起始行为1即表示表头不设置**//*
            //这里设置65535可能又问题，因为这个是excel的最大行数，如果数据量超过这个数，就会报错
            CellRangeAddressList addressList = new CellRangeAddressList(2, 65535, 3, rowCelNum - 1);
            *//*设置下拉框数据**//*
            DataValidationConstraint constraint = helper.createExplicitListConstraint(entry.getValue());
            DataValidation dataValidation = helper.createValidation(constraint, addressList);

            //阻止输入非下拉选项的值
            dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
            dataValidation.setShowErrorBox(true);
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.createErrorBox("提示", "输入值与单元格定义格式不一致");
            dataValidation.createPromptBox("填写说明", "填写内容只能为下拉数据集中的类型");

            sheet.addValidationData(dataValidation);
        }
    }*/


    private Map<Integer, List<String>> selectMap;

    private int index;

    private char[] alphabet = new char[]{'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L',
            'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'};

    public ShiftSheetWriteHandler(List<PotAttendShiftDTO> shiftList, int rowCelNum,Integer rowNum) {
        //转map
        Map<Integer, List<String>> shiftMp = transShiftListToMap(shiftList);
        this.selectMap = shiftMp;
        this.index = 0;
        this.rowCelNum = rowCelNum;
        this.rowNum = rowNum;
    }

    private Map<Integer, List<String>> transShiftListToMap(List<PotAttendShiftDTO> shiftList) {
        Map<Integer, List<String>> mapDropDown = new HashMap<>();
        List<String> shiftTimeArr = new ArrayList<>();
        for (int i = 0; i < shiftList.size(); i++) {
            PotAttendShiftDTO shift = shiftList.get(i);
            String shiftName = shift.getShiftName();
            List<PotAttendShiftTime> shiftTimeList = shift.getAttendShiftTimeList();
            StringBuffer sbf = new StringBuffer();
            if (CollectionUtil.isNotEmpty(shiftTimeList)) {
                for (int j = 0; j < shiftTimeList.size(); j++) {
                    PotAttendShiftTime shiftTime = shiftTimeList.get(j);
                    String time = DateUtil.format(shiftTime.getStartTime(), "HH:mm") + "-" + DateUtil.format(shiftTime.getEndTime(), "HH:mm");
                    sbf.append(time);
                    if (j != shiftTimeList.size() - 1) {
                        sbf.append("跳");
                    }
                }
            }
            String fullName = DictTranslateor.translate(AttendConstant.SHIFT_NAME_DICT, shiftName);
            shiftTimeArr.add(fullName + " " + sbf);

        }
        mapDropDown.put(1, shiftTimeArr);
        return mapDropDown;
    }


    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        if (selectMap == null || selectMap.size() == 0) {
            return;
        }
        // 需要设置下拉框的sheet页
        Sheet curSheet = writeSheetHolder.getSheet();
        DataValidationHelper helper = curSheet.getDataValidationHelper();
        String dictSheetName = "字典sheet";

        Workbook workbook = writeWorkbookHolder.getWorkbook();

        // 数据字典的sheet页
        Sheet dictSheet = workbook.createSheet(dictSheetName);
        // 从第二个工作簿开始隐藏，为了用户的友好性，将字典sheet隐藏掉
        this.index++;
        // 设置隐藏
        workbook.setSheetHidden(this.index, true);
        for (Map.Entry<Integer, List<String>> entry : selectMap.entrySet()) {
            // 设置下拉单元格的首行、末行、首列、末列
            CellRangeAddressList rangeAddressList = new CellRangeAddressList(3, 3 + rowNum - 1, 3, rowCelNum - 1);
            int rowLen = entry.getValue().size();
            // 设置字典sheet页的值 每一列一个字典项
            for (int i = 0; i < rowLen; i++) {
                Row row = dictSheet.getRow(i);
                if (row == null) {
                    row = dictSheet.createRow(i);
                }
                row.createCell(entry.getKey()).setCellValue(entry.getValue().get(i));
            }
            String excelColumn = getExcelColumn(entry.getKey());
            // 下拉框数据来源 eg:字典sheet!$B1:$B2
            String refers = dictSheetName + "!$" + excelColumn + "$1:$" + excelColumn + "$" + rowLen;
            // 创建可被其他单元格引用的名称
            Name name = workbook.createName();
            // 设置名称的名字
            name.setNameName("dict" + entry.getKey());
            // 设置公式
            name.setRefersToFormula(refers);
            // 设置引用约束
            DataValidationConstraint constraint = helper.createFormulaListConstraint("dict" + entry.getKey());
            // 设置约束
            DataValidation validation = helper.createValidation(constraint, rangeAddressList);
            if (validation instanceof HSSFDataValidation) {
                validation.setSuppressDropDownArrow(false);
            } else {
                validation.setSuppressDropDownArrow(true);
                validation.setShowErrorBox(true);
            }
            // 阻止输入非下拉框的值
            validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
            validation.createErrorBox("提示", "此值与单元格定义格式不一致！");
            validation.setShowErrorBox(true);
            validation.setSuppressDropDownArrow(true);
            validation.createPromptBox("填写说明", "填写内容只能为下拉数据集中的类型");
            // 添加下拉框约束
            writeSheetHolder.getSheet().addValidationData(validation);
        }


    }

    /**
     * 将数字列转化成为字母列
     *
     * @param num
     * @return
     */
    private String getExcelColumn(int num) {
        String column = "";
        int len = alphabet.length - 1;
        int first = num / len;
        int second = num % len;
        if (num <= len) {
            column = alphabet[num] + "";
        } else {
            column = alphabet[first - 1] + "";
            if (second == 0) {
                column = column + alphabet[len] + "";
            } else {
                column = column + alphabet[second - 1] + "";
            }
        }
        return column;

    }
}

service实现部分就不贴了，跟导出的思路一致，多了一个下拉框数据shiftList而已。

3、导入

controller方法

/**
     * 导入排班计划2
     *
     * @param file 文件
     */
    @OperateLog(moduleName = moduleName, operateContent = "导入排班计划")
    @RequestMapping(value = "/importShiftPlan/{parkId}", method = RequestMethod.POST)
    public RestResponse importShiftPlan2(@RequestParam("file") MultipartFile file, @PathVariable("parkId") Long parkId) {
        try {
            service.importShiftPlan(file, parkId);
            return RestResponse.success();
        } catch (Exception e) {
            log.error("导入排班计划失败", e);
            throw new RuntimeException("导入排班计划失败");
        }
    }

importShiftPlan方法实现


    @Override
    public void importShiftPlan(MultipartFile file, Long parkId) throws IOException {
        //参数校验
        Assert.notNull(file, "文件为空");
        Assert.notNull(parkId, "园区id为空");

        //解析文件
        //EasyExcel.read(file.getInputStream(), AttendShiftPlanExcelDto.class, new AttendShiftPlanListener()).sheet(0).doRead();
        //List<Map<Integer,Object>> list = EasyExcel.read(file.getInputStream(), AttendShiftPlanExcelDto.class, new AttendShiftPlanListener()).sheet(0).doReadSync();
        //headRowNumber设置表示设置的行与之前的行都是列头，监听里的数据处理就从这个行之后开始读
        AttendShiftPlanListener attendShiftPlanListener = new AttendShiftPlanListener();
        List<Map<Integer, Object>> list = EasyExcel.read(file.getInputStream(), attendShiftPlanListener).sheet(0).headRowNumber(3).doReadSync();
        List<String> headList = attendShiftPlanListener.getHeaders();
        Assert.notEmpty(list, "导入数据为空");
        Assert.notEmpty(headList, "表头信息为空");
		//do your code
		//有表头了，有数据了，剩下的就是大家表演了

    }

AttendShiftPlanListener
监听处理数据，其实保存数据可以在监听里就处理了。

/**
 * 排班计划导入监听器
 *
 * @author zwmac
 */
@Slf4j
public class AttendShiftPlanListener extends AnalysisEventListener<Map<Integer, String>> {

    private static final int BATCH_COUNT = 5;

    private List<AttendShiftPlanExcelDto> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);

    @Getter
    private List<String> headers = new ArrayList<>();

    // 存储自定义表头
    Map<Integer, String> customHeaders = new HashMap<>();

    @Override
    public void onException(Exception exception, AnalysisContext context) throws Exception {
        super.onException(exception, context);
    }

    @Override
    public void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) {
    //列头处理
        Integer rowIndex = context.readRowHolder().getRowIndex();
        if(rowIndex == 1) {
            for (int i = 3; i < headMap.size(); i++) {
                String header = headMap.get(i).getStringValue();
                if (!"null".equals(header) && !isFixedHeader(header)) {
                    customHeaders.put(i, header);
                }
            }
            Map<Integer, String> hdMap = ConverterUtils.convertToStringMap(headMap, context);
            for (int i = 0; i < hdMap.size(); i++) {
                headers.add(hdMap.get(i));
            }
        }

    }

    /**
     * 判断是否是固定表头
     */
    private boolean isFixedHeader(String header) {
        //定义固定表头集合
        List<String> list = Arrays.asList("姓名", "部门", "账号");
        return list.contains(header);
    }

    @Override
    public void invoke(Map<Integer, String> data, AnalysisContext context) {
    //数据处理
        AttendShiftPlanExcelDto shiftPlanExcelDto = new AttendShiftPlanExcelDto();
        StringBuilder sb = new StringBuilder();
        shiftPlanExcelDto.setUserName(data.get(0));
        shiftPlanExcelDto.setOrgName(data.get(1));
        shiftPlanExcelDto.setUserAccount(data.get(2));

        JSONObject jsonObject = new JSONObject();
        for (int i = 0; i < data.size() - 1; i++) {
            jsonObject.put(customHeaders.get(i), data.get(i));
        }
        shiftPlanExcelDto.setShiftPlanDataJson(jsonObject.toJSONString());

        cachedDataList.add(shiftPlanExcelDto);
        if (cachedDataList.size() >= BATCH_COUNT) {
            //saveData();其实这里就可以直接处理保存
            cachedDataList.clear();
        } else {
            log.info("解析到一条数据:{}", data);
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {

    }
}

AttendShiftPlanExcelDto


import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

/**
 * @author zwmac
 */
@Data
public class AttendShiftPlanExcelDto {

    @ExcelProperty(value = "姓名", index = 0)
    private String userName;

    @ExcelProperty(value = "部门", index = 1)
    private String orgName;

    @ExcelProperty(value = "账号", index = 2)
    private String userAccount;

    /**
     * 动态数据列
     */
    @ExcelIgnore
    private String shiftPlanDataJson;
}