0

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, enter image description here

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

Ramit
  • 109
  • 1
  • 9
  • I believe the same question was raised [here](https://stackoverflow.com/questions/38185924/populate-dropdown-with-phpexcel/45751041#45751041) and a partial solution provided. If I need to add multiple list it not works – aniruddha Aug 18 '17 at 11:23
  • Not fully related, but you should give a try to http://github.com/box/spout that is much much lighter than phpexcel – Massimiliano Arione Aug 18 '17 at 11:49
  • 1
    @massimiliano : I have checked box sprout. However, it doesn't fit my need as it doesn't support excel formula and data validations. If you have a link to documented implementation of box sprout using data validation, please do share – Ramit Aug 18 '17 at 12:20

0 Answers0