PHPExcel output to browser not working

Question

I am not really a PHP expert as my past experience is mostly geared towards the system engineering side. I am facing a problem with PHPExcel which gives me this error "Cannot modify header information - headers already sent by in line 1" when I want to output my XLSX file to the browser.

Here is the sample of my code

$host="localhost";
$uname="lol";
$pass="lol123";
$database = "lol12345"; 
$table="MemReport";
$table1="CPUReport";
$table2="TrafficReport";
$table3="HDDReport";


// create a file pointer connected to the output stream
$output = fopen('Memory.csv', 'w+');
$output1 = fopen('CPU.csv', 'w+');
$output2 = fopen('Traffic.csv', 'w+');
$output3 = fopen('HDD.csv', 'w+');

// output the column headings
fputcsv($output, array('HostName','Date','Time','Percent','TholdDescription'));
fputcsv($output1, array('HostName','Date','Time','Percent','TholdDescription'));
fputcsv($output2, array('HostName','Date','Time','Percent','TholdDescription'));
fputcsv($output3, array('HostName','Date','Time','Percent','TholdDescription'));

// fetch the data
mysql_connect($host, $uname, $pass);
mysql_select_db($database);
echo mysql_error(); 

$rows = mysql_query("SELECT * FROM $table order by date ASC, Time ASC");
echo mysql_error(); 
$rows1 = mysql_query("SELECT * FROM $table1 order by date ASC, Time ASC");
echo mysql_error(); 
$rows2 = mysql_query("SELECT * FROM $table2 order by date ASC, Time ASC");
echo mysql_error();
$rows3 = mysql_query("SELECT * FROM $table3 order by date ASC, Time ASC");
echo mysql_error(); 
// loop over the rows, outputting them
while ($row = mysql_fetch_assoc($rows)) 
fputcsv($output, $row);
while ($row1 = mysql_fetch_assoc($rows1))   
fputcsv($output1, $row1);
while ($row2 = mysql_fetch_assoc($rows2))
fputcsv($output2, $row2);
while ($row3 = mysql_fetch_assoc($rows3))
fputcsv($output3, $row3);


include '/tmp/Classes/PHPExcel/IOFactory.php';


$inputFileType = 'CSV';
$inputFileNames = array('HDD.csv','Traffic.csv','CPU.csv','Memory.csv');
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$inputFileName = array_shift($inputFileNames);
$objPHPExcel = $objReader->load($inputFileName);

$objPHPExcel->getActiveSheet()->setTitle(pathinfo($inputFileName,PATHINFO_BASENAME));
foreach($inputFileNames as $sheet => $inputFileName) {
    $objReader->setSheetIndex($sheet+1);
    $objReader->loadIntoExisting($inputFileName,$objPHPExcel);
    $objReader->loadIntoExisting($inputFileName,$objPHPExcel);
    $objPHPExcel->getActiveSheet()->setTitle(pathinfo($inputFileName,PATHINFO_BASENAME));

}

$loadedSheetNames = $objPHPExcel->getSheetNames();
foreach($loadedSheetNames as $sheetIndex => $loadedSheetName) {
$objPHPExcel->setActiveSheetIndexByName($loadedSheetName);

$sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
//var_dump($sheetData);
//var_dump($sheetData);

$myfile = "Report.xlsx";
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

header("Content-Disposition: attachment;filename=$myfile"); header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');


}

any help is appreciated, most of my code has been copy and pasted and then edited to my knowledge, also i was able to get to email the xlsx file but just have problem sending it for download. I don't mind reading the file from the directory for download.

regards,

Lin

Just make sure you don't echo out anything before the header() function on your script. Comment out the lines you echo something and try removing spaces or empty lines in the beginning of your file. — sotoz
In general you are not allowed to echo not even one space as you will send the headers prematurely with that space. — sotoz
No spaces or tabs, no error messages, no newline characters, no BOM markers, no other output whatsoever — Mark Baker

Mark Baker Mark Baker · Accepted Answer · 2014-10-14T13:37:56

No spaces or tabs, no error messages, no newline characters, no BOM markers, no other output whatsoever.

Note that the error message you're seeing tells you which file/line was responsible for the output headers already sent by xxx in line 1.... so that's where to look.

At line 1 of a file, it's most likely to be something before your opening <?php tag, or a file saved with a BOM header

PHPExcel output to browser not working

1 Answers