联级下拉框支持

[SghSean]

希望能支持联动下拉框
例如 地址联动选择，在源码目前只发现单下拉框的数据验证

[SghSean]

demo.xlsx
例如上传文件的 A1 跟 B1 的联动

[wangguanquan]

帮忙再做一个三级联动的示例文件

[SghSean]

test.xlsx
三级下拉框，数据隐藏

[wangguanquan]

扩展起来比较麻烦，也没有找到太好的办法来简化设置，多级联动需要有上下级关系，每一级还要设置单元格范围，下面给一个示例代码，支持3级联动，理论是没有限制几级，如果有超过3级联动需求将new UnsupportedOperationException("最多支持3级联动")异常拿掉就可以

整体思路如下

1. 自定义RelationListValidation并可以使用addSubRelation添加关联
2. 自定义MyXMLWorkbookWriter并支持名称管理器
3. 自定义MyXMLWorksheetWriter并解析RelationListValidation类型，并将下拉数据添写到新worksheet追加到末尾并设置隐藏

由于代码比较长，每个自定义类我都独立回复

[wangguanquan]

 // 自定义ListValidation，支持上下级ListValidation
public class RelationListValidation extends ListValidation<String> {
    public List<InnerRelation> subs;
    public String indirect;
    public List<Dimension> sqrefs;

    public RelationListValidation addSubRelation(Map<String, List<String>> subRelation, Dimension sqref) {
        if (this.subs == null) {
            this.subs = new ArrayList<>();
        }
        this.subs.add(new InnerRelation(subRelation, sqref));
        return this;
    }

    public RelationListValidation in(List<String> options, Dimension sqref) {
        this.options = options;
        this.sqref = sqref;
        return this;
    }

    public boolean hasSubRelation() {
        return subs != null && !subs.isEmpty();
    }

    public InnerRelation getSub(int level) {
        int len = subs != null ? subs.size() : 0;
        return level >= 1 && level <= len ? subs.get(level - 1) : null;
    }

    public static class InnerRelation {
        public Map<String, List<String>> options;
        public Dimension sqref;

        public InnerRelation(Map<String, List<String>> options, Dimension sqref) {
            this.options = options;
            this.sqref = sqref;
        }
    }

    private String getSqrefStr() {
        if (sqrefs != null && !sqrefs.isEmpty()) {
            StringBuilder buf = new StringBuilder(sqrefs.get(0).toString());
            for (int i = 1; i < sqrefs.size(); i++) {
                buf.append(' ').append(sqrefs.get(i));
            }
            return buf.toString();
        } else if (sqref != null) {
            return sqref.toReferer();
        }
        return "";
    }

    @Override
    public String toString() {
        return "<" + (isExtension() ? "x14:" : "" ) + "dataValidation type=\"" + getType()
            + (operator == null || operator == Operator.between ? "" : "\" operator=\"" + operator)
            + "\" allowBlank=\"" + (allowBlank ? 1 : 0)
            + "\" showInputMessage=\"" + (showInputMessage ? 1 : 0)
            + "\" showErrorMessage=\"" + (showErrorMessage ? 1 : 0)
            + (isExtension() ? "\">" : "\" sqref=\"" + getSqrefStr() + "\">")
            + validationFormula()
            + "</" + (isExtension() ? "x14:" : "" ) + "dataValidation>";
    }

    @Override
    public String validationFormula() {
        String val;
        if (isExtension()) {
            val = "<x14:formula1><xm:f>" + otherSheetName + "!" + referer.toReferer() + "</xm:f></x14:formula1><xm:sqref>" + sqref + "</xm:sqref>";
        } else if (options != null) {
            val = "<formula1>\"" + options.stream().map(String::valueOf).collect(Collectors.joining(",")) + "\"</formula1>";
        } else if (referer != null) {
            val = "<formula1>" + referer.toReferer() + "</formula1>";
        } else if (StringUtil.isNotEmpty(indirect)) {
            val = "<formula1>INDIRECT(" + indirect + ")</formula1>";
        } else {
            val = "<formula1>\"\"</formula1>";
        }
        return val;
    }
}

[wangguanquan]

