Import Export Excel & CSV in Laravel 9

--- title: Laravel Export/Import: Step-by-Step Guide with Repo Example published: true date: 2023-01-17 10:40:37 UTC tags: - PHP - Laravel - ExportImport canonical_url: https://fajarwz.com/blog/laravel-export-import-step-by-step-guide-with-repo-example/ --- ![Data illustration](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fq1ynj7kvxvip1wcxz5h.png) One of the common tasks in web development is the ability to export and import data. This can be useful for a variety of reasons, such as allowing users to download their data, or allowing administrators to bulk upload data into the system. In the Laravel ecosystem, we can use [`SpartnerNL/Laravel-Excel`](https://github.com/SpartnerNL/Laravel-Excel) package to handle our export and import functionality. This package is an easy-to-use wrapper for the popular PhpSpreadsheet library, and it allows us to easily export and import data from various file formats, including Excel, CSV, and ODS. ## Installing Laravel 9 To get started, we need to have Laravel installed on our machine. To do this, we will first need to have Composer, the PHP package manager, installed. Once Composer is installed, we can use it to install Laravel by running the following command: ``` composer create-project --prefer-dist laravel/laravel:^9.0 blog-laravel-excel ``` This will create a new Laravel project in a directory called `blog-laravel-excel.` ## Initializing the Database Before we can start exporting and importing data, we need to have a database set up and some sample data to work with. For this tutorial, we will create a database called `blog_laravel_excel`. Once the database is created, we will need to adjust the database connection settings in our Laravel app's `.env` file to match the new database name, as well as the database username and password. ## Initializing the Migration In Laravel 9, there is already a users migration in the `database/migrations` directory. However, you will need to make sure that it includes the necessary fields for name, email, and password before you run the migration. ```php public function up() { Schema::create('users', function (Blueprint $table) { $table->id(); $table->string('name'); $table->string('email')->unique(); $table->timestamp('email_verified_at')->nullable(); $table->string('password'); $table->rememberToken(); $table->timestamps(); }); } ``` ## Initializing the Seeder We also need to create a users seeder but in Laravel 9, there is already a factory and code in place to generate the seeders. You can use the factory to generate fake data for the users table. Just make sure that there is a factory called `UserFactory` in the `database/factories/UserFactory.php` file, and that it contains the following code: ```php */ class UserFactory extends Factory { /** * Define the model's default state. * * @return array */ public function definition() { return [ 'name' => fake()->name(), 'email' => fake()->unique()->safeEmail(), 'email_verified_at' => now(), 'password' => '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', // password 'remember_token' => Str::random(10), ]; } /** * Indicate that the model's email address should be unverified. * * @return static */ public function unverified() { return $this->state(fn (array $attributes) => [ 'email_verified_at' => null, ]); } } ``` In addition, check the `run()` method from `database/seeders/DatabaseSeeder.php` file, to make sure it is calling the `UserFactory` to generate the necessary number of fake data for seeding the users table. ```php public function run() { \App\Models\User::factory(10)->create(); } ``` Once we have our migration and seeder set up, we can run the following command to run the migration and seed the data into the `users` table: ``` php artisan migrate --seed ``` ## Installing the Laravel-Excel Package Next, we need to install the Laravel-Excel package. We can do this by running the following command: ``` composer require maatwebsite/excel ``` This will add the version 3.1 package to our project and make it available for use. ## Package Configuration With our database set up and seeded with data, we can now move on to configuring the Laravel-Excel package. The `Maatwebsite\Excel\ExcelServiceProvider` is auto-discovered and registered by default, so no need to add it manually in the config file. The `Excel` facade is also auto-discovered, so there is no need to register it manually. To publish the config, we can run the vendor publish command: ``` php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=config ``` This command will create a new config file named `config/excel.php`, where you can customize the package's settings as per your requirement. > **Read also:** > * [Remote Work: Effective Strategies for Communication and Timely Responses](https://fajarwz.com/blog/remote-work-effective-strategies-for-communication-and-timely-responses/) > * [Laravel with(): How to Solve the N+1 Problem with Eager Loading Relationships](https://fajarwz.com/blog/laravel-with-how-to-solve-the-n+1-problem-with-eager-loading-relationships/) ## Create Route file Create 3 routes in the routes/web.php file that handle the import and export functionality for Excel and CSV files. ```php name('users.index'); // view Route::post('import', [UserController::class, 'import'])->name('users.import'); // import route Route::get('export', [UserController::class, 'export'])->name('users.export'); // export route ``` ## Create Export Class Create an export class in the `app/Exports` directory by utilizing the `make:export` command. ``` php artisan make:export UserExport --model=User ``` Here is the final code in app/Exports/UserExport.php. ```php $row['name'], 'email' => $row['email'], 'password' => bcrypt($row['password']), ]); } } ``` ## Create UserController We will create a controller to manage the import and export of files such as Excel and CSV. To create the controller, we can use the following command: ``` php artisan make:controller UserController ``` This command will generate a new controller file in the `app/Http/Controllers` directory. Create index, import, and export methods as previously defined in our web routes. Alternatively, place the following code in the `app/Http/Controllers/UserController.php` file. ```php User::all(), ]); } public function import(Request $request) { // use Excel facade to import data, by passing in the UserImport class and the uploaded file request as arguments Excel::import(new UserImport, $request->file('file')->store('temp')); return back(); } public function export() { // use Excel facade to export data, by passing in the UserExport class and the desired file name as arguments return Excel::download(new UserExport, 'users.xlsx'); } } ``` ## Create the Blade View In this step, we will create a view for managing the importing and exporting of files through the front-end. To do this, create a `resources/views/users/index.blade.php` file and insert the following code: ```blade.php Import Export Excel & CSV in Laravel 9 {{-- we will use Bootstrap 5 --}}

