Using HSSFDataValidation for list constraints

["Weber, Christian" <Ch...@ids-scheer.com>]

Hi all,

 

I want to add an HSSFDataValidation to a sheet on my excel workbook. I
found already some code examples where an HSSFDataValidation was created
by a no-arg constructor but this seems to be not possible anymore under
3.2-FINAL.

 

So instead I used this code:

 

DVConstraint constraint =
DVConstraint.createExplicitListConstraint(excelListValues);

            CellRangeAddressList cellRange = new CellRangeAddressList(
cell.getRowIndex(),

 
cell.getColumnIndex(),

 
cell.getRowIndex(),

 
cell.getColumnIndex() );

            HSSFDataValidation dataValidation = new
HSSFDataValidation(cellRange, constraint);

            cell.getSheet().addValidationData(dataValidation);

 

for a given HSSFCell cell.

 

But when I open the finished XLS there is no list constraint set for the
cell. Did I forget to call some other method to activate the constraint
or something?

 

 

Thanks in advance

Best regards

 

Christian

Re: Using HSSFDataValidation for list constraints

[Christian <ch...@ids-scheer.com>]

Hi Pierre,

thanks for the quick response.

The error was on my side and quite simple - I put the constructor parameters
"lastRow" and "firstCol" in the wrong order which resulted in an invalid
CellRangeAddressList. 
POI then does not raise an exception but seems to ignore such ranges quietly. I
suppose the lesson is: better read the API carefully :)

Best regards
Christian





---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

Re: Using HSSFDataValidation for list constraints

[Pierre Lavignotte <pi...@gmail.com>]

Hello Christian,

Here is what I use to add DataValidation to my workbooks :

    public static void setNamedRangeCellDataValidation(HSSFSheet sheet, int
firstRow, int firstColumn, int lastRow, int lastColumn, String rangeName) {

        CellRangeAddressList rangeList = new CellRangeAddressList();
        rangeList.addCellRangeAddress(new CellRangeAddress(firstRow,
lastRow, firstColumn, lastColumn));

        DVConstraint dvconstraint =
DVConstraint.createFormulaListConstraint(rangeName);

        HSSFDataValidation dataValidation = new
HSSFDataValidation(rangeList, dvconstraint);

        sheet.addValidationData(dataValidation);
    }

It looks quite similar, except the way I create the DVConstraint because I
use named ranges to store value.

Regards,
Pierre

Cordialement,
Pierre Lavignotte
Ingénieur Conception & Développement
http://pierre.lavignotte.googlepages.com


On Mon, Feb 9, 2009 at 4:08 PM, Weber, Christian <
Christian.Weber@ids-scheer.com> wrote:

> Hi all,
>
>
>
> I want to add an HSSFDataValidation to a sheet on my excel workbook. I
> found already some code examples where an HSSFDataValidation was created
> by a no-arg constructor but this seems to be not possible anymore under
> 3.2-FINAL.
>
>
>
> So instead I used this code:
>
>
>
> DVConstraint constraint =
> DVConstraint.createExplicitListConstraint(excelListValues);
>
>            CellRangeAddressList cellRange = new CellRangeAddressList(
> cell.getRowIndex(),
>
>
> cell.getColumnIndex(),
>
>
> cell.getRowIndex(),
>
>
> cell.getColumnIndex() );
>
>            HSSFDataValidation dataValidation = new
> HSSFDataValidation(cellRange, constraint);
>
>            cell.getSheet().addValidationData(dataValidation);
>
>
>
> for a given HSSFCell cell.
>
>
>
> But when I open the finished XLS there is no list constraint set for the
> cell. Did I forget to call some other method to activate the constraint
> or something?
>
>
>
>
>
> Thanks in advance
>
> Best regards
>
>
>
> Christian
>
>