Problem with Validation List

Topics: Developer Forum, User Forum
Jul 29, 2009 at 8:18 PM
Edited Jul 29, 2009 at 8:23 PM


I have a problem whith Validation List on Excel 2007. I get the options from a DB. They are 55 options that I include on my script. First, I do an Array and late I use implode() to separate by comma.

But, when I create the Excel, he show me this message:

If I clic YES, there no options on spreadsheet.

When I LIMIT the options to 20, there is no problem whit Excel. But, the problem isn't the quantity of options, the problem is with the quantity of characters. If I short the options string, there is no problem. Maybe exist a limit of characters to pass to the method? Or what?

My code:




$rendicion = new Rendiciones();

$destinosSecundario = implode(',', $rendicion->obtenerDestinos(false, true)); // Options from DB
$destinosSecundario = utf8_encode($destinosSecundario);

//Format: tab;column;start row;end row
$destinos = Array('0;R;17;24', '1;R;5;25', '2;S;5;23', '3;Q;5;22', '4;N;5;24'); // Places on the spreadsheet where I put the options

if($_GET['tipo'] == 1)
	$tipo = 'Fondo fijo';
elseif($_GET['tipo'] == 2)
	$tipo = 'Rendicion de gasto';
	$tipo = 'Fondo por rendir';

/** Include path **/
set_include_path(get_include_path() . PATH_SEPARATOR . 'class/PHPExcel');

/** PHPExcel */
require_once 'PHPExcel.php';

/** PHPExcel_IOFactory */
include 'PHPExcel/IOFactory.php';

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

// Read from Excel2007 (.xlsx) template
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load("$tipo.xlsx");

// Include options

for($i = 0; $i < count($destinos); $i++){

	list($hoja, $letra, $desde, $hasta) = split("[;]", $destinos[$i]);
	for($j = $desde; $j <= $hasta; $j++){
		$celda = $letra.$j;
		$objValidation = $objPHPExcel->getActiveSheet()->getCell($celda)->getDataValidation();
		$objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_LIST );
		$objValidation->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_INFORMATION );
		$objValidation->setErrorTitle('Valor erroneo');
		$objValidation->setError('El valor no pertenece a la lista.');


header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="'.$tipo.'.xlsx"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->setOffice2003Compatibility(true); // Try also to set it to false with the same result



Jul 30, 2009 at 7:09 PM

>> Maybe exist a limit of characters to pass to the method?

I verified this. Whenever the formula is more than 255 characters, the file opens as corrupt in MS Office Excel 2007. This must be an Excel limitation.

$objValidation->setFormula1($formula); // max 255 characters in $formula

Perhaps try to use named ranges as a workaround instead of using a comma-separated list. I think this is considered better practice anyway.

Aug 1, 2009 at 2:22 AM

Thanks for the answer!

I'll use named ranges for this issue.