Easy Dropdowns in Excel with Laravel

Last Updated at : March 4, 2018 By - Usama Muneer | 2081

    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

    Telescope gives knowledge into the solicitations coming into your application, exemptions, log sections, database questions, lined employments, mail, notices, reserve activities, planned errands, variable dumps and that's just the beginning.

    Laravel Live Islamabad, ended on 24th Nov'18 & we got a lot to learn from the top-notch of speakers & influencers. Thanks to the sponsors for pulling off such a great event in the capital of Pakistan.

    Previously Localization in Laravel was done by creating separate directories for each locales and place in under resources/lang/ and add key value pairs to every single keyword in a number of files as per their modules.

    Another #LaraconUS in the books & It was the 6th instalment of #LaraconUS happened at Chicago with full swing & we got a lot to learn this time with exceptional line of speakers and never-ending community support by fellow Artisans