Easy Dropdowns in Excel with Laravel

March 4, 2018 By - Usama Muneer | 102

    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.


    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
             new \PHPExcel_NamedRange(
                'php', $sheet, 'A2:A4'
           * Validate and display gathered data in other cell
           * @var  $objValidation */
          $objValidation = $sheet->getCell('B1')->getDataValidation();
          $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. */
       new \PHPExcel_NamedRange(
          'sd', $sheet->_parent->getSheet( 1 ), "A2:A" . $sheet->_parent->getSheet( 1 )->getHighestRow()


    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.

    blog user
    Usama Muneer

    A web enthusiastic, self-motivated & detail-oriented professional Full-Stack Web Developer from Karachi, Pakistan with experience in developing applications using JavaScript, WordPress & Laravel specifically. Loves to write on different web technologies with an equally useful skill to make some sense out of it.

    Related Posts

    While Developing applications in Laravel, there comes situations where you have date in simple string format. For instance getting raw response from an API. But you want to convert that string into Carbon instance and use the Carbon helpers on it. Well there's a simple solution to it.

    Laravel ships in with the awesomeness of Database Migrations which allows you to version control your database. But there are number of events when you are required to change the datatypes or attributes assigned to your table columns inside of your migrations.

    Exporting Excel files come handy when you're building some inventory kind of an application. In this short tutorial, we'll go through on exporting excel files with Laravel & look on for some crazy hacks and tricks in between.

    If you want your table to have literally one of anything, then one to one is the most simplest relationship. For instance, a token is generated for resetting a user password & its a simple one to one relationship between users and password_resets table.