Data Validation

Topics: Developer Forum, User Forum
Feb 11, 2011 at 3:35 PM

How i  cant validate the cell for only numer or date.....

this is the code for list:

$objValidation = $objPHPExcel->getActiveSheet()->getCell('B5')->getDataValidation();
$objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_LIST );
$objValidation->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_INFORMATION );
$objValidation->setAllowBlank(false);
$objValidation->setShowInputMessage(true);
$objValidation->setShowErrorMessage(true);
$objValidation->setShowDropDown(true);
$objValidation->setErrorTitle('Input error');
$objValidation->setError('Value is not in list.');
$objValidation->setPromptTitle('Pick from list');
$objValidation->setPrompt('Please pick a value from the drop-down list.');
$objValidation->setFormula1('"Item A,Item B,Item C"');	// Make sure to put the list items between " and "  !!!

 

but, how for date or number...

 

 

thanks

Coordinator
Feb 11, 2011 at 10:27 PM
$objValidation = $objPHPExcel->getActiveSheet()->getCell('A1')->getDataValidation();

$objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_WHOLE );
$objValidation->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_STOP );
$objValidation->setAllowBlank(true);
$objValidation->setShowInputMessage(true);
$objValidation->setShowErrorMessage(true);
$objValidation->setErrorTitle('Input error');
$objValidation->setError('Only Number is permitted!');
$objValidation->setPromptTitle('Allowed input');
$objValidation->setPrompt('Only numbers between 10 and 20 are allowed.');
$objValidation->setFormula1(10);
$objValidation->setFormula2(20);

In Excel2007, this will enforce an integer value between 10 and 20 when entered in cell A1. Excel5 restricts to an integer value, but doesn't enforce range.

To valid real number entry, change the Type to PHPExcel_Cell_DataValidation::TYPE_DECIMAL

$objValidation = $objPHPExcel->getActiveSheet()->getCell('A1')->getDataValidation();
$objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_DECIMAL);
$objValidation->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_STOP );
$objValidation->setAllowBlank(true);
$objValidation->setShowInputMessage(true);
$objValidation->setShowErrorMessage(true);
$objValidation->setErrorTitle('Input error');
$objValidation->setError('Only Number is permitted!');
$objValidation->setPromptTitle('Allowed input');
$objValidation->setPrompt('Only real numbers between 10 and 20 are allowed.');
$objValidation->setFormula1(10.0);
$objValidation->setFormula2(20.0);

For dates:

$objValidation = $objPHPExcel->getActiveSheet()->getCell('A1')->getDataValidation();

$objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_DATE);
$objValidation->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_STOP );
$objValidation->setAllowBlank(true);
$objValidation->setShowInputMessage(true);
$objValidation->setShowErrorMessage(true);
$objValidation->setErrorTitle('Input error');
$objValidation->setError('Only Date is permitted!');
$objValidation->setPromptTitle('Allowed input');
$objValidation->setPrompt('Only dates between 01/01/2011 and 31/03/2011 are allowed.');
$objValidation->setFormula1(40544);
$objValidation->setFormula2(40633);

 

Aug 3, 2014 at 9:27 AM
Hi Mark Baker,

I'm new to PHPExcel & recently started using it. I have a similar problem which reported by g_sanchez.
This is my requirement. For a specific cell, I want the end users to allow inputting only the date values in yyyy-mm-dd format. If the user input value is not in the given format then it should reject the value prompting an error message.

Eg.
User enters 2012 to A1 cell, it should fail, giving an error message saying "invalid date. date should be a valid date in yyyy-mm-dd format".
User enters 2012-03-81 to A1 cell, it should fail, giving an error message saying "invalid date. date should be a valid date in yyyy-mm-dd format".
User enters 2012-03-02 to A1 cell, it should pass.

I have tried the solution that you have given too. But whatever the value I input (eg. 01/01/2011, 02/01/2011, 31/03/2011, 30/03/2011) it gives me the validation error. Perhaps you could tell me what I should put into setFormula1() ( &/or setFormula2() ) method for my scenario?

Once the filling is done, i want to save the excel file & read it from phpexcel. Then create csv files for each sheet. I already have found the way how to do that. However please note even after the validation is done, when creating csv files i want the date values to be in yyyy-mm-dd format if the values are validated.

Hope my question is clear. Any help would be much appreciated.
Aug 25, 2014 at 5:15 AM
Still no reply :(
Can anyone who knows about this update me with a possible solution for this issue?

Thanks in advance!