Hey Mark,
Excellent !!!.I hope this is remedy for this issue.Let me dig into this and
let you know.
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-tp23921169p23977750.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]
