PHPExcel file cannot open file because the file format or file extension is not valid

Question

I'm stuck with this problem, it's not displaying the actual excel file. Please check my code below:

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

/** PHPExcel */
require_once 'PHPExcel.php';
include 'PHPExcel/Writer/Excel2007.php';


// Create new PHPExcel object
#echo date('H:i:s') . " Create new PHPExcel object\n";
$objPHPExcel = new PHPExcel();
$excel = new PHPExcel();

$objPHPExcel->getProperties()->setTitle("Payroll");

if(!$result){
die("Error");
}
$col = 0; 
$row = 2; 
while($mrow = mysql_fetch_assoc($result)) { 
$col = 0; 
foreach($mrow as $key=>$value) { 
    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $value); 
    $col++; 
} 
$row++;  
} 

 // Set active sheet index to the first sheet, so Excel opens this as the first sheet
 $objPHPExcel->setActiveSheetIndex(0)
        ->setCellValue('A1', 'Scholar Id')
        ->setCellValue('B1', 'Lastname')
        ->setCellValue('C1', 'Middlename')
        ->setCellValue('D1', 'Firstname')
        ->setCellValue('E1', 'Barangay')
        ->setCellValue('F1', 'Level')
        ->setCellValue('G1', 'Allowance')
        ->setCellValue('H1', 'Has claimed?');
        $objPHPExcel->getActiveSheet()->getStyle('A1:H1')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(12);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(18);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(18);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(18);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(18);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(14);
$objPHPExcel->getActiveSheet()->getStyle('A1:H1')->getAlignment()- >setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->setShowGridlines(true);

$objPHPExcel->getActiveSheet()->getStyle('A1:H1')->applyFromArray(
array(
    'fill' => array(
        'type' => PHPExcel_Style_Fill::FILL_SOLID,
        'color' => array('rgb' => 'FFFF00')
    )
)
); 


// Save Excel 2007 file
echo date('H:i:s') . " Write to Excel2007 format\n";
#$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="payroll.xlsx"');
header('Cache-Control: max-age=0'); 
$writer->save('php://output');

Whenever you see this error, the first thing you should do is open the file in a text editor and look for whitespace characters (space, tab, newlines, etc) at the beginning or end of file, or a BOM marker, or any obvious PHP human-readable error messages in the content of the file — Mark Baker, Jan 10 '13 at 07:26
@MarkBaker when I opened the file it contains no codes or anything — , Jan 10 '13 at 09:28

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

64

I got it working now! Thanks to this phpexcel to download

I changed the code to this:

// 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');
header('Content-Disposition: attachment; filename="payroll.xlsx"');
$objWriter->save('php://output');

I think this line:

ob_end_clean();

solved my problem.

edited May 23 '17 at 12:10

Community

1
1

answered Jan 10 '13 at 11:10

12

if ob_end_clean() solved the problem, then it means something in your code was generating output... best fix to find out what, and remove it.... viewing the generated file in a text editor should have given a clue as to what output was being generated – Mark Baker Jan 10 '13 at 15:25
@bEtTyBarnes. Yes. Certainly it (ob_end_clean) did solved my problem. Thank you! – Rony Samuel Sep 21 '17 at 09:49
This worked for me too, I was having an error of invalid format error message and when i put this code, it worked for me as well. Thank you! – Gosi Oct 15 '18 at 02:08
Not working for me on php 7.4 – Muhammad Tarique May 20 '22 at 19:28

score 5 · Answer 2 · answered Oct 15 '14 at 08:39

I don't know if i can help i had the same problem and i solved with

ob_end_clean();

i put it just after

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

So don't change the header and save it as xslx anyway, the problem is the buffer!

score 2 · Answer 3 · answered Jan 10 '13 at 07:28

2

The most likely culprit if this is a cut-and-paste of your script is the

echo date('H:i:s') . " Write to Excel2007 format\n";

If you're sending to the browser for download, then there must be no other output (echoes, print statements, dropping in and out of PHP) than the output generated to php://output by PHPExcel itself

answered Jan 10 '13 at 07:28

Mark Baker

209,507
32
346
385

I commented out the echo date('H:i:s') . " Write to Excel2007 format\n"; but it still is not displaying the excel file? But the excel file is saved in my php excel folder. I want the user to choose where he/she wants to save it? How can I do that? – Jan 10 '13 at 08:15
The browser should be directed to offer the download dialogue by the headers that you send.... but I'm confused by your question and coomments. The question suggested that you were getting the download dialogue, but the file was corrupted. Now you're saying that you don't get the download dialogue – Mark Baker Jan 10 '13 at 10:58
Yes I myself am confused. Sorry, but right now my problem is the browser is download the excel.php itself, the excel file is downloaded as well in my folder, but I want the user to choose where he should save it. – Jan 10 '13 at 11:04

score 2 · Answer 4 · edited May 23 '17 at 11:33

2

Please make sure that all files (e.g. that are included) are in UTF-8 without BOM encoding.
You can identify this in different ways, e.g. see this link.

Only if you need UTF-8 with BOM - please use ob_end_clean(); before data outputing to browser, as pointed in other answers here.

edited May 23 '17 at 11:33

Community

1
1

answered Feb 29 '16 at 08:59

Andron

6,413
4
43
56

score 1 · Answer 5 · answered Mar 12 '19 at 09:45

1

This answer save my life. Thanks @ARN

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, "Excel2007");
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="export_result.xlsx"');
for ($i = 0; $i < ob_get_level(); $i++) {
   ob_end_flush();
}
ob_implicit_flush(1);
ob_clean();
$objWriter->save("php://output");

answered Mar 12 '19 at 09:45

Python

699
5
5

1

While this code may answer the question, providing additional context regarding how and/or why it solves the problem would improve the answer's long-term value.[Read this](https://stackoverflow.com/help/how-to-answer) – Shanteshwar Inde Mar 12 '19 at 10:05

score 0 · Answer 6 · answered Jan 26 '18 at 10:22

0

I have the same problem, the problem is simple. Just put code below :

ob_end_clean();

after :

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

answered Jan 26 '18 at 10:22

Ahmad Wijaya

11
1
7

score 0 · Answer 7 · answered Oct 15 '18 at 13:11

i think the solution of this problem is the same as here: Google Chrome errors while exporting XLS file using PHP

just add a space between attachement; and filename, that way :

header("Content-Disposition: attachment; filename=\"Past_Due_Report.xls\"");

as i can see in your own answer that's what you did, and is probably what fixed your problem.

ARN · Answer 8 · 2018-11-29T13:21:20.057

0

I had the same problem but calling ob_end_clean() didn't work. My CMS (drupal 7) had messed up my headers so I got a new line (hex 0A) at the beginning of the content even if I called ob_end_clean() before the file output.

With the code below I finaly got rid of the leading new line:

 for ($i = 0; $i < ob_get_level(); $i++) { ob_end_flush(); }
      ob_implicit_flush(1);

 ob_clean();

edited Nov 29 '18 at 13:21

answered Nov 29 '18 at 13:04

ARN

679
7
15

score 0 · Answer 9 · answered Aug 04 '20 at 03:48

0

I have the same problem, but i using library phpspreadsheet

just put this function:

ob_end_clean();

after this code :

$writer = new Xlsx($spreadsheet);

answered Aug 04 '20 at 03:48

Ibnu Mardini

11
4

PHPExcel file cannot open file because the file format or file extension is not valid

9 Answers9

Linked