6

I am creating an xlsx file using phpexcel. I have many formulas in the file. when the file is downloaded, some of the formula cells (not all) appear blank until I click the Enable Editing option.

I am using the following code :

$objPHPExcel = new PHPExcel();
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$xlsName.'"');
header('Cache-Control: max-age=0');
$objWriter->save('php://output');

The problem here occurs with users who are using previous Office versions who don't have the enable editing option. For them these cells appear blank.

Simone
  • 636
  • 2
  • 8
  • 25
  • At least provide some more detail..... if some formulae cells are blank, then give me some idea of which formulae cells...... – Mark Baker Oct 26 '15 at 07:53
  • 1
    Though you shouldn't be using `Excel2007` Writer and sending headers telling the browser that the file is `application/vnd.ms-excel`. That's the header for an `.xls` file, which is created using the `Excel5` Writer. The `Excel2007` Writer generates `.xlsx` files, which have the content-type `application/vnd.openxmlformats-officedocument.spreadsheetml.sheet` – Mark Baker Oct 26 '15 at 07:56
  • I tried with application/vnd.openxmlformats-officedocument.spreadsheetml.sheet as well. Then i had changed it to this because i had got it from http://stackoverflow.com/questions/8566196/phpexcel-to-download link – Simone Oct 26 '15 at 11:30
  • The headers won't make a difference to the problem you're having with formulae..... but they do make a difference to how the browser handles the file..... use the correct content type for the file format that you're using – Mark Baker Oct 26 '15 at 11:39
  • But I can't help with the formulae unless I know what formulae are causing problems..... I can't guess that.... and your code snippet shows no formulae, and will only ever create an empty spreadsheet, so how can I even begin to guess? – Mark Baker Oct 26 '15 at 11:40
  • formular for the cell which shows the reult before enable editing : =IF(Consolidated!C21<>"",ROUND(IF(Consolidated!C21<>0,Consolidated!C21/D21*100,"0"),2),"") and formula which doesnt give : =IF(Consolidated!C22<>"",ROUND(IF(Consolidated!C22<>0,Consolidated!C22/D22*100,"0"),2),"")... Consolidated is the name of the previous sheet.The formula is the same for both cells. – Simone Oct 26 '15 at 12:35
  • As the only difference between those two is the cell references, I still can't even begin to guess.... perhaps if you did some basic debugging using [PHPExcel's Calculation Engine debugger](https://gist.github.com/MarkBaker/5908357), it might give some clues – Mark Baker Oct 26 '15 at 12:43

1 Answers1

6

I came across this answer somewhere else. If anyone else is looking for answers.

$spreadsheet = new \PHPExcel();
$writer = new \PHPExcel_Writer_Excel2007($spreadsheet);

//Do things with the $spreadsheet

//This is the solution, do it before saving
$writer->setPreCalculateFormulas(); 
$writer->save($saving_filepath);
balslev
  • 331
  • 4
  • 11