基于EasyExcel实现模板导出功能(含查数据库下拉,市州区县级联下拉)

        最近的项目涉及到excel导入导出功能,既然涉及到导入,那给客户提供导入模板是必不可少的功能。本文总结了csdn各大前辈的精华,能够实现模板导出功能,并包含指定字段查库下拉选,指定字段级联下拉选(例如市州区县)。

目录

1. 引入easyexcel相关依赖

      2. 设置处理下拉的handler

3. 设置级联下拉的handler

4. 创建对应的导入实体类

5. 在对应模块的实现类编写获取导入模板的方法

      6. 控制层编写


1. 引入easyexcel相关依赖

<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>easyexcel</artifactId>
  <version>2.2.10</version>
</dependency>

ps:如果遇到各种bug就先看看自己的pom文件那几个对应的poi版本是不是一致的。如果你也遇到了项目存在老版本poi导致poi冲突的话可以看看我写的另一篇文章:

解决easyexcel的poi版本冲突_JesseXW的博客-CSDN博客

2. 设置处理下拉的handler

/**
 * 处理选定的工作表
 *
 * @author: Jesse
 * @date: 2023/6/27
 */
public class SelectedSheetWriteHandler implements SheetWriteHandler {

    private final Map<Integer, List<String>> selectedMap;

    /**
     * Called before create the sheet
     */
    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {}

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

    /**
     * Called after the sheet is created
     */
    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        if (selectedMap == null || selectedMap.size() == 0) {
            return;
        }
        // 需要设置下拉框的sheet页
        Sheet curSheet = writeSheetHolder.getSheet();
        DataValidationHelper helper = curSheet.getDataValidationHelper();
        String dictSheetName = "sheet2";
        Workbook workbook = writeWorkbookHolder.getWorkbook();
        // 数据字典的sheet页
        Sheet dictSheet = workbook.createSheet(dictSheetName);
        // 隐藏数据字典的sheet页
        workbook.setSheetHidden(workbook.getSheetIndex(dictSheet), true);
        for (Map.Entry<Integer, List<String>> entry : selectedMap.entrySet()) {
            // 设置下拉单元格的首行、末行、首列、末列
            CellRangeAddressList rangeAddressList = new CellRangeAddressList(1, 65533, entry.getKey(), entry.getKey());
            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("提示", "此值与单元格定义格式不一致!");
            // 添加下拉框约束
            writeSheetHolder.getSheet().addValidationData(validation);
        }
    }

    public Map<Integer, List<String>> getSelectedMap() {
        return selectedMap;
    }

    public SelectedSheetWriteHandler(Map<Integer, List<String>> selectedMap) {
        this.selectedMap = selectedMap;
    }

    /**
     * 将数字列转化成为字母列
     * @param num
     * @author: Jesse
     * @date: 2023/6/27
     * @return: String
     */
    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;
    }
}

ps:此处已针对下拉结果超50不显示问题进行了对应的优化~(解决方案为额外创建了张隐藏表,将下拉选项都放在隐藏表中)

3. 设置级联下拉的handler

/**
 * 级联下拉处理器
 *
 * @author: Jesse
 * @date: 2023/6/27
 */
public class CascadeWriteHandler implements SheetWriteHandler {
    private List<String> largeList;          // 大类的字符串集合
    Map<String, List<String>> siteMap;    // 大类和小类的对应关系的map集合

    public CascadeWriteHandler(List<String> largeList, Map<String, List<String>> siteMap) {
        this.largeList = largeList;
        this.siteMap = siteMap;
    }

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

    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        //获取工作簿
        Sheet sheet = writeSheetHolder.getSheet();
        Workbook book = writeWorkbookHolder.getWorkbook();
        //创建一个专门用来存放地区信息的隐藏sheet页
        //因此不能在现实页之前创建,否则无法隐藏。
        Sheet hideSheet = book.createSheet("site");
        book.setSheetHidden(book.getSheetIndex(hideSheet), true);
        // 将具体的数据写入到每一行中,行开头为父级区域,后面是子区域。
        int rowId = 0;
        Row proviRow = hideSheet.createRow(rowId++);
        proviRow.createCell(0).setCellValue("大类列表");
        for (int i = 0; i < largeList.size(); i++) {
            Cell proviCell = proviRow.createCell(i + 1);
            proviCell.setCellValue(largeList.get(i));
        }
        Iterator<String> keyIterator = siteMap.keySet().iterator();
        while (keyIterator.hasNext()) {
            String key = keyIterator.next();
            List<String> son = siteMap.get(key);
            Row row = hideSheet.createRow(rowId++);
            for (int i = 0; i < son.size(); i++) {
                Cell cell = row.createCell(i + 1);
                cell.setCellValue(son.get(i));
            }
            // 添加名称管理器
            String range = getRange(1, rowId, son.size());
            Name name = book.createName();
            name.setNameName(key);
            String formula = "site!" + range;
            name.setRefersToFormula(formula);
        }

