I am facing an issue when am trying to generate an excel with more than one Type_List dropdown using PHPExcel on Symfony 2.8.
I am using PHPExcel_Cell_DataValidation::TYPE_LIST to generate the columns of drop-down type. My code works fine when am generating only one column in the active sheet as a drop-down. However, when am trying to generate more than one columns as drop-down type in a sheet, am getting an unusable excel.
Here is a sample screenshot of the error am getting on opening the generated excel,
Here is the action code that I wrote,
//generating an excel using PHPexcel
public function generateExcel2Action(Request $request)
{
$responseString = 'Initial Mem. '.(memory_get_peak_usage(true)/1024/1024).' MB <br>';
$startTime = microtime(true);
//make a new object
$objPHPExcel = new PHPExcel();
//set properties
$objPHPExcel->getProperties()->setCreator("Ramit Mitra")
->setLastModifiedBy("Ramit Mitra")
->setTitle("Sample excel doc")
->setSubject("Office 2007 XLSX Test Document")
->setDescription("Test document for Office 2007 XLSX, generated using PHPExcel.")
->setKeywords("office 2007 openxml php")
->setCategory("Test result file");
//Excel headers
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Shipment Number');
$objPHPExcel->getActiveSheet()->setCellValue('B1', 'Country');
$objPHPExcel->getActiveSheet()->setCellValue('C1', 'Region');
$objPHPExcel->getActiveSheet()->setCellValue('D1', 'Choose Storage Condition');
$objPHPExcel->getActiveSheet()->setCellValue('E1', 'Choose Storage Area');
$objPHPExcel->getActiveSheet()->setCellValue('F1', 'Choose Help Text');
$objPHPExcel->getActiveSheet()->setCellValue('G1', 'Memory in use');
//QUERY AND SET DATA
$em = $this->getDoctrine()->getManager();
//create all options
$suSC = ['Cold', 'Hot', 'Normal'];
$suSA = ['SM', 'LG', 'XS', 'XL'];
$suHT = ['Text 1', 'Text 2', 'Text 3'];
$i = 2;
$allCountries = $em->getRepository('DemoBundle:Country')->findAll();
foreach($allCountries as $key => $val) {
//shpno
$objPHPExcel->getActiveSheet()->setCellValue("A$i", 'SHP'.rand(10000,90000));
//country
$objPHPExcel->getActiveSheet()->setCellValue("B$i", $val->getName());
//region
$objPHPExcel->getActiveSheet()->setCellValue("C$i", $val->getRegion()->getName());
//Choose Storage Condition
$objValidation = $objPHPExcel->getActiveSheet()->getCell("D$i")->getDataValidation();
$objValidation->setType(\PHPExcel_Cell_DataValidation::TYPE_LIST);
$objValidation->setErrorStyle(\PHPExcel_Cell_DataValidation::STYLE_INFORMATION);
$objValidation->setAllowBlank(false);
$objValidation->setShowInputMessage(true);
$objValidation->setShowDropDown(true);
$objValidation->setPromptTitle('Pick Storage Condition');
$objValidation->setPrompt('Please pick a value from the drop-down list.');
$objValidation->setErrorTitle('Input error');
$objValidation->setError('Value is not in list');
$objValidation->setFormula1('"'.implode(',', $suSC).'"');
unset($objValidation);
//Choose Storage Area
$objValidation1 = $objPHPExcel->getActiveSheet()->getCell("E$i")->getDataValidation();
$objValidation1->setType(\PHPExcel_Cell_DataValidation::TYPE_LIST);
$objValidation1->setErrorStyle(\PHPExcel_Cell_DataValidation::STYLE_INFORMATION);
$objValidation1->setAllowBlank(false);
$objValidation1->setShowInputMessage(true);
$objValidation1->setShowDropDown(true);
$objValidation1->setPromptTitle('Pick Storage Area');
$objValidation1->setPrompt('Please pick a value from the drop-down list.');
$objValidation1->setErrorTitle('Input error');
$objValidation1->setError('Value is not in list');
$objValidation1->setFormula1('"'.implode(',', $suSA).'"');
unset($objValidation1);
//Choose Help Text
$objValidation2 = $objPHPExcel->getActiveSheet()->getCell("F$i")->getDataValidation();
$objValidation2->setType(\PHPExcel_Cell_DataValidation::TYPE_LIST);
$objValidation2->setErrorStyle(\PHPExcel_Cell_DataValidation::STYLE_INFORMATION);
$objValidation2->setAllowBlank(false);
$objValidation2->setShowInputMessage(true);
$objValidation2->setShowDropDown(true);
$objValidation2->setPromptTitle('Pick Help Text');
$objValidation2->setPrompt('Please pick a value from the drop-down list.');
$objValidation2->setErrorTitle('Input error');
$objValidation2->setError('Value is not in list');
$objValidation2->setFormula1('"'.implode(',', $suHT).'"');
unset($objValidation2);
//mem in use
$objPHPExcel->getActiveSheet()->setCellValue("G$i", (memory_get_peak_usage(true)/1024/1024).' MB');
$i++;
}
unset($allCountries);
// create the writer
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$file = $this->get('kernel')->getRootDir() . "\..\web" . $this->getRequest()->getBasePath()."/temp/test2.xlsx";
$objWriter->save($file);
unset($objWriter);
$endTime = microtime(true) - $startTime;
$responseString .= 'Final Mem. '.(memory_get_peak_usage(true)/1024/1024).' MB <br>'."File generated @ $file <br>Executed in :: $endTime";
unset($startTime);
unset($endTime);
//send response
return new Response($responseString);
}
Please provide a solution to this. I need to generate multiple columns in the same sheet as drop-downs.
Basic Env details: Windows 7 OS, PHP 7.1.1 on XAMPP Symfony 2.8.26