You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Saurabh Bhatla <Sa...@kkr.com> on 2012/10/25 16:50:21 UTC
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.
===============================================================================
RE: XLSX template issue: Able to enter any values/links in cells
with lists (restrictions)
Posted by Saurabh Bhatla <Sa...@kkr.com>.
Works like a charm. Thank you!
-----Original Message-----
From: Louis.Masters@log-net.com [mailto:Louis.Masters@log-net.com]
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 <Sa...@kkr.com> wrote on 2012-10-25 10:50:21 AM:
> From: Saurabh Bhatla <Sa...@kkr.com>
> To: "user@poi.apache.org" <us...@poi.apache.org>,
> 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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: XLSX template issue: Able to enter any values/links in cells with lists
(restrictions)
Posted by Lo...@log-net.com.
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 <Sa...@kkr.com> wrote on 2012-10-25 10:50:21 AM:
> From: Saurabh Bhatla <Sa...@kkr.com>
> To: "user@poi.apache.org" <us...@poi.apache.org>,
> 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.
>
===============================================================================
>