How do I add validation rules to cells in XLSX using Docx4java

29 Views Asked by At

I'm using Docx4j (v 4.11.9, with Jakarta 3.0.1) to take a template spreadsheet, add new rows, and output a new copy. That much is working great.

However, I have the following code to create a validation rule on a range of cells in a spreadsheet. The 'Data' sheet already exists in the template, and I want to validate that the values in cells E2:Ex (where x is the number of new data rows).

        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);

However,

  1. the file won't open cleanly in Excel,
  2. The output XML is different from the XML Excel generates if I attach the rules by hand.

Excel's output:

    <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>

Java output when marshalled by Docx4j:

    <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>

The principal differences are that I can see are:

  1. the boolean attributes are 'true' instead of '1'
  2. sqref is an attribute, not a tag in the dataValidation stanza
  3. I'm not confident that the formula is correctly ... formulated (though the range reference is correct).

Any suggestions?

0

There are 0 best solutions below