Export HTML DataTable to excel in Laravel
If you are searching for add an export button in your laravel project, so your searching ends here. In this tutorial, I would like to share with you how to export Excel in the PHP laravel framework. I will show you step by step example of an EXPORT excel file using maatwebsite/excel version 3 composer package.
In this example, we will use maatwebsite/excel composer package for exporting Datatable. maatwebsite/excel provide an easy way to import and export using a database model. maatwebsite/excel updated version 3 and they provide a great way to import-export data from the database, so first follow a few steps to get an example.
Create MySQL table
CREATE TABLE `rooms` ( `id` int(11) NOT NULL AUTO_INCREMENT, `room_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `price` varchar(50) COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Update composer
composer update
Install Maatwebsite Package
composer require maatwebsite/excel
Now open config/app.php file and add service provider and aliase.
'providers' => [ .... Maatwebsite\Excel\ExcelServiceProvider::class, ], 'aliases' => [ .... 'Excel' => Maatwebsite\Excel\Facades\Excel::class, ],
Add HTML Export Button
<a href="<?php echo url('/admin/add-service'); ?>"><button type="button" class="btn btn-success"> Export </button></a>
Create Route
Route::get('export-excel', 'YourController@export-excel');
Create a Directory
create a folder under the public folder and give the name export
E:\xampp\htdocs\myProject\public\export
Controller Code
<?php namespace App\Http\Controllers; use Illuminate\Http\Request; use DB; use PhpOffice\PhpSpreadsheet\Spreadsheet; use PhpOffice\PhpSpreadsheet\Writer\Xlsx; class HomeController extends Controller { public function export_excel(){ $query = DB::table('rooms')->select('id' ,'room_name', 'price')->get()->toArray(); $spreadsheet = new Spreadsheet(); $sheet = $spreadsheet->getActiveSheet(); $sheet->setCellValue('A1', 'ID'); $sheet->setCellValue('B1', 'Room Name'); $sheet->setCellValue('C1', 'Price'); $rows = 2; foreach($query as $empDetails){ $sheet->setCellValue('A' . $rows, $empDetails->id); $sheet->setCellValue('B' . $rows, $empDetails->room_name); $sheet->setCellValue('C' . $rows, $empDetails->price); $rows++; } $fileName = "export_details.xlsx"; $writer = new Xlsx($spreadsheet); $writer->save("public/export/".$fileName); header("Content-Type: application/vnd.ms-excel"); return redirect(url('/')."/public/export/".$fileName); } } ?>
Hope this tutorial will be helpful for you.