[FIXED] How to use liuggio/ExcelBundle to post excel data to database

Issue

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?

Solution

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 ] );
       }
   
   }
   

Answered By - maestro

Answer Checked By - David Goodson (PHPFixing Volunteer)