// 自定义MyXMLWorkbookWriter增加名称管理器节点
public class MyXMLWorkbookWriter extends XMLWorkbookWriter {
    @Override
    protected void writeWorkbook(Path root) throws IOException {
        DocumentFactory factory = DocumentFactory.getInstance();
        //use the factory to create a root element
        Element rootElement = null;
        //use the factory to create a new document with the previously created root element
        String[] prefixs = null, uris = null;
        String rootName = null;
        TopNS topNs = getClass().getAnnotation(TopNS.class);
        boolean hasTopNs = (topNs != null);
        if (hasTopNs) {
            prefixs = topNs.prefix();
            uris = topNs.uri();
            rootName = topNs.value();
            for (int i = 0; i < prefixs.length; i++) {
                if (prefixs[i].isEmpty()) {
                    rootElement = factory.createElement(rootName, uris[i]);
                    break;
                }
            }
        }
        if (rootElement == null) {
            if (hasTopNs) {
                rootElement = factory.createElement(rootName);
            } else {
                LOGGER.error("Workbook missing necessary information.");
                return;
            }
        }

        if (prefixs.length > 0) {
            for (int i = 0; i < prefixs.length; i++) {
                rootElement.add(Namespace.get(prefixs[i], uris[i]));
            }
        }

        // book view
        rootElement.addElement("bookViews").addElement("workbookView").addAttribute("activeTab", "0");

        // sheets
        Element sheetEle = rootElement.addElement("sheets");
        Map<String, String> definedNames = new HashMap<>();
        for (int i = 0; i < workbook.getSize(); i++) {
            Sheet sheetInfo = workbook.getSheetAt(i);
            Element st = sheetEle.addElement("sheet")
                .addAttribute("sheetId", String.valueOf(sheetInfo.getId()))
                .addAttribute("name", sheetInfo.getName());
            if (sheetInfo.isHidden()) {
                st.addAttribute("state", "hidden");
            }
            Relationship rs = relManager.getByTarget("worksheets/sheet" + sheetInfo.getId() + Const.Suffix.XML);
            if (rs != null) {
                st.addAttribute(QName.get("id", Namespace.get("r", uris[StringUtil.indexOf(prefixs, "r")])), rs.getId());
            }
            @SuppressWarnings("unchecked")
            Map<String, String> names = (Map<String, String>) sheetInfo.getExtPropValue("DEFINED_NAME");
            if (names != null && !names.isEmpty()) {
                definedNames.putAll(names);
            }
        }

        // 新增名称管理器
        if (!definedNames.isEmpty()) {
            Element names = rootElement.addElement("definedNames");
            for (Map.Entry<String, String> entry : definedNames.entrySet()) {
                names.addElement("definedName").addAttribute("name", entry.getKey()).addText(entry.getValue());
            }
        }

        Document doc = factory.createDocument(rootElement);
        FileUtil.writeToDiskNoFormat(doc, root.resolve(rootName + Const.Suffix.XML)); // write to desk
    }
}

[wangguanquan]

// 自定义XMLWorksheetWriter并预解析RelationListValidation
public class MyXMLWorksheetWriter extends XMLWorksheetWriter {
    @Override
    protected void afterSheetData() throws IOException {
        // 预处理
        @SuppressWarnings("unchecked")
        List<Validation> validations = (List<Validation>) sheet.getExtPropValue(Const.ExtendPropertyKey.DATA_VALIDATION);
        if (validations != null && !validations.isEmpty()) {
            List<List<Object>> rows = null;
            Map<String, String> definedNames = null;
            List<Validation> dynamicValidations = null;
            String refSheetName = sheet.getName() + "_valid";
            for (Validation val : validations) {
                if (val instanceof RelationListValidation && ((RelationListValidation) val).hasSubRelation()) {
                    RelationListValidation rlv = (RelationListValidation) val;
                    if (rows == null) {
                        rows = new ArrayList<>();
                        definedNames = new LinkedHashMap<>();
                    }
                    // 最多支持3级联动，如果有更多级需求可直接删除该限制
                    if (rlv.subs.size() > 2) throw new UnsupportedOperationException("最多支持3级联动");
                    // 第二层数据
                    List<String> options = rlv.options;
                    RelationListValidation.InnerRelation ir1 = rlv.getSub(1);
                    Map<String, List<String>> sub = ir1.options;
                    for (String k : options) {
                        List<String> v = sub.get(k);
                        if (v == null || v.isEmpty()) continue;
                        List<Object> dataRows = new ArrayList<>(v.size() + 1);
                        rows.add(dataRows);
                        dataRows.add(k);
                        dataRows.addAll(v);
                        definedNames.put(k, refSheetName + "!" + new Dimension(rows.size(), (short) 2, rows.size(), (short) dataRows.size()).toReferer());
                    }

                    // 第三层数据
                    RelationListValidation.InnerRelation ir2 = rlv.getSub(2);
                    if (ir2 != null) {
                        Map<String, List<String>> sub2 = ir2.options;
                        for (String kk : sub.values().stream().flatMap(Collection::stream).collect(Collectors.toList())) {
                            List<String> vv = sub2.get(kk);
                            if (vv == null || vv.isEmpty()) continue;
                            List<Object> dataRows = new ArrayList<>(vv.size() + 1);
                            rows.add(dataRows);
                            dataRows.add(kk);
                            dataRows.addAll(vv);
                            definedNames.put(kk, refSheetName + "!" + new Dimension(rows.size(), (short) 2, rows.size(), (short) dataRows.size()));
                        }
                    }

                    // 修改当前Validation为引用
                    rlv.otherSheetName = refSheetName;
                    rlv.referer = new Dimension(1, (short) 1, options.size(), (short) 1);

                    // 新增一个INDIRECT
                    RelationListValidation newRlv = new RelationListValidation();
                    List<Dimension> sqrefs = new ArrayList<>();
                    sqrefs.add(ir1.sqref);
                    if (ir2 != null) sqrefs.add(ir2.sqref);
                    newRlv.sqrefs = sqrefs;
                    newRlv.indirect = Sheet.toCoordinate(rlv.sqref.firstRow, rlv.sqref.firstColumn);
                    if (dynamicValidations == null) dynamicValidations = new ArrayList<>();
                    dynamicValidations.add(newRlv);
                }
            }

            // 将数据添加到最后一个sheet页，并设置隐藏
            if (rows != null && !rows.isEmpty()) {
                sheet.getWorkbook().addSheet(new SimpleSheet<>(refSheetName, rows).hidden());
                sheet.putExtProp("DEFINED_NAME", definedNames);
                validations.addAll(dynamicValidations);
            }
        }

        super.afterSheetData();
    }
}

[wangguanquan]

测试代码

@Test public void testRelationListValidation() throws IOException {
    List<Validation> validations = new ArrayList<>();
    validations.add(new RelationListValidation().in(Arrays.asList("江苏省", "山东省", "湖南省"), Dimension.of("A2:A100"))
        .addSubRelation(new LinkedHashMap<String, List<String>>(){{
            put("江苏省", Arrays.asList("南京市", "苏州市", "扬州市"));
            put("山东省", Arrays.asList("青岛市", "德州市", "烟台市"));
            put("湖南省", Arrays.asList("长沙市", "株洲市"));
        }}, Dimension.of("B2:B100"))
        .addSubRelation(new LinkedHashMap<String, List<String>>(){{
            put("南京市", Arrays.asList("南1", "南2", "南3"));
            put("苏州市", Arrays.asList("苏Z", "苏5", "苏U"));
            put("扬州市", Arrays.asList("杨C"));
            put("青岛市", Arrays.asList("青1", "青L"));
            put("德州市", Arrays.asList("德A", "德B", "德E"));
            put("烟台市", Arrays.asList("烟A", "烟B", "烟C", "烟D"));
            put("长沙市", Arrays.asList("长1", "长2"));
            put("株洲市", Arrays.asList("株1", "株2", "株3", "株4"));
        }}, Dimension.of("C2:C100"))
    );
    new Workbook().addSheet(new ListSheet<>(new Column("省份"), new Column("城市"), new Column("区县"))
            .putExtProp(Const.ExtendPropertyKey.DATA_VALIDATION, validations).setSheetWriter(new MyXMLWorksheetWriter()))
        .setWorkbookWriter(new MyXMLWorkbookWriter())
        .writeTo(Paths.get("./testRelationListValidation.xlsx"));
}

[wangguanquan]

代码比较乱测试可用，如果在源码上修改则不需要这么复杂，关注后续迭代吧

[SghSean]

好的，谢谢，一直有在关注 迭代

[wangguanquan]

v0.5.27 已支持级联下拉，同时TemplateSheet模板工作表也支持复制级联下拉，WIKI帮助文档

[SghSean]

v0.5.27 已支持级联下拉，同时TemplateSheet模板工作表也支持复制级联下拉，WIKI帮助文档

好的，感谢