Phpexcel make msexcel crash when using two type of validation

Topics: Developer Forum, User Forum
Mar 19, 2012 at 11:47 AM

Hello All,

 

Kindly see below code on why ms excel crash after entering some data on D3

 

$workbook = new PHPExcel();
$workbook->setActiveSheetIndex(0);
$sheet	= $workbook->getActiveSheet();
$filename = "akel.xls";
$sheet->setTitle("akel");

for ( $row=2;$row<=10;$row++ ) {
        $objValidation1 = $sheet->getCell('D'.$row)->getDataValidation();
        $objValidation1->setType( PHPExcel_Cell_DataValidation::TYPE_DECIMAL );
        $objValidation1->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_INFORMATION );
        $objValidation1->setAllowBlank(true);
        $objValidation1->setShowInputMessage(true);
        $objValidation1->setShowErrorMessage(true);
        $objValidation1->setErrorTitle('Net Energy Input Error');
        $objValidation1->setError('Only numeric entries are allowed.' );
        $objValidation1->setPromptTitle('Net Energy Input Error');
        $objValidation1->setPrompt('Only numeric entries are allowed.' );

        $objValidation2 = $sheet->getCell('G'.$row)->getDataValidation();
        $objValidation2->setType( PHPExcel_Cell_DataValidation::TYPE_DECIMAL );
        $objValidation2->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_INFORMATION );
        $objValidation2->setAllowBlank(true);
        $objValidation2->setShowInputMessage(true);
        $objValidation2->setShowErrorMessage(true);
        $objValidation2->setErrorTitle('Net Energy Input Error');
        $objValidation2->setError('Only numeric entries are allowed.' );
        $objValidation2->setPromptTitle('Net Energy Input Error');
        $objValidation2->setPrompt('Only numeric entries are allowed.' );

        $objValidation = $sheet->getCell('E'.$row)->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.');
        $objValidation->setFormula1('"Available,Scheduled Outage"');
        $sheet->setCellValue('H'.$row,'Available');

        $objValidation = $sheet->getCell('H'.$row)->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.');
        $objValidation->setFormula1('"Available,Scheduled Outage"');
}

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$filename.'"');

$objWriter = PHPExcel_IOFactory::createWriter($workbook,'Excel5');
$objWriter->save('php://output');

 

Thank you.