problem with DataValidation -- SOLVED

Topics: Developer Forum, User Forum
Jan 29, 2008 at 3:25 PM
Hi, I'm using the DataValidation but I can't open the xlsx file. I think code will be better than explanations. But before, few words about the environment :
PHP on Ubuntu/Linux, php -v = PHP 5.2.3-1ubuntu6.3 (cli) (built: Jan 10 2008 09:38:37)
PHPExcel version 1.5.5, 2007-12-24
Excel 2002 version (10.6834.6830) SP3 with Office 2007 compatibility pack from MicroSoft (version 12.0.6021.5000)

Code BEGIN
require_once('PHPExcel.php');
require_once('PHPExcel/Writer/Excel2007.php');

$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setTitle('f1');
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex(1);
$objPHPExcel->getActiveSheet()->setTitle('f2');
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('A1','=f2!B2');
$objPHPExcel->setActiveSheetIndex(1);

$objValidation = $objPHPExcel->getActiveSheet()->getCell('B2')->getDataValidation();
$objValidation->setType( PHPExcelCellDataValidation::TYPE_LIST );
$objValidation->setErrorStyle( PHPExcelCellDataValidation::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"');
$objPHPExcel->getActiveSheet()->getCell('B2')->setDataValidation($objValidation);

$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex(2);
$objPHPExcel->getActiveSheet()->setTitle('f3');

$objPHPExcel->setActiveSheetIndex(0);
// Save Excel 2007 file
$objWriter = new PHPExcelWriterExcel2007($objPHPExcel);
$objWriter->setOffice2003Compatibility(true);
$objWriter->save('test.xlsx');
Code END

What I did wrong?

Thank you,
JP
Coordinator
Jan 30, 2008 at 6:04 AM
The problem is in this line:
$objValidation->setFormula1('"Item A", "Item B", "Item C"');

This should be:
$objValidation->setFormula1('"Item A, Item B, Item C"');

Basically the full list of comma-separated values must be between " quotes ".
Jan 30, 2008 at 12:52 PM
maartenba, thank you! You spot my problem.

JP