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-tp23921169p23976272.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]
