【工具使用】【EasyExcel】Excel 模板实现省市区联动
1 前言
上次模板 Excel 省市区的联动是用的 POI，今儿再用 EasyExcel 实现一个，贴一下实现思路。
EasyExcel 官网地址，其实人家官网写的很清楚了，分场景分示例，用法都给贴出来了，感觉可以实现大多数的模板。我们这里的省市区就是要用到自定义拦截器的功能。
2 实现
Maven 依赖：
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.3.3</version>
</dependency>
实现：
public class Demo { @Data static class Template { @ExcelProperty(value = "序号") @ColumnWidth(20) private String num; @ExcelProperty(value = "省") @ColumnWidth(20) private String provinceName; @ExcelProperty(value = "市") @ColumnWidth(20) private String cityName; @ExcelProperty(value = "区") @ColumnWidth(20) private String districtName; } /** * 这个处理器其实可以做很多事情 * 他能拿到整个 Excel 的 WorkHook * 也能拿到当前的 sheet * 也能拿到当前的 cell * 所以能做的自定义操作很丰富 */ @Slf4j static class CustomCellWriteHandler implements CellWriteHandler { @Override public void afterCellDispose(CellWriteHandlerContext context) { // 当前的 cell Cell cell = context.getCell(); // 这里可以对cell进行任何操作 // 这里就要考虑 你要针对哪一列进行个性化处理 一定要记得加判断 因为每个 cell 都会执行到这里 所以要记得区分 if (BooleanUtils.isTrue(context.getHead()) && cell.getColumnIndex() == 1) { // 1 表示 省那一列 要对省市区进行联动下拉处理 ExcelAreaUtil.writeAreaInfo(context, 1); } } } public static class ExcelAreaUtil { @Data static class AreaInfo { private String name; private List<AreaInfo> areaList; public AreaInfo(String name) { this.name = name; } public AreaInfo(String name, List<AreaInfo> areaList) { this.name = name; this.areaList = areaList; } } /** * @param context handler 的 上下文 * @param startIndex 省的列号 从0开始的 */ public static void writeAreaInfo(CellWriteHandlerContext context, int startIndex) { // 获取到当前的 sheet 后续要给省市区列加下拉 WriteSheetHolder writeSheetHolder = context.getWriteSheetHolder(); Sheet sheet = writeSheetHolder.getSheet(); // 省市区挨着的列号 int pIndex = startIndex; int cIndex = pIndex + 1; int aIndex = cIndex + 1; // 准备点数据 AreaInfo cityOne = new AreaInfo("临汾市", Lists.newArrayList(new AreaInfo("尧都区"))); AreaInfo cityTwo = new AreaInfo("西安市", Lists.newArrayList(new AreaInfo("新城区"))); List<AreaInfo> provinceList = Lists.newArrayList( new AreaInfo("山西省", Lists.newArrayList(cityOne)), new AreaInfo("陕西省", Lists.newArrayList(cityTwo)) ); List<AreaInfo> cityList = Lists.newArrayList(cityOne, cityTwo); // 获取到当前的 excel 因为要创建隐藏的 sheet 也就是省市区的实际内容都来自于隐藏的 sheet Workbook workbook = context.getWriteSheetHolder().getParentWriteWorkbookHolder().getWorkbook(); Sheet hideSheet = workbook.createSheet("area"); workbook.setSheetHidden(workbook.getSheetIndex(hideSheet), true); // 写隐藏 sheet 的省市区信息 int rowId = 0; // 设置第一行，存省的信息 Row provinceRow = hideSheet.createRow(rowId++); provinceRow.createCell(0).setCellValue("省列表"); for (int i = 0; i < provinceList.size(); i++) { Cell provinceCell = provinceRow.createCell(i + 1); provinceCell.setCellValue(provinceList.get(i).getName()); } for (AreaInfo pro : provinceList) { String pName = pro.getName(); List<AreaInfo> cList = pro.getAreaList(); Row cRow = hideSheet.createRow(rowId++); cRow.createCell(0).setCellValue(pName); for (int j = 0; j < cList.size(); j++) { AreaInfo cInfo = cList.get(j); Cell cCell = cRow.createCell(j + 1); cCell.setCellValue(cInfo.getName()); } // 添加名称管理器 String range = getRange(1, rowId, cList.size()); Name name = workbook.createName(); // key不可重复 name.setNameName(pName); String formula = "area!" + range; name.setRefersToFormula(formula); } for (AreaInfo dInfo : cityList) { String pName = dInfo.getName(); List<AreaInfo> cList = dInfo.getAreaList(); Row cRow = hideSheet.createRow(rowId++); cRow.createCell(0).setCellValue(pName); for (int j = 0; j < cList.size(); j++) { Cell cCell = cRow.createCell(j + 1); cCell.setCellValue(cList.get(j).getName()); } // 添加名称管理器 String range = getRange(1, rowId, cList.size()); Name name = workbook.createName(); // key不可重复 name.setNameName(pName); String formula = "area!" + range; name.setRefersToFormula(formula); } // 给省添加下拉 int lastRow = 100; setDataValidation(sheet, 1, lastRow, pIndex, pIndex); // 给市 区 添加下拉 // 设置第二列和第三列的有效性 for (int i = 2; i < lastRow; i++) { setDataValidation(CellReference.convertNumToColString(pIndex), sheet, i - 1, i - 1, cIndex, cIndex); setDataValidation(CellReference.convertNumToColString(cIndex), sheet, i - 1, i - 1, aIndex, aIndex); } } public static void setDataValidation(Sheet sheetPro, int firstRow, int lastRow, int firstCol, int lastCol) { DataValidationHelper dvHelper = sheetPro.getDataValidationHelper(); String formula = getFormula(1, 1, 1, 34); // 省规则 DataValidationConstraint provConstraint = dvHelper.createFormulaListConstraint(formula); // 四个参数分别是：起始行、终止行、起始列、终止列 CellRangeAddressList provRangeAddressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol); DataValidation provinceDataValidation = dvHelper.createValidation(provConstraint, provRangeAddressList); // 验证 provinceDataValidation.createErrorBox("error", "请选择正确的省份"); provinceDataValidation.setShowErrorBox(true); provinceDataValidation.setSuppressDropDownArrow(true); sheetPro.addValidationData(provinceDataValidation); } public static void setDataValidation(String offset, Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) { DataValidationHelper dvHelper = sheet.getDataValidationHelper(); String formulaString = String.format("INDIRECT($%s%s)", offset, firstRow + 1); DataValidation dataValidation = getDataValidationByFormula(formulaString, dvHelper, firstRow, lastRow, firstCol, lastCol); sheet.addValidationData(dataValidation); } private static DataValidation getDataValidationByFormula(String formulaString, DataValidationHelper dvHelper, int firstRow, int lastRow, int firstCol, int lastCol) { // 加载下拉列表内容 // 举例：若formulaString = "INDIRECT($A$2)" 表示规则数据会从名称管理器中获取key与单元格 A2 值相同的数据， // 如果A2是江苏省，那么此处就是江苏省下的市信息。 DataValidationConstraint dvConstraint = dvHelper.createFormulaListConstraint(formulaString); // 设置数据有效性加载在哪个单元格上。 // 四个参数分别是：起始行、终止行、起始列、终止列 CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol); // 绑定 数据有效性对象 XSSFDataValidation dataValidation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions); dataValidation.setEmptyCellAllowed(true); dataValidation.setSuppressDropDownArrow(true); dataValidation.setShowErrorBox(true); // 设置输入错误提示信息 dataValidation.createErrorBox("选择错误提示", "你输入的值未在备选列表中，请下拉选择合适的值！"); return dataValidation; } private static String getFormula(int firstRow, int lastRow, int firstCol, int lastCol) { String ss = String.format("$%s$%s:$%s$%s", CellReference.convertNumToColString(firstCol), firstRow, CellReference.convertNumToColString(lastCol), lastRow); return String.format("=%s!%s", "area", ss); } private static String getRange(int offset, int rowId, int colCount) { String columnLetter1 = CellReference.convertNumToColString(offset); String columnLetter2 = CellReference.convertNumToColString(offset + colCount - 1); return String.format("$%s$%s:$%s$%s", columnLetter1, rowId, columnLetter2, rowId); } } public static void main(String[] args) { EasyExcel.write("test.xlsx", Template.class) .registerWriteHandler(new CustomCellWriteHandler()) .sheet("测试") .doWrite(Collections.EMPTY_LIST); } }
可以直接执行的。
3 效果展示
