You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by AnuB <ab...@hotmail.com> on 2008/08/27 23:34:09 UTC
Re: query regarding HSSFDataValidation
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-tp18706750p19190204.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
Re: query regarding HSSFDataValidation
Posted by Nick Burch <ni...@torchbox.com>.
On Mon, 1 Sep 2008, Pierre Lavignotte wrote:
> Here is a patch file for my update in HSSFSheet.java.
Thanks, I've applied much of this to svn. (Some parts look to have already
been fixed). It'd be good to get a unit test for this support, so we can
make sure it doesn't get broken in the future. Could you put together a
small class that uses this functionality, which we can use as the basis
for a unit test?
Thanks
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: query regarding HSSFDataValidation
Posted by Pierre Lavignotte <pi...@gmail.com>.
Hi,
Here is a patch file for my update in HSSFSheet.java.
I used POI 3.1 FINAL source.
Changes are :
- Added the NamePtg support in the HSSFDataValidation formula,
- Replaced the deprecated calls to FormulaParser.parse(String formula)
by static method FormulaParser.parse(String formula, HSSFWorkbook
workbook),
- Created new method getDVRecords() so you can get the existing
DataValidation Records and extend them to other cells.
Pierre
On Thu, Aug 28, 2008 at 7:32 PM, Pierre Lavignotte
<pi...@gmail.com> wrote:
> If I can find what is a patch exactly, yes :)
>
>
> On Thu, Aug 28, 2008 at 7:06 PM, Nick Burch <ni...@torchbox.com> wrote:
>> On Thu, 28 Aug 2008, Pierre Lavignotte wrote:
>>>
>>> Here is a modified version of HSSFSheet from POI 3.1-FINAL
>>
>> Are you able to produce a patch of your changes? If they're useful to people
>> playing with data validation, then we can apply them to svn
>>
>> Nick
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>
>
>
> --
> Cordialement,
> Pierre Lavignotte
> Ingénieur Conception & Développement
> http://pierre.lavignotte.googlepages.com
>
--
Cordialement,
Pierre Lavignotte
Ingénieur Conception & Développement
http://pierre.lavignotte.googlepages.com
Re: query regarding HSSFDataValidation
Posted by Pierre Lavignotte <pi...@gmail.com>.
If I can find what is a patch exactly, yes :)
On Thu, Aug 28, 2008 at 7:06 PM, Nick Burch <ni...@torchbox.com> wrote:
> On Thu, 28 Aug 2008, Pierre Lavignotte wrote:
>>
>> Here is a modified version of HSSFSheet from POI 3.1-FINAL
>
> Are you able to produce a patch of your changes? If they're useful to people
> playing with data validation, then we can apply them to svn
>
> Nick
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
--
Cordialement,
Pierre Lavignotte
Ingénieur Conception & Développement
http://pierre.lavignotte.googlepages.com
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: query regarding HSSFDataValidation
Posted by Nick Burch <ni...@torchbox.com>.
On Thu, 28 Aug 2008, Pierre Lavignotte wrote:
> Here is a modified version of HSSFSheet from POI 3.1-FINAL
Are you able to produce a patch of your changes? If they're useful to
people playing with data validation, then we can apply them to svn
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: query regarding HSSFDataValidation
Posted by Pierre Lavignotte <pi...@gmail.com>.
Hi again,
I went into the code and solved the issue.
Here is a modified version of HSSFSheet from POI 3.1-FINAL
In method addValidationData(HSSFDataValidation obj_validation), I've
added the following line, so the NamedArea are correctly handled :
ptg[k] instanceof org.apache.poi.hssf.record.formula.NamePtg
Now you can use named ranges to target list of values on other sheets.
It also works if I add the Area3DPtg and use direct sheet references
in formulas but Excel doesn't allow that (it rejects the formula when
you go to Data => Validation and click OK).
I also added a method getDVRecord() so you can get the existing data
validation rules and go into their HSSFCellRangeAdress if you need.
I guess you have to call
DVRecord.getCellRangeAddress().addADDRStructure() if you need to
duplicate a data validation rule but I didn't try yet.
Pierre
On Thu, Aug 28, 2008 at 11:06 AM, Pierre Lavignotte
<pi...@gmail.com> wrote:
> 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 <mb...@solbright.com> 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 <mb...@solbright.com> 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 <mb...@solbright.com> 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: user-unsubscribe@poi.apache.org
>>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Cordialement,
>>>>> Pierre Lavignotte
>>>>> Ingénieur Conception & Développement
>>>>> http://pierre.lavignotte.googlepages.com
>>>>>
>>>>> ---------------------------------------------------------------------
>>>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>
>>>>>
>>>>>
>>>>
>>>> --
>>>> 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: user-unsubscribe@poi.apache.org
>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>
>>>>
>>>
>>>
>>>
>>> --
>>> Cordialement,
>>> Pierre Lavignotte
>>> Ingénieur Conception & Développement
>>> http://pierre.lavignotte.googlepages.com
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>> For additional commands, e-mail: user-help@poi.apache.org
>>>
>>>
>>>
>>
>> --
>> 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: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>
>
>
> --
> Cordialement,
> Pierre Lavignotte
> Ingénieur Conception & Développement
> http://pierre.lavignotte.googlepages.com
>
--
Cordialement,
Pierre Lavignotte
Ingénieur Conception & Développement
http://pierre.lavignotte.googlepages.com
Re: query regarding HSSFDataValidation
Posted by Pierre Lavignotte <pi...@gmail.com>.
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 <mb...@solbright.com> 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 <mb...@solbright.com> 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 <mb...@solbright.com> 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: user-unsubscribe@poi.apache.org
>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> Cordialement,
>>>> Pierre Lavignotte
>>>> Ingénieur Conception & Développement
>>>> http://pierre.lavignotte.googlepages.com
>>>>
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>
>>>>
>>>>
>>>
>>> --
>>> 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: user-unsubscribe@poi.apache.org
>>> For additional commands, e-mail: user-help@poi.apache.org
>>>
>>>
>>
>>
>>
>> --
>> Cordialement,
>> Pierre Lavignotte
>> Ingénieur Conception & Développement
>> http://pierre.lavignotte.googlepages.com
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>>
>
> --
> 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: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
--
Cordialement,
Pierre Lavignotte
Ingénieur Conception & Développement
http://pierre.lavignotte.googlepages.com
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: query regarding HSSFDataValidation
Posted by mbhatt <mb...@solbright.com>.
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 <mb...@solbright.com> 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 <mb...@solbright.com> 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: user-unsubscribe@poi.apache.org
>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>
>>>>
>>>
>>>
>>>
>>> --
>>> Cordialement,
>>> Pierre Lavignotte
>>> Ingénieur Conception & Développement
>>> http://pierre.lavignotte.googlepages.com
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>> For additional commands, e-mail: user-help@poi.apache.org
>>>
>>>
>>>
>>
>> --
>> 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: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>
>
>
> --
> Cordialement,
> Pierre Lavignotte
> Ingénieur Conception & Développement
> http://pierre.lavignotte.googlepages.com
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
--
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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: query regarding HSSFDataValidation
Posted by Pierre Lavignotte <pi...@gmail.com>.
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 <mb...@solbright.com> 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 <mb...@solbright.com> 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: user-unsubscribe@poi.apache.org
>>> For additional commands, e-mail: user-help@poi.apache.org
>>>
>>>
>>
>>
>>
>> --
>> Cordialement,
>> Pierre Lavignotte
>> Ingénieur Conception & Développement
>> http://pierre.lavignotte.googlepages.com
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>>
>
> --
> 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: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
--
Cordialement,
Pierre Lavignotte
Ingénieur Conception & Développement
http://pierre.lavignotte.googlepages.com
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: query regarding HSSFDataValidation
Posted by mbhatt <mb...@solbright.com>.
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 <mb...@solbright.com> 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: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>
>
>
> --
> Cordialement,
> Pierre Lavignotte
> Ingénieur Conception & Développement
> http://pierre.lavignotte.googlepages.com
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
--
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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: query regarding HSSFDataValidation
Posted by Pierre Lavignotte <pi...@gmail.com>.
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 <mb...@solbright.com> 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: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
--
Cordialement,
Pierre Lavignotte
Ingénieur Conception & Développement
http://pierre.lavignotte.googlepages.com
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: query regarding HSSFDataValidation
Posted by mbhatt <mb...@solbright.com>.
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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org