        ///开始设置(大类小类)下拉框
        DataValidationHelper dvHelper = sheet.getDataValidationHelper();
        // 大类规则
        DataValidationConstraint expConstraint = dvHelper.createExplicitListConstraint(largeList.toArray(new String[]{}));
        CellRangeAddressList expRangeAddressList = new CellRangeAddressList(1, 999, 4, 4);//todo 调整模板后位置需同步修改firstCol
        setValidation(sheet, dvHelper, expConstraint, expRangeAddressList, "提示", "你输入的值未在备选列表中,请下拉选择合适的值");

        // 小类规则(各单元格按个设置)
        // "INDIRECT($A$" + 2 + ")" 表示规则数据会从名称管理器中获取key与单元格 A2 值相同的数据,如果A2是浙江省,那么此处就是浙江省下面的市
        // 为了让每个单元格的公式能动态适应,使用循环挨个给公式。
        // 循环几次,就有几个单元格生效,次数要和上面的大类影响行数一一对应,要不然最后几个没对上的单元格实现不了级联
        for (int i = 2; i < 1000; i++) {
            CellRangeAddressList rangeAddressList = new CellRangeAddressList(i-1 , i-1, 5, 5);//todo 调整模板后位置需同步修改firstCol
            DataValidationConstraint formula = dvHelper.createFormulaListConstraint("INDIRECT($E$" + i + ")");//todo 调整模板后位置需同步修改字母
            setValidation(sheet, dvHelper, formula, rangeAddressList, "提示", "你输入的值未在备选列表中,请下拉选择合适的值");
        }
    }

    /**
     * 设置验证规则
     * @param sheet          sheet对象
     * @param helper      验证助手
     * @param constraint   createExplicitListConstraint
     * @param addressList  验证位置对象
     * @param msgHead     错误提示头
     * @param msgContext   错误提示内容
     */
    private void setValidation(Sheet sheet, DataValidationHelper helper, DataValidationConstraint constraint, CellRangeAddressList addressList, String msgHead, String msgContext) {
        DataValidation dataValidation = helper.createValidation(constraint, addressList);
        dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
        dataValidation.setShowErrorBox(true);
        dataValidation.setSuppressDropDownArrow(true);
        dataValidation.createErrorBox(msgHead, msgContext);
        sheet.addValidationData(dataValidation);
    }

    /**
     * @param offset   偏移量,如果给0,表示从A列开始,1,就是从B列
     * @param rowId    第几行
     * @param colCount 一共多少列
     * @return 如果给入参 1,1,10. 表示从B1-K1。最终返回 $B$1:$K$1
     * @author denggonghai 2016年8月31日 下午5:17:49
     */
    public String getRange(int offset, int rowId, int colCount) {
        char start = (char) ('A' + offset);
        if (colCount <= 25) {
            char end = (char) (start + colCount - 1);
            return "$" + start + "$" + rowId + ":$" + end + "$" + rowId;
        } else {
            char endPrefix = 'A';
            char endSuffix = 'A';
            if ((colCount - 25) / 26 == 0 || colCount == 51) {// 26-51之间,包括边界(仅两次字母表计算)
                if ((colCount - 25) % 26 == 0) {// 边界值
                    endSuffix = (char) ('A' + 25);
                } else {
                    endSuffix = (char) ('A' + (colCount - 25) % 26 - 1);
                }
            } else {// 51以上
                if ((colCount - 25) % 26 == 0) {
                    endSuffix = (char) ('A' + 25);
                    endPrefix = (char) (endPrefix + (colCount - 25) / 26 - 1);
                } else {
                    endSuffix = (char) ('A' + (colCount - 25) % 26 - 1);
                    endPrefix = (char) (endPrefix + (colCount - 25) / 26);
                }
            }
            return "$" + start + "$" + rowId + ":$" + endPrefix + endSuffix + "$" + rowId;
        }
    }
}

ps:级联下拉处理器这是csdn一个大佬写的,他的文章对于每块代码是干什么的写的很详尽,强烈建议各位去拜读一下,对代码的实际运行更为印象深刻,链接我会贴在文末。(但不知道这个大佬是故意的还是啥,他给的示例代码里面有个错误会导致级联失效,但是你仔细阅读他后面写的实现逻辑你就会发现他哪写错了,这里的代码我已经修正了,各位读者感兴趣可以去看看,写的真的很6!!)

4. 创建对应的导入实体类

/**
 * 项目的excel模板类
 *
 * @author: Jesse
 * @date: 2023/6/26
 */

@ColumnWidth(22)
@HeadRowHeight(30)
public class ExcelProjectDto {

    /**
     * 项目名称
     */
    @ExcelProperty(value = "项目名称")//表头注解
    private String projName;

    /**
     * 项目所在企业名称
     */
    @ExcelProperty(value = "企业名称")
    private String enterpriseName;

    /**
     * 项目所在地级名称
     */
    @ExcelProperty(value = "地市")
    private String area20Name;

    /**
     * 项目所在县级名称
     */
    @ExcelProperty(value = "区县")
    private String area30Name;

