PHPExcel export gives me rubbish

Question

So, i´m trying to make PHPExcel export som data from db and open a Save dialog for the user to save a excel file.

In my network preview i got this:

PK%y~GGD²Xð[Content_Types].xmlMNÃ0÷"ò%nY vAa (0ö¤±êØgúw{&i@ÕnbEö{ßøyìÑdÛ¸l mð¥×ÁX¿(ÅÛü)¿òF¹à¡;@1_æ»±Øc)j¢x/%êEày¦ ©QÄ¿i!£ÒKµy3ÜJ<§Z1½0?YÙL%zV cäÖIb7Øûìa/lÙ¥P1:«qáríÍjªÊj0A¯Íuë""íàÙ( ÁWìMä)Tjå({Ü²û>óOãÂ,XÙÁÚÆ>Bÿ~×ÏõÓ¤6ÜJ=¡oBZ¾°¼tìíX4Êú¾Cgñ,QgmrLÙÙ¯cïeëàtø± Zõ?´»hPvÃðÂ±úÿuÕjÀ¼Râæ¸øûî}¬CvÖøPK%y~G¶78éK_rels/.relsÍjÃ0ï{........

Just a lot of what?

And of course no save dialog opens. I had this code working if i choose to save the file on server instead of open the "save dialog".

PHP

/** Error reporting */
error_reporting(E_ALL);

//Load phpexcel includes    
require '../Classes/PHPExcel.php';

/** PHPExcel_Writer_Excel2007 */
include '../Classes/PHPExcel/Writer/Excel2007.php';

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

// Add some data
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->SetCellValue('A1', 'Nr');
$objPHPExcel->getActiveSheet()->SetCellValue('B1', 'Höjd');
$objPHPExcel->getActiveSheet()->SetCellValue('C1', 'Typ');
$objPHPExcel->getActiveSheet()->SetCellValue('D1', 'Längd');
$objPHPExcel->getActiveSheet()->SetCellValue('E1', 'AV');
$objPHPExcel->getActiveSheet()->SetCellValue('F1', 'Öppningar');

$objPHPExcel->getActiveSheet()->SetCellValue('G1', 'Vikt');


//Fetch data from DB
$query = "SELECT * FROM table WHERE objekt_nr = '$objNr' ORDER BY length(element_nr), element_nr ASC";
try{
    $stmt = $db->prepare($query);
    $result = $stmt->execute();
}
catch(PDOException $ex){
    die("Failed to run query: " . $ex->getMessage());
}
//Insert on first row after heading 
$row = 2;
while($value = $stmt->fetch()){
    //Set start Column
    $column = "A";

    $objPHPExcel->getActiveSheet()->SetCellValue($column++.$row, $value['element_nr']);
    $objPHPExcel->getActiveSheet()->SetCellValue($column++.$row, $value['hojd']);
    $objPHPExcel->getActiveSheet()->SetCellValue($column++.$row, $value['typ']);
    $objPHPExcel->getActiveSheet()->SetCellValue($column++.$row, $value['langd']);
    $objPHPExcel->getActiveSheet()->SetCellValue($column++.$row, $value['avdrag']."");
    $objPHPExcel->getActiveSheet()->SetCellValue($column++.$row, $value['oppningar']."");

    $objPHPExcel->getActiveSheet()->SetCellValue($column++.$row, $value['vikt']);

    //INCREASE Row Nr
    $row++;
}

// Rename sheet
$objPHPExcel->getActiveSheet()->setTitle($objNr);

header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
header("Content-Disposition: attachment; filename=\"results.xlsx\"");
header("Cache-Control: max-age=0");

// Write file to the browser
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->save('php://output');

}

This works: $objWriter->save('file.xlsx');
And this gives me rubbish: $objWriter->save('php://output');

Why?

The sample you posted looks correct for a .xlsx document. You should probably have a Content-Transfer-Encoding: binary header. — Alex K., Nov 30 '15 at 15:24
That "rubbish" is the __binary__ stream that comprises an xlsx document, a zipped collection of xml files.... look at the headers being received by your browser (and look at the headers being sent in PHPExcel's `/Examples/01simple-download-xlsx.php` — Mark Baker, Nov 30 '15 at 17:01
Your code works fine for me, when I corrected two things: I set the variable `$objNr` in Method `setTitle()` and removed the last curly brace. Then the file downloaded itself and could be opened in Office 2013. — Philipp Palmtag, Dec 01 '15 at 07:53

score 3 · Accepted Answer · edited May 23 '17 at 12:23

You are getting exactly what you asked for: a (zipped) XLSX document. However, your browser is probably not expecting that - you need to tell it "hey, this is not HTML, this is an Excel file!"

In your code, you have to send a Content-Type header, before any other output:

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

and maybe also a header saying "you want to save this as a file":

header('Content-Disposition: attachment; filename=' . $whateverFileName . '.xlsx');

You do have these in your code; however, you are probably sending something else to the browser before your code gets to those lines. Check that your script outputs no data (not even whitespace or BOMs - if your network preview has a hex view, like e.g. Fiddler, you should see 0 bytes sent in the response body if you don't call the ->save() method) before it gets to the header() functions. See How to fix "Headers already sent" error in PHP

Thank you. I will try to move these lines around. I´m calling the PHP file from ajax, so maby i must put the headers in another file to?! — Björn C, Dec 01 '15 at 12:17
That was no good. Now, i can´t even enter the page before the `save dialog`appears ;D — Björn C, Dec 01 '15 at 12:21

PHPExcel export gives me rubbish

1 Answers1