Import and Export Excel File Data in Laravel

1163 VIEWS

Introduction 

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. 

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 Table

After 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 Laravel

To 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 Laravel

In 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 Routes

Then, 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 application

Now, 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

Conclusion 

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 here

 


Faith Kilonzi is a full-stack software engineer, technical writer, and a DevOps enthusiast, with a passion for problem-solving through implementation of high-quality software products. She holds a bachelor’s degree in Computer Science from Ashesi University. She has experience working in academia, fin-tech, healthcare, research, technology, and consultancy industries in both Kenya, Ghana, and in the USA. Driven by intellectual curiosity, she combines her passion for teaching, technology, and research to create technical digital content.


Discussion

Click on a tab to select how you'd like to leave your comment

Leave a Comment

Your email address will not be published. Required fields are marked *