We have share new web tutorial on Laravel and in this post we have discuss how to export mysql data in laravel by using Laravel maatwebsite package for excel. By using this package we will seen how can we export data from mysql database to Excel spreadsheet in Laravel framework. Maatwebsite Laravel package give us very large functionality for export data into excel or csv file format in Laravel.
If we have developed very big ERP or e-commerce web application in Laravel framework then at that time we have required export of our mysql table data to excel format for reducing our database entry time in excel or csv file. So, by on single click we can transfer large amount of data in excel spreadsheet for our required purpose. We have already publish how to export data to excel sheet in PHP and codeigniter framework by using PHPExcel library. But in Laravel framework we will use maatwebsite package for excel file.
So, in this post we will seen how to make excel export functionality in our Laravel application by using maatwebsite package for excel. Here we have discuss step by step process for how to use maatwebsite package in Laravel application for export mysql table data to excel sheet.
Step 1 : Install maatwebsite/excel package
For install maatwebsite/excel package in our Laravel application we have to write following command in our command prompt.
composer require maatwebsite/excel
This command will download and install this package in our Laravel framework. After this we want to register this package in our application for we have to go to config/app.php file and add service provider and aliase details.
'providers' => [
....
Maatwebsite\Excel\ExcelServiceProvider::class,
],
'aliases' => [
....
'Excel' => Maatwebsite\Excel\Facades\Excel::class,
],
Step 2 : Create Controller
First we want to make one controller for handle http request in our laravel application. By using this controller we will display mysql data on web page and then after it will also handle request for export data to excel. First we want to write following command in command prompt for make controller in our laravel application.
php artisan make:controller ExportExcelController
This command will make ExportExcelController.php file under app/Http/Controllers folder. In this controller we have make two method. First index() method will display mysql table data on web page and second excel() method will export mysql table data to excel file.
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use DB;
use Excel;
class ExportExcelController extends Controller
{
function index()
{
$customer_data = DB::table('tbl_customer')->get();
return view('export_excel')->with('customer_data', $customer_data);
}
function excel()
{
$customer_data = DB::table('tbl_customer')->get()->toArray();
$customer_array[] = array('Customer Name', 'Address', 'City', 'Postal Code', 'Country');
foreach($customer_data as $customer)
{
$customer_array[] = array(
'Customer Name' => $customer->CustomerName,
'Address' => $customer->Address,
'City' => $customer->City,
'Postal Code' => $customer->PostalCode,
'Country' => $customer->Country
);
}
Excel::create('Customer Data', function($excel) use ($customer_array){
$excel->setTitle('Customer Data');
$excel->sheet('Customer Data', function($sheet) use ($customer_array){
$sheet->fromArray($customer_array, null, 'A1', false, false);
});
})->download('xlsx');
}
}
?>
Step 3 : Create View
After this for display output in browser we have to create view files under resources/views folder. This file for display output of laravel application on web page.
<!--
export_excel.blade.php
!-->
<!DOCTYPE html>
<html>
<head>
<title>Export Data to Excel in Laravel using Maatwebsite</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
<style type="text/css">
.box{
width:600px;
margin:0 auto;
border:1px solid #ccc;
}
</style>
</head>
<body>
<br />
<div class="container">
<h3 align="center">Export Data to Excel in Laravel using Maatwebsite</h3><br />
<div align="center">
<a href="{{ route('export_excel.excel') }}" class="btn btn-success">Export to Excel</a>
</div>
<br />
<div class="table-responsive">
<table class="table table-striped table-bordered">
<tr>
<td>Customer Name</td>
<td>Address</td>
<td>City</td>
<td>Postal Code</td>
<td>Country</td>
</tr>
@foreach($customer_data as $customer)
<tr>
<td>{{ $customer->CustomerName }}</td>
<td>{{ $customer->Address }}</td>
<td>{{ $customer->City }}</td>
<td>{{ $customer->PostalCode }}</td>
<td>{{ $customer->Country }}</td>
</tr>
@endforeach
</table>
</div>
</div>
</body>
</html>
Step 4 : Set Route
Lastly we want to set route for index() and excel() method in our application. For this we have to go to routes/web.php file and write following code for set route for above two method.
<?php
//routes/web.php
Route::get('/export_excel', 'ExportExcelController@index');
Route::get('/export_excel/excel', 'ExportExcelController@excel')->name('export_excel.excel');
?>
So here our code is ready for How to export mysql table data to excel file by using maatwebsite/excel package in Laravel.
Love
ReplyDeletecan you create video for import data from excel to database plesase
ReplyDeleteexcellent contribution. thank you very much.
ReplyDeletefacing problem
ReplyDelete"Trying to get property 'name' of non-object"
$st_array[]=array(
'name'=>$st->name,
'mobile'=>$st->mobile,
'email'=>$st->email,
'image'=>$st->image
);
thiss code
is this can be able to laravel 5.5
ReplyDeleteYou can download the 2.1 version , it worked with laravel 5.4
Deletethanks,but maatwebsite/excel has not create function in 3.0 version !
ReplyDeleteand
Call to undefined method Maatwebsite\Excel\Excel::create()
appears!!
same error here... do you find any solution?? plz reply
Deletesame problem. Do you have some fix?
Deletesame here
Delete...
same error here
Deletesame here
Deletemay be looping error, recheck all
DeleteCan anyone help me in downloading xlsx formate from command line in laravel,i could download csv formate,but xlsx file formate is showing error
ReplyDeletehelp me please this error Call to undefined method Maatwebsite\Excel\Excel::create()
ReplyDeleteNice tutorial thank you :)
ReplyDeleteIt worked for me, download laravel updated version
ReplyDeleteCode should be upgraded as the version has been changed....this code has some faults
ReplyDeleteJust passed it. The solution should accept ::download or ::store methods. ::Create is not more available in Maatwebsite/Excel 3.0
ReplyDeletehi, i also get same error
ReplyDeletemy error: Call to undefined method Maatwebsite\Excel\Excel::create()
laravel verson: 5.8
php: 7.1
hi
ReplyDeletei have same error appears
ReplyDeleteCall to undefined method Maatwebsite\Excel\Excel::create()
use the statement below as the return statement.
ReplyDeletereturn Excel::download($customer_array, 'users.xlsx');
Call to undefined method Maatwebsite\Excel\Excel::create()
ReplyDeleteerror
i have create function for upload data to my database and success, and a create function for export to excel form download cant show and not found show some error. please suggest me .thanks
ReplyDeleteHello Sir!
ReplyDeleteThis is very helpful tutorial and working fine. Can you please add filter in this existing code like date range and user wise export features?
Thank you.
Create function not found so not working this please give the best solution for this error
ReplyDeleteCall to undefined method Maatwebsite\Excel\Excel::create()
ReplyDeletemy laravel version
"maatwebsite/excel": "^3.1" and
"laravel/framework": "5.8.*"
https://wa.me/919925405228
Method App\Http\Controllers\downloadController::excel() does not exist
ReplyDeleteSuch error occurs..help me
Unable to resolve NULL driver for [Maatwebsite\Excel\Transactions\TransactionManager].
ReplyDeleteThank you but Call to undefined method Maatwebsite\Excel\Excel::create()
ReplyDelete