[FIXED] How to check if the first row or columns of Excel file is valid in Laravel Excel?

Issue

I'm trying to check if the excel file a user has uploaded has the correct columns/first row/attributes before reading into its other rows. I'm using Laravel 4 with MaatWebsite's Laravel Excel.

I have created a function which allows users to update the employee table of the system's database by importing an excel file.

Now the correct excel file, has, let's say, 3 columns: firstname, lastname, and username. If the excel file has all these attributes/columns, I will now read each of the following rows and validate each row, which isn't really a part of my problem as of now.

But if any of the 3 attribute/column is not present in the excel file, I'd ignore the request and return an error.

I tried using this, which gets lastname attribute:

$sample = Excel::selectSheetsByIndex(0)->load($readFile, function($reader){})->get(array("lastname"));

But even though lastname hasn't been found, $sample is still not null so I won't be able to check if lastname is present or not.

How can I check if the attributes/columns are present/not?

UPDATE:

The answer that I selected would work perfectly if the first row after the attributes row has all the needed attributes. For example: If it has values for firstname, lastname, and username.

But if in cases where first name value (or any attritbute value for that matter) of the first non-attribute row (attribute row referring to the column names) is missing, then the provided script would return false, even if the excel file has all the firstname, lastname, and username attributes.

So I modified the script to this:

1. First, I read the excel file. I also declared a Boolean variable to mark if the excel file is valid or not.

$excelChecker = Excel::selectSheetsByIndex(0)->load('path/to/file', function($reader){})->get()->toArray();

$excelIsValid = false;

2. Then I loop through all the results and check if at least once, all the values required (firstname, lastname, and username) have been set or are present.

   foreach($excelChecker as $ex){
   
       if(isset($ex["lastname"]) && isset($ex["firstname"]) && isset($ex["username"])){
            $excelIsValid = true;
      }
   }
   

Explanation:

If $excelIsValid is still false at the end of the loop, then not once did the file had all the attributes required. This either means the file is empty, has the wrong attribute names, or does not have any valid row. In short, the file is invalid and should not be in the system.

Solution

I prepared this sample script:

Route::get('/', function() {

    $isError = false;

    Excel::load('file.xls', function($reader) use (&$isError) {

        $firstrow = $reader->first()->toArray();

        if (isset($firstrow['firstname']) && isset($firstrow['lastname']) && isset($firstrow['username'])) {
            $rows = $reader->all();
            foreach ($rows as $row) {
                echo $row->firstname.' '.$row->lastname.' '.$row->username."<br />";
            }
        }
        else {
            $isError = true;

        }

    });
    if ($isError) {
        return View::make('error');
    }
});

The script loads file and check if there are columns you want. It doesn't check if there are more columns - of course if you want you can add exta check - count($firstow) == 3. If there is error it sets $isError to true and in route it displays a template.

Answered By - Marcin Nabiałek

Answer Checked By - Candace Johnson (PHPFixing Volunteer)