I have the same problem with POI 3.2 FINAL as described previously, except my
formula in my named range is a little different (and it referred to another
sheet). I tried it like the previous poster, using the named range's name
for the DVConstraint. I tried using the reference from the named range
instead of the name of the named range in the DVConstraint, and that works,
but it defeats the purpose of using a named range. For now, I'm just going
with the reference from the named range for the data validation.
Here's a modified code snippet using the named range followed by the code
that makes it work:
<code>
.....
//This code sets up the named range I am trying to use
//wkbk is the HSSFWorkbook object I am working with
//sheet is the HSSFSheet object I am working with here and is for
namedRanges only E.g. Sheet2
//firstRowInRange and lastRowInRange are HSSFRow objects that were
created earlier
//firstCellInRange and lastCellInRange are HSSFCell objects that
were created earlier
HSSFName namedRange = wkbk.createName();
namedRange.setNameName("namedRangeName);
CellReference firstCR = new
CellReference(firstRowInRange.getRowNum(), (int)
firstCellInRange.getCellNum());
CellReference lastCR = new CellReference(lastRowInRange.getRowNum(),
(int) lastCellInRange.getCellNum());
AreaReference ref = new AreaReference(firstCR, lastCR);
String reference1 = ref.formatAsString();
String reference = wkbk.getSheetName(wkbk.getSheetIndex(sheet)) +
"!" + ref.formatAsString();
namedRange.setReference(reference);
.....
//different method - The sheet object is not the same sheet as
above, but is referring to the named range on that sheet. E.g. Sheet1
CellRangeAddressList range = new CellRangeAddressList(startRow,
endRow, colNo, colNo);
DVConstraint constraint =
DVConstraint.createFormulaListConstraint("namedRangeName");
HSSFDataValidation periodValidation = new
HSSFDataValidation(range,constraint);
periodValidation.setSuppressDropDownArrow(false);
periodValidation.setShowErrorBox(false);
periodValidation.setShowPromptBox(false);
periodValidation.setEmptyCellAllowed(true);
sheet.addValidationData(periodValidation);
.....
</code>
Here's the code using the reference for the namedRange I am trying to use
(this code replace the line DVConstraint constraint =
DVConstraint.createFormulaListConstraint("namedRangeName");):
<code>
HSSFName periodTypeName =
wkbk.getNameAt(wkbk.getNameIndex("namedRangeName"));
String periodTypeRef = periodTypeName.getReference();
DVConstraint constraint =
DVConstraint.createFormulaListConstraint(periodTypeRef);
</code>
I hope this helps you jhummer, but I also hope that POI fixes it so we can
use a named range
--
View this message in context:
http://www.nabble.com/Named-Range-not-working-tp21182105p21301551.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
