Symfony 3/3.4中PhpSpreadsheet使用咨询及Excel操作示例请求

Hey there! Let's tackle your Symfony 3.x and PhpSpreadsheet questions one by one—this is a common use case, so I'll break it down with clear steps and examples.

1. Importing Excel Data to Database in Symfony 3.4 with PhpSpreadsheet #

Here's a step-by-step approach to get your Excel data into your database:

Step 1: Install PhpSpreadsheet #

First, add the library to your project via Composer:

composer require phpoffice/phpspreadsheet

Step 2: Create an Import Handler (Command or Service) #

Command-line commands are great for import tasks because they're easy to test and run manually. Here's an example command that reads an Excel file, maps rows to an entity, and persists to the database:

// src/AppBundle/Command/ImportExcelCommand.php
namespace AppBundle\Command;

use Symfony\Bundle\FrameworkBundle\Command\ContainerAwareCommand;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Output\OutputInterface;
use PhpOffice\PhpSpreadsheet\IOFactory;
use AppBundle\Entity\YourEntity; // Replace with your actual entity

class ImportExcelCommand extends ContainerAwareCommand
{
    protected function configure()
    {
        $this
            ->setName('app:import-excel')
            ->setDescription('Imports data from Excel to your database');
    }

    protected function execute(InputInterface $input, OutputInterface $output)
    {
        // Path to your Excel file (adjust this to your file's location)
        $filePath = $this->getContainer()->getParameter('kernel.root_dir') . '/../exemple_file.xlsx';
        
        // Load the spreadsheet
        $spreadsheet = IOFactory::load($filePath);
        $worksheet = $spreadsheet->getActiveSheet();
        $highestRow = $worksheet->getHighestRow();

        $em = $this->getContainer()->get('doctrine.orm.entity_manager');

        // Skip header row (remove this loop if your file has no header)
        for ($row = 2; $row <= $highestRow; $row++) {
            // Extract data from each column (adjust columns to match your file)
            $rowData = [
                'name' => $worksheet->getCell('A' . $row)->getValue(),
                'email' => $worksheet->getCell('B' . $row)->getValue(),
                'phone' => $worksheet->getCell('C' . $row)->getValue(),
            ];

            // Map data to your entity
            $entity = new YourEntity();
            $entity->setName($rowData['name']);
            $entity->setEmail($rowData['email']);
            $entity->setPhone($rowData['phone']);

            $em->persist($entity);
        }

        // Save all entities to the database
        $em->flush();
        $output->writeln(sprintf('Successfully imported %d rows!', $highestRow - 1));
    }
}

Step 3: Run the Command #

Execute the command in your terminal:

php bin/console app:import-excel

Note: Don't forget to replace YourEntity and the field mappings with your actual entity and Excel columns.

2. Using PhpSpreadsheet in Symfony 3: roromix/SpreadsheetBundle or Direct Library? #

Should You Use roromix/SpreadsheetBundle? #

The roromix/SpreadsheetBundle is a Symfony-specific wrapper for PhpSpreadsheet that integrates the library into Symfony's service container. It's totally valid to use in Symfony 3—just make sure you install version 2.x (which supports Symfony 3 and PhpSpreadsheet):

composer require roromix/spreadsheet-bundle "^2.0"

Then register the bundle in your AppKernel.php:

// app/AppKernel.php
public function registerBundles()
{
    $bundles = [
        // ... other bundles
        new roromix\Bundle\SpreadsheetBundle\RoromixSpreadsheetBundle(),
    ];
    return $bundles;
}

The bundle provides convenience like pre-configured services, but if you don't need those extras, you can just use the raw PhpSpreadsheet library directly (as shown in the first section)—both approaches work perfectly in Symfony 3.

Example: Reading exemple_file.xlsx (Using the Bundle) #

Here's how to read your Excel file in a controller using the bundle:

// src/AppBundle/Controller/ExcelReaderController.php
namespace AppBundle\Controller;

use Symfony\Bundle\FrameworkBundle\Controller\Controller;
use Symfony\Component\HttpFoundation\Response;

class ExcelReaderController extends Controller
{
    public function readAction()
    {
        // Get the spreadsheet factory service from the bundle
        $spreadsheetFactory = $this->get('roromix_spreadsheet.factory');
        
        // Load your Excel file
        $spreadsheet = $spreadsheetFactory->loadSpreadsheet(
            $this->get('kernel')->getRootDir() . '/../exemple_file.xlsx'
        );

        $worksheet = $spreadsheet->getActiveSheet();
        $highestRow = $worksheet->getHighestRow();

        // Loop through each row and process data
        $rowData = [];
        for ($row = 1; $row <= $highestRow; $row++) {
            $rowData[] = [
                'column_a' => $worksheet->getCell('A' . $row)->getValue(),
                'column_b' => $worksheet->getCell('B' . $row)->getValue(),
            ];
        }

        // For demonstration, we'll dump the data
        dump($rowData);

        return new Response('Check the Symfony profiler for the parsed data!');
    }
}

Example: Direct Library Usage (No Bundle) #

If you prefer skipping the bundle, here's a minimal example to read your file:

// Anywhere in your Symfony app (controller, service, etc.)
use PhpOffice\PhpSpreadsheet\IOFactory;

$filePath = __DIR__ . '/../../exemple_file.xlsx'; // Adjust path as needed
$spreadsheet = IOFactory::load($filePath);
$worksheet = $spreadsheet->getActiveSheet();
$highestRow = $worksheet->getHighestRow();

for ($row = 1; $row <= $highestRow; $row++) {
    echo "Row $row: ";
    echo "A = " . $worksheet->getCell('A' . $row)->getValue() . ", ";
    echo "B = " . $worksheet->getCell('B' . $row)->getValue() . "\n";
}

Note: I assumed your file is .xlsx—if it's actually .xlst (a less common format), PhpSpreadsheet should still handle it, but you may need to specify the reader explicitly if auto-detection fails.

内容的提问来源于stack exchange，提问作者Imad Amzil