Laravel 9 Import and Export CSV & Excel to Database

{{-- create a form tag with method post for the import feature --}} {{-- display users data --}} @foreach ($users as $key => $user) @endforeach

#	Name	Email
{{ ++$key }}	{{ $user->name }}	{{ $user->email }}

``` > **Read also:** > * [Send Slack Notifications From Laravel App](https://fajarwz.com/blog/send-slack-notifications-from-laravel-app/) > * [Create Login With Google OAuth Using Laravel Socialite](https://fajarwz.com/blog/create-login-with-google-oauth-using-laravel-socialite/) ## Run and Test It Now we have our UI, controller, and model set up, we can test our export and import functionality. We can do this by running the project and navigating to the users index page. ``` php artisan serve ``` Here is the endpoint that we can finally test: ``` http://127.0.0.1:8000/ ``` ![Index page](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ridlr7fwdy3myuw51iuy.png) We can then test uploading an excel file and check if the data is imported correctly, and also test exporting the data and check if the exported file is in the correct format. ### Import Feature Here is the data that will be imported: ![Import data](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8c28k9lr55gmbeooembv.png) Upload it by clicking 'Import data' and here is the list of user data after it has been successfully imported: ![Index page after import](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jj3fd2gth3o4fycuxx2d.png) ### Export feature Here is the exported data: ![Exported data](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/c0vq2lhxibsmzw83kfvy.png) ## Conclusion In conclusion, using the Laravel-Excel package, it is easy to add export and import functionality to a Laravel application. With its simple syntax and powerful features, it makes it easy to handle various file formats and manipulate data. This tutorial gives a simple example of how to implement export and import functionality in a Laravel application, but the package can be used for more complex use cases as well. The repository for this example can be found at [fajarwz/blog-laravel-excel](https://github.com/fajarwz/blog-laravel-excel).

exportimport

Laravel Export/Import: Step-by-Step Guide with Repo Example

Join Our Newsletter. No Spam, Only the good stuff.