Works like a charm. Thank you!
-----Original Message-----
From: [email protected] [mailto:[email protected]]
Sent: Thursday, October 25, 2012 11:15 AM
To: POI Users List
Subject: Re: XLSX template issue: Able to enter any values/links in cells with
lists (restrictions)
I have this working in 3.8:
constraint =
validationHelper.createExplicitListConstraint(valList); //String[] valList
dataValidation =
validationHelper.createValidation(constraint, addressList);
dataValidation.setSuppressDropDownArrow(true);
dataValidation.createPromptBox("Valid Values", prompt);
dataValidation.setShowPromptBox(true);
dataValidation.setShowErrorBox(true);
dataValidation.setErrorStyle(DataValidation.ErrorStyle.
STOP);
dataValidation.setEmptyCellAllowed(false);
dataValidation.createErrorBox("Validation Error",
validationMsg);
sheet.addValidationData(dataValidation);
-Lou
Saurabh Bhatla <[email protected]> wrote on 2012-10-25 10:50:21 AM:
> From: Saurabh Bhatla <[email protected]>
> To: "[email protected]" <[email protected]>,
> Date: 2012-10-25 10:51 AM
> Subject: XLSX template issue: Able to enter any values/links in cells
> with lists (restrictions)
>
> All,
>
> I recently upgraded to the latest version of POI and started
> generating XLSX files instead of XLS.
>
> After generating XLSX files if I have a cell with list (Restricted
> values) then the list shows up fine in excel but the cell can be
> overridden to type in a value or a formula. Excel shows me an error if
> try to do the same in XLS file generated from POI.
>
> Here is the code I am using now:
>
> public String yesNoArray[] = {"Yes", "No"};
>
> DataValidationHelper validationHelper = new XSSFDataValidationHelper
> ((XSSFSheet)excelUtils.sheet); CellRangeAddressList cellRange = new
> CellRangeAddressList (excelUtils.cell.getRowIndex(),
> excelUtils.cell.getRowIndex(), excelUtils.cell.getColumnIndex(),
> excelUtils.cell.getColumnIndex());
> DataValidationConstraint constraint =
> validationHelper.createExplicitListConstraint(yesNoArray);
> DataValidation dataValidation dataValidation =
> validationHelper.createValidation(constraint, cellRange);
> dataValidation.setSuppressDropDownArrow(true);
> dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
> dataValidation.createErrorBox("Invalid input", "Only Yes/No values are
> allowed."); sheet.addValidationData(dataValidation);
>
>
> Here is what I used for generating XLS files:
>
> DataValidationConstraint constraint =
> DVConstraint.createExplicitListConstraint(yesNoArray);
> CellRangeAddressList cellRange = new CellRangeAddressList
> (excelUtils.cell.getRowIndex(), excelUtils.cell.getRowIndex(),
> excelUtils.cell.getColumnIndex(), excelUtils.cell.getColumnIndex());
> HSSFDataValidation dv = new HSSFDataValidation(cellRange, constraint);
> dv.setEmptyCellAllowed(true); dv.setShowPromptBox(false);
> dv.createErrorBox("Invalid input", "Only Yes/No values are allowed.");
> sheet.addValidationData(dv);
>
>
> I get an alert if I create a worksheet with restricted list directly
> in Excel (XLSX) and try to type a value in the cell.
>
> Has anyone faced this issue before? Any help/guidance will be greatly
> appreciated.
>
> Thanks
> Saurabh
>
>
>
===============================================================================
> Please refer to http://www.kkr.com/legal/email_disclaimer.php
> for important disclosures regarding this electronic communication.
>
===============================================================================
>
===============================================================================
Please refer to http://www.kkr.com/legal/email_disclaimer.php
for important disclosures regarding this electronic communication.
===============================================================================
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
