https://issues.apache.org/bugzilla/show_bug.cgi?id=51848
Bug #: 51848
Summary: Incorrect DataValidation attributes created in XML
Product: POI
Version: 3.8-dev
Platform: PC
OS/Version: Windows Vista
Status: NEW
Severity: major
Priority: P2
Component: XSSF
AssignedTo: [email protected]
ReportedBy: [email protected]
Classification: Unclassified
The following code snippet:
String[] concurrenceConstraintValues = new String[]{ "Concur",
"Non-Concur", "N/A" };
XSSFDataValidationConstraint concurrenceConstraint = new
XSSFDataValidationConstraint( concurrenceConstraintValues );
CellRangeAddressList concurrenceRangeAddressList = new
CellRangeAddressList( 1, // Ignore the header
row
MAX_ROWS_IN_SHEET - 1, // All rows in sheet
G8_CONCURRENCE_COLUMN_INDEX - 1, // Begin on concurrence column (add one
because Excel does not use zero indexing)
G8_CONCURRENCE_COLUMN_INDEX - 1 ); // End on same column (add one because
Excel does not use zero indexing)
// Setup a validation constraints for limiting the text length in
g8CommentsColumn
XSSFDataValidationConstraint commentsConstraint = new
XSSFDataValidationConstraint( DVConstraint.ValidationType.TEXT_LENGTH,
DVConstraint.OperatorType.BETWEEN,
Integer.toString( 0 ),
Integer.toString( RelConstants.MAX_CONCURRENCE_COMMENT_LENGTH ) );
CellRangeAddressList commentsRangeAddressList = new CellRangeAddressList(
1, // Ignore the header row
MAX_ROWS_IN_SHEET - 1, // All rows in sheet
G8_COMMENTS_COLUMN_INDEX - 1, // Begin on comments column (add one because
Excel does not use zero indexing)
G8_COMMENTS_COLUMN_INDEX - 1 ); // End on same column (add one because Excel
does not use zero indexing)
// Apply the validation constraints to all sheets
String[] sheetNames = new String[]{ getAriTable().getReportName(),
getImiTable().getReportName(),
getMedicalTable().getReportName() };
XSSFSheet sheet;
XSSFDataValidationHelper dataValidationHelper;
DataValidation dataValidation;
for( String sheetName : sheetNames )
{
sheet = workbook.getSheet( sheetName );
if( sheet != null )
{
dataValidationHelper = new XSSFDataValidationHelper( sheet );
// Create and add a DataValidation for g8Concurrence
dataValidation = dataValidationHelper.createValidation(
concurrenceConstraint, concurrenceRangeAddressList );
dataValidation.setSuppressDropDownArrow( false );
dataValidation.setEmptyCellAllowed( true );
dataValidation.setErrorStyle( DataValidation.ErrorStyle.STOP );
dataValidation.createErrorBox(
getAriTable().getG8ConcurrenceColumn().getHeader(),
"Valid values include: " +
Arrays.deepToString( concurrenceConstraintValues ) );
sheet.addValidationData( dataValidation );
// Create and add a DataValidation for g8Comments
dataValidation = dataValidationHelper.createValidation(
commentsConstraint, commentsRangeAddressList );
dataValidation.setSuppressDropDownArrow( true );
dataValidation.setEmptyCellAllowed( true );
dataValidation.setErrorStyle( DataValidation.ErrorStyle.STOP );
dataValidation.createErrorBox(
getAriTable().getG8CommentsColumn().getHeader(),
"Text length cannot exceed " +
Integer.toString( RelConstants.MAX_CONCURRENCE_COMMENT_LENGTH ) + "
characters." );
sheet.addValidationData( dataValidation );
}
}
produces the XML snippet:
<dataValidations count="2">
<dataValidation type="list"
sqref="O2:O1048576"
errorStyle="stop"
allowBlank="true"
showDropDown="true"
errorTitle="G8 Concurrence"
error="Valid values include: [Concur, Non-Concur, N/A]">
<formula1>"Concur,Non-Concur,N/A"</formula1>
</dataValidation>
<dataValidation type="textLength"
operator="between"
sqref="P2:P1048576"
errorStyle="stop"
allowBlank="true"
errorTitle="G8 Comments"
error="Text length cannot exceed 500 characters.">
<formula1>0</formula1>
<formula2>500</formula2>
</dataValidation>
</dataValidations>
Data Validations are created but not enabled in Excel. Manually enabling them
in Excel produces the following XML snippet:
<dataValidations count="2">
<dataValidation type="list"
allowBlank="1"
showErrorMessage="1"
errorTitle="G8 Concurrence"
error="Valid values include: [Concur, Non-Concur, N/A]"
sqref="O2:O1048576">
<formula1>"Concur,Non-Concur,N/A"</formula1>
</dataValidation>
<dataValidation type="textLength"
allowBlank="1"
showErrorMessage="1"
errorTitle="G8 Comments"
error="Text length cannot exceed 500 characters."
sqref="P2:P1048576">
<formula1>0</formula1>
<formula2>500</formula2>
</dataValidation>
</dataValidations>
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
