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
– Mark Baker, Commented Aug 27, 2016 at 22:15

Pascal · Accepted Answer · 2023-03-17 08:08:43Z

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-27 22:19:22Z

1

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() );

edited Aug 27, 2016 at 22:19

answered Aug 27, 2016 at 22:01

Andrej

7,5041 gold badge22 silver badges22 bronze badges

1 Comment

user2804064 Over a year ago

Actually I start it at 2 $lignes=2

Collectives™ on Stack Overflow

Creating Excel file from array using php and symfony

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related