A guide to import Data from an excel file in a Filament based app
Introduction
Importing data from external sources is a crucial part of any web app and some of these common external sources are CSVs and Excel files.
Thankfully, the Laravel community has built amazing packages that make this process a breeze.
In this article, we'll be looking at importing data from Excel files using a popular laravel package Laravel Excel
Configuration
We'll be making use of an existing project that we built for a filament tutorial on YouTube, if you haven't checked it yet, you can check the video here.
Basically, it's a simple student management system and we'll be adding a feature to import student's data.
And, since we'll be importing data from an Excel file, we also need an Excel file which should consist of data based on the type of import, in this case, our Excel file consists of the student's data including name, email, address, phone number and class/section data.
Implementing the Import Feature
Let's start by defining the import class where we'll define how we want to perform the import, Laravel Excel provides different ways to implement this, through Collection or from Model, in this case, we'll be using the model method. The documentation can be found here
Let's start by defining our Import Class, let's name this class StudentsImport
php artisan make:import StudentsImport
This will create a new file inside app/Imports, let's go ahead and populate our model method, we'll also implement WithHeadingRow which lets us use heading names while accessing data from Excel, and SkipsEmptyRows traits which will basically skip any empty rows.
The final model method looks like this:
public function model(array $row)
{
return new Student([
'section_id' => self::getSectionId($row['class'], $row['section']),
'class_id' => self::getClassId($row['class']),
'name' => $row['name'],
'email' => $row['email'],
'address' => $row['address'],
'phone_number' => $row['phone_number'],
]);
}
If we look closely into the method, there are two static methods defined called getSectionId and getClassId, these methods are necessary because the user will pass the Class name and Section name, and we need to find their respective IDs, for Section, we're passing Class data as well, since every Section belongs to a Class and we need to find the Section relative to that Class.
The final StudentsImport class looks like this:
<?php
namespace App\Imports;
use App\Models\Classes;
use App\Models\Section;
use App\Models\Student;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\SkipsEmptyRows;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
class StudentsImport implements ToModel, WithHeadingRow, SkipsEmptyRows
{
public function model(array $row)
{
return new Student([
'section_id' => self::getSectionId($row['class'], $row['section']),
'class_id' => self::getClassId($row['class']),
'name' => $row['name'],
'email' => $row['email'],
'address' => $row['address'],
'phone_number' => $row['phone_number'],
]);
}
public static function getClassId($class)
{
$class = Classes::where('name', $class)->first();
return $class->id;
}
public static function getSectionId($class, $section)
{
$class_id = self::getClassId($class);
$section_model = Section::where([
'class_id' => $class_id,
'name' => $section
])->first();
return $section_model->id;
}
}
Defining the Filament Action
Let's go ahead and define our Custom Action, and where to define this action depends on the use case. In this case, we'll define this Action on the Students Index page.
Let's give it the name importStudents, define the label as Import Students along with a color of danger, and inside the form method, we'll pass the FileUpload component and name the form field as attachment
The action looks like this for now:
Action::make('importStudents')
->label('Import Students')
->color('danger')
->form([
FileUpload::make('attachment'),
])
For the action method, we'll grab the file from the storage folder, and make sure to link the storage folder to the public folder, this lets us access the files publicly.
The storage can be linked with the following command:
php artisan storage:link
After grabbing the file, we'll pass it to the StudentsImport class we defined earlier, this will take care of fetching the Class/Section ID from the database and inserting all the data into the database using a single insert query.
We'll also notify the user about the import using Filament's Notification package.
The final action looks like this:
Action::make('importStudents')
->label('Import Students')
->color('danger')
->form([
FileUpload::make('attachment'),
])
->action(function (array $data) {
// $data is an array which consists of all the form data
$file = public_path("storage/" . $data['attachment']);
Excel::import(new StudentsImport, $file);
Notification::make()
->success()
->title('Students Imported')
->body('Students data imported successfully.')
->send();
}),
FilamentPhp Course
I guess you've already figured out that I love the TALL stack along with FilamentPHP and I have been exploring it a lot lately, and to further improve your skills and help you get started, I have created a Udemy Course based on this stack, where we'll build multiple practical projects and learn a ton of stuff along the way.
