Import Export CSV And Excel File in Laravel 9

Published on : July 22,2022

Hi Developer,

In this article you will learn how to easily import and export csv file in Laravle 9 application while communicating with the PHP MySQL database using Maatwebsite/Laravel-Excel package.

Set up Laravel Environment

In general, to run the PHP commands or even interact with Laravel, you need to set up Composer on your development machine. After downloading and setting up the composer follow the below process.

install a new Laravel application:

composer create-project laravel/laravel import-export-excel-csv-laravel9 --prefer-dist

Go to project root directory using bellow command:

cd import-export-excel-csv-laravel9

Add Database Information in .env

Next setup Import Export Excel and CSV File to MySQL database information . So you need to make the connection between Laravel and MySQL by adding the database name, username, and password in .env file.

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=laravel //database name 
DB_USERNAME=root
DB_PASSWORD=

Install Maatwebsite/Laravel-Excel Package

After set database connection You need to run the below command to install Maatwebsite/Laravel-Excel package in Laravel App:

composer require maatwebsite/excel

After installation above package, open config/app.php file and add bellow lines in providers and aliases array simultaneously:

'providers' => [
  .......
  Maatwebsite\Excel\ExcelServiceProvider::class, 
 ],  

'aliases' => [ 
  .......
  'Excel' => Maatwebsite\Excel\Facades\Excel::class,
],

After updated config/app.php file you have to run vendor publish command so easily publish the config and push it inside the config/excel.php:

php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider"

Now, you can see a newly generated config file in excel.php inside config folder.

Create a Demo Records in Database Table

You have to create some records in the database to export and import to CSV/Excel format, so use the migrate command to migrate the User table which comes default with Laravel.

php artisan migrate

Run command to enter into Psy PHP Shell:

php artisan tinker

Run bellow command to create some dummy records, and yes you can check them inside the database users table:

User::factory()->count(60)->create();

Create Data Imports Class

Create import and export class specifically for maatwebsite package, and later you will have to use both the classes in the controller file.

php artisan make:import UsersImport --model=User

Next stpe is copy or write bellow code inside app/Imports/UsersImport.php file:

<?php

namespace App\Imports;
use App\Models\User;
use Illuminate\Support\Facades\Hash;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithHeadingRow;

class UsersImport implements ToModel
{
    /**
    * @param array $row
    *
    * @return \Illuminate\Database\Eloquent\Model|null
    */
    public function model(array $row)
    {
        return new User([
            'name'     => $row[0],
            'email'    => $row[1],
            'password' => Hash::make($row[2])
        ]);
    }
}

Also, fire enter command in your console and hit enter to generate UsersExport class:

php artisan make:export UsersExport --model=User

You can see following file has been generated in app/Exports/UsersExport.php path:

<?php

namespace App\Exports;

use App\Models\User;
use Maatwebsite\Excel\Concerns\FromCollection;

class UsersExport implements FromCollection
{
    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        return User::all();
    }
}

Creating and Getting Ready Controller

To create the class file of importing and exporting Excel and CSV files in Laravel 9, you have to generate a new controller and write logic within the controller

Run the below command generates the UserImportExportController.

php artisan make:controller UserImportExportController

We have created three methods, the importExport() method contains the view method which initializes the view in laravel app, whereas importFile() and exportFile() processes import and export features respectively.

Add the following code in the app/Http/Controllers/UserImportExportController.php.

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;

use Maatwebsite\Excel\Facades\Excel;
use App\Exports\UsersExport;
use App\Imports\UsersImport;

class UserImportExportController extends Controller
{

    public function importExport()
    {
       return view('welcome');
    }
   
    public function importFile(Request $request) 
    {
        Excel::import(new UsersImport, $request->file('file')->store('temp'));
        return back();
    }

    public function exportFile() 
    {
        return Excel::download(new UsersExport, 'users.xlsx');
    }  
}

Creat Route

You need to add three routes to displaying view, import and export excel and CSV file:

Addind the following code inside routes/web.php file:

<?php

use Illuminate\Support\Facades\Route;
use App\Http\Controllers\UserImportExportController;


Route::get('import-export', [UserImportExportController::class, 'importExport']);
Route::post('import-file', [UserImportExportController::class, 'importFile'])->name('import-file');
Route::get('export-file', [UserImportExportController::class, 'exportFile'])->name('export-file');

Create Blade View

Now, finally, you have to create a view which rewards our intense hard work, for the Laravel 9 export to excel example go to resources/views/welcome.blade.php file and replace it with the below code.

<!DOCTYPE html>
<html lang="">
<head>
	<meta charset="utf-8">
	<meta http-equiv="X-UA-Compatible" content="IE=edge">
	<meta name="viewport" content="width=device-width, initial-scale=1">
	<title>Import Export Csv</title>
</head>
<body>
	<div class="container mt-5 text-center">
		<form action="{{ route('import-file') }}" method="POST" enctype="multipart/form-data"> 
			@csrf 
			<div class="form-group mb-5" style="max-width: 600px; margin: 0 auto;">
				<div class="custom-file text-left">
					<input type="file" name="file" class="custom-file-input" id="customFile">
					<label class="custom-file-label" for="customFile">Browse file</label>
				</div>
			</div>
			<button class="btn btn-danger">Click to Import</button>
			<a class="btn btn-primary" href="{{ route('export-file') }}">Click to Export</a>
		</form>
	</div>
</html>

Run Laravel Application

And, now we are done all coding steps just start the app and test:

php artisan serve

Here is the endpoint to show the result:

http://localhost:8000/import-export

Now, you can easily export and import the Anytype if database tables records from the database in .xlsx format:

The Laravel Import and Export into Excel article is over,

Hope it can help you…

Categories : Laravel

Tags : PHP Laravel

Praful Sangani

I'm a passionate full-stack developer with expertise in PHP, Laravel, Angular, React Js, Vue, Node, Javascript, JQuery, Codeigniter, and Bootstrap. I enjoy sharing my knowledge by writing tutorials and providing tips to others in the industry. I prioritize consistency and hard work, and I always aim to improve my skills to keep up with the latest advancements. As the owner of Open Code Solution, I'm committed to providing high-quality services to help clients achieve their business goals.