Creating Excel file from array using php and symfony

Question

I'm trying to export an array as XLS file using PHP and symfony as shown by the code below. Once the XLS file created, I can only get the last row of the array, and it's displayed in the first line of my file. It seems that the "lignes" variable is not incremented. I can't figure out what's wrong, can anyone help with that?

  foreach ($adresses as $ad ){

            $phpExcelObject->setActiveSheetIndex(0)->setCellValue('A' . $lignes, $ad->getId());
            $phpExcelObject->setActiveSheetIndex(0)->setCellValue('E' . $lignes, $ad->getTypeVoie());
            $phpExcelObject->setActiveSheetIndex(0)->setCellValue('F' . $lignes, $ad->getVoie());
            $phpExcelObject->setActiveSheetIndex(0)->setCellValue('G' . $lignes, $ad->getTypeQuartier());
            $phpExcelObject->setActiveSheetIndex(0)->setCellValue('H' . $lignes, $ad->getQuartier());
            $phpExcelObject->setActiveSheetIndex(0)->setCellValue('I' . $lignes, $ad->getCodePostale());
            $phpExcelObject->setActiveSheetIndex(0)->setCellValue('K' . $lignes, $ad->getPays());
            $phpExcelObject->setActiveSheetIndex(0)->setCellValue('J' . $lignes, $ad->getVille());
            $lignes++;

        }
        $writer = $this->get('phpexcel')->createWriter($phpExcelObject, 'Excel2007');
        $writer->save($fichier->getWebPathOut() );

`$lignes++` inside your loop.... that's the row number, and you never change it — Mark Baker, Aug 27 '16 at 22:15

score 3 · Answer 1 · edited Mar 17 '23 at 08:08

The code could be like below. This is working code. Hope you got your answere here in this code. This functionality uses php excel "phpoffice/phpexcel": "^1.8".

public function downloadAction(Request $request)
{
    $phoneListId = $request->get('phonelist_id');

    $em = $this->getDoctrine()->getManager();
    $phoneList = $em->getRepository(PhoneList::class)->find($phoneListId);

    $phoneNumbers = $phoneList->getPhoneNumbers();

    // ask the service for a Excel5
    $phpExcelObject = $this->get('phpexcel')->createPHPExcelObject();

    $phpExcelObject->getProperties()->setCreator("liuggio")
        ->setTitle($phoneList->getTitle())
        ->setSubject($phoneList->getTitle());

    $sheet = $phpExcelObject->setActiveSheetIndex(0);

    $sheet->setCellValue('A1', 'Name');
    $sheet->setCellValue('B1', 'Number');
    $sheet->setCellValue('C1', 'Phone Number');
    $sheet->setCellValue('D1', 'Designation');
    $sheet->setCellValue('E1', 'Office');

    $counter = 2;
    foreach ($phoneNumbers as $phoneNumber) {
        $sheet->setCellValue('A' . $counter, $phoneNumber->getName());
        $sheet->setCellValue('B' . $counter, $phoneNumber->getNumber());
        $sheet->setCellValue('C' . $counter, $phoneNumber->getPhoneNumber());
        $sheet->setCellValue('D' . $counter, $phoneNumber->getDesignation());
        $sheet->setCellValue('E' . $counter, $phoneNumber->getOffice());
        $counter++;
    }

    $phpExcelObject->getActiveSheet()->setTitle($phoneList->getTitle());

    // Set active sheet index to the first sheet, so Excel opens this as the first sheet
    $phpExcelObject->setActiveSheetIndex(0);

    // create the writer
    $writer = $this->get('phpexcel')->createWriter($phpExcelObject, 'Excel5');
    // create the response
    $response = $this->get('phpexcel')->createStreamedResponse($writer);
    // adding headers
    $dispositionHeader = $response->headers->makeDisposition(
        ResponseHeaderBag::DISPOSITION_ATTACHMENT,
        $phoneList->getTitle() . '.xls'
    );
    $response->headers->set('Content-Type', 'application/vnd.ms-excel; charset=utf-8');
    $response->headers->set('Pragma', 'public');
    $response->headers->set('Cache-Control', 'maxage=1');
    $response->headers->set('Content-Disposition', $dispositionHeader);

    return $response;
}

Andrej · Accepted Answer · 2016-08-27T22:19:22.627

I will try something like:

    $lignes = 1;
    $phpExcelObject->setActiveSheetIndex(0);
    foreach ($adresses as $ad ){
            $phpExcelObject->getActiveSheet()
            ->setCellValue('A' . $lignes, $ad->getId())
            ->setCellValue('E' . $lignes, $ad->getTypeVoie())
            ->setCellValue('F' . $lignes, $ad->getVoie())
            ->setCellValue('G' . $lignes, $ad->getTypeQuartier())
            ->setCellValue('H' . $lignes, $ad->getQuartier())
            ->setCellValue('I' . $lignes, $ad->getCodePostale())
            ->setCellValue('K' . $lignes, $ad->getPays())
            ->setCellValue('J' . $lignes, $ad->getVille())
            $lignes++;

        }
        $writer = $this->get('phpexcel')->createWriter($phpExcelObject, 'Excel2007');
        $writer->save($fichier->getWebPathOut() );

Actually I start it at 2 `$lignes=2` – user2804064 Aug 27 '16 at 22:26 — user2804064, Aug 27 '16 at 22:26

Creating Excel file from array using php and symfony

2 Answers2

Linked