| In this post, I demonstrate how to import and export bulk Excel file data into and from a Laravel application using the PhpSpreadsheet library. PhpSpreadsheet is a PHP library that provides support for reading and writing different types of file formats such as Excel, CSV, HTML, SpreadsheetML and LibreOffice Calc, among others. Show
In modern web development, developers tend to build upon data-driven systems whenever the need to import or export data arises, while analyzing or reporting on it. In doing so, most applications use CSV or Excel files alongside the database when dealing with bulk data, since this is clearly more efficient than entering data, one entry at a time. Prerequisites To proceed with this tutorial, you need to either have an existing Laravel project or install a fresh project setup. If you are new to Laravel, follow these instructions to install a new project setup. Step 1 – Create TableAfter successfully installing a Laravel project, create a database and a table. Run the following SQL script in your database environment to create a ‘tbl_customer’ table that you can use to store your data. -- -- Database: `mydata` -- -- -------------------------------------------------------- -- -- Table structure for table `tbl_customer` -- CREATE TABLE `tbl_customer` ( `CustomerID` int(11) NOT NULL, `CustomerName` varchar(250) NOT NULL, `Gender` varchar(30) NOT NULL, `Address` text NOT NULL, `City` varchar(250) NOT NULL, `PostalCode` varchar(30) NOT NULL, `Country` varchar(100) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Indexes for table `tbl_customer` -- ALTER TABLE `tbl_customer` ADD PRIMARY KEY (`CustomerID`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `tbl_customer` -- ALTER TABLE `tbl_customer` MODIFY `CustomerID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=155; COMMIT;
Step 2 – Create a Mysql Database Connection in LaravelTo make a database connection, open the .ENV and define your database configuration. DB_CONNECTION=mysql DB_HOST=localhost DB_PORT=3306 DB_DATABASE=mydata DB_USERNAME=root DB_PASSWORD=
Step 3: Install PhpSpreadsheet in LaravelIn your project directory, run this command on your terminal to install PhpSpreadsheet library composer require phpoffice/phpspreadsheet Step 4: Create your View File (welcome.blade.php)Inside the resources/views directory, create a welcome.blade.php file which contains the form for uploading excel files, a table for loading the uploaded data from the database, as well as a button for exporting the data. Paste the following code here: <!DOCTYPE html>
<html>
<head>
<title>PHPSpreadsheet in Laravel</title>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/normalize/5.0.0/normalize.min.css">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.1.3/css/bootstrap.min.css" />
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.1.3/css/bootstrap.css" />
<link rel="stylesheet" href="https://cdn.datatables.net/1.10.21/css/dataTables.bootstrap4.min.css" />
</head>
<body>
<div class="container">
<div class="card-header bg-secondary dark bgsize-darken-4 white card-header">
<h4 class="text-white">Handling Excel Data using PHPSpreadsheet in Laravel</h4>
</div>
<div class="row justify-content-centre" style="margin-top: 4%">
<div class="col-md-8">
<div class="card">
<div class="card-header bgsize-primary-4 white card-header">
<h4 class="card-title">Import Excel Data</h4>
</div>
<div class="card-body">
@if ($message = Session::get('success'))
<div class="alert alert-success alert-block">
<button type="button" class="close" data-dismiss="alert">×</button>
<strong>{{ $message }}</strong>
</div>
<br>
@endif
<form action="{{url("import")}}" method="post" enctype="multipart/form-data">
@csrf
<fieldset>
<label>Select File to Upload <small class="warning text-muted">{{__('Please upload only Excel (.xlsx or .xls) files')}}</small></label>
<div class="input-group">
<input type="file" required class="form-control" name="uploaded_file" id="uploaded_file">
@if ($errors->has('uploaded_file'))
<p class="text-right mb-0">
<small class="danger text-muted" id="file-error">{{ $errors->first('uploaded_file') }}</small>
</p>
@endif
<div class="input-group-append" id="button-addon2">
<button class="btn btn-primary square" type="submit"><i class="ft-upload mr-1"></i> Upload</button>
</div>
</div>
</fieldset>
</form>
</div>
</div>
</div>
</div>
<div class="row justify-content-left">
<div class="col-md-12">
<br />
<div class="card">
<div class="card-header bgsize-primary-4 white card-header">
<h4 class="card-title">Customer Data Table</h4>
</div>
<div class="card-body">
<div class="pull-right">
<a href="{{url("export")}}" class="btn btn-primary" style="margin-left:85%">Export Excel Data</a>
</div>
<div class=" card-content table-responsive">
<table id="example" class="table table-striped table-bordered" style="width:100%">
<thead>
<th>Customer Name</th>
<th>Gender</th>
<th>Address</th>
<th>City</th>
<th>Postal Code</th>
<th>Country</th>
</thead>
<tbody>
@if(!empty($data) && $data->count())
@foreach($data as $row)
<tr>
<td>{{ $row->CustomerName }}</td>
<td>{{ $row->Gender }}</td>
<td>{{ $row->Address }}</td>
<td>{{ $row->City }}</td>
<td>{{ $row->PostalCode }}</td>
<td>{{ $row->Country }}</td>
</tr>
@endforeach
@else
<tr>
<td colspan="10">There are no data.</td>
</tr>
@endif
</tbody>
</table>
{!! $data->links() !!}
</div>
</div>
</div>
</div>
</div>
<script type="text/javascript" src="https://code.jquery.com/jquery-3.5.1.js"></script>
<script>
$(document).ready(function() {
$('#example').DataTable();
} );
</script>
</body>
</html>
Step 5: Create a Controller (ExcelController.php)In the app/Http/Controllers directory, create a controller file( ExcelController.php) where you can write functions to load the blade file and the data from the database, and also import and export the uploaded data.
To make use of the Phpspreadsheet library, you have to import its alias namespaces in the Controller. In this case, import the following classes: use Illuminate\Http\Request; use Illuminate\Support\Facades\DB; use PhpOffice\PhpSpreadsheet\Spreadsheet; use PhpOffice\PhpSpreadsheet\Reader\Exception; use PhpOffice\PhpSpreadsheet\Writer\Xls; use PhpOffice\PhpSpreadsheet\IOFactory; You will also need to make use of the following methods/functions inside the controller: index() – This is the root method of this controller class. Inside the function, create a query to fetch data from the tbl_customer , which will be loaded in the welcome.blade.php file, in table format. If the database table is empty, the table on the interface will be empty. importData() – This method has a request variable that contains the uploaded excel file data. Inside the function, validate the file format and check if it contains the data you need, after which you can read the containing data using the PhpSpreadsheet package. The PhpSpreadsheet library uses the IOFactory class that loads the excel data and extracts it row by row into a PHP array. Use the array to insert the extracted data into the tbl_customer table. After successfully importing data, redirect back to the main page, returning a success message. exportData()- This method queries the tbl_customer table to load the customer data and uses the PhpSpreadsheet library to write the data into an Excel file. It will download the excel file containing the data onto the local PC. <?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Reader\Exception;
use PhpOffice\PhpSpreadsheet\Writer\Xls;
use PhpOffice\PhpSpreadsheet\IOFactory;
class ExcelController extends Controller
{
/**
* @return \Illuminate\Contracts\View\Factory|\Illuminate\View\View
*/
function index()
{
$data = DB::table('tbl_customer')->orderBy('CustomerID', 'DESC')->paginate(5);
return view('welcome', compact('data'));
}
/**
* @param Request $request
* @return \Illuminate\Http\RedirectResponse
* @throws \Illuminate\Validation\ValidationException
* @throws \PhpOffice\PhpSpreadsheet\Exception
*/
function importData(Request $request){
$this->validate($request, [
'uploaded_file' => 'required|file|mimes:xls,xlsx'
]);
$the_file = $request->file('uploaded_file');
try{
$spreadsheet = IOFactory::load($the_file->getRealPath());
$sheet = $spreadsheet->getActiveSheet();
$row_limit = $sheet->getHighestDataRow();
$column_limit = $sheet->getHighestDataColumn();
$row_range = range( 2, $row_limit );
$column_range = range( 'F', $column_limit );
$startcount = 2;
$data = array();
foreach ( $row_range as $row ) {
$data[] = [
'CustomerName' =>$sheet->getCell( 'A' . $row )->getValue(),
'Gender' => $sheet->getCell( 'B' . $row )->getValue(),
'Address' => $sheet->getCell( 'C' . $row )->getValue(),
'City' => $sheet->getCell( 'D' . $row )->getValue(),
'PostalCode' => $sheet->getCell( 'E' . $row )->getValue(),
'Country' =>$sheet->getCell( 'F' . $row )->getValue(),
];
$startcount++;
}
DB::table('tbl_customer')->insert($data);
} catch (Exception $e) {
$error_code = $e->errorInfo[1];
return back()->withErrors('There was a problem uploading the data!');
}
return back()->withSuccess('Great! Data has been successfully uploaded.');
}
/**
* @param $customer_data
*/
public function ExportExcel($customer_data){
ini_set('max_execution_time', 0);
ini_set('memory_limit', '4000M');
try {
$spreadSheet = new Spreadsheet();
$spreadSheet->getActiveSheet()->getDefaultColumnDimension()->setWidth(20);
$spreadSheet->getActiveSheet()->fromArray($customer_data);
$Excel_writer = new Xls($spreadSheet);
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="Customer_ExportedData.xls"');
header('Cache-Control: max-age=0');
ob_end_clean();
$Excel_writer->save('php://output');
exit();
} catch (Exception $e) {
return;
}
}
/**
*This function loads the customer data from the database then converts it
* into an Array that will be exported to Excel
*/
function exportData(){
$data = DB::table('tbl_customer')->orderBy('CustomerID', 'DESC')->get();
$data_array [] = array("CustomerName","Gender","Address","City","PostalCode","Country");
foreach($data as $data_item)
{
$data_array[] = array(
'CustomerName' =>$data_item->CustomerName,
'Gender' => $data_item->Gender,
'Address' => $data_item->Address,
'City' => $data_item->City,
'PostalCode' => $data_item->PostalCode,
'Country' =>$data_item->Country
);
}
$this->ExportExcel($data_array);
}
}
Step 6: Set the RoutesThen, open to routes/web.php file to set up the links to the controller functions as follows: Route::get('/', '[email protected]');
Route::post('/import', '[email protected]');
Route::get('/export', '[email protected]');
Step 7: Run the applicationNow, go to the command prompt, and write the following command: php artisan serve
If you want to run the project on a different port, use the command below. Specify your preferred port number. php artisan serve --port=8080
This command will return the base url of the Laravel application, which you will write on your browser, and interact with it. Handling Excel Data using PHPSpreadsheet in Laravel  This post provided a step-by-step guide on exporting and importing excel file data in a Laravel project. The same logic may be used to tailor the functionality as per specific project needs. Get full code How to install Excel in Laravel 8?Step 1: Install Laravel Project. ... . Step 2: Configure Database Details. ... . Step 3: Install maatwebsite/excel package. ... . Step 4: Generate Fake Data and Migrate Table. ... . Step 5: Create a Routes. ... . Step 6: Create Import Class. ... . Step 7: Create Export Class. ... . Step 8: Create Controller.. How to import CSV file into laravel?Add CSV import to your JavaScript Laravel app. Step 1: install Laravel JavaScript and UseCSV library. First, we will need to install the Laravel JavaScript package: composer require laravel/javascript. ... . Step 2: Create the importer button. ... . Step 3: Add the JavaScript. ... . Step 4: Compile the JavaScript code and test.. How to import CSV file in Laravel 9?How to Import Export Excel & CSV File in Laravel 9. Step 1 – Download Laravel 9 Application.. Step 2 – Database Configuration.. Step 3 – Install maatwebsite/excel Package.. Step 4 – Configure maatwebsite/excel.. Step 5 – Create Routes.. Step 6 – Compose Import Export Class.. Step 7 – Create ExcelCSV Controller By Artisan Command.. How do I import a XLSX file?In the Get External Data - Excel Spreadsheet dialog box, in the File name box, specify the name of the Excel file that contains the data that you want to import. Click Browse and use the File Open dialog box to locate the file that you want to import. Specify how you want to store the imported data.
|
How to import excel file in laravel
Pos Terkait
Copyright © 2024 ketiadaan Inc.
