Symfony2，Doctrine，从数据库获取数据以生成excel

Question

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;

Answer 1

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++;
}