Empty PHPExcel file using liuggio/ExcelBundle in Symfony

Question

I have some code that iterates over the rows and columns of an Excel sheet and replaces text with other text. This is done with a service that has the excel file and a dictionary as parameters like this.

$mappedTemplate = $this->get('app.entity.translate')->translate($phpExcelObject, $dictionary);

The service itself looks like this.

public function translate($template, $dictionary)
    {

        foreach ($template->getWorksheetIterator() as $worksheet) {

            foreach ($worksheet->getRowIterator() as $row) {
                $cellIterator = $row->getCellIterator();
                $cellIterator->setIterateOnlyExistingCells(false); // Loop all cells, even if it is not set
                foreach ($cellIterator as $cell) {
                    if (!is_null($cell)) {
                        if (!is_null($cell->getCalculatedValue())) {

                            if (array_key_exists((string)$cell->getCalculatedValue(), $dictionary)) {

                                $worksheet->setCellValue(
                                    $cell->getCoordinate(),
                                    $dictionary[$cell->getCalculatedValue()]
                                );

                            }
                        }
                    }
                }
            }
        }

        return $template;

    }

After some debugging I found out that the text actually is replaced and that the service works like it should. The problem is that when I return the new PHPExcel file as a response to download, the excel is empty.

This is the code I use to return the file.

// create the writer
        $writer = $this->get('phpexcel')->createWriter($mappedTemplate, 'Excel5');
        // create the response
        $response = $this->get('phpexcel')->createStreamedResponse($writer);
        // adding headers
        $dispositionHeader = $response->headers->makeDisposition(
            ResponseHeaderBag::DISPOSITION_ATTACHMENT,
            $file_name
        );
        $response->headers->set('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        $response->headers->set('Pragma', 'public');
        $response->headers->set('Cache-Control', 'maxage=1');
        $response->headers->set('Content-Disposition', $dispositionHeader);

        return $response;

What am I missing?

You are never "saving" the worksheet to the writer, arent you? — mblaettermann, Dec 29 '15 at 12:04
You are returning the " return $template;" but you have modified the worksheet — liuggio, Dec 29 '15 at 22:55

mblaettermann · Answer 1 · 2016-01-02T14:20:24.643

Your code is missing the calls to the writer.

You only create the writer, but never use it, at least not in your shared code examples:

$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$response = $this->get('phpexcel')->createStreamedResponse($objWriter)

Another thing is the content type: Do you have the apache content types setup correctly?

$response->headers->set('Content-Type', 'application/vnd.ms-excel; charset=utf-8');

Empty PHPExcel file using liuggio/ExcelBundle in Symfony

1 Answers1