How to make dropdown in excell sheet using php

Many times we are required to have pre-populated list in the excell sheet. So that user can select the options from the list while entering data in the sheets.

There are number of libraries you can use to make this task done. But PHPOffice / PHPExcell is the one I have used in this article.

You can download the library from github. Github PHPOffice / PHPExcell Download now

Simply unzip the library and make index.php file in the same folder of this library.

And then write the code below to make an excell sheet with the dropdown box.

error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);

define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');

require_once dirname(__FILE__) . './Classes/PHPExcel.php';


echo date('H:i:s') , " Create new PHPExcel object" , EOL;
$objPHPExcel = new PHPExcel();
var_dump($objPHPExcel);

$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()
    ->setCellValue('B5', "SELECT ITEM")
;

$configs = "DUS800, DUG900+3xRRUS, DUW2100, 2xMU, SIU, DUS800+3xRRUS, DUG900+3xRRUS, DUW2100";

$objValidation = $objPHPExcel->getActiveSheet()->getCell('B5')->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('"'.$configs.'"');

// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);


// Save Excel 95 file
echo date('H:i:s') , " Write to Excel5 format" , EOL;
$callStartTime = microtime(true);

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('populate.xls');

It’s a ready to go code. Simple copy and paste and your excel sheet will be downloaded in the same folder with the dropdown.

Note : There is a limit of 255 characters of select box you show in the sheet. When you input many list items in the select box then it will be null.