CODEWITHSUNDEEP
phpphpexcel
user1410081
user1410081

Reputation:

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

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');

Upvotes: 12

Views: 67812

Answers (10)

John Rix
John Rix

Reputation: 6673

It is worth noting that output buffering in PHP can be nested. That is, you can call ob_start() multiple times and this will create separate buffers, with a corresponding call to an ob_end_*() method closing off the most recently started buffer.

Some PHP-based CMSes such as Joomla will use output buffering during rendering of a page request by default (eg. to allow HTTP headers to be generated and output much later in the page rendering process), so simply calling ob_end_clean() before creating your PHPExcel writer instance as suggested in some of the other solutions here will break that entire process.

For this reason, you would be better served to match your ob_end_clean() call with a prior ob_start() call before you start generating your XLSX file content, so that when you do call ob_end_clean(), you are not inadvertently closing a buffer you did not explicitly open.

ob_start();

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

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


// Output more stuff to my Excelfile...


// Save Excel 2007 file
echo date('H:i:s') . " Write to Excel2007 format\n";

ob_end_clean();

$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');

In my case when encountering this error, I was able to isolate the location where the corruption of the Excel file was occurring to requireing a particular PHP file that was largely unrelated to the content generation. To be brutally honest, it is quite unclear even now WHY this was causing the corruption as the required file in question was quite trivial. However, wrapping just that in ob_start() and ob_end_clean() calls was enough to solve the problem.

Upvotes: 0

Muhammad Fatkurozi
Muhammad Fatkurozi

Reputation: 31

I have the same problem, but i using library phpspreadsheet

just put this function:

ob_end_clean();

after this code :

$writer = new Xlsx($spreadsheet);

Upvotes: 0

Python
Python

Reputation: 779

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");

Upvotes: 1

ARN
ARN

Reputation: 719

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();

Upvotes: 0

cetipabo
cetipabo

Reputation: 473

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.

Upvotes: 0

Ahmad Wijaya
Ahmad Wijaya

Reputation: 11

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

ob_end_clean();

after :

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

Upvotes: 0

Andron
Andron

Reputation: 6621

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.

Upvotes: 2

user1410081
user1410081

Reputation:

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.

Upvotes: 65

marco burrometo
marco burrometo

Reputation: 1135

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!

Upvotes: 5

Mark Baker
Mark Baker

Reputation: 212402

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

Upvotes: 2

Related Questions