How to upload data from a spreadsheet (excel) in laravel 8 and laravel 7

#laravel #php #webdev #beginners

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.