8

PHPExcel generated excel file not working. (File format or extension is invalid)

The example code of PHPExcel exceutes successfully on my local PC, the file gets downloaded to local and is opened in Excel 2007 successfully. But using the same code on my server gives an Invalid file to excel, which when I OPEN AND REPAIR, then it opens successfully.

I don't want to use the Open and Repair option, but only the Open option.

Couldn't find out the problem? Please help.

$objPHPExcel->setActiveSheetIndex(0);

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

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

exit;
William-H-M
  • 1,050
  • 1
  • 13
  • 19
sqlchild
  • 8,754
  • 28
  • 105
  • 167
  • 1
    Without knowing the exact content of the xlsx file, it's very difficult to identify which of the several million possibilities MS Excel is complaining about... what additional features (such as autofilters, charts, etc) are you using? What version of MS Excel? If you write the file to the server's disk rather than saving via the browser, do you still have the same problem? Does it still complain if you use the Excel5 Writer and save as an .xls file? Can you upload a copy of the file to the PHPExcel website for investigation? – Mark Baker May 29 '13 at 06:25
  • @Mark Baker : no on the server the file is properly generated and when i download it , the it opens successfully in excel without any problems. – sqlchild May 29 '13 at 08:13
  • 1
    That almost certainly means that your script is echoing something into the php://output stream, whether a leading or trailing space or blank line, or a gap between `?>` and ` – Mark Baker May 29 '13 at 08:16
  • ok sir am rechecking it – sqlchild May 29 '13 at 08:39
  • sir, am uploading the excel file on the PHPExcel website, please check. would be of great help. there's just two columns and two rows and i have removed all spaces. – sqlchild May 29 '13 at 08:46
  • @Mark Baker :where do i upload the file ????? couldn't find the link on PHPExcel website??? – sqlchild May 29 '13 at 08:53
  • https://phpexcel.codeplex.com/workitem/10749 – Mark Baker May 29 '13 at 08:56
  • @Mark Baker: Please find the link sir, https://phpexcel.codeplex.com/workitem/19782 – sqlchild May 29 '13 at 09:37
  • @Mark Baker: sir, you got the errors in the file???? – sqlchild May 30 '13 at 04:36
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/30876/discussion-between-sqlchild-and-mark-baker) – sqlchild May 30 '13 at 04:41

7 Answers7

30

Add a line ob_end_clean(); at the end of your program before the line $objWriter->save('php://output');

The code looks like: //for XLSX

$objPHPExcel->setActiveSheetIndex(0);

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


header('Content-Disposition: attachment;filename="item_list.xlsx"');

header('Cache-Control: max-age=0');


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

ob_end_clean();
$objWriter->save('php://output');


exit;
GAMITG
  • 3,810
  • 7
  • 32
  • 51
Govind
  • 434
  • 4
  • 8
19

The file that you've posted has a single space character before the PHPExcel output... check your script to see where this might be sent to the php://output stream. Check that there's no space before your initial <?php opening tag; watch out in particular for ?> <?php or similar closing/opening tags. And also check any files that might be included by your script

Mark Baker
  • 209,507
  • 32
  • 346
  • 385
  • 5
    I could remove as much white-spaces i could , but still it didn't work , i couldn't find the single space before the output , so i found ob_end_clean() on a post on stackoverflow and used it and it worked. – sqlchild Jun 04 '13 at 06:40
  • 6
    `ob_end_clean()` is ___always___ going to be a workround.... somewhere, there will be a space or a BOM header or some output text that causes this problem.... the correct solution is always to identify that output and eliminate it – Mark Baker Aug 17 '15 at 15:05
  • I had the same problem, solved with ob_end_clean(); – MrGreen Apr 18 '19 at 07:28
6

Check for any spurious characters being echoed into the file by opening it in a text editor. If there are, you can probably figure out where they're being introduced. Spaces or line breaks are obvious culprits.

Also, make sure that there are no spaces or newlines before the <?php tag or following the ?> tag in any of your files.

The xlsx format is actually a zipped collection of xml files, so if there are no obvious text strings (beside the PK signature) visible in a text editor, then try unzipping it. That may give other clues to the issue.

If you're running on a Windows platform, then there have been some buggy versions of the ZipArchive php_zip.dll link library that can cause this error. You can use PCLZip as an alternative to ZipArchive.

Ian Atkin
  • 6,302
  • 2
  • 17
  • 24
3

IS application/vnd.ms-excel

AND NOT

application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

EXAMPLE FOR XLS:

$objPHPExcel = new PHPExcel();

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="item_list.xlsx"');
header('Cache-Control: max-age=0');
$objWriter->save('php://output');

EXAMPLE FOR XLSX:

$objPHPExcel = new PHPExcel();

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="item_list.xlsx"');
header('Cache-Control: max-age=0');
$objWriter->save('php://output');
Rogerio de Moraes
  • 1,527
  • 18
  • 15
1

Writing an Excel file isn't simply a matter of giving an arbitrary file an extension of xlsx or xls.

You can write a BIFF5 or BIFF8 xls file (all versions of Excel post 95 will read this), or there is the Excel 2003 XML format, which can be opened by both Excel 2003 and Excel2007 (and Excel 2010 as well).

For more detail and example to do with excel 2007 you can refer to the PHPExcel examples page.

Let me know if I can help you more.

Ian Atkin
  • 6,302
  • 2
  • 17
  • 24
liyakat
  • 11,825
  • 2
  • 40
  • 46
0

I faced same issue and found there was a new line character, which was coming from the beginning of the desired data and was causing this error. Later I found that I had a php file which had closing tag 

"?>"

Usually it is recommended that one should avoid using closing php tag as they also go to output buffer. Using ob_end_clean() is also good option.

More details can be found Why would one omit the close tag?

Community
  • 1
  • 1
vivek
  • 1
0

I solved my problem, I installed zip using command

sudo apt-get install php7.0-zip and Restart the server sudo service apache2 restart It works for me I used https://github.com/PHPOffice/PhpSpreadsheet

Suraj Kumar Maurya
  • 990
  • 7
  • 8