DO NOT REPLY [Bug 45504] New: HSSF - Unsupported Names in LIST data validation formulas

[bu...@apache.org]

https://issues.apache.org/bugzilla/show_bug.cgi?id=45504

           Summary: HSSF - Unsupported Names in LIST data validation
                    formulas
           Product: POI
           Version: 3.0-dev
          Platform: PC
        OS/Version: Windows XP
            Status: NEW
          Severity: normal
          Priority: P2
         Component: HSSF
        AssignedTo: dev@poi.apache.org
        ReportedBy: konrad.mrozek@tietoenator.com


Created an attachment (id=22330)
 --> (https://issues.apache.org/bugzilla/attachment.cgi?id=22330)
Patch

You cannot set source of dropdown in List Data Validation type. To reproduce
it:

1. create excel with named area NAMEDAREA.
2. load it using POI.
3. create HSSFDataValidation object.
4. set validation type to DATA_TYPE_LIST.
5. set first formula to "NAMEDAREA".
6. save workbook to file.
7. open excel.
8. try to type anything to cell. (result: excel crashes).
9. try to expand list for cell. (it cannot be expaned).

The reason for this bug is wrong formula class of NAME token. It should be
reference, not value. I attached patch, which solves the problem.


-- 
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: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org

DO NOT REPLY [Bug 45504] HSSF - Unsupported Names in LIST data validation formulas

[bu...@apache.org]

https://issues.apache.org/bugzilla/show_bug.cgi?id=45504


Josh Micich <jo...@gildedtree.com> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|NEW                         |RESOLVED
         Resolution|                            |FIXED




--- Comment #1 from Josh Micich <jo...@gildedtree.com>  2008-08-03 16:29:05 PST ---
Fixed with bug 44953.
The junit (TestDataValidation) was augmented to include some tests for named
ranges.

Your patch works for this particular case (a formula with a single named
range), however updating RVA (operand class) settings is rather complex (rules
are applied via the tree structure, not to isolated tokens).  What was required
was to set the operand type of the tree root node.  There are different rules
for determining the RVA values of nodes elsewhere in the tree.

Note - the API was changed a little bit .  The following code should now do
what you described:

HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("Sheet1");
HSSFName namedRange = wb.createName();
namedRange.setNameName("NAMEDAREA");
namedRange.setReference("Sheet1!$A$2:$A$7");

// add list data to cells A1:A9
for (int i = 0; i < 10; i++) {
        sheet.createRow(i + 0).createCell((short) 0).setCellValue(new
HSSFRichTextString("Data " + i));
}

// Add data validation to cell C1
DVConstraint dc = DVConstraint.createFormulaListConstraint("NAMEDAREA");
CellRangeAddressList cral = new CellRangeAddressList(0, 0, 2, 2); // cell "C1"
HSSFDataValidation dv = new HSSFDataValidation(cral, dc);
dv.setEmptyCellAllowed(false);
dv.setErrorStyle(HSSFDataValidation.ErrorStyle.STOP);
dv.setShowErrorBox(true);
dv.createErrorBox("Error", "invalid entry");
dv.setSuppressDropDownArrow(false);
sheet.addValidationData(dv);

try {
        OutputStream os = new
FileOutputStream("c:/temp/testDV-namedRange.xls");
        wb.write(os);
        os.close();
} catch (IOException e) {
        throw new RuntimeException(e);
}


-- 
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: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org