How to check if the first row or columns of Excel file is valid in Laravel Excel?
Solution 1
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.
Solution 2
In Laravel Excel 3.1 you can use
public function import()
{
$column= (new HeadingRowImport)->toArray('users.xlsx');
if (isset($column['firstname']) && isset($column['lastname']) && isset($column['username'])) {
//dd('columns found');
//your code
}else{
//dd('columns not found');
//if not found show error or something
}
}
You can do anything inside of that if else or you can use your logic
christianleroy
Updated on June 04, 2022Comments
-
christianleroy almost 2 years
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
, andusername
. 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 iflastname
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
, andusername
.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 thefirstname
,lastname
, andusername
attributes.So I modified the script to this:
- 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;
-
Then I loop through all the results and check if at least once, all the values required (
firstname
,lastname
, andusername
) 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 stillfalse
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.