Yes it works, I had a stupid IF before my code that was checking the
cell in C2 :)
But your HSSFName is not usefull and is not evaluated correctly if I
use it in the strFormula.
Pierre
On Thu, Aug 28, 2008 at 10:39 AM, mbhatt <[EMAIL PROTECTED]> wrote:
>
> Hi,
>
> The following snippet works with version 3.1 Final. I am using just 2 cells
> A1, A2 instead of A1-A10. You will see a drop down in B1.
>
>
> HSSFSheet sheet = wb.getSheet("Sheet1");
> HSSFName namedRange = wb.createName();
> namedRange.setNameName("NAMEDAREA");
> namedRange.setReference("Sheet1!$A$1:$A$2");
>
> int start_row = (short)0;
> String strFormula = "$A$1:$A$2";
> HSSFDataValidation data_validation = new
>
> HSSFDataValidation((short)(start_row),(short)1,(short)(start_row),(short)1);
>
> data_validation.setDataValidationType(HSSFDataValidation.DATA_TYPE_LIST);
> data_validation.setFirstFormula(strFormula);
> data_validation.setSecondFormula(null);
> data_validation.setExplicitListFormula(true);
> data_validation.setSurppressDropDownArrow(false);
> data_validation.setEmptyCellAllowed(true);
> data_validation.setShowPromptBox(false);
> data_validation.createErrorBox("Invalid input !", "Something is
> wrong. check condition !");
> sheet.addValidationData(data_validation);
>
> Pierre, can you try running the above code with the latest 3.1 version?
>
> Thanks,
> Medha
>
>
> Pierre Lavignotte wrote:
>>
>> I just tried again with a new workbook from scratch.
>> I filled A1 to A10 with values from 1 to 10 and set the value 3 in B1.
>>
>> Then I use your example :
>>
>> String strFormula = "$A$1:$A$10";
>> int start_row = (short)0;
>> HSSFDataValidation data_validation = new HSSFDataValidation((short)
>> (start_row),(short)1,(short)(start_row),(short)1);
>> data_validation.setDataValidationType (HSSFDataValidation.DATA_TYPE_LIST);
>> data_validation.setFirstFormula(strFormula);
>> data_validation.setSecondFormula(null);
>> data_validation.setExplicitListFormula(true);
>> data_validation.setSurppressDropDownArrow(false);
>> data_validation.setEmptyCellAllowed(true);
>> data_validation.setShowPromptBox(false);
>> data_validation.createErrorBox("Invalid input !", "Something is wrong
>> ; check condition !");
>> data_validation.createPromptBox("Hi , dear user !", "So , you just
>> selected me ! Thanks !");
>> sheet.addValidationData(data_validation);
>>
>> Excel doesn't crash anymore but there is no drop down list in B1...
>>
>> But it works if I set a value in C2, the drop down appears and is
>> correctly filled !!!!
>>
>> If I use a reference to another sheet, the list is empty, but this is
>> not allowed by Excel (I use Excel 2003).
>> If I use a named area reference, the list is empty but if I open the
>> Data => Validation menu and just click OK, the list is correctly
>> filled.
>>
>> I use build poi-3.5-beta1-20080718.
>>
>> To be continued...
>> Pierre
>>
>> On Thu, Aug 28, 2008 at 9:20 AM, mbhatt <[EMAIL PROTECTED]> wrote:
>>>
>>> Hi Pierre,
>>>
>>> Make sure that your formula in the data validation is correct (ie. valid
>>> cell references ), also it wouldn't work across the sheets. So if u r
>>> creating a data validation in a sheet for which you are populating the
>>> data
>>> from another sheet, then it wouldn't work.
>>>
>>>
>>>
>>> Pierre Lavignotte wrote:
>>>>
>>>> Hi,
>>>>
>>>> I'm also looking for Data Validation with list of values.
>>>>
>>>> mbhatt, I tried your exemple but the generated workbook make Excel
>>>> crash.
>>>> What can I be missing ?
>>>>
>>>> Thanks,
>>>> Pierre
>>>>
>>>> On Thu, Aug 28, 2008 at 6:27 AM, mbhatt <[EMAIL PROTECTED]> wrote:
>>>>>
>>>>> Hi,
>>>>>
>>>>> No, this is still a bug that is existing in the 3.1 version. They
>>>>> haven't
>>>>> fixed that yet.
>>>>>
>>>>> Thanks.
>>>>>
>>>>>
>>>>> AnuB wrote:
>>>>>>
>>>>>> Hi,
>>>>>> Did you find any solution for the issue. I am facing the same
>>>>>> problem.
>>>>>> I am using 3.1 final version of apache poi api which is the latest
>>>>>> release. But I still have the issue.
>>>>>> Thanks in Advance
>>>>>>
>>>>>>
>>>>>> mbhatt wrote:
>>>>>>>
>>>>>>> Hi,
>>>>>>>
>>>>>>> I am trying to use HSSFDataValidation to generate lists in my
>>>>>>> workbook
>>>>>>> dynamically. The drop-down is populated correctly when i use
>>>>>>> cell/area
>>>>>>> references that are within the same sheet. But if i use a formula
>>>>>>> that
>>>>>>> contains another sheet name - e.g. "sheet2!$a$1:$a$10" , then the
>>>>>>> drop
>>>>>>> down doesn't work.
>>>>>>>
>>>>>>> Following is the code snippet that I am using:
>>>>>>>
>>>>>>> String strFormula = "metadata!$A$1:$A$10";
>>>>>>>
>>>>>>> int start_row = (short)0;
>>>>>>> HSSFDataValidation data_validation = new
>>>>>>>
>>>>>>> HSSFDataValidation((short)(start_row),(short)0,(short)(start_row),(short)0);
>>>>>>>
>>>>>>> data_validation.setDataValidationType(HSSFDataValidation.DATA_TYPE_LIST);
>>>>>>> data_validation.setFirstFormula(strFormula);
>>>>>>> data_validation.setSecondFormula(null);
>>>>>>> data_validation.setExplicitListFormula(false);
>>>>>>> data_validation.setSurppressDropDownArrow(false);
>>>>>>> data_validation.setEmptyCellAllowed(false);
>>>>>>> data_validation.setShowPromptBox(false);
>>>>>>> data_validation.createErrorBox("Invalid input !",
>>>>>>> "Something
>>>>>>> is wrong ; check condition !");
>>>>>>> first_sheet.addValidationData(data_validation);
>>>>>>>
>>>>>>> Can anyone please help me on this?
>>>>>>>
>>>>>>> Thanks.
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>> --
>>>>> View this message in context:
>>>>> http://www.nabble.com/query-regarding-HSSFDataValidation-tp18706750p19194009.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]
>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> Cordialement,
>>>> Pierre Lavignotte
>>>> Ingénieur Conception & Développement
>>>> http://pierre.lavignotte.googlepages.com
>>>>
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: [EMAIL PROTECTED]
>>>> For additional commands, e-mail: [EMAIL PROTECTED]
>>>>
>>>>
>>>>
>>>
>>> --
>>> View this message in context:
>>> http://www.nabble.com/query-regarding-HSSFDataValidation-tp18706750p19195412.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]
>>>
>>>
>>
>>
>>
>> --
>> Cordialement,
>> Pierre Lavignotte
>> Ingénieur Conception & Développement
>> http://pierre.lavignotte.googlepages.com
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [EMAIL PROTECTED]
>> For additional commands, e-mail: [EMAIL PROTECTED]
>>
>>
>>
>
> --
> View this message in context:
> http://www.nabble.com/query-regarding-HSSFDataValidation-tp18706750p19196505.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]
>
>
--
Cordialement,
Pierre Lavignotte
Ingénieur Conception & Développement
http://pierre.lavignotte.googlepages.com
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
