PHPExcel output unreadable and no errors thrown

Question

Hello I have the following code

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

// Set document properties
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
                         ->setLastModifiedBy("Maarten Balliauw")
                         ->setTitle("Office 2007 XLSX Test Document")
                         ->setSubject("Office 2007 XLSX Test Document")
                         ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
                         ->setKeywords("office 2007 openxml php")
                         ->setCategory("Test result file");

    $exchange = $_POST['exchange'];
    $jobchange = $_POST['estimate'];
    $wpchange = $_POST['wp'];

    $username = "----";
    $password = "----";
    $hostname = "----";

    $dbhandle = mysql_connect($hostname, $username, $password) or die("Unable to connect to MySQL");
    $selected = mysql_select_db("----", $dbhandle) or die("Could not select examples");

    $query = "SELECT * FROM btsec WHERE WP='$wpchange' AND Exchange='$exchange' AND Estimate='$jobchange'";

    $result = mysql_query($query);

    $acellnum = "3";
    $bcellnum = "3";
    $ccellnum = "3";
    $dcellnum = "3";

$objPHPExcel->setActiveSheetIndex(0)
        ->setCellValue('A1', 'Section ID')
        ->setCellValue('B1', 'Length')
        ->setCellValue('C1', 'Status')
        ->setCellValue('D1', 'TM');

    while ($row = mysql_fetch_array($result)) {     
    // Query 
    // Add some data
$objPHPExcel->setActiveSheetIndex(0)
        ->setCellValue("A".$acellnum, $row['SectionID'])
        ->setCellValue("B".$bcellnum, $row['Length'])
        ->setCellValue("C".$ccellnum, $row['Status'])
        ->setCellValue("D".$dcellnum, $row['TM']);

        $acellnum++;
        $bcellnum++;
        $ccellnum++;
        $dcellnum++;
}       

// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Simple');


// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);


// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="01simple.xls"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0

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

It is generating the excel file, but all it outputs is text similar to this

ÐÏࡱá;þÿ

Opening in a text editor reveals no obvious errors and the script returns none either. I'm trying to output this to an Excel 2007 compatible format. Does anyone have any idea why this is happening?

EDIT: May not be relevant but Excel throws an error that the format does not match the extension

Solution 1

// Save Excel 2007 file
#echo date('H:i:s') . " Write to Excel2007 format\n";
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
ob_end_clean();
// We'll be outputting an excel file
header('Content-type: application/vnd.ms-excel');
// It will be called file.xls
header('Content-Disposition: attachment; filename="sectionlist.xlsx"');
$objWriter->save('php://output');

This fixed it and made it work! ob_end_clean was the solution.

Solution 2

To solve this problem try :

  header('Content-Type: 'application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="test.xlsx"');
header('Cache-Control: max-age=0');

and

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

Solution 3

My PhpExcel Library just stopped working properly. The result excel file was all written with really weird characters.

This:

ob_end_clean();

was my solution too.

Solution 4

Try changing this:

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

to this:

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

Also, excel 2007 expects the file to be .xlsx so you should probably change that in your header statement.

View more solutions

Comments

[noobie-php]

this out put is thrown when there is error in the code.

[Des Hutch]

@noobie-php any obvious error you can see in the code?

[noobie-php]

well excel errors are not that user friendly, i would suggest you trace problem by echo and die(); with chunks of your code, i am not sure if these errors are maintained in some log files. But if there is any out there you can look into it.

[user4035]

I didn't get any errors, when running your code with static data. Can you give reproducible code we could run and see the error. Without headers, just saving to plain excel file?

[Mark Baker]

Try saving to a file on the server (without the header() statements) rather than direct to the web browser, and see if that is readable

[Des Hutch]

@user4035 going to try that now. Was just on the drive home. Also testing another machine with Office 2010 as this is 2013

[Des Hutch]

@MarkBaker going to try that and see what I get

[Des Hutch]

Older version of office failed direct download attempt. Same result

[Des Hutch]

@user4035 Saving to the server rather than the browser produced a nice clean excel sheet. Any ideas what the headers could be doing to it?

[Des Hutch]

@MarkBaker Saving to the server rather than the browser produced a nice clean excel sheet. Any ideas what the headers could be doing to it?

[Mark Baker]

The chances are that you have leading or trailing whitespace characters in the file sent to the browser: echo or print statements in the code, or even simply a spurious newline character after a closing ?> tag

[Des Hutch]

@MarkBaker I had already checked that. No white space att all. Removed all but the 2 headers for Excel and still the same result

[Mark Baker]

In that case, if it isn't spurious characters, I'm completely clueless.... I know what has always caused this problem in the past whenever it occurs, and the fact that it works if you write the file directly to disk proves that this is the case, but if you can't identify any spurious characters in the web file then I have absolutely no ideas whatsoever

[Des Hutch]

@MarkBaker it is rather frustrating. I've gone through the script and deleted every unneeded white space and newline. Might have to sleep on this one!

[Mark Baker]

I'd suggest a binary comparison of the valid file saved to disk with the downloaded file: there will be differences, but it might highlight something

[Des Hutch]

May give that ago and see if it throws anything

[Des Hutch]

@MarkBaker ob_end_clean was the solution!

[Mark Baker]

If ob_end_clean() works, it's generally indicative that something has been sent to php://output prior to the streamed file, even if it was purely a spurious linefeed after a ?>

[Des Hutch]

@MarkBaker I totally agree with you. Where is it though is a complete mystery that has driven me round the bend! :)

[Des Hutch]

This increased the text in the generated Excel file, but still unreadable

[Des Hutch]

I did. Just ended with a completely blank file

[Des Hutch]

It works correctly when I save to the server, it is just when I try it as a direct download it corrupts. No leading spaces before <?PHP either as I've read can cause this

[elitechief21]

And you tried removing all of the extra headers except the first 2 that appear in your code?

[Des Hutch]

Yes and it had no effect. Also removed every white space across the script. Nothing...

[Des Hutch]

ob_end_clean was the solution!