How to import data with Excel file in laravel

There are 2 plugins for importing data into the database using an Excel file in Laravel.

  1. PhpSpreadsheet
  2. maatwebsite/excel

but in this article, we are going to discuss PhpSpreadsheet, because the maatwebsite/excel does not perform well when you Import a large amount of data using an Excel file.

So last time i used maatwebsite/excel to upload my data into a database using Excel, but that were not getting more than 512 rows of Excel, then I used PhpSpreadsheet and it worked well even when I uploaded 800000 rows at one time, so let me show you I was done it with the help of PhpSpreadsheet:

Software requirements

  • PHP version 8.0 or newer to develop using PhpSpreadsheet.
  • You will need these PHP extensions, in my case I was using xammp, in xammp you can find the php.ini file that is part of the config of Apache, in that file you have to uncommitted code where you will get extensions
    Committed code:

Uncommitted Code:

like you have to remove ; from before the extension keyword.

Installation of phpspreadsheet:

So at the root directory of your project you need to run this command to install phpspreadsheet in your project:

If you are building your installation on a development machine that is on a different PHP version to the server where it will be deployed, or if your PHP CLI version is not the same as your run-time such as <strong>php-fpm</strong> or Apache’s <strong>mod_php</strong>, then you might want to add the following to your composer.json before installing:

then

How to Insert Data using phpspreadsheet:

Now we have to know how to insert data from Excel using phpspreadsheet, So here is an example of inserting data by Excel file using phpspreadsheet

1:Load Spreadsheet

This line uses the PHPExcel library (or a similar library) to load an Excel spreadsheet. <strong>$file</strong> seems to be an object representing the uploaded file, and <strong>getRealPath()</strong> retrieves the real path of the file.

2: Get Active Sheet:

This line gets the active sheet from the loaded spreadsheet. The active sheet is the sheet that is currently selected or visible.

3: Initialize Data Array

This line initializes an empty array called <strong>$data</strong>, which will be used to store the processed data before inserting it into the database.

4: Chunk Size for Insert

I have divided data into chunk like every step of loop will be insert 1000 rows because there is some limitation of server when you want to upload large amount of data as i said i uploaded 800000.

5: Loop through Rows

this loop to get each row from excel file

6: Loop through Cells in Row

Inside the row loop, another loop iterates through each cell in the current row. It retrieves the value of each cell and stores it in the <strong>$rowData</strong> array.

7: Prepare Data for Insert

This line creates an associative array with a single key ‘data’ and the corresponding value from the first cell of the current row. This structure is used to prepare the data for database insertion. if you want more columns you can do like:

in this example, the index of variable $rowData shows the location of the column of a row of Excel file, like the 0 index indicates the value of the first column and the index 1 is for the value of the second column

8: Insert Data

After processing a chunk of rows (determined by $chunkSize), the script inserts the accumulated data into the ‘tbl_data’ table using Laravel’s query builder (DB::table). The $data array is then reset for the next chunk.

9: Insert any remaining Record

After the row iteration is complete, this code block inserts any remaining records in the <strong>$data</strong> array into the ‘tbl_data‘ table.

Using following these steps you can upload unlimited data of of your excel file using Spreadsheet

Conclusion

In summary, the script reads data from an Excel spreadsheet row by row, processes it, and inserts it into a database table in chunks for optimization. The chunk size is determined by the <strong>$chunkSize</strong> variable. Finally, any remaining records are inserted into the database.

for further you can visit official website of PhpSpreadsheet : https://phpspreadsheet.readthedocs.io/

Leave a Comment

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