Import Excel data in Symfony database

Question

I'm working on a project where I need to import Excel data to my Symfony database. But the problem is that I don't know how to do that. I tried with ExcelBundle. The project is: User has to use a form button to send his Excel file and I need to extract the data without headers to fill my Database. Can you help me ?

Answer 1

If you can get your excel spreadsheet into CSV format, there is a really good package that can deal with it!

Have a look at this: http://csv.thephpleague.com/9.0/

Here's their example showing how easy it is to get your table into the DB

<?php

use League\Csv\Reader;

//We are going to insert some data into the users table
$sth = $dbh->prepare(
    "INSERT INTO users (firstname, lastname, email) VALUES (:firstname, :lastname, :email)"
);

$csv = Reader::createFromPath('/path/to/your/csv/file.csv')
    ->setHeaderOffset(0)
;

//by setting the header offset we index all records
//with the header record and remove it from the iteration

foreach ($csv as $record) {
    //Do not forget to validate your data before inserting it in your database
    $sth->bindValue(':firstname', $record['First Name'], PDO::PARAM_STR);
    $sth->bindValue(':lastname', $record['Last Name'], PDO::PARAM_STR);
    $sth->bindValue(':email', $record['E-mail'], PDO::PARAM_STR);
    $sth->execute();
}

Give it a try!

Answer 2

As mentioned in a comment you can use PHPExcel. Install the library using composer

composer require phpoffice/phpexcel

A typical reader might look something like

class GameImportReaderExcel
{

    public function read($filename)
    {
        // Tosses exception
        $reader = \PHPExcel_IOFactory::createReaderForFile($filename);

        // Need this otherwise dates and such are returned formatted
        /** @noinspection PhpUndefinedMethodInspection */
        $reader->setReadDataOnly(true);

        // Just grab all the rows
        $wb = $reader->load($filename);
        $ws = $wb->getSheet(0);
        $rows = $ws->toArray();

        foreach($rows as $row) {
            // this is where you do your database stuff
            $this->processRow($row);
        }

Call the reader class from your controller

public function (Request $request)
{
    $file = $request->files->has('file') ? $request->files->get('file') : null;
    if (!$file) {
        $errors[] = 'Missing File';
    }

    $reader = new GameImportReaderExcel();
    $reader->read($file->getRealPath());

That should get you started. And yes you could convert to csv but why bother. Just as easy to read the raw file and save your users an extra step.

Answer 3

You can use fgetcsv PHP function, an exemple here.

Beford the Excel file must be changed to a CSV file.