I had some routes that fetch data from a DataBase, open a template-file.xlsx and put that data, then, sent create a response to autodownloaded .xlsx file.
Worked at localhost never met any issues, but today deployed the project, and everything working fine, except that routes.
It generates me a result-xlsx file, but when I try to open it, it's treated like a xml and it's all in random symbols and so on.
I'm using PHPExcel bundle for Symfony, that has some shortcuts for PHPExcel library, but in fact is exactly the same.
I'm sure that I don't have problems in my code, but I'll provide it below :
My code :
/**
* @Route("/download-til/{id}", name="downloadTIL")
* @Method({"GET","HEAD", "POST"})
*/
public function downloadTILAction(Request $request, $id, Loan $loan)
{
if($loan->getLine()->getDosier()->getUserId() != $this->getUser()){
throw $this->createNotFoundException("Access denied"); }
$query = $this->getDoctrine()
->getRepository('AppBundle:Loan')
->find($id);
$em = $this->getDoctrine()->getManager();
$query1 = $em->createQuery(
'SELECT p
FROM AppBundle:Charge p
WHERE p.loanId = :loanId
AND p.isActive = true
')->setParameter('loanId', $id);
$query2 = $em->createQuery(
'SELECT p
FROM AppBundle:Payment p
WHERE p.loanId = :loanId
')->setParameter('loanId', $id);
$query3 = $em->createQuery(
'SELECT d
FROM AppBundle:Deferral d
WHERE d.loanId = :loanId
')->setParameter('loanId', $id);
$query4 = $em->createQuery(
'SELECT p
FROM AppBundle:Charge p
WHERE p.loanId = :loanId
AND p.isActiveTEG = true
')->setParameter('loanId', $id);
$query5 = $em->createQuery(
'SELECT p
FROM AppBundle:Charge p
WHERE p.loanId = :loanId
')->setParameter('loanId', $id);
$calculator = new Calculator(
$query->getDate(),
$query->getCapital(),
$query->getRate(),
$query->getFrequency(),
$query->getDuration(),
$query->getType(),
$query1->getArrayResult(),//charges with active SCH
$query2->getArrayResult(),//forced payments
$query3->getArrayResult(),//deferrals
$query4->getArrayResult(),//charges with active BANK
$query5->getArrayResult(),//get absolute all charges(active and inactive), to sort them in Calculator, and return to view
$query->getForcedTIL()
);
$array = $calculator->getArray();
$chargesArray = $calculator->getChargeTableArray();
$dur = $query->getDuration();
// ask the service for a Excel5
$dir = $this->get('kernel')->getRootDir();
$phpExcelObject = $this->get('phpexcel')->createPHPExcelObject($dir.'/OutputTIL.xlsx');
$phpExcelObject->getProperties()->setCreator("liuggio")
->setLastModifiedBy("Expertiseur")
->setTitle("Office 2005 XLSX Test Document")
->setSubject("Office 2005 XLSX Test Document")
->setDescription("Raport")
->setKeywords("office 2005 openxml php")
->setCategory("Analyse result file");
$phpExcelObject->setActiveSheetIndex(0)
->setCellValue('B3', $query->getCapital())
->setCellValue('E3', $dur)
->setCellValue('H3', $array[0]['Date'])
->setCellValue('K3', $array[0]['Rate']/100)
->setCellValue('B12', $array[0]['Date'])
->setCellValue('I12', $array[$dur-1]['Date'])
->setCellValue('D12', $chargesArray[1000]['bankInterest1'])
->setCellValue('E12', $chargesArray[1000]['legalInterest1'])
->setCellValue('E13', $chargesArray[1000]['currentReparation1'])
->setCellValue('J12', $chargesArray[1000]['bankInterest2'])
->setCellValue('K12', $chargesArray[1000]['legalInterest2'])
->setCellValue('K13', $chargesArray[1000]['currentReparation2'])
->setCellValue('H15', $chargesArray[1000]['reparationGlobalPotential']);
for($i = 20, $j = $dur; $j < $dur*2; $i++,$j++) {
$phpExcelObject->setActiveSheetIndex(0)
->setCellValue('A'.$i, $i-19)
->setCellValue('B'.$i, $array[$i-20]['Date'])
->setCellValue('C'.$i, $array[$i-20]['Capital'])
->setCellValue('D'.$i, $array[$i-20]['Rate']/100)
->setCellValue('E'.$i, $array[$i-20]['Interest'])
->setCellValue('F'.$i, $array[$i-20]['Payment'])
->setCellValue('H'.$i, $array[$j]['CapitalTIL'])
->setCellValue('I'.$i, $array[$j]['RateTIL']/100)
->setCellValue('J'.$i, $array[$j]['InterestTIL'])
->setCellValue('K'.$i, $array[$j]['PaymentTIL'])
;
}
$BStyle = array(
'borders' => array(
'allborders' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN
)));
$highestRow = $phpExcelObject->setActiveSheetIndex(0)->getHighestDataRow();
$phpExcelObject->setActiveSheetIndex(0)->getStyle("A20:K{$highestRow}")->applyFromArray($BStyle);
$phpExcelObject->getActiveSheet()->setTitle('Output TIL');
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
// create the writer
$writer = $this->get('phpexcel')->createWriter($phpExcelObject, 'Excel2007');
// create the response
$response = $this->get('phpexcel')->createStreamedResponse($writer);
// adding headers
$dispositionHeader = $response->headers->makeDisposition(
ResponseHeaderBag::DISPOSITION_ATTACHMENT,
'4 Calcul au tx legal.xlsx'
);
$response->headers->set('Content-Type', 'text/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;
}//downloadTILAction
What can cause that error? Thanks!
Choose the right Content-Type:
For BIFF .xls files
application/vnd.ms-excelFor Excel2007 and above .xlsx files
application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
To change this line:
$response->headers->set('Content-Type', 'text/vnd.ms-excel; charset=utf-8');
to this:
$response->headers->set('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=utf-8');
As a bonus, an answer for a question you didn't ask:
Possibly without even knowing, you are using type hinting with the built-in Doctrine Converter. That makes these lines completely useless:
$query = $this->getDoctrine()
->getRepository('AppBundle:Loan')
->find($id);
Just use $loan->getDate() instead. And instead of $query* = $em->createQuery() you can use associations. If your Association Mapping is correct (if not, fix it!) you can use $loan->getPayments(). You're using a very powerful and extensive framework, so use it's features!