Monday, 25 February 2019

Import Excel File in Laravel



If you are using Laravel Framework for your web development, and if you are beginner in Laravel. Then this post will help you to learn something new in Laravel. Because in this post you can find how to import Excel spreadsheet data and insert into mysql database in Laravel. Here for importing Excel file data here we will use Laravel Maatwebsite package. By using this package we can easily import data from Excel sheet and insert into table in Laravel application.

In some of the businees houses in which there are many large number of data has been stored in Excel, and after storing that data, they want some application which helps them to store in web application database, that means import into web application database. Then at that time if your web application has been made in PHP Laravel framewor then this post will help you to make importing excel file data feature in Laravel application by using Maatwebsite package.

In this post we will see or learn how can we import any type of Excel speadsheet in .xls, .xlsx or CSV data imported into Mysql database in Laravel. For communicate excel file data in Laravel, here have use Maatwebsite Laravel Excel package, this package will help to communicate excel file data in Laravel application. Below you can find complete step by step process of how to use Laravel maatwebsite package for import Excel sheet data into database in Laravel.






Step 1 - Create Table


First, we have to create table in Mysql Database, so run following SQL script, it will make tbl_customer table 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 - Mysql Database connection in Laravel


After this you have to make database connection. For this first you have to open database.php file from config. And in this file you have to define your database configuration.


<?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,
        ],

.............

];



After this you have to open .env file, and in this file also you have to define Mysql database configuration also.


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 excel file data in Laravel, you have to first download Maatwebsite package, this package will communicate with Excel spreadsheet data. First for download package, you have to go to command prompt and write following command.


composer require maatwebsite/excel


This command will download this package in your Laravel working folder. After this we have to register this package in our Laravel application. For this we have to go to config/app.php file. And in this file you have to define providers and aliases.


<?php

return [

........

    'providers' => [

.......

        Maatwebsite\Excel\ExcelServiceProvider::class,

    ],

    'aliases' => [

........
        
        'Excel' => Maatwebsite\Excel\Facades\Excel::class,

    ],

];



This way we can register Maatwebsite package in Laravel application, now we can use this package for importing excel file data.

Step 4 - Controllers (ImportExcelController.php)


Now we have to make controller for handle http request for import data. In this controller we have use two use statement. First use DB is used for do mysql database operation, and second use Excel is for Maatwebsite package for import excel sheet data. In this controller, we have make two method.

index() - This is root method of this class, in this method it will fetch data from customer table and that data will be load in import_blade.php file in table format.

import() - This method has request for import excel file data. In this method first it has validate excel file format. If selected file other than excel sheet then it will return validation error. But suppose selected file is excel then it will proceed for import data. For import data here it has called Excel package class which has get data from excel file and convert into PHP array and then after insert into customer table. After successfully import of data it will return 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 (import_excel.blade.php)


This file has been load by index() method of ImportExcelController, On this file we have make form for select excel file from local computer for import data. Below form it will display tbl_customer table data. And above form we have define for display validation error message and success message.


<!DOCTYPE html>
<html>
 <head>
  <title>Import Excel File in Laravel</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">Import Excel File in Laravel</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 this we have to set the route of controller method. For this we have to open to routes/web.php file. In this file we can define route.


<?php

.......

Route::get('/import_excel', 'ImportExcelController@index');
Route::post('/import_excel/import', 'ImportExcelController@import');


Step 7 - Run Laravel Application


Lastly, we have to run Laravel application, for this we have to go to command prompt, and write following command.


php artisan serve


This command will Laravel application, it will return base url of Laravel application. Now for this application, we have to write following url in browser.


http://127.0.0.1:8000/import_excel


Above is the complete process of How to make import excel file data feature in Laravel using Maatwebsite Package.

30 comments:

  1. I am learning more things from your tutorials.Thanks Bro.

    /* small change in this tutorial */

    put $value in place of $row then run perfectly

    foreach($data->toArray() as $key => $value)
    {
    foreach($value as $row)
    {//echo ($value['customer_name']);exit;
    /* put $value in place of $row then run perfectly */
    $insert_data[] = array(
    'CustomerName' => $value['customer_name'],
    'Gender' => $value['gender'],
    'Address' => $value['address'],
    'City' => $value['city'],
    'PostalCode' => $value['postal_code'],
    'Country' => $value['country']
    );

    }
    }

    ReplyDelete
  2. Hello?
    Your Maatwebsite version is 2.1 and my Maatwebsite version 3.1. And i can't import excel file. 2.1 version is not working with laravel 5.7. how can i import excel file?
    Error -> Call to undefined method Maatwebsite\Excel\Excel::load()

    ReplyDelete
  3. Call to undefined method Maatwebsite\Excel\Excel::load() error show in laravel 5.7

    ReplyDelete
  4. Symfony \ Component \ Debug \ Exception \ FatalThrowableError (E_ERROR)

    Call to undefined method Maatwebsite\Excel\Excel::load()

    error while excel upload in database

    ReplyDelete
  5. soy nuevo en el framework php laravel me funciono en la version 5.8
    muchas Gracias por tu Aporte

    ReplyDelete
  6. How can I Excel::store() using new version of Maatwebsite

    ReplyDelete
  7. error Undefined index: customer_name
    pls help

    ReplyDelete
  8. Need excel file for testing

    ReplyDelete
  9. it comes error

    Call to undefined method Maatwebsite\Excel\Excel::load()

    ReplyDelete
  10. Hi, I am able to export CSV file but getting error "Property [manufacturer] does not exist on this collection instance." on loading XLSX file, pls suggest

    ReplyDelete
  11. Great Tutorial, please how to upload the same excel file and update rows without duplicate data

    ReplyDelete
  12. Hi. Just a small question. Is there a way where we can get selected Data from file. Like I just want data of a particular User. I dont need to perform loop operations. i need something like this.
    $data = \Excel::load($fileExcel)->where("column Name","Column Value")->get();

    ReplyDelete
  13. Call to undefined method Maatwebsite\Excel\Excel::load()
    i can change the maatwebsite version but i got this error

    ReplyDelete
  14. Nice tutorial. Unfortunately it failed on installing maatwebsite/excel with: Installation request for laravel/framework (locked at v6.0.1, required as ^6.0) -> satisfiable by laravel/framework[v6.0.1].

    ReplyDelete
  15. clear and neat explanation thank you very much.

    ReplyDelete
  16. Call to undefined method Maatwebsite\Excel\Excel::load()

    ReplyDelete
  17. how does the excel has to look?

    ReplyDelete
  18. Hello
    i have run this command as below
    composer require maatwebsite/excel

    then there is an error as function load below
    $data = Excel::load($path)->get();

    how can i fix the error? i have google the solution and it say that the load function can not be used more and need to use import().
    pls help me..thank you

    ReplyDelete
  19. duplicate data avoid or when importing the excel data then duplicate data update. please help me i am using maatwebsite package.

    ReplyDelete
  20. "Call to undefined method Maatwebsite\Excel\Excel::load()" affter run code help me please

    ReplyDelete
  21. Be careful as the load method has been remove for version 3.* check it out https://docs.laravel-excel.com/3.1/getting-started/upgrade.html#upgrading-to-3-from-2-1

    ReplyDelete
  22. I got error Undefined index: customer_name

    ReplyDelete
  23. Load doesn't work in maatwebsite excel version 3.0.

    ReplyDelete
  24. please explicitly tell what laravel version and maatwebsite version used in your tutorial so that any people trying to follow your guide will not misleaded. By the way it is nice tutorial, I have learnt many from your website. Thanks.

    ReplyDelete