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,
- the file won't open cleanly in Excel,
- 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:
- the boolean attributes are 'true' instead of '1'
- sqref is an attribute, not a tag in the dataValidation stanza
- I'm not confident that the formula is correctly ... formulated (though the range reference is correct).
Any suggestions?