Laravel Excel: Export and Import Made Easy
Exporting and importing data is one of the most crucial feature in today's world, most of the modern application are very dependent on these kind of functionality to generate reports, financials, visitors or payment data. Sometimes you will have to generate excel sheet from the local database, or some other times you will have to import a data from a excel sheet to database. You can consider this as a way of communication between the client and application. Laravel provides many efficient options to build such functionality in a swift. In this complete guideline, we will explore both the functionality using Laravel Excel package and try with alternative methods to have the best insight.
How to export excel file on laravel 8
- Before you start
We will be working with laravel 8 and the package we will be using using is Laravel excel. Lets assume we have a User model with some data on the table. We will be creating a export functionality for this model.Lets start...
- Package Installation
Install the package in your laravel application by running this command:
composer require maatwebsite/excel
after that, you need to publish the config:
php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=config
Done! you have installed the package.
- Preparing routes and controllers:
Lets add a button in frontend, on clicking which will export the user model’s data in excel file
<a href="{{ url('/export') }}" target="_blank">Export</a>
then we create the route:
Route::get('/export', [UsersController::class, 'export']);
Note: you should have a controller called UsersController
after that we add the following function in UsersController:
namespace App\Http\Controllers;
use App\Exports\UsersExport;
use Maatwebsite\Excel\Facades\Excel;
class UsersController extends Controller
{
public function export()
{
return Excel::download(new UsersExport, 'users.xlsx');
}
}
note this controller function is expecting a file called UsersExport, we will be generating that now.
Also note that in second parameter, it is taking a string called ‘users.xlsx’ which will be our actual file name when we export. We can change the filename from here
- Export procedure
Now we will be generating the UsersExport file.
Lets run this command:
php artisan make:export UsersExport --model=User
Note: This command will create a template file for UserExport under App\Exports directory. If you dont want to use a specific model, you can omit --model=User from the command.
your export file should look like this(UsersExport.php):
namespace App\Exports;
use App\Models\User;
use Maatwebsite\Excel\Concerns\FromCollection;
class UsersExport implements FromCollection
{
public function collection()
{
return User::all();
}
}
We are done here.
Lets run the system
Php artisan serve
If all things we have done correctly, our export systems should be ready. Lets click the export button and a excel file should be downloaded to your local.
Laravel Excel: Customise export data by using collection
We have discussed above about how to start with exporting excel in laravel application. Now, we will see, how we can customize the export data by exporting from a collection.
- what is a collection?
In PHP Laravel, a collection works like a special container which holds a bunch of items in it. Usually it holds an array of data but you can perform a lot of actions on those data for your convenience. For example, filtering data, sorting or transforming to certain values. Collections are very handy way to manage all these actions very easily. In other words, you can think of it as a supercharged array that comes with various built in functions.
$collection = collect([1, 2, 3, 4, 5]);
$filtered = $collection->filter(function ($value) {
return $value > 2;
})->map(function ($value) {
return $value * 2;
});
- Export data from collection
By exporting excel from a collection, we can have the certain abilities like modifying header, changing values or adding custom columns
Lets imagine, you have a Order model, and you need following columns in excel file and the required data accordingly:
- Orders
- Id
- Order Date
- Invoice ID
- Customer Name
- Total Charge
Lets say, we have a order model with required fields in the database.
Now we will be going through same procedure like we did in the last article, the main difference is here, instead of user model, we will be using order model.
Assume, we have went through these steps:
- frontend button included
- route created
- export file generated from command
- controller integrated
So now we should have a file called OrderExport.
Lets modify the file like this:
<?php
namespace App\Exports;
use App\Models\Order;
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
class OrderExport implements FromCollection, WithHeadings
{
public function collection()
{
// get all the values from order models
$orders = Order::all();
// customize rows and columns as required
$rows = array();
foreach ($orders as $order){
$i = 1;
$cols = array();
$date = $order->created_at->format('d-m-Y');
array_push($cols, $i, $date, $order->orderId, $order->customer_name, $order->total_charge );
array_push($rows, $cols);
$i++;
}
// export rows as collection
return new Collection($rows);
}
public function headings(): array
{
return [
'#',
'Date',
'Order ID',
'Customer Name',
'Total Charge',
];
}
}
- Bit of explanation
First in the headings method, We are assigning the required column headings in excel file.
Then in the collection method, we first get all the data from orders model, then customize it and return it as a collection.
Laravel excel - Customize and export file from view
So we learnt about basic usage of laravel excel package, and customizing data from collection. Now, we will see an alternative approach for generating excel files. We can use laravel view to generate the file.
- Why using view
Exporting data from collection might seem a bit complicated. By using Laravel view, we can have more simplistic and quicker approach to have the same result.
- Lets proceed
We will use the same order model above to generate the excel data using Laravel view.
Assume, we have went through these steps from instruction above:
- frontend button included
- route created
- export file generated from command (OrderExport)
- controller integrated
Lets modify OrderExport the file like this:
<?php
namespace App\Exports;
use App\Models\Order;
use Illuminate\Contracts\View\View;
use Maatwebsite\Excel\Concerns\FromView;
class OrdersExport implements FromView
{
public function view(): View
{
return view('exports.orders', [
'orders' => Order::all()
]);
}
}
From here, laravel-excel will look for a file under Exports/orders.php, by which the excel file will be generated.
orders.php file should look like this:
<table>
<thead>
<tr>
<th>Index</th>
<th>Order No</th>
<th>Order Date</th>
<th>Customer Name</th>
<th>Total Charge</th>
</tr>
</thead>
<tbody>
@foreach($orders as $index => $order)
<tr>
<td>{{ $index + 1 }}</td>
<td>{{ $order->order_no }}</td>
<td>{{ $order->created_at->format('d-m-Y') }}</td>
<td>{{ $order->customer_name }}</td>
<td>{{ $order->total_charge }}</td>
</tr>
@endforeach
</tbody>
</table>
Note: here the <th>. columns will work as column head in excel file and the <td> values will show the customized values in corresponding fields. You can modify data as your need.
Done. If we have implemented the steps correctly, now it should generate a beautiful excel file when you click on the export button. Please dont forget to run the server before that.
Laravel Excel: How to import data from excel file to model
Apart from export functionality, we sometimes need to import a excel file and save data into model too. For those cases, laravel excel package is also very handy. As a developer it is a important feature to learn about. Lets explore how we can work with importing from excel files.
- Scenario
Lets assume, we have a model called Products and we have these fields:
- name
- description
- price
and we have a client who gave us a excel file where these data are provided. For example, column names are:
- Product Name
- Product Description
- Product Price
Now we will try to upload these data into our model.
- Lets jump in
Lets start by adding a form into the frontend.
<form action="{{ url('/import') }}" method="POST" enctype="multipart/form-data">
@csrf
<input type="excel" name="excel" accept=".xlsx, .xls, .csv">
<input type="submit" value="submit">
</form>
Here a excel file should be submitted with the form.
Then please add the route in web.php.
Route::post('import', [ImportController::class, 'import']);
In the controller, please add the following function:
<?php
use Maatwebsite\Excel\Facades\Excel;
use App\Imports\ProductImport;
public function import(Request $request){
$import = Excel::import(new ProductImport, $request->file('excel'), \Maatwebsite\Excel\Excel::XLSX );
return redirect()->back();
}
Now, as you can see, we need a ProductImport file to do the work.
Lets run this command to generate the fille:
php artisan make:import ProductImport
this command will generate a file under app\Imports\ProductImport.php.
Lets open the file and modify the codes like below:
<?php
namespace App\Imports;
use App\Models\Product;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\Validator;
use Maatwebsite\Excel\Concerns\ToCollection;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Maatwebsite\Excel\Imports\HeadingRowFormatter;
HeadingRowFormatter::default('none');
class ProductImport implements ToCollection, WithHeadingRow
{
public function collection(Collection $rows)
{
// validate
Validator::make($rows->toArray(), [
'*.Product Name' => 'required',
'*.Product Description' => 'required',
'*.Product Price' => 'required|numeric',
])->validate();
// save data to model
foreach ($rows as $row) {
$product = new Product;
$product->name = $row['Product Name'];
$product->description = $row['Product Description'];
$product->price = $row['Product Price'];
$product->save();
}
}
}
- Bit of understanding
Lets try to understand a bit with the code. Here each row from the excel file will be given as a data collection. we can access the columns in each row with file's column name like an array, for example $row['Product Name'] or $row['Product Price'].
Also we can add validation for each column. For example, Product Price column’s value should be required and numeric. This rule will be applied for all the fields under the column.
So we are done here. Now lets try to upload a excel file with correct header column and submit. All the excel data should be available in your Product model. Also dont forget to run your server.
Conclusion
From the discussion above, you can clearly understand how flexible it is to use Laravel Excel package for generating required documents you need. Personally I like the export technique from view but other options are also handy of course. What is your preference in this case? Let me know your thoughts in the comments below and give a love to this article.
Have a great day.
Comments