If you are using Laravel Framework for your web development and are a beginner in Laravel. Then this post will help you learn something new in Laravel. In this publication, you will learn how to import Excel worksheet data and insert it into the mysql database in Laravel. We use the Laravel Maat website package to import data from Excel files. With this package we can easily import data from the Excel spreadsheet and paste it into the spreadsheet in the Laravel application.
In some stores where a large amount of data has been saved in Excel, after saving this data, they want an application that supports them when they are saved in the web application database, ie when they are imported into the web application database. If your web application was created in PHP Laravel Framewor at this point, this publication will help you import the data function of Excel files into the Laravel application using the Maat website package.
In this publication, you will learn how to import any type of Excel spreadsheet into XLS, XLSX, or CSV files that have been imported into the MySQL database in Laravel. Use the Excel package Maatwebsite Laravel to transfer data from Excel files to Laravel. This package helps to transfer data from Excel files in the Laravel application. Below is a complete step-by-step guide on how to import Excel data into the Laravel database using the Maatwebsite package.
In some stores where a large amount of data has been saved in Excel, after saving this data, they want an application that supports them when they are saved in the web application database, ie when they are imported into the web application database. If your web application was created in PHP Laravel Framewor at this point, this publication will help you import the data function of Excel files into the Laravel application using the Maat website package.
In this publication, you will learn how to import any type of Excel spreadsheet into XLS, XLSX, or CSV files that have been imported into the MySQL database in Laravel. Use the Excel package Maatwebsite Laravel to transfer data from Excel files to Laravel. This package helps to transfer data from Excel files in the Laravel application. Below is a complete step-by-step guide on how to import Excel data into the Laravel database using the Maatwebsite package.
Step 1 - Create Table
First we have to create a table in the MySQL database. Therefore, run the following SQL script so that the tbl_customer table is created in your database.
--
-- Database: `testing`
--
-- --------------------------------------------------------
--
-- 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=146;
Step 2 – Laravel Database connection
Then you have to establish the database connection. To do this, you must first open the database.php file from the configuration. And in this file you have to define the configuration of your database.
Then you have to open the .env file and also define the configuration of the MySQL database in this file.
<?php
return [
'default' => env('DB_CONNECTION', 'mysql'),
...........
'mysql' => [
'driver' => 'mysql',
'host' => env('DB_HOST', 'localhost'),
'port' => env('DB_PORT', '3306'),
'database' => env('DB_DATABASE', 'testing'),
'username' => env('DB_USERNAME', 'root'),
'password' => env('DB_PASSWORD', ''),
'unix_socket' => env('DB_SOCKET', ''),
'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',
'prefix' => '',
'strict' => true,
'engine' => null,
],
.............
];
Then you have to open the .env file and also define the configuration of the MySQL database in this file.
DB_CONNECTION=mysql
DB_HOST=localhost
DB_PORT=3306
DB_DATABASE=testing
DB_USERNAME=root
DB_PASSWORD=
Step 3 - Download Maatwebsite Package
If you want to import data from Excel files into Laravel, you must first download the Maat website package. This package communicates with the data in the Excel spreadsheet. To download the package, you must first go to the command prompt and enter the following command.
This way we can register the Maat website package in the Laravel application. Now we can use this package to import data from Excel files.
composer require maatwebsite/excel
<?php
return [
........
'providers' => [
.......
Maatwebsite\Excel\ExcelServiceProvider::class,
],
'aliases' => [
........
'Excel' => Maatwebsite\Excel\Facades\Excel::class,
],
];
This way we can register the Maat website package in the Laravel application. Now we can use this package to import data from Excel files.
Step 4 - Controllers
Now we need to create a controller that handles the http request to import data. We have used two instructions for use in this control. The first use of DB is used for MySQL database operation and the second use of Excel for the Maatwebsite package for importing Excel worksheet data. We have two methods in this controller.
index (): This is the root method of this class. With this method, data is retrieved from the client table and loaded into the file import_blade.php in a table format.
import (): This method prompts you to import data from Excel files. With this method, you must first validate the Excel file format. If the selected file is not an Excel file, a verification error is returned. Assume that the selected file is Excel and continue importing the data. To import data here, it was called an Excel package class, which retrieved data from the Excel file and converted it into a PHP array and then inserted it into the client table. After successfully importing the data, you will receive a success message.
index (): This is the root method of this class. With this method, data is retrieved from the client table and loaded into the file import_blade.php in a table format.
import (): This method prompts you to import data from Excel files. With this method, you must first validate the Excel file format. If the selected file is not an Excel file, a verification error is returned. Assume that the selected file is Excel and continue importing the data. To import data here, it was called an Excel package class, which retrieved data from the Excel file and converted it into a PHP array and then inserted it into the client table. After successfully importing the data, you will receive a success message.
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use DB;
use Excel;
class ImportExcelController extends Controller
{
function index()
{
$data = DB::table('tbl_customer')->orderBy('CustomerID', 'DESC')->get();
return view('import_excel', compact('data'));
}
function import(Request $request)
{
$this->validate($request, [
'select_file' => 'required|mimes:xls,xlsx'
]);
$path = $request->file('select_file')->getRealPath();
$data = Excel::load($path)->get();
if($data->count() > 0)
{
foreach($data->toArray() as $key => $value)
{
foreach($value as $row)
{
$insert_data[] = array(
'CustomerName' => $row['customer_name'],
'Gender' => $row['gender'],
'Address' => $row['address'],
'City' => $row['city'],
'PostalCode' => $row['postal_code'],
'Country' => $row['country']
);
}
}
if(!empty($insert_data))
{
DB::table('tbl_customer')->insert($insert_data);
}
}
return back()->with('success', 'Excel Data Imported successfully.');
}
}
Step 5 - View File
This file was uploaded using the ImportExcelController's index () method. In this file we have a form that allows you to select the Excel file on the local computer to import data. The data is displayed in the tbl_customer table below the form. And on the form we have defined to display the verification error message and the success message.
<!DOCTYPE html>
<html>
<head>
<title>How To Import Excel File in Laravel Framework</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>
</head>
<body>
<br />
<div class="container">
<h3 align="center">How To Import Excel File in Laravel Framework</h3>
<br />
@if(count($errors) > 0)
<div class="alert alert-danger">
Upload Validation Error<br><br>
<ul>
@foreach($errors->all() as $error)
<li>{{ $error }}</li>
@endforeach
</ul>
</div>
@endif
@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>
@endif
<form method="post" enctype="multipart/form-data" action="{{ url('/import_excel/import') }}">
{{ csrf_field() }}
<div class="form-group">
<table class="table">
<tr>
<td width="40%" align="right"><label>Select File for Upload</label></td>
<td width="30">
<input type="file" name="select_file" />
</td>
<td width="30%" align="left">
<input type="submit" name="upload" class="btn btn-primary" value="Upload">
</td>
</tr>
<tr>
<td width="40%" align="right"></td>
<td width="30"><span class="text-muted">.xls, .xslx</span></td>
<td width="30%" align="left"></td>
</tr>
</table>
</div>
</form>
<br />
<div class="panel panel-default">
<div class="panel-heading">
<h3 class="panel-title">Customer Data</h3>
</div>
<div class="panel-body">
<div class="table-responsive">
<table class="table table-bordered table-striped">
<tr>
<th>Customer Name</th>
<th>Gender</th>
<th>Address</th>
<th>City</th>
<th>Postal Code</th>
<th>Country</th>
</tr>
@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
</table>
</div>
</div>
</div>
</div>
</body>
</html>
Step 6 - Set Route
After that, we need to set the path of the driver method. For this we have to open the routes / web.php file. In this file we can define the path.
<?php
.......
Route::get('/import_excel', 'ImportExcelController@index');
Route::post('/import_excel/import', 'ImportExcelController@import');
Step 7 - Run Laravel Application
Finally, we have to run the Laravel application. To do this, we have to go to the command prompt and enter the following command.
This command runs the Laravel application and returns the base URL of the Laravel application. Now we have to write the following URL into the browser for this application.
Above is the complete process of how to use the data import feature of Excel files in Laravel using the Maat website package.
php artisan serve
This command runs the Laravel application and returns the base URL of the Laravel application. Now we have to write the following URL into the browser for this application.
http://127.0.0.1:8000/import_excel
Above is the complete process of how to use the data import feature of Excel files in Laravel using the Maat website package.
