使用EasyExcel导出模板并设置级联下拉及其原理分析

2024-03-02 18:36| 来源: 网络整理| 查看: 265

一、概述

项目中有时会遇到需要导出一个Excel模板，然后在导出的Excel中填充数据，最终再调用接口批量把Excel中的数据导入到数据库当中的需求。

其中级联下拉选择，手机号校验，性别校验等都是比较常见的校验。

这里就已上面三种情况，使用EasyExcel，结合POI来进行说明。

网上已经有一些使用POI完成上述功能的文章了。这里就以EasyExcel为主体，POI为辅来进行实现。

二、Maven坐标准备 com.alibaba easyexcel 3.1.1 org.apache.poi poi 4.1.2 org.apache.poi poi-ooxml 4.1.2 org.apache.poi poi-ooxml-schemas 4.1.2 三、模板导出具体实现 3.1 实现省市级联下拉 1）准备好级联写出处理器策略实现类 import com.alibaba.excel.write.handler.SheetWriteHandler; import com.alibaba.excel.write.metadata.holder.WriteSheetHolder; import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddressList; import java.util.*; public class CascadeWriteHandler implements SheetWriteHandler { private List largeList; // 大类的字符串集合 Map siteMap; // 大类和小类的对应关系的map集合 public CascadeWriteHandler(List largeList, Map siteMap) { this.largeList = largeList; this.siteMap = siteMap; } @Override public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) { } @Override public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) { Map siteMap = new HashMap(); //获取工作簿 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 String key = keyIterator.next(); List son = siteMap.get(key); Row row = hideSheet.createRow(rowId++); row.createCell(0).setCellValue(key); for (int i = 0; i })); CellRangeAddressList expRangeAddressList = new CellRangeAddressList(1, 999, 0, 0); setValidation(sheet, dvHelper, expConstraint, expRangeAddressList, "提示", "你输入的值未在备选列表中，请下拉选择合适的值"); // 小类规则(各单元格按个设置) // "INDIRECT($A$" + 2 + ")" 表示规则数据会从名称管理器中获取key与单元格 A2 值相同的数据，如果A2是浙江省，那么此处就是浙江省下面的市 // 为了让每个单元格的公式能动态适应，使用循环挨个给公式。 // 循环几次，就有几个单元格生效，次数要和上面的大类影响行数一一对应，要不然最后几个没对上的单元格实现不了级联 for (int i = 2; i 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 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; } } } 2）准备好导出实体类 @Data @AllArgsConstructor @NoArgsConstructor public class CascadeVO { @ExcelProperty("省") private String largeType ; @ExcelProperty("市") private String smallType ; } 3）编写Controller接口导出模板 @RestController @RequestMapping("/excel") public class ExcelController { @SneakyThrows @GetMapping("/downloadCascade") public void downloadCascade(HttpServletResponse response){ response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setCharacterEncoding("utf-8"); // 这里URLEncoder.encode可以防止中文乱码当然和easyexcel没有关系 String fileName = URLEncoder.encode("导出模板-级联下拉框", "UTF-8").replaceAll("\\+", "%20"); // 设置文件名称 response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx"); List dataList = new ArrayList(); // 准备要写出的数据（这里可以从数据库中查询出来后再进行） // 查询所有的省名称 List provNameList = new ArrayList(); provNameList.add("浙江省"); provNameList.add("广东省"); // 整理数据，放入一个Map中，mapkey存放父地点，value 存放该地点下的子区域 Map siteMap = new HashMap(); siteMap.put("浙江省", CollUtil.newArrayList("杭州市", "金华市", "宁波市")); siteMap.put("广东省", CollUtil.newArrayList("广州市", "深圳市", "韶光市")); // 写出数据 EasyExcel.write(response.getOutputStream(), CascadeVO.class) .sheet("sheet1") .registerWriteHandler(new CascadeWriteHandler(provNameList, siteMap)) .doWrite(dataList); } } 4）导出示例 3.2 实现性别校验

下拉框都类似，创建的是一种显示列表约束：createExplicitListConstraint

1）准备下拉框处理器策略实现类 import com.alibaba.excel.write.handler.SheetWriteHandler; import com.alibaba.excel.write.metadata.holder.WriteSheetHolder; import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder; import org.apache.poi.ss.usermodel.DataValidation; import org.apache.poi.ss.usermodel.DataValidationConstraint; import org.apache.poi.ss.usermodel.DataValidationHelper; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.util.CellRangeAddressList; import java.util.HashMap; import java.util.List; import java.util.Map; public class DropDownWriteHandler implements SheetWriteHandler { List dropDown; // 下拉框显示的数值 public DropDownWriteHandler(List dropDown) { this.dropDown = dropDown; } @Override public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) { } @Override public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) { // 开始设置男/女下拉框 // 定义一个map key是需要添加下拉框的列的index value是下拉框数据 Map mapDropDown = new HashMap(3); //性别下拉选项 String[] downArray = dropDown.toArray(new String[dropDown.size()]); // {"男", "女"}; //下拉选在Excel中对应的列 mapDropDown.put(2, downArray); // 获取Sheet表 Sheet sheet = writeSheetHolder.getSheet(); //设置下拉框 DataValidationHelper dvHelper = sheet.getDataValidationHelper(); for (Map.Entry entry : mapDropDown.entrySet()) { // 起始行、终止行、起始列、终止列起始行为1即表示表头不设置 CellRangeAddressList addressList = new CellRangeAddressList(1, 999, entry.getKey(), entry.getKey()); // 设置下拉框数据 (设置长度为0的数组会报错，所以这里需要判断) if (entry.getValue().length > 0) { //创建显式列表约束 DataValidationConstraint constraint = dvHelper.createExplicitListConstraint(entry.getValue()); // 指定行列约束以及错误信息 DataValidation dataValidation = dvHelper.createValidation(constraint, addressList); dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP); dataValidation.setShowErrorBox(true); dataValidation.setSuppressDropDownArrow(true); dataValidation.createErrorBox("提示", "你输入的值未在备选列表中，请下拉选择合适的值"); sheet.addValidationData(dataValidation); } } } } 2）准备好导出实体类 @Data @AllArgsConstructor @NoArgsConstructor public class DropDownVO { @ExcelProperty("性别（男/女）") private String gender ; } 3）编写Controller接口导出模板 @RestController @RequestMapping("/excel") public class ExcelController { @SneakyThrows @GetMapping("downloadDropDown") public void downloadDropDown(HttpServletResponse response){ response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setCharacterEncoding("utf-8"); // 这里URLEncoder.encode可以防止中文乱码当然和easyexcel没有关系 String fileName = URLEncoder.encode("导出模板-普通下拉框", "UTF-8").replaceAll("\\+", "%20"); // 设置文件名称 response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx"); List dataList = new ArrayList(); // 准备要写出的数据（这里可以从数据库中查询出来后再进行） List dropDown = CollUtil.newArrayList("男", "女"); // 写出数据 EasyExcel.write(response.getOutputStream(), CascadeVO.class) .sheet("sheet1") .registerWriteHandler(new DropDownWriteHandler(dropDown)) .doWrite(dataList); } } 3.3 实现手机号和数字校验

可以创建自定义约束：createExplicitListConstraint

也可以创建普通的数值类约束来进行简单的单元格约束：createNumericConstraint

1）准备自定义处理器策略实现类 import com.alibaba.excel.write.handler.SheetWriteHandler; import com.alibaba.excel.write.metadata.holder.WriteSheetHolder; import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder; import org.apache.poi.ss.usermodel.DataValidation; import org.apache.poi.ss.usermodel.DataValidationConstraint; import org.apache.poi.ss.usermodel.DataValidationHelper; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.util.CellRangeAddressList; /** * 号码下拉框处理策略 */ public class NumberWriteHandler implements SheetWriteHandler { @Override public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) { } @Override public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) { // 获取Sheet表 Sheet sheet = writeSheetHolder.getSheet(); // 验证助手 DataValidationHelper dvHelper = sheet.getDataValidationHelper(); //设置电话号码校验规则 DataValidationConstraint CustomConstraint = dvHelper.createCustomConstraint("AND(LEFT(D2,1)=\"1\",MID(D2,2,1)+0>=3,MID(D2,2,1)+0

【本文地址】

公司简介

联系我们