Hi Mark,
Thanks for the reply ,I tried the same but I'm helpless to get solved this
issue.Might be some other way to get DVRecords in 3.5 version.
Regards,
Naga.
MSB wrote:
>
> 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-tp23921169p23936582.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]
