Transform your excel data into a relational database: Symfony

New to Back-End Development with Symfony?
Have been struggling while searching how to develop the functionality of importing Excel data in your database? No problem!

I did a lot of research to present you, my fellow reader, the cream of the crop, thus, you can save a lot of time, and learn how to import data from an XLSX file to your DataBase with Symfony…

Let’s break the ice :

I’ll be working with phpSpreadSheet, testing with Postman.

Environment:

• phpMyAdmin
• Symfony: v4.12.10
• PHP: v7.2.19

To start with :

1. Install the phpSpreadSheet in your Symfony project:

composer require phpoffice/phpspreadsheet

2. I’ll assume that you already have a Controller, a user Entity, so all you need to do at first is to import it:

use PhpOffice\PhpSpreadsheet\IOFactory;

3. Define your route in the Controller:

/**
   * @Route("/upload-excel", name="xlsx")
*/

4. Assuming that you have an XLSX file containing a list of users that you want to add to your User table, something like that:

example of an excel file containing a list of users

5. Let’s code 💻:

Wondering what is the purpose of each variable?
No worries, I’ll explain it:

• Now, Let’s observe the result in Postman:

• Continuing with code; we will upload the data in our DB:

• Now if we check our User Table we’ll find the data imported from the excel file.

BOOM 😁!

Bonus Step:

• Check that the person connected and making the request has a “ROLE_ADMIN”

Hope that this will save you time, help you with your project and your learning.

#Ps: I am brand new in Back-End development and it is the first time that I use Symfony to build the back-end of my site.