I am certain that there must be another way around this problem but Imust
admit that I cannot see it.
This morning, I quickly ran a file containing a data validation through the
BiffViewer utility and found that the validation records are recovered as
the following shows;
Offset=0x000006A5(1701) recno=94 sid=0x01B2 size=0x0012(18)
[DVAL]
.options = 0
.horizPos = 0
.vertPos = 0
.comboObjectID = ffffffff
.DVRecordsNumber = 1
[/DVAL]
Offset=0x000006BB(1723) recno=95 sid=0x01BE size=0x002B(43)
[DV]
options=c0103 title-prompt='\0' title-error='\0' text-prompt='\0'
text-error='\0'
Formula 1:
class org.apache.poi.hssf.record.formula.NamePtg
Formula 2:
Regions: (0,0,1,1)
[/DV]
Next, I tried to run the createRecords() method to recover an array of type
Record that I hoped I would be able to iterate through in order to get at
the DVAL and DV entries but it threw an exception telling me that the record
size was incorrect. Maybe that is a problem unique to 'my' sample file and
it may be worth your while, therefore, running the same method just to
check.
The information is there, I am sure of it, but getting at it is a challenge.
The next place that I am going to look at is the POIFSFileSystem class. I
hope that will offer me a way in to the records - the raw data read from the
Excel file as it were. As always, will let you know if I find anything
interesting.
Yours
Mark B
Nagineni wrote:
>
> 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-tp23921169p23937805.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]
