Selecting a sheet using Laravel-Excel

Question

I'm using the very useful https://github.com/Maatwebsite/Laravel-Excel package.

As I liked the idea of keeping my controllers clear of the excel import code, I'm loading the uploaded file using ExcelFile injections, see here: http://www.maatwebsite.nl/laravel-excel/docs/import#injection

This is my code for the ExcelFile Injection:

StudentImport.php

namespace App\Excel;

class StudentImport extends \Maatwebsite\Excel\Files\ExcelFile {

    public function getFile()
    {
        return \Input::file('student_file');
    }

    public function getFilters()
    {
        return [];
    }

}

However, my problem is that I don't understand where I run methods like: ->selectSheets('mysheet') when using this approach.

My current work around is doing the following in my controller after using the ExcelFile injection to grab the file.

ExcelController.php

public function import(StudentImportFormRequest $request, StudentImport $import)
{
    $results = $import->get();

    foreach($results as $sheet) {
        $sheetTitle = $sheet->getTitle();

        if($sheetTitle === 'students') {
            foreach($sheet as $row) {
                // do something
            }
        }
    }

}

I believe I may need to extend the ExcelFile class and add a new method which will be a wrapper around the selectSheets() method or add it in to the loadFile() method somehow - this seems to be the place where the filters and settings are set up so I guess this is where you might add in a selection of a specific sheet?

Also, I would like to know how to set certain columns to strings as they currently being read as numbers. Currently I have text being output as floats (i.e. they have a following decimal point!), when I dd() the first and only row without using a Value Binder I get the following:

CellCollection {#862 ▼
  #title: null
  #items: array:8 [▼
    "name" => "John Smith"
    "refno" => "s123"
    "nid" => 1234567890.0
    "birth_date" => Carbon {#861 ▼
      +"date": "1971-01-05 00:00:00.000000"
      +"timezone_type": 3
      +"timezone": "UTC"
    }
    "is_active" => 1.0
    "course_title" => "Computer Science"
    "institution_id" => 1.0
    "course_id" => 1.0
  ]
}

I've tried implementing a ValueBinder as mentioned in the docs: http://www.maatwebsite.nl/laravel-excel/docs/import#formatting but have had no success yet. I only have one column which needs to be read as a date, the rest should be read as text and the following code doesn't work:

namespace App\Excel;

use PHPExcel_Cell;
use PHPExcel_Cell_DataType;
use PHPExcel_Cell_IValueBinder;
use PHPExcel_Cell_DefaultValueBinder;

class StudentValueBinder extends PHPExcel_Cell_DefaultValueBinder implements PHPExcel_Cell_IValueBinder
{
    public function bindValue(PHPExcel_Cell $cell, $value = null)
    {
        if ($cell->getColumn() !== 'D') {

            $cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_STRING);

            return true;
        }

        // else return default behavior
        return parent::bindValue($cell, $value);
    }
}

Any advice would be most appreciated! Thank you.

Answer 1

The getFile method is supposed to return a file name:

namespace App\Excel 
class MyExcelClass {
public function getFile()
    {
        return storage_path('excel') . '/file.xls';
    }
}

The storage path just points to your storage folder and then whatever sub directory you pass to the storage_path.

So really StudentImport->getFile(), at least in the docs, just returns '/path/to/MyProject/storage/excel/file.xls;.

While you can separate and extend control further, let's start with a base case.

In your controller:

use Maatwebsite\Excel\Facades\Excel;
use App\Excel\MyExcelClass;
class ExcelController extends Controller
{
    public function importAndManipulate() {
        Excel::load(MyExcelClass::getFile(), function ($reader){
            $reader->first(); // get first sheet
            foreach($reader as $sheet) // loop through sheets
            {
               $sheetTitle = $sheet->getTitle();
            }
        });
    }
}

You can also try $reader->selectSheet('NameOfSheet'). If this doesn't work trying calling selectSheet on the facade either after loading the sheet or within the callback.

I admit the documentation can be hard to parse. Why don't you start with storing an excel file somewhere in the storage path and hardcoding the path into the load function.

http://www.maatwebsite.nl/laravel-excel/docs/import