OK, I have been able to do some digging around and it seems as though my
original answer may have been both wrong and right.
In Version 3.2 final the HSSFSheet class contains a method called
getDVRecords() which the javadoc says gets a List of the DVRecords
associated with the sheet. The DVRecords class - again to quote the javadoc
- "stores data validation settings and a list of cell ranges which contain
these settings"
So, I created a simple Excel workbook with one sheet and a single data
validation. Next, I ran it against this code;
public void getValidation() {
java.io.FileInputStream inputStream = null;
java.util.List validationRecords = null;
java.util.Iterator listIterator = null;
org.apache.poi.hssf.record.DVRecord validationRecord = null;
Object obj = null;
HSSFWorkbook workbook = null;
HSSFSheet sheet = null;
HSSFRow row = null;
HSSFCell cell = null;
HSSFDataValidation dataValidation = null;
try {
inputStream = new java.io.FileInputStream(new
java.io.File("C:/temp/data validation.xls"));
workbook = new HSSFWorkbook(inputStream);
sheet = workbook.getSheetAt(0);
validationRecords = sheet.getDVRecords();
System.out.println(validationRecords.size());
listIterator = validationRecords.iterator();
while(listIterator.hasNext()) {
obj = listIterator.next();
if(obj instanceof org.apache.poi.hssf.record.DVRecord) {
validationRecord =
(org.apache.poi.hssf.record.DVRecord)obj;
System.out.println(validationRecord.toString());
}
}
}
catch(Exception ex) {
System.out.println("Caught an: " + ex.getClass().getName());
System.out.println("Message : " + ex.getMessage());
System.out.println("Stacktrace follows: ");
ex.printStackTrace(System.out);
}
finally {
try {
if(inputStream != null) {
inputStream.close();
}
}
catch(Exception ex) {
}
}
}
and saw quite simply nothing. I had hoped that this line;
System.out.println(validationRecords.size());
would print out the size of the List object. It did, zero!
Thinking that there may be some problem with the data validation Excel
created, I ran some code to generate a data validation with POI;
public void validateCellFromList() {
HSSFWorkbook workbook = null;
HSSFSheet sheet = null;
HSSFSheet dataSheet = null;
HSSFDataValidation numericDataValidation = null;
HSSFDataValidation textDataValidation = null;
HSSFDataValidation dateDataValidation = null;
HSSFName namedRange = null;
HSSFRow row = null;
HSSFCell cell = null;
CellRangeAddressList numericCellAddressList = null;
CellRangeAddressList textCellAddressList = null;
CellRangeAddressList dateCellAddressList = null;
DVConstraint numericDVConstraint = null;
DVConstraint textDVConstraint = null;
DVConstraint dateDVConstraint = null;
File outputFile = null;
FileOutputStream fos = null;
int start_row = 0;
try {
// New Workbook.
outputFile = new File("C:/temp/Cell Validation From List.xls");
fos = new FileOutputStream(outputFile);
workbook = new HSSFWorkbook();
// Add a sheet
sheet = workbook.createSheet("List Validation");
dataSheet = workbook.createSheet("Data Sheet");
row = sheet.createRow(0);
cell = row.createCell(0);
cell.setCellValue(10);
row = sheet.createRow(1);
cell = row.createCell(0);
cell.setCellValue(20);
row = sheet.createRow(2);
cell = row.createCell(0);
cell.setCellValue(30);
namedRange = workbook.createName();
namedRange.setNameName("NAMEDAREA");
namedRange.setReference("'Data Sheet'!$A$1:$A$3");
//=Sheet1!$C$4:$C$7
// Create CellRabngeAddressList objects to place the validations
// into cells A1, A2 and A3.
numericCellAddressList = new CellRangeAddressList(0, 0, 1, 1);
// Explicitly specify the values for both validation objects
// as lists of Strings.
//numericDVConstraint =
DVConstraint.createExplicitListConstraint(new String[]{"$A$1", "$A$2",
"$A$3"});
numericDVConstraint =
DVConstraint.createNumericConstraint(DVConstraint.ValidationType.INTEGER ,
DVConstraint.OperatorType.BETWEEN, "10", "100");
//numericDVConstraint =
DVConstraint.createFormulaListConstraint("NAMEDAREA");
//numericDVConstraint =
DVConstraint.createFormulaListConstraint("=(<=100)");
//numericDVConstraint =
DVConstraint.createFormulaListConstraint("Data Sheet!$A$1:$A$3");
// Specify the values as a range. Can also use other operators
such as gretaer than etc
//dvConstraint =
DVConstraint.createNumericConstraint(DVConstraint.ValidationType.INTEGER ,
DVConstraint.OperatorType.BETWEEN, "100", "110");
// Instantiate the validation objects and place each onto the
worksheet.
numericDataValidation = new
HSSFDataValidation(numericCellAddressList, numericDVConstraint);
numericDataValidation.setSuppressDropDownArrow(false);
sheet.addValidationData(numericDataValidation);
System.out.println(sheet.getDVRecords().size());
// Write the workbook away.
workbook.write(fos);
}
catch(Exception pEx) {
System.out.println("Caught an: " + pEx.getClass().getName());
System.out.println("Message : " + pEx.getMessage());
System.out.println("Stacktrace foillows: ");
pEx.printStackTrace(System.out);
}
finally {
if(fos != null) {
try {
fos.flush();
fos.close();
}
catch(Exception ex) {
// IGNORE //
}
}
}
}
which had that same line - System.out.println(sheet.getDVRecords().size());
- just after I added the validatin to the sheet. Again, it printed out the
value zero.
Try playing with the code yourself to see if you can make it behave.
To summarise, it seems that it should be possible to get at this
information, if you are using version 3.2 as I cannot find the
getDVRecords() method defined in the current version which I think is 3.5
beta 6. Also, the getDVRecords() method is not supported for the XSSFSheet
class.
Sorry I cannot help further.
Yours
Mark B
Nagineni wrote:
>
> Hi,
>
> I'm new to POI.I learned reading excel data from java using POI.
>
> I'm not able to find the way to read list box vlues from the excel
> sheet.Could any one provide me the sample code to read excel sheet list
> box values so that I can dig into more?
>
> I'm helpless from my google search.Please do help me.
>
>
> Regards,
> Naga.
>
>
--
View this message in context:
http://www.nabble.com/sample-code--to-read-excel-listbox-values-tp23921169p23929585.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]
