CODEWITHSUNDEEP
phpexcelsymfonydoctrine-orm
Storm Parker
Storm Parker

Reputation: 633

Symfony2, Doctrine, getting data from database to generate excel

I want to retrieve rows of data from the database in order to export an Excel file in Symfony. I'm having trouble looping through each rows and I'm only getting the last element of the results. I am guessing I made a mistake somewhere with either the right kind of loops to have or that it's not looping correctly. Or maybe I am not retrieving the data correctly? I am only used to retrieving data via Doctrine and outputting the loop in twig templates so this has been confusing me. Help much appreciated.

$em = $this->getDoctrine()->getManager();
    $query = $em->createQuery('
           .......query ')
            ->setParameter('no', $no);

$results = $query->getResult(\Doctrine\ORM\Query::HYDRATE_ARRAY);

$excel = $this->get('phpexcel')->createPHPExcelObject();
$excel->getProperties()->setCreator('iStyle')
    ->setTitle('Inventory Report');
$i = 2;
$excel->setActiveSheetIndex(0);
$excel->getActiveSheet()->setTitle('Inventory. '.$no)
        ->setCellValue('A1', 'Report')
        ->mergeCells('A1:G1')
        ->setCellValue('A'.$i, 'No.')
        ->setCellValue('B'.$i, 'Color')
        ->setCellValue('C'.$i, 'Weight')
        ->setCellValue('D'.$i, 'SKU')
        ->setCellValue('E'.$i, 'Dimensions')
        ->setCellValue('F'.$i, 'Qty Available');

for($d = 0; $d < count($results); $d++) {

        $i = 3;

        $excel->getActiveSheet()
            ->setCellValue('A'.$i, $results[$d]['no'])
            ->setCellValue('B'.$i, $results[$d]['color'])
            ->setCellValue('C'.$i, $results[$d]['weight'])
            ->setCellValue('D'.$i, $results[$d]['sku'])
            ->setCellValue('E'.$i, $results[$d]['dimensions'])
            ->setCellValue('F'.$i, $results[$d]['qty']);
        $i++;
    }

$writer = $this->get('phpexcel')->createWriter($excel, 'Excel5');
    $response = $this->get('phpexcel')->createStreamedResponse($writer);
    $response->headers->set('Content-Type', 'text/vnd.ms-excel; charset=utf-8');
    $response->headers->set('Content-Disposition', 'attachment;filename=invreport-'.$no.'.xls');
    $response->headers->set('Pragma', 'public');
    $response->headers->set('Cache-Control', 'maxage=1');

    return $response;

Upvotes: 1

Views: 1796

Answers (1)

phpisuber01
phpisuber01

Reputation: 7715

In your for loop you are setting $i explicitly to 3 then incrementing it $i++. Therefore, you are never appending a new row, your simply updating the last row A3, B3, etc over and over again leaving you with the last row to be updated.

Take the $i = 3; out of the loop and place it before the for(..) call.

Example

$i = 3;
for($d = 0; $d < count($results); $d++) {
        $excel->getActiveSheet()
            ->setCellValue('A'.$i, $results[$d]['no'])
            ->setCellValue('B'.$i, $results[$d]['color'])
            ->setCellValue('C'.$i, $results[$d]['weight'])
            ->setCellValue('D'.$i, $results[$d]['sku'])
            ->setCellValue('E'.$i, $results[$d]['dimensions'])
            ->setCellValue('F'.$i, $results[$d]['qty']);
        $i++;
}

Upvotes: 2

Related Questions