如何使用Docx4j为XLSX单元格添加数据验证规则？

问题 #

使用Docx4j（v4.11.9，搭配Jakarta 3.0.1）基于模板电子表格添加新行功能正常，但为单元格区域E2:Ex（x为新增数据行数）添加数据验证规则后，出现两个问题：

1. 生成的文件无法在Excel中正常打开
2. 生成的XML与手动在Excel中添加规则生成的XML存在差异

代码实现 #

CTDataValidations validationList = new CTDataValidations();
CTDataValidation validation = new CTDataValidation();
validationList.getDataValidation().add(validation);
validationList.setCount(1L);
validation.setAllowBlank(true);
validation.setError("Please indicate whether the transaction was approved or declined");
validation.setErrorTitle("Pick Status");
validation.setShowErrorMessage(true);
validation.setShowInputMessage(true);
validation.setType(STDataValidationType.LIST);
validation.setFormula1("Data!$A$2:$A$3");
validation.getSqref().add("E2:E" + (maxRow + 1));

CTExtensionList extensionlist = new CTExtensionList();
CTExtension extension = new CTExtension();
extensionlist.getExt().add(extension);
extension.setUri( "{CCE6A557-97BC-4b89-ADB6-D9C93CAAB3DF}");

QName CTDataValidations_QNAME = new QName("http://schemas.microsoft.com/office/spreadsheetml/2009/9/main", "dataValidations");
JAXBElement<CTDataValidations> validationListWrapped = new JAXBElement<>(CTDataValidations_QNAME, CTDataValidations.class, CTExtension.class, validationList);

extension.setAny(validationListWrapped);

worksheet.setExtLst(extensionlist);

XML对比 #

Excel手动生成的XML

<extLst>
    <ext uri="{CCE6A557-97BC-4b89-ADB6-D9C93CAAB3DF}" xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main">
        <x14:dataValidations count="1" xmlns:xm="http://schemas.microsoft.com/office/excel/2006/main">
            <x14:dataValidation type="list" allowBlank="1" showInputMessage="1" showErrorMessage="1" errorTitle="Pick Status" error="Please indicate whether the transaction was approved or declined" xr:uid="{A2522356-22CD-4601-A03C-CF3226D99E9C}">
                <x14:formula1>
                    <xm:f>Data!$A$2:$A$3</xm:f>
                </x14:formula1>
                <xm:sqref>E2:E3</xm:sqref>
            </x14:dataValidation>
        </x14:dataValidations>
    </ext>
</extLst>

Docx4j生成的XML

<extLst>
    <ext uri="{CCE6A557-97BC-4b89-ADB6-D9C93CAAB3DF}">
        <x14:dataValidations xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main" count="1">
            <dataValidation type="list" allowBlank="true" showInputMessage="true" showErrorMessage="true" errorTitle="Pick Status" error="Please indicate whether the transaction was approved or declined" sqref="E2:E2">
                <formula1>Data!$A$2:$A$3</formula1>
            </dataValidation>
        </x14:dataValidations>
    </ext>
</extLst>

主要差异 #

• 布尔属性值为true而非1
• sqref是属性而非dataValidation内的标签
• 公式格式缺少<xm:f>包裹

解决建议 #

1. 修正布尔属性值为1/0 #

Excel对x14命名空间下的数据验证布尔属性要求用1（代表true）和0（代表false），而非标准布尔字符串。可以通过配置JAXB Marshaller强制转换：

Marshaller marshaller = jc.createMarshaller();
marshaller.setProperty("com.sun.xml.bind.marshaller.BooleanAdapter", new BooleanAdapter() {
    @Override
    public String marshal(Boolean v) {
        return v != null && v ? "1" : "0";
    }
});

2. 调整sqref为子元素而非属性 #

x14规范要求sqref作为<xm:sqref>子元素存在，需手动构建该元素并添加到dataValidation的任意元素列表：

// 创建xm命名空间的sqref元素
QName sqrefQName = new QName("http://schemas.microsoft.com/office/excel/2006/main", "sqref", "xm");
CTSqref sqref = CTSqref.Factory.newInstance();
sqref.setArray(new String[]{"E2:E" + (maxRow + 1)});
JAXBElement<CTSqref> sqrefElement = new JAXBElement<>(sqrefQName, CTSqref.class, sqref);

// 添加到dataValidation并移除原属性
validation.getAny().add(sqrefElement);
validation.unsetSqref();

3. 修正公式格式为<x14:formula1>包含<xm:f> #

Excel要求公式包裹在<xm:f>标签内，外层嵌套<x14:formula1>，需手动构建该结构：

// 创建xm命名空间的f元素
QName fQName = new QName("http://schemas.microsoft.com/office/excel/2006/main", "f", "xm");
CTF f = CTF.Factory.newInstance();
f.setStringValue("Data!$A$2:$A$3");
JAXBElement<CTF> fElement = new JAXBElement<>(fQName, CTF.class, f);

// 创建x14命名空间的formula1元素
QName formula1QName = new QName("http://schemas.microsoft.com/office/spreadsheetml/2009/9/main", "formula1", "x14");
CTDataValidationFormula1 formula1 = CTDataValidationFormula1.Factory.newInstance();
formula1.getContent().add(fElement);
JAXBElement<CTDataValidationFormula1> formula1Element = new JAXBElement<>(formula1QName, CTDataValidationFormula1.class, formula1);

// 添加到dataValidation并移除原公式设置
validation.getAny().add(formula1Element);
validation.unsetFormula1();

4. 补充命名空间声明 #

在<ext>标签上添加x14和xm命名空间声明，与手动生成的XML对齐：

extension.addNewAttribute("xmlns:x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
extension.addNewAttribute("xmlns:xm", "http://schemas.microsoft.com/office/excel/2006/main");

内容的提问来源于stack exchange，提问作者Ben Ketteridge