使用php和Symfony从数组创建Excel文件

Question

我正在尝试使用PHP和Symfony将数组导出为XLS文件，如下所示。一旦创建了XLS文件，我只能获取数组的最后一行，并且它显示在我的文件的第一行。似乎“lignes”变量没有递增。我无法弄清楚问题出在哪里，有人可以帮忙吗？

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

- user2804064

---

在循环中使用$lignes++，它代表该行的行号，永远不要改变它。 - Mark Baker

Answer 1

代码可能像下面这样。这是有效的代码。希望你在这段代码中找到了答案。此功能使用phpexcel "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;
}

- Bhaktaraz

Answer 2

我会尝试类似这样的内容：

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

- Andrej

---

实际上我是从2行开始的 $lignes=2。 - user2804064