eec
eec copied to clipboard
wangguanquan
联级下拉框支持
扩展起来比较麻烦，也没有找到太好的办法来简化设置，多级联动需要有上下级关系，每一级还要设置单元格范围，下面给一个示例代码，支持3级联动，理论是没有限制几级，如果有超过3级联动需求将new UnsupportedOperationException("最多支持3级联动")异常拿掉就可以
整体思路如下
- 自定义RelationListValidation并可以使用addSubRelation添加关联
- 自定义MyXMLWorkbookWriter并支持名称管理器
- 自定义MyXMLWorksheetWriter并解析RelationListValidation类型，并将下拉数据添写到新worksheet追加到末尾并设置隐藏
由于代码比较长，每个自定义类我都独立回复
// 自定义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;
}
}
// 自定义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
}
}
// 自定义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();
}
}
测试代码
@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"));
}