Importing Data from Excel in Symfony

Importing data from Excel files is a common requirement in Symfony applications, especially for handling bulk data entry. In this article, we'll explore how to process Excel files, validate data, and store it in a database using Symfony.

Prerequisites

Ensure you have a Symfony project set up. If not, create one:

composer create-project symfony/skeleton my_project
cd my_project

Then, install the required packages:

composer require phpoffice/phpspreadsheet

Creating the Import Service

We'll create a service to handle Excel file processing. First, generate a service class:

mkdir src/Service

Create ExcelImporter.php:

namespace App\Service;

use PhpOffice\PhpSpreadsheet\IOFactory;
use Doctrine\ORM\EntityManagerInterface;
use App\Entity\Employee;
use App\Entity\Department;

class ExcelImporter
{
    private EntityManagerInterface $entityManager;

    public function __construct(EntityManagerInterface $entityManager)
    {
        $this->entityManager = $entityManager;
    }

    public function import(string $filePath): array
    {
        $spreadsheet = IOFactory::load($filePath);
        $sheet = $spreadsheet->getSheetByName('Employees');
        $rows = $sheet->toArray(null, true, true, true);
        
        $errors = [];
        
        foreach ($rows as $index => $row) {
            if ($index === 0) continue; // Skip header row
            
            try {
                $employee = new Employee();
                $employee->setName($row['A']);
                $employee->setPosition($row['B']);
                $employee->setDepartment($this->findDepartment($row['C']));
                $employee->setSalary((float) $row['D']);
                
                $this->entityManager->persist($employee);
            } catch (\Exception $e) {
                $errors[] = "Error at row $index: " . $e->getMessage();
            }
        }
        
        $this->entityManager->flush();
        return $errors;
    }
    
    private function findDepartment(string $name): ?Department
    {
        return $this->entityManager->getRepository(Department::class)->findOneBy(['name' => $name]);
    }
}

Creating the Import Controller

Now, let's create a controller to handle file uploads.

namespace App\Controller;

use App\Service\ExcelImporter;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\Routing\Annotation\Route;
use Symfony\Component\HttpFoundation\File\UploadedFile;

class ImportController extends AbstractController
{
    private ExcelImporter $excelImporter;

    public function __construct(ExcelImporter $excelImporter)
    {
        $this->excelImporter = $excelImporter;
    }

    #[Route('/import', name: 'import_form', methods: ['GET'])]
    public function showForm(): Response
    {
        return $this->render('import.html.twig');
    }

    #[Route('/import', name: 'import_employees', methods: ['POST'])]
    public function import(Request $request): Response
    {
        $file = $request->files->get('excel_file');

        if (!$file instanceof UploadedFile) {
            return $this->json(['error' => 'Aucun fichier envoyé'], Response::HTTP_BAD_REQUEST);
        }

        $filePath = $file->getPathname();
        $errors = $this->excelImporter->import($filePath);

        if (!empty($errors)) {
            return $this->json(['errors' => $errors], Response::HTTP_BAD_REQUEST);
        }

        return $this->json(['message' => 'Importation réussie !'], Response::HTTP_OK);
    }
}

Creating the Upload Form

Create templates/import.html.twig:

<!DOCTYPE html>
<html lang="en">
<head>
    <title>Import Excel</title>
</head>
<body>
    <h1>Upload Excel File</h1>
    <form action="{{ path('import_employees') }}" method="post" enctype="multipart/form-data">
        <input type="file" name="excel_file" required>
        <button type="submit">Upload</button>
    </form>
</body>
</html>

Testing the Import Feature

Run your Symfony server:

symfony server:start

Go to http://127.0.0.1:8000/import, upload an Excel file, and verify that data is correctly imported into the database.

Conclusion

Importing Excel files in Symfony is straightforward using phpoffice/phpspreadsheet. You can improve this by adding:

Validation rules (e.g., ensuring required columns are not empty)
Error logging
Background processing for large files

Let me know if you have questions or improvements! 🚀

Oussama GHAIEB