Good day, today I was working on something so I said, let me dish it out for your guys, I was working on a project management app, and the app allows a user to create a project, I added a feature that will let the user upload an excel sheet which will contain different cells, the app will then extract the value of the cells and add them to the different fields that are in the columns of the table in the database.
I will guide you on how to achieve the same with our CRUD app, the techniques can be extended to a larger scale, in fact in my app, it is so complex, I can extract many cells and add it to different tables and also as many rows of data with different data. Feel free to reach out to me in case you need clarification or you are experiencing an error.
Click on my profile to follow me to get more updates.
As usual of my articles, you can get the finish codes from the repo, and also the initial code from my previous article, How to create modal in Laravel 8 and Laravel 6/7 with AJax,
Step 1: Setup the app
- git clone https://github.com/Kingsconsult/laravel_8_modal.git
- cd laravel_8_modal/
- composer install
- npm install
- cp .env.example .env
- php artisan key:generate
- Add your database config in the .env file (you can check my articles on how to achieve that)
- php artisan migrate
- php artisan serve (if the server opens up, http://127.0.0.1:8000, then we are good to go)
- Navigate to http://127.0.0.1:8000/projects
Step 2: Install mattwebsite/excel
This is the package we are going to be using for our spreadsheet, a simple, but an elegant wrapper around PhpSpreadsheet.
composer require maatwebsite/excel
Step 3: Register the class in providers (optional)
You can register the provider class in the providers, this is optional because maatwebsite/excel is auto-discovered.
Go to config/app.php and add this
Maatwebsite\Excel\ExcelServiceProvider::class,
Also add the facade in the aliases below
'Excel' => Maatwebsite\Excel\Facades\Excel::class,
Step 4: Publish the config
php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider"
Step 5: Create an import class
php artisan make:import ProjectsImport --model=Models/Project
A folder called imports will be created in app/ that contains the ProjectsImport.php
Step 6: Write our import class
Edit the Projectsimport.php and add this concern to the top
use Maatwebsite\Excel\Concerns\WithHeadingRow;
This will help us to read the heading of our row and appropriate the various cells to the respective fields in the table, also implements it to the ProjectsImport class
<?php
namespace App\Imports;
use App\Models\Project;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
class ProjectsImport implements ToModel, WithHeadingRow
{
/**
* @param array $row
*
* @return \Illuminate\Database\Eloquent\Model|null
*/
public function model(array $row)
{
return new Project([
'name' => $row['name'],
'introduction' => $row['introduction'],
'location' => $row['location'],
'cost' => $row['cost']
]);
}
}
Step 7: Create the route
Go the web route file in the routes/web/ folder and add the following code, make sure to write your post route before the resource route.
<?php
use Illuminate\Support\Facades\Route;
use App\Http\Controllers\ProjectController;
/*
|--------------------------------------------------------------------------
| Web Routes
|--------------------------------------------------------------------------
|
| Here is where you can register web routes for your application. These
| routes are loaded by the RouteServiceProvider within a group which
| contains the "web" middleware group. Now create something great!
|
*/
Route::get('/', function () {
return view('welcome');
});
Route::post('projects/importProject', [ProjectController::class, 'importProject'])->name('importProject');
Route::resource('projects', ProjectController::class);
Step 8: Create the method in the controller
In our route, we created a post route, where the URI is 'projects/importProject', and the controller method is 'importProject', we also give it the same name. I always give my route name for reference and also to shorten my code in the frontend.
So we told our route that there is a method in our ProjectController called "importProject", now we need to create the method.
Call the class on the top of the controller, after the namespace
This is now our method in ProjectController
Step 9: Modify the index.blade.php file to accept the file upload
We added a form which contains an input tag with file type and name file, also a submit button, don't forget to add enctype="multipart/form-data" to the form tag.
Step 10: Create the spreadsheet
We are going to create a spreadsheet that contains two (2) sheets with different details, each sheet will serve as another row in our table,
first sheet
second sheet
That is all we need, let's try it
going to http://127.0.0.1:8000/projects
clicking on "choose file"
A file called crud sheet.xlsx have been selected
Clicking on upload button
You will see that no 1 and 2 are the projects we inputted in our spreadsheet and there is a notification of a successful creation of projects.
You can get the complete code from the repo.
Follow me for more of my articles, you can leave comments, suggestions and reactions
click the link to view my profile and follow me
Visit my other posts
Top comments (1)
Hi King
Can you please connect me on my mail id, mkjkit@gmail.com. I have some work for you.