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! 🚀
