Data validation problem with Excel2007 writer open on Excel 2003

Topics: Developer Forum
Jul 1, 2009 at 10:07 AM

Hello,

I have a problem with data validation when I open my file with Excel 2003.

The problem occurs when my data validation refers to a cell which is before the cell where the data validation is. Here an example :

Data validation on cell B1, the operation is "greater than" the cell A1 => in that case I have an error message on opening

Data validation on cell B1, the operation is "greater then" the cell C1 => in that case I haven't an error message on opening

In all cases, the file is fully operationnal even if I have the error message on opening.

Here the error message http://img7.imageshack.us/img7/3117/errorbrg.jpg (sorry it's a french version ;-) )

I give you my test code :

$excel = new PHPExcel();
$excel->setActiveSheetIndex(0);

// Must set value (or style) on cell to have data validation on it (another issue maybe)
$excel->getActiveSheet()->getCell('B1')->setValue(0);

$objValidation = new PHPExcel_Cell_DataValidation();
$objValidation->setType(PHPExcel_Cell_DataValidation::TYPE_WHOLE);
$objValidation->setOperator(PHPExcel_Cell_DataValidation::OPERATOR_GREATERTHAN);
$objValidation->setErrorStyle(PHPExcel_Cell_DataValidation::STYLE_STOP);
$objValidation->setAllowBlank(false);
$objValidation->setShowInputMessage(false);
$objValidation->setShowErrorMessage(true);
$objValidation->setError("Invalid input");
$objValidation->setFormula1('A1');

$excel->getActiveSheet()->getCell('B1')->setDataValidation($objValidation);

$objWriter = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
$objWriter->setOffice2003Compatibility(true);        // Try also to set it to false with the same result
$objWriter->save('test.xlsx');

Thanks for your help

Developer
Jul 1, 2009 at 1:52 PM

I can verify this problem. Interestingly, opening in MS Office Excel 2007, no problem.

Copying to work item:
http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=10224