Easy Dropdowns in Excel with Laravel

March 4, 2018 By - Usama Muneer | 721

    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

    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.

    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

    If you've no idea what macroable functionality is, take it as something you want to extend in within Laravel with a piece of code that doesn't exists in the Class.

    The back-end is one of the integral parts of modern web application development & PHP is one of most preferred platforms.

    If you're on MacOSX & use Laravel valet for managing your development environment. Then this issue might arouse when you try to upgrade your PHP version or even Laravel Valet. But there's an easy fix for that.

    Maintaining Database Relationships in Laravel is quite a task but when it comes to nested relationships, things get a bit tangled up to handle.