poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Saurabh Bhatla <Saurabh.Bha...@kkr.com>
Subject RE: XLSX template issue: Able to enter any values/links in cells with lists (restrictions)
Date Thu, 25 Oct 2012 15:46:19 GMT
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 <Saurabh.Bhatla@kkr.com> wrote on 2012-10-25 10:50:21 AM:

> From: Saurabh Bhatla <Saurabh.Bhatla@kkr.com>
> To: "user@poi.apache.org" <user@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


Mime
View raw message