Easy Dropdowns in Excel with Laravel

Sometimes you need to provide dropdowns in your exported excel files for more enhanced look on the data. Here's how we can achieve it.

Pre-requisite

• Setup your Laravel project for easy exporting excel files.

Create a new Sheet

\Excel::create( 'file_name', function ( $excel ) {
   $excel->sheet( 'sheet_name', function ( $sheet ) {
      $sheet->row( 1, array(
         'My Row Title'

      ) );
      $sheet->SetCellValue( "A1", "PHP" );
      $sheet->SetCellValue( "A2", "WordPress" );
      $sheet->SetCellValue( "A3", "Laravel" );
      $sheet->SetCellValue( "A4", "Magento" );

      //Gather data from these cells
      $sheet->_parent->addNamedRange(
         new \PHPExcel_NamedRange(
            'php', $sheet, 'A2:A4'
         )
      );

      /**
       * Validate and display gathered data in other cell
       * @var  $objValidation */
      $objValidation = $sheet->getCell('B1')->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('php'); //note this!

   } );

} )->get( 'xlsx' );

Here almost every method is self-explanatory, run it and see the results in your excel file.

Testing out in different scenerio

Scenerio # 01: List of dropdowns

Now assume as if we need to have a list of dropdowns. Well we can do something like:

$col_count = request()->col_count; //Getting the value of column count
for ( $i = 1; $i <= $col_count; $i ++ ) {
   $objValidation = $sheet->getCell( 'B' . $i )->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( 'php' ); //note this!
}

Scenerio # 02: Getting Data from another sheet

/** gathering data from sheet at first index and get cells till its last entry. */
$sheet->_parent->addNamedRange(
   new \PHPExcel_NamedRange(
      'sd', $sheet->_parent->getSheet( 1 ), "A2:A" . $sheet->_parent->getSheet( 1 )->getHighestRow()
   )
);

Conclusion

Let us know if you are stuck in organising your excels with Laravel. If you got another scenerio for us to try out some solution, let us know in the comment section below. You can also follow us on Twitter.