That's good news!
Another thought did occur to me this morning. It may be better to organise
the data for each validation into a single row rather than a single column
as my example showed. That way, it would be far easier both to create the
data rows and store them away again. All you would need to do is change the
formula so that rather than saying somehting like "'Data Sheet'!$A$2:$A$30",
it would be "'Data Sheet'!$B$1:$Z$1" for example. Whether this would be
better from the user's perspective however, I cannot be sure.
I also forgot to say that the named area is not compulsory. You can pass the
"'Data Sheet'!$B$1:$Z$1" string to the create method of the DVConstraint
class as far as I remember, like this;
dvConstraint = DVConstraint.createFormulaListConstraint("'Data
Sheet'!$B$1:$Z$1");
Then you will not need to create a named area at all. If you are creating
large worksheets, memory can be a problem and reducing the number of objects
may often help.
All the best and if there is anything else we can help out with, just post a
message to the list.
Yours
Mark B
Nagineni wrote:
>
>
> Mark, tons of thanks :).It is working perfectly.I can proceed to work with
> out stop now.Only thing is to have extra sheet for the listbox data.
>
> Regards,
> Naga.
>
>
> MSB wrote:
>>
>> Ah, well they may well be a work around for you here. Recently, I did
>> quite a bit of digging around and found that it was possible to dreate a
>> data validation using POI that looked to a named area on another sheet
>> for it's data. This morning, I just performed a little test for which I
>> used Excel but an fairly certain it could work here as well.
>>
>> What I did was move to Sheet2 and create a very large named area, far
>> larger than was necessary to contain the data I needed for the
>> valiadation. Next, I went to Sheet1 and created a validation using that
>> data. Excel presented me with a list that included just the items I had
>> entered, no empty spaces for the additional cells I included in the named
>> area. Also, it was possible to add to the data in the named area and see
>> those values appear in the list immediately.
>>
>> Now, I am working on an update to the Quick Guide that shows how to
>> create data validations using POI. It is perfectly possible to do this
>> sort of thing using the API;
>>
>> HSSFWorkbook workbook = new HSSFWorkbook();
>> HSSFSheet sheet = workbook.createSheet("Data Validation");
>> // Insert a sheet that will hold the data JUST for the
>> // lists in the data validation(s). Add a named area
>> // and indicate the cells it refers to.
>> HSSFSheet dataSheet = workbook.createSheet("Data Sheet");
>> HSSFNamedRange namedRange = workbook.createName();
>> namedRange.setNameName("list1");
>> namedRange.setRefersToFormula("'Data Sheet'!$A$2:$A$300");
>> // The CellRangeAddressList indicates the cell that will
>> // contain the data validation. In this case it is cell
>> // A1.
>> CellRangeAddressList addressList = new CellRangeAddressList(
>> 0, 0, 0, 0);
>> dvConstraint = DVConstraint.createFormulaListConstraint("list1");
>> HSSFDataValidation dataValidation = new HSSFDataValidation
>> (addressList, dvConstraint);
>> datavalidation.setSuppressDropDownArrow(false);
>> sheet.addValidationData(dataValidation);
>>
>> This will create a 'space' on the Data Sheet that is almost 250 rows deep
>> where you and the user can write data for the lists away to. It would be
>> ferfectly possible to create many of these areas on the Data Sheet, name
>> each one differently and allow the user to add or remove data from the
>> lists. If you look at the range of cells passed to the
>> setRefersToFormula() method, you can see that it runs from cells A2 to
>> A300; I thought that cell A1 could contain a column heading telling the
>> user what the list of values was for. Depending on the technical
>> capabilities of your users, this may even be an easier technique for them
>> to use.
>>
>> When you read the database, you can populate the lists, when you write
>> the file back again, you can read the lists on the data sheets and write
>> the values back to the database. If you think that it is necessary so to
>> do, you can even protect the sheet to prevent un-authorised changes to
>> the lists contents.
>>
>> That MAY be one way around the problem for you.
>>
>> Yours
>>
>> Mark B
>>
>> PS. I put together that piece of code from a number of examples in the
>> Quick Guide update. I have not tested it as it currently stands but each
>> component part I do know works. Anyway, if you have any problems, just
>> let me know. The code will work with version 3.5, I amnot sure about
>> earlier versions as I dod not know for certain when the
>> HSSFDataValidation classes constructor was changed.
>>
>>
>>
>> Nagineni wrote:
>>>
>>> Hi Mark,
>>>
>>> Great thanks for the response and the help you are doing to get resolved
>>> this issue.
>>> Here is my requirement.
>>> 1.Want to dump values from db to excel file.
>>> some of the fields have multiple values so want to place these values
>>> in listbox.
>>> Here is very basic code I have written
>>>
>>> public class ExcelListDemo{
>>> /**
>>> * @param args
>>> */
>>> public static void main(String[] args) {
>>> // New Workbook.
>>> File outputFile = new File("C:/Documents and
>>> Settings/nravilla/Desktop/temp.xls");
>>> try {
>>> FileOutputStream fos = new FileOutputStream(outputFile);
>>> HSSFWorkbook workbook = new HSSFWorkbook();
>>> HSSFSheet sheet = workbook.createSheet("List Sheet");
>>> String[] strFormula = new String[] { "100", "200", "300", "400",
>>> "500" };
>>> CellRangeAddressList addressList = new CellRangeAddressList();
>>> addressList.addCellRangeAddress(0, 0, 1, 2);
>>> DVConstraint constraing =
>>> DVConstraint.createExplicitListConstraint(strFormula);
>>> HSSFDataValidation dataValidation = new
>>> HSSFDataValidation(addressList, constraing);
>>> dataValidation.setEmptyCellAllowed(false);
>>> dataValidation.setShowPromptBox(true);
>>> dataValidation.setSuppressDropDownArrow(false);
>>> dataValidation.createErrorBox("Invalid input !", "Something is
>>> wrong. check condition!");
>>> sheet.addValidationData(dataValidation);
>>> workbook.write(fos);
>>> } catch (Exception e) {
>>> System.out.println(e);
>>> }
>>> }
>>> }
>>>
>>> 2.so once excel sheet has listbox of values ,user can edit to enter more
>>> values.(FYI with the above lines of code user is not able to enter
>>> values in the listbox.I'm looking into that issue as well.)
>>>
>>> so assue the listbox values 100,200...500.Then use can add
>>> 600,700,....1000.
>>>
>>> 3.When I import this sheet ,I shoud be able to store these
>>> 600,700...1000 into databse.
>>>
>>> Please let me know if you need more informaion on this.
>>>
>>> Regards,
>>> Naga.
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> MSB wrote:
>>>>
>>>> Just a message to let you know that I have not been able to find an
>>>> answer to your question yet and can only hope that this is not holding
>>>> up a project. Tomorrow is an office day for me so I will be able to
>>>> spend some time digging around I hope and will let you know if I do
>>>> find an answer.
>>>>
>>>> Can you give me a rough idea of what you are trying to achieve with POI
>>>> please? If it does not prove to be possible to get at the contents of
>>>> the list, then we may need to look at possible alternative approaches
>>>> to solving your problem and they do exist - albeit that they have other
>>>> problems that may rule them out; OLE for example only works on Windows
>>>> platforms, cannot be used in a client server architecture and has no
>>>> way to catch and handle errors gracefully.
>>>>
>>>> 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-tp23921169p23981325.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]
