Setting Validation -> Drop Down - Corrupt Spreadsheet

Topics: Developer Forum
Mar 6, 2012 at 5:02 PM

I am attempting to create a dropdown validation for cell A1 (to simply see if I can get it to work) and when I open the created spreadsheet, I am getting a message that it is corrupt. I have tried to output the spreadsheet to Excel2007 as well as Excel5 with the same result. Here is my code:

 

	header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    header("Content-Disposition: attachment;filename=\"TEST_template.xlsx\"");
    header("Cache-Control: max-age=0");

    require_once "PHPExcel.php";	

	$objPHPExcel = new PHPExcel();

    $objPHPExcel->getProperties()->setCreator( "TEST" );
    $objPHPExcel->getProperties()->setLastModifiedBy( "TEST" );
    $objPHPExcel->getProperties()->setTitle( "Test Template" );
    $objPHPExcel->getProperties()->setSubject( "Test Template" );
    $objPHPExcel->getProperties()->setDescription( "Test Template" );

    $objPHPExcel->setActiveSheetIndex(0);
    $objPHPExcel->getActiveSheet()->setTitle( "Test Template" );
		
    $objValidation = $objPHPExcel->getActiveSheet()->getCell("A1")->getValidation();

    $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( '"A,C"' );
					
	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    $objWriter->save('php://output');
    $objPHPExcel->disconnectWorksheets();

When I attempt to open the file, I get the following:

Excel cannot open the file 'TEST_template,xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.

Any help is appreciated

Jack

Mar 6, 2012 at 5:52 PM

More Information: I am running PHPExcel 1.7.6 on Zend Framework 1.11. If I do not use the validation and simply set values and style a cell, it works fine. I only get the corrupt error message when I attempt to use validation.

 

Thank you

Jack

Mar 6, 2012 at 6:20 PM

Ok, I am reviewing other posts and am seeing what other people include as relevant information. I see that people are including the version of Excel that they are attempting to open the excel spreadsheet with, so I figured I would provide that too.

I have attempted to open the spreadsheet(s) with Excel 2003 and Excel 2010 with same results.

-Jack-

Mar 6, 2012 at 8:02 PM

Ok, I found this in the webserver error log:

Call to undefined method PHPExcel_Cell::getValidation()

I have added require_once "PHPExcel/Cell.php"; and it still did not fix the issue. Does anyone have an example of using Data Validation with the Zend framework? Can anyone provide any direction on resolving the undefined method error?

Thanks in advance

Jack

Mar 6, 2012 at 8:11 PM

Ok, I figured it out. There is a type, getValidation above should be getDataValidation.

Thanks

Jack

Mar 28, 2012 at 8:31 PM

You may try http://www.recoverytoolbox.com/repair_excel.html Software repairs excel worksheets, fonts, formulas, cells, columns, rows and etc. It handles such Excel errors as: Microsoft Excel unable to read file or This file is not in a recognizable format. Tool has the compatibility with Windows 98 and higher.

Sep 25, 2012 at 8:11 AM
Edited Sep 25, 2012 at 8:12 AM

the above code does work, but instead of

$objValidation = $objPHPExcel->getActiveSheet()->getCell("A1")->getValidation();

i used

$objValidation = $objPHPExcel->getActiveSheet()->getCell("A1")->getValidationData();

 

may i know if anyone have tried one with selected value?