CODEWITHSUNDEEP
symfonyphpexcel
maestro
maestro

Reputation: 81

How to use liuggio/ExcelBundle to post excel data to database

Without Symfony, this is how I post excel data to database.

//  Include PHPExcel_IOFactory

require_once ('../Classes/PHPExcel/IOFactory.php');
$inputFileName = 'abc.xls';

//  Read your Excel workbook
try {
    $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
    $objPHPExcel = $objReader->load($inputFileName);
} catch(Exception $e) {
    die('Error loading file "'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage());
}

//  Get worksheet dimensions
$sheet = $objPHPExcel->getSheet(0); 
$highestRow = $sheet->getHighestRow(); 
$highestColumn = $sheet->getHighestColumn();

//  Loop through each row of the worksheet in turn
for ($row = 1; $row <= $highestRow; $row++){ 

    //  Read a row of data into an array
    $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row,
                                    NULL,
                                    TRUE,
                                    FALSE);
    //  Insert row data array into your database of choice here

}

Now with the ExcelBundle, I am stuck. The documentation doesn't help me at all in this task. I've tried every advice given in similar questions and I cant manage to do this.

Creating an object from a file like the example below doesn't work at all:

$phpExcelObject = $this->get('phpexcel')->createPHPExcelObject('file.xls');

How to achieve this task?

Upvotes: 1

Views: 1480

Answers (1)

maestro
maestro

Reputation: 81

I worked this out as follows:

  1. Make sure the path Liuggio/ExcelBundle/Controller/FakeController.php exists
  2. Make sure you update the app/config config.yml and routing.yml are updated with the provided files (Liuggio/ExcelBundle/Tests/app)

  3. Assuming that you are updating the product table, Update the FakeController as follows:

    <?php

namespace  Liuggio\ExcelBundle\Controller;

use Symfony\Bundle\FrameworkBundle\Controller\Controller;
use Symfony\Component\HttpFoundation\Response;
use AppBundle\Entity\Product;

class FakeController extends Controller
{
    public function insertAction()
    {
        $data = [];
        $appPath = $this->container->getParameter('kernel.root_dir');
        $file = realpath($appPath . '/../web/excelFiles/abc.xls');

        $phpExcelObject = $this->get('phpexcel')->createPHPExcelObject($file);
        $sheet = $phpExcelObject->getActiveSheet()->toArray(null, true, true, true);

        $em = $this->getDoctrine()->getManager();
        $data['sheet'] = $sheet;
        //READ EXCEL FILE CONTENT
        foreach($sheet as $i=>$row) {
        if($i !== 1) {
            $product = new Product();

            $product->setProductCode($row['A']); 
            $product->setProductName($row['B']);
            $product->setProductRetailPrice($row['C']);
            $product->setProductCost($row['D']);
            $product->setProductTax($tax);
            $product->setCategory($category);
            //... and so on


            $em->persist($product);
            $em->flush();
            //redirect appropriately
            }
        }
        $data['obj'] = $phpExcelObject;
        return $this->render('excel/read.html.twig', ['data' => $data ] );
    }

}

Upvotes: 1

Related Questions