poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Nagineni <naganirange...@yahoo.com>
Subject Re: sample code to read excel listbox values
Date Thu, 11 Jun 2009 09:03:21 GMT

Hey Mark,

Excellent !!!.I hope this is remedy for this issue.Let me dig into this and
let you know.


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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

View raw message