[FIXED] How to cache phpexcel with symfony2 phpexcel bundle

Issue

Is there any way to speed up my function to work with larger excel file? There is my code:

$excelService = $this->get('xls.service_xls5');

$excelObj = $this->get('xls.load_xls5')->setReadDataOnly(true)->load('../web/bundles/static/pliki/'.$name); 

$sheet = $excelObj->getSheet(0); 
$highestRow = $sheet->getHighestRow(); 
$highestColumn = $sheet->getHighestColumn(); 

for ($row = 1; $row <= $highestRow; $row++){ 

    $nazwaKlienta = $sheet->getCellByColumnAndRow(0,$row)->getValue();
    $ulica = $sheet->getCellByColumnAndRow(1,$row)->getValue();
    $miasto = $sheet->getCellByColumnAndRow(2,$row)->getValue();
    $kod = $sheet->getCellByColumnAndRow(3,$row)->getValue();
    $notatka = $sheet->getCellByColumnAndRow(4,$row)->getValue();
    $kolor = $sheet->getCellByColumnAndRow(5,$row)->getValue();

    if ($row == $highestRow) {
        $excelObj->disconnectWorksheets(); 
        unset($excelObj); 
    }

    $em = $this->getDoctrine()->getManager();
    $klient = new Klient();
    $klient->setNazwa($nazwaKlienta);
    (...)

    $em->persist($klient);
    $em->flush();
    $klientId = $klient->getId();
    $punkt = new Punkty();
    $punkt->setIdKlienta($klient);
    $em->persist($punkt);
    $em->flush();
}

This code open an existing excel file and read all data and write it to database. It works fine with data amount about 150-200 but i need to work with data about 2000 rows. How can i cache it with phpexcelBundle or maybe there is another way?

Solution

A cache mechanism could help you:

Is there $name into the db? if yes don't do the job just take the value from the db.

You could also use APC setting an expiration time of the given $name.

please have a look to:

rationally-boost-your-symfony2-application-with-caching-tips-and-monitoring

slide 27.

Answered By - liuggio

Answer Checked By - Cary Denson (PHPFixing Admin)