Bug 51848 - Incorrect DataValidation attributes created in XML
Summary: Incorrect DataValidation attributes created in XML
Status: RESOLVED LATER
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.8-dev
Hardware: PC Windows Vista
: P2 major (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2011-09-19 22:31 UTC by Phil
Modified: 2019-03-10 16:18 UTC (History)
0 users



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Phil 2011-09-19 22:31:01 UTC
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>
Comment 1 Dominik Stadler 2016-08-15 13:44:13 UTC
Unfortunately the code-snippet contains lots of references to other code which is not included, so it is hard to actually reproduce this. 

Progress on this can be sped up a lot if you can provide a self-contained snippet of code together with any sample-file that is used.
Comment 2 Dominik Stadler 2019-03-10 16:18:44 UTC
No more information received for a long time so we cannot fix anything here until we get more. It might also have been fixed via one of the numerous fixed in the meantime.