    public String getProjName() {
        return projName;
    }

    public void setProjName(String projName) {
        this.projName = projName;
    }

    public String getEnterpriseName() {
        return enterpriseName;
    }

    public void setEnterpriseName(String enterpriseName) {
        this.enterpriseName = enterpriseName;
    }

    public String getArea20Name() {
        return area20Name;
    }

    public void setArea20Name(String area20Name) {
        this.area20Name = area20Name;
    }

    public String getArea30Name() {
        return area30Name;
    }

    public void setArea30Name(String area30Name) {
        this.area30Name = area30Name;
    }

    public ExcelProjectDto(String projName, String enterpriseName, String area20Name, String area30Name) {
        this.projName = projName;
        this.enterpriseName = enterpriseName;
        this.area20Name = area20Name;
        this.area30Name = area30Name;
    }

    public ExcelProjectDto() {
    }
}

ps:根据自己的业务逻辑进行编写即可,可以使用Lombok的@Data让代码更简洁

5. 在对应模块的实现类编写获取导入模板的方法

public void getImportTemplate(HttpServletResponse response) {
        String fileName = "项目信息导入模板" + System.currentTimeMillis() + ".xlsx";//这里的时间戳可以不要,fileName改为你想要的名称即可

        //获取当前桌面路径  直接导出文件到桌面上(自测可以解开这些注释,但是也注意注释其他对应的代码)
//        String path = FileSystemView.getFileSystemView().getHomeDirectory().getPath();
//        String exportFileName = path + "/" + fileName;

        //解决编码问题
        try {
            fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setCharacterEncoding("utf-8");
            response.setDateHeader("Expires", -1);
            //设置响应头部信息,格式为附件,以及文件名
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName);
            ArrayList<ExcelProjectDto> dataList = new ArrayList<>();
            //设置市州区县级联信息(根据你项目实际情况来进行查询)
            List<AreaListNodeVo> areaListNodeVos = areaService.getChildrenAreaListNodeVos("510000");//四川省区划id
            List<String> provNameList = areaListNodeVos.stream().map(AreaListNodeVo::getName).collect(Collectors.toList());
            HashMap<String, List<String>> siteMap = new HashMap<>(36);
            for (AreaListNodeVo areaNodeVo : areaListNodeVos) {
                List<AreaListNodeVo> area30Vos = areaService.getChildrenAreaListNodeVos(areaNodeVo.getId());
                List<String> area30Names = area30Vos.stream().map(AreaListNodeVo::getName).collect(Collectors.toList());
                siteMap.put(areaNodeVo.getName(), area30Names);
            }
            Map<Integer, List<String>> selectedMap = new HashMap<>(64);
            List<EnterpriseListNodeVo> nodeVoList = enterpriseService.getAllListNodeVos();
            List<String> enterpriseNameList = nodeVoList.stream().map(EnterpriseListNodeVo::getEnterpriseName).collect(Collectors.toList());
            if (CollectionUtil.isNotEmpty(enterpriseNameList)) {
                selectedMap.put(3, enterpriseNameList);//这里的3是指在导出的模板excel的第4列,因为是从0开始的,之前我给出的实体类我删掉了一些敏感字段
            }
            WriteSheet writeSheet = EasyExcel.writerSheet(0, "项目信息").head(ExcelProjectDto.class).registerWriteHandler(new CascadeWriteHandler(provNameList, siteMap)).registerWriteHandler(new SelectedSheetWriteHandler(selectedMap)).build();
//            ExcelWriter excelWriter = EasyExcel.write(exportFileName).build();
            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
            //此处只导出实体类中的数据所以只new 一个空的list
            excelWriter.write(dataList, writeSheet);
            excelWriter.finish();
        } catch (UnsupportedEncodingException e) {
            logger.error("===========导出Excel编码异常:" + e.getMessage() + "===========");
        } catch (IOException e) {
            logger.error("===========导出Excel文件异常" + e.getMessage() + "===========");
        }
    }

6. 控制层编写

@ApiOperation("获取导入模板")
@RequestMapping(value = "/get-import-template", method = RequestMethod.GET)
public void getImportTemplate(HttpServletResponse response) {
    demoService.getImportTemplate(response);
}

ps:@ApiOperation("获取导入模板")是swagger的,可以不加

=========================================================================

最终效果:

 其中企业名称作为查库下拉,市州级联的会作为隐藏sheet

 

查库下拉效果:

 

地市区县级联效果:

 

 ps:但是还是会出现这种bug:

1. 先手动输入区县,再下拉选择地市,可以直接成功保存

2.直接复制粘贴进去,不管是否正确,都可以直接保存

只有手动按照顺序选择地市,再选择区县才是100%正确的!真的是防止不了用户手贱啊!(扶额)只能后端在最终导入数据时多做做校验了......

 =========================================================================

最后贴上各位大佬的链接:

        👆👆👆强烈推荐!!!

        👆如果想更优雅一点通过注解实现easyexcel下拉,可以参考这篇

评论 4
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值