Bug 45504 - HSSF - Unsupported Names in LIST data validation formulas
Summary: HSSF - Unsupported Names in LIST data validation formulas
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.0-dev
Hardware: PC Windows XP
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2008-07-30 05:18 UTC by Konrad Mrożek
Modified: 2008-08-03 16:29 UTC (History)
0 users

Patch (773 bytes, patch)
2008-07-30 05:18 UTC, Konrad Mrożek
Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
Description Konrad Mrożek 2008-07-30 05:18:48 UTC
Created attachment 22330 [details]

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.
Comment 1 Josh Micich 2008-08-03 16:29:05 UTC
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();

// 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.createErrorBox("Error", "invalid entry");

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