DataValidation with NamedRange

Topics: Developer Forum, User Forum
May 7, 2008 at 7:51 PM
Hello PHPExcel Experts,

I try to use DataValidation with a NamedRange. But my Dropdownlist is only filled with the Values from the NamedRange when I opened the created document with Excel 2007, selected the Cell with the DataValidation defined, switched to the "Data" Tab and opened "Data Validation" and confirming that with OK. Then the Dropdownlist is filled with the Values from the NamedRange. Before I only see =SAMPLECHECKTABLE.

I'm running PHPExcel on Debian with Apache2 and PHP Version 5.2.0-8+etch10. Here's my sample code to demonstrate this Problem:

<?php

/**
* Demonstration problem with PHPExcel DataValidation using NamedRange
*/
setincludepath(getincludepath() . PATH_SEPARATOR . 'PHPExcel1.6.1/Classes/');

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

/** PHPExcelWriterExcel2007 */
include 'PHPExcel/Writer/Excel2007.php';

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

// Set properties
$objPHPExcel->getProperties()->setCreator("Gregor Wolf");
$objPHPExcel->getProperties()->setLastModifiedBy("Gregor Wolf");
$objPHPExcel->getProperties()->setTitle("Demonstration of the PHPExcel DataValidation Bug");
// Create Worksheet 2 for Data Validation List
$objPHPExcel->createSheet( );
$objPHPExcel->setActiveSheetIndex(1);
$objPHPExcel->getActiveSheet()->setTitle("Data Validation List");

// Define a Name & Array for the Check Table
$checkTableName = "SAMPLECHECKTABLE";
$checkTable = array(
'DE' => 'Germany',
'US' => 'United States of America'
);
$column = 0;
$row = 1;
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($column, $row, $checkTableName);
while (list($key, $val) = each($checkTable)) {
$row++;
$objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow($column, $row, $key);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($column+1, $row, $val);
}
$column++;
// Definie NameRange
$cell = $objPHPExcel->getActiveSheet()->getCellByColumnAndRow($column, $row);
$columnString = $cell->getColumn( );
$range = $columnString.'2:'.$columnString.$row;
$objPHPExcel->addNamedRange( new PHPExcel_NamedRange($checkTableName, $objPHPExcel->getActiveSheet(), $range) );

// Add some data
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setTitle("Use Data Validation List");

$row = 1;
$column = 0;
// Fill Cell Value
$objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow($column, $row, 'Germany');
// Add Data Validation with values of NameRange
$objValidation = $objPHPExcel->getActiveSheet()->getCellByColumnAndRow($column, $row)->getDataValidation();
$objValidation->setType( PHPExcelCellDataValidation::TYPE_LIST );
$objValidation->setErrorStyle( PHPExcelCellDataValidation::STYLE_INFORMATION );
$objValidation->setAllowBlank(false);
$objValidation->setShowInputMessage(true);
$objValidation->setShowErrorMessage(true);
$objValidation->setShowDropDown(true);
$formula = '"='.$checkTableName.'"';
$objValidation->setFormula1($formula);
$objPHPExcel->getActiveSheet()->getCellByColumnAndRow($column, $row)->setDataValidation($objValidation);

// Add another Row
$row++;
$objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow($column, $row, 'United States of America');
// Clone Data Validation
// $objPHPExcel->getActiveSheet()->getCellByColumnAndRow($column, $row)->setDataValidation(clone $objValidation);

// Add another Row
$row++;
$objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow($column, $row, 'Germany');

$objValidation = $objPHPExcel->getActiveSheet()->getCellByColumnAndRow($column, $row)->getDataValidation();
$objValidation->setType( PHPExcelCellDataValidation::TYPE_LIST );
$objValidation->setErrorStyle( PHPExcelCellDataValidation::STYLE_INFORMATION );
$objValidation->setAllowBlank(false);
$objValidation->setShowInputMessage(true);
$objValidation->setShowErrorMessage(true);
$objValidation->setShowDropDown(true);
$objValidation->setFormula1('"Germany,United States of America"');
$objPHPExcel->getActiveSheet()->getCellByColumnAndRow($column, $row)->setDataValidation($objValidation);

// Setting worksheet's default style
$objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setName('Arial');
$objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(8);

$filename = "DataValidationBug.xlsx";
$objWriter = new PHPExcelWriterExcel2007($objPHPExcel);
$objWriter->save($filename);

?>

Hope someone can give me a tip.

Best regards
Gregor
Coordinator
May 8, 2008 at 7:26 AM
Try changing
$formula = '"='.$checkTableName.'"';
into
$formula = $checkTableName;
May 8, 2008 at 8:02 PM
Hi Maartenba,

thank you so mutch. That solved my problem. Now I only facing the problem that my Apache2 exits with a Segmentation fault when I try to use the clone function to get the Validation also for other cells using:

$objPHPExcel->getActiveSheet()->getCellByColumnAndRow($column, $row)->setDataValidation(clone $objValidation);

Any tips on that? How can I provide more debug information?

Best regards
Gregor
Coordinator
May 9, 2008 at 7:38 AM
Can you try running the script from console?
May 9, 2008 at 6:33 PM
when I run the script from the console the only output is 'Segmentation fault'. Also when I run it with php -e DataValidation.php. Any tips?
Coordinator
May 9, 2008 at 8:36 PM
Can you send me your complete script (maarten ---AT--- phpexcel ---DOT--- NET) if possible?