[FIXED] How to increase cell character limit PHPExcel TYPE LIST

Issue

Using PHPExcel to create a dropdown list like so:

$objValidation = $objPHPExcel->getActiveSheet()->getCell("A".$i)->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.');
$formulaString = '"' .  implode(", ", $items_array) . '"';
$objValidation->setFormula1($formulaString);

$objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
$filename = "MyExcel.xlsx";
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$filename.'"');
header('Cache-Control: max-age=0');
$objWriter->save('php://output');

The problem is it cuts off at 1023 characters hence breaking the "dropdown" function.

How do I increase the character limit?

Solution

As discussed in the comments, there is a limit to the length of a static list in data validation.

You should put the validation data in a range in the workbook somewhere and provide this range for the validation:

// Get options from another sheet
$objvalidation->setFormula1('ExampleSheet!A1:A100');

Answered By - Phylogenesis

Answer Checked By - Dawn Plyler (PHPFixing Volunteer)