XLSX Excel error 'Excel cannot open the file because the file format is not valid. Verify that the file has not been corrupted and that the file extension matches the file format of the file.

Topics: User Forum
Apr 22, 2013 at 3:54 PM
Edited Apr 22, 2013 at 4:11 PM
Hi,


I have seen this issue in other discussions placed and tried everything that every one suggests with no luck.

The file downloads ok but Excel will not open and give the error and as above. I have tried renaming the to the older xls suffix. Excel will open the file but after giving a warning of 'the file you are trying to open is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source.... Once opened it displays a bunch of random characters, the same as if I would change the suffix to .rtf or txt and open the file in note pad.


The start of the random txt starts with PK which I understand is how a Excel spread sheet should start with. I cannot see any readable error messages php or otherwise.


Below is my code:
<?php
// This is the API connector servlet to allow the frontend JS to communicate with the back
$login_required = true;
if (!$_SESSION) { session_start(); }
require_once("ugs/class_ugs_client.php");
require_once("classes/class_product.php");
$p= new Product;
$p->sarDBConnect();

if ($_SESSION['current_recordset'] != "") {
    $query = mysql_query($_SESSION['current_recordset']);
    while($q = mysql_fetch_assoc($query)){
        $output[] = $q;
    }
    /** PHPExcel */
    require_once 'classes/PHPExcel.php';
    
    
    // Create new PHPExcel object
    $objPHPExcel = new PHPExcel();
    
    // Set properties
    $objPHPExcel->getProperties()->setCreator("Result Clothing")
                                 ->setTitle("Result Backorder Report")
                                 ->setDescription("Custom Generated Report")
                                 ->setKeywords("Result Backorder Report");
    
    $row = 1;
    foreach ($output as $k=>$v) {
        // Add some data
        if ($_SESSION['userinfo']['distid'] == "RESULT") {
            $objPHPExcel->setActiveSheetIndex(0)
                        ->setCellValue('A'.$row, $v['CUSTNMBR'])
                        ->setCellValue('B'.$row, $v['USCATVLS_1'])
                        ->setCellValue('C'.$row, $v['ITEMNMBR'])
                        ->setCellValue('D'.$row, $v['CSTPONBR'])
                        ->setCellValue('E'.$row, $v['SOPNUMBE'])
                        ->setCellValue('F'.$row, $v['QUANTITY'])
                        ->setCellValue('G'.$row, $v['QTY'])
                        ->setCellValue('H'.$row, $v['ARRIVALDATE']);
                        $row++;
        } else {
            $objPHPExcel->setActiveSheetIndex(0)
                        ->setCellValue('A'.$row, $v['USCATVLS_1'])
                        ->setCellValue('B'.$row, $v['ITEMNMBR'])
                        ->setCellValue('C'.$row, $v['CSTPONBR'])
                        ->setCellValue('D'.$row, $v['SOPNUMBE'])
                        ->setCellValue('E'.$row, $v['QUANTITY'])
                        ->setCellValue('F'.$row, $v['QTY'])
                        ->setCellValue('G'.$row, $v['ARRIVALDATE']);
                        $row++;
        }
    }
    
    // Rename sheet
    $objPHPExcel->getActiveSheet()->setTitle('Result Custom Backorder Report');
    
    
    // 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 (Excel2007)
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="result-backorder-report.xlsx"');
    header('Cache-Control: max-age=0');
    
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    $objWriter->save('php://output');
    exit;
} 
 else {
    print "Sorry, you need to look at the report first";
}
?>


I have read lots of not putting any print or echo on the same page as this will cause errors and I have also read that there shouldn't be and closing ?> As you will see from the code above it reads
exit;
} 
 else {
    print "Sorry, you need to look at the report first";
}
?>
I have taken this out hoping this would fix the issue, but it doesn't make any difference.


Any suggestions will be appreciated thanks.


P.S I have update the PHPExcel files so I have the latest version and I have tried a test using the 01simple-download-xlsx.php. This downloads fine but there was no button to instruct to download, just the usual pop up. Excel was able to open the downloaded file
Coordinator
Apr 22, 2013 at 4:06 PM
The answer to this question is always the same.

Open the file in a text editor, and look for leading or trailing whitespace or a BOM marker (in which case your script is injecting that via echo, print, closing and opening PHP tags, or a script file that's saved as UTF-8 with BOM (save as UTF-8 without BOM). Alternatively, look for any obvious plaintext error messages generated by PHP or PHPExcel
Apr 23, 2013 at 12:00 PM
Edited Apr 23, 2013 at 2:55 PM
Hi Mark, Thanks for your reply. I am still having some problems with this and if you can give me some more pointers as to where the problem may lie. Please find attached the exel.php file from which the code was lifted from as I placed my discussion on the forum. As stated on the forums and your reply I have taken out the ‘else’ I have and every after the $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save('php://output'); exit; including the closing ?> PHP tag but still no joy. I opened the file in notepad and saved it to UTF-8 and tried again, again the excel sheet wouldn’t open in Excel but in word it looked all jibberish but slightly different looking jibberish, Attached is a copy of the Excel spreadsheet after it was downloaded. I not too sure of the trailing white space in it you mention, there is definitely no opening white space and the BOM markers you I did have a look for but wasn’t too sure as what they look like exactly. Any help would be graftefully appreciated. Regards, Adam
<?php
// This is the API connector servlet to allow the frontend JS to communicate with the back
$login_required = true;
if (!$_SESSION) { session_start(); }
require_once("ugs/class_ugs_client.php");
require_once("classes/class_product.php");
$p= new Product;
$p->sarDBConnect();

if ($_SESSION['current_recordset'] != "") {
    $query = mysql_query($_SESSION['current_recordset']);
    while($q = mysql_fetch_assoc($query)){
        $output[] = $q;
    }
    /** PHPExcel */
    require_once 'classes/PHPExcel.php';
    
    
    // Create new PHPExcel object
    $objPHPExcel = new PHPExcel();
    
    // Set properties
    $objPHPExcel->getProperties()->setCreator("Result Clothing")
                                 ->setTitle("Result Backorder Report")
                                 ->setDescription("Custom Generated Report")
                                 ->setKeywords("Result Backorder Report");
    
    $row = 1;
    foreach ($output as $k=>$v) {
        // Add some data
        if ($_SESSION['userinfo']['distid'] == "RESULT") {
            $objPHPExcel->setActiveSheetIndex(0)
                        ->setCellValue('A'.$row, $v['CUSTNMBR'])
                        ->setCellValue('B'.$row, $v['USCATVLS_1'])
                        ->setCellValue('C'.$row, $v['ITEMNMBR'])
                        ->setCellValue('D'.$row, $v['CSTPONBR'])
                        ->setCellValue('E'.$row, $v['SOPNUMBE'])
                        ->setCellValue('F'.$row, $v['QUANTITY'])
                        ->setCellValue('G'.$row, $v['QTY'])
                        ->setCellValue('H'.$row, $v['ARRIVALDATE']);
                        $row++;
        } else {
            $objPHPExcel->setActiveSheetIndex(0)
                        ->setCellValue('A'.$row, $v['USCATVLS_1'])
                        ->setCellValue('B'.$row, $v['ITEMNMBR'])
                        ->setCellValue('C'.$row, $v['CSTPONBR'])
                        ->setCellValue('D'.$row, $v['SOPNUMBE'])
                        ->setCellValue('E'.$row, $v['QUANTITY'])
                        ->setCellValue('F'.$row, $v['QTY'])
                        ->setCellValue('G'.$row, $v['ARRIVALDATE']);
                        $row++;
        }
    }
    
    // Rename sheet
    $objPHPExcel->getActiveSheet()->setTitle('Result Custom Backorder Report');
    
    
    // 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 (Excel2007)
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="result-backorder-report.xlsx"');
    header('Cache-Control: max-age=0');
    
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    $objWriter->save('php://output');
    exit;
} 
Apr 23, 2013 at 1:27 PM
This is start of the xlsx code taken from the spreadsheet opened in notepad.

PK    ‘V–BG’D²X  ð     [Content_Types].xml­”MNÃ0…÷œ"ò%nY „švAa •(0ö¤±êØ–gúw{&i‰@ÕnbEö{ßøyìÑdÛ¸l
    m𥑁×ÁX¿(ÅÛü)¿’òF¹à¡;@1_æ»˜±Øc)j¢x/%ê…Eˆày¦
©QÄ¿i!£ÒKµ y3ÜJ<§œZ1½0?YÙL%zV
cäÖIb7؇û‰ìa/lÙ¥P1:«qáríÍjªÊj0A¯–Íuë""íàÙ(Œ  ”Á€WìMä)Tjå({ܲû>óOãÂ,XÙ­ÁÚÆ>Bÿ†~×­Ï‚õÓ¤6ÜJ=¡oBZ¾‡°¼tìíX4Êú¾Cgñ,…ˆ’Qg mrLÙÙ¯cïeëàtø±   Zõ?‰‡´»hPvÃð±úÿuÕj•À¼Râæ¸øûî}¬CvÖøPK    ‘V–B¶78é   K     _rels/.rels­’ÍjÃ0€ï{
£{£´…1F^Ê ·2²Ðlå‡$–±½-}ûy‡±ºÒÃŽ–¥OŸ„vûyÕ;‡Ø‹Ó°.JPìŒØÞµ^ê§Õ¨˜ÈYű†3GØWw»g)åšØõ>ªqQC—’DŒ¦ã‰b!ž]þi$L”ò3´èÉÔ2nÊòÃoT¦:Z
áh· ê³ç[ØÒ4½áƒ˜·‰]ºÐyNì,Û•¹>¤>£j
-'
VÌ)‡#’÷EF^6ÚÜnô÷´8q"K‰ÐHàë>_ׄÖÿ¹¢eƏÍ<⇄áUdøvÁÅ
TŸPK    ‘V–B„$±Vé   ¹     xl/_rels/workbook.xml.rels­’ÁjÃ0Dïý
±÷ZvZJ)‘s)\[÷„´¶LlIh7mý÷UH¡ŸÄ¬Ø™ÇHëÍ÷8ˆOLÔ¯ *JèM°½ï|4ÛûgÄÚ[=
&$ØÔwë74çr}$‘M<)pÌñEJ2GMEˆèóMÒ¨9ËÔɨÍ^w(Weù$ÓÜêO±³
ÒÎV š)â¼CÛö_ƒ9ŒèùJ„$ž†Ì/:dG]d×ãWKÆsÞÅsúŸ<«[‹VàtBûÎ)?ð¼‰ùøÌã’0_!íÉ!òä4úEÍÇ©yñãêPK    ‘V–B.%0›  P     docProps/app.xmlSAnÛ0¼÷ï1e#(
ƒbÐ(-rhP#v’ó–ZY„)R ׂÝ×—’!Enz*O³³ƒáKÊ»ScY‡!ïr¾\dœ¡Ó¾4nŸó—Ý÷›/œEW‚õs~ÆÈïÔ'¹  ¾Å@#K.æ¼&j×BD]cq‘Ú.u* T†½ðUe4>x}lБXeÙg'BWbyÓN†üâ¸îèMK¯û|ñuwn“Ÿ’_ÛÖ
”©žŒ>úŠØ·“F+ż)“Ñõ1:«LŠy)·,ÉXU`#JñNÈG„þÎ6`BT²£u‡š|`ÑüN·¶âìDìã伃`À¿È.Å€m)¨7±F¤(ÅDp®cs«–ƒ k¡˜‚$|qgÈbüYm Ð?/片|–ñãÑ+Ž‘|ÃîA|(1°gl} ¹ÇíYø¦wžM¤ð!£blË'p°ÇÞ`B?Œ;Ä—vç€pÇ5)·5,Ó§qM„|Lç
¶×5¸=–£æc£<¯—¢–«E–ÖðfFNŠ÷¿ þ PK    ‘V–B÷5?ҍ  B     docProps/core.xml¥’OOã0Åï|
ËW”ÚIY„¢4HtÅ^iE»Wc­·ñÙÓ
ýöë¸mNGï7o<ÏÓ\¿˜Žüƒµ³ZÎ8%`¥SÚ®ôqu[\QQX%:gaA÷éu{ÖH_KàWpjˆ$ÙXK¿ D_3匈³DØ$>»`¦2¬™r+ÖÀ*Î/™J `ƒaáGGz´Tr´ô»Ðe%t`Àbdå¬d¯,B0ñƬLH£qïáCô$ŽôKÔ#Ø÷ý¬Ÿg4½¿dî~>äUm‡¨$жQ²–ºÐÞCÜuH–ÃMŠµamȱïRâÏÔ;}´[ëzK–¤aï‘æ¸æÁIÏ«Ëœ”ßóå÷Õ-m+^Î~QTÕªäõ·ªž_œó²æ|xÆ“WWsœô5Û“KÎ5vpJâ&ý¾
¹ïæDÄÀ*ˆ2hé"Ûå.¢3äXyÓIÇ”úâîé/Hl³8)½-ìû4/~>
åÓN³Ö.¤¤ÙûêÍÙ·ÿPK    ‘V–B­­áJ       xl/theme/theme1.xmlíY[sÚ8~ß_¡ñ{k6%™’L Ðî¶i3 íN¶°UdË#‰¤üû=¾€­X´¤Mgw§å,ù;÷‹ÌËó/)'wT*&²±ã=ï9„f¡ˆX‹ù³‘C”†,.2:v¶T9çg¼„SÐ”$ÏÔ)ŒDëüÔuUˆÛ ž‹œfxo%d
—2v#   ÷È6ån¿×º)°Ì!¤ÈõýjÅBJKçlÇ|Æñ+Ӫع¼
K‰mŠ­½âGmÕ”Kr|ì œHÜ/èíJ㍱Ó+?Ž{öÒÝq}€¶E7/?5]M­û%Œ—{Bo¸ÜóïWü»¸Ùl6y{~% Â-õ:X>ò&;ž-PuÙå=í=ßÄ·ø:ø“ÉdœøAƒ÷;øQoè_ô
¼ßàƒ®þ“‹éthàƒ?ìàç/N†¾‰/A   gÙºƒ.⹏̲üµ>Bøh— 
ÊmeWEŸéC¹–Âg!ç(ƒšeDosº‚qSH—’A! N)´îT[¡êl²ˆ
%ËõØù+¬ˆr~N΋ú²¸µã”‰³ƒ´   ²Â.$,Û°K©"ïè=¹)jnaL—òq‹˜A  "-À™Nà»-pnBMG}”XÞ6à«ÍgC×ÛDn4³ ß$©¼‚O„´šó¦Õ6g“ÅvárÓÆÝ ÜÙdO„s¶É1O™å4¡†š×£1ͨ&Å=±¦ÔBö‰1ïW,”B‰•&Ÿ™ ³ºdÁ–ÚNôš¥—-ØCmøæê#™ncIïL$&?pKÊ
7¾‚†Ôª1¤¼|:±)y»•¡áp¥1Ò1å‚Ì"ª”æ½Üê¾ì3Ö°_ñmj"¥fkò-ÑF^Šõ44·ê̲¤ýS­1E\mUB˜R¬1÷GFõãÊú‹{‚w6²îÉFwMYöµVËöÚ_¥Õ^àc…Ñ`áþ‡mõ6Ù5ÅLþÝUwÕ_±«ªåc{iÓ>ÝöL[òK¸+Æù­ÞrúV•W¡ÒÑ7ËEI´Ÿ§ó/kq.–P^)ôßL'·  ä(Æ+%Īf+’…S¼swydh|¹ìÎoˆ}%¢j{Ð>×íÙ”«Xµ
Ç
¼ø1a^<RšØ¥_•æ¶¼‰Õ@ 8´{Ã~%38
¿Wvayò©"ZÇȳâ
ŽtÛèÛ^kI;ü˜´c‚Ôç<A”z(¹Ýr䙹"÷¨UÐB>vV8'áeš#?U4 àq6vB]›òÍb~h°=-½ÞAƒ
¹TúTRQ•·v¯=²Fÿ~à~xÜïÕb0òþE-܇¡¥«
õfYßMåmÝ“%ßÈ@½ý*»"¦ð™Ñß-$V¨_'žYùu<|½RWð<º'Z±¯àåõ^‡rÕRÏ= ûwš2xBS‚_×”"sqlDåq   Ç   ¤Èѱ#¤Nv¡<aá\âàPÊB½–E¡áÅ»âBWz×ô­ŠGÕäâDß°˜H†N'’Òk]Ûù
f^¿ý|Ý1ªûÌ^]•W¿KzGù¢¨Þaa¿C’]7©Qâ͵U×2žÿ‡'¿÷=ãA#ÈÌ,â·š~ëQpòc*<òQÛ·[ÜŽ~Ôæxø Å6n&CÞÌ·qƒÑ'û‰’`">Õå·ß\¢Î£–q«Ÿ;F5!õ~þðÙröà€³{½ŸãìÀâëàë®v»%ê¶2åªó§‘X~FÙ—xPÚp­ªwE_ð¨9ݽîG>nCzöPK    ‘V–Bz'´«Û  1     xl/sharedStrings.xml…XMsâ8½ï¯pqJ;–dË6SÀ棌CÄ‘š°ªdcgj÷߯v2‹^{ŽyŠD«»ßë'w¾ýsxq~îÞŠýéØmñ/¬åìŽßOOûãnë!¿û3j9E¹=>m_NÇ]·õï®h}ëýÑ)ŠÒÑ[E·õ\–¯_]·øþ¼;l‹/§×ÝQ¯üuz;lKýçÛ·x}ÛmŸŠçÝ®<¼¸‚±À=l÷Ç–ó~Üÿý¾‹OïDzÛ^ØêuŠ}¯SöÒÕxÁï¸e¯ãèg1ó<…ÑAO¯Wî‡Î"&™#e$ƒëåþº?IñióþzVD[aýº^ñ}…Qøÿ:3
‚›$MÓÑõR>ˆ¯!.\&u†¹Îá
‚øð˵P&«dŽk#…Âè|ý8¾^Yº!CßwnÄ-ÎQ]xÜÏ®WyÜGa æ+šØ{º
…sÙ—
¹e÷4¹]Íì%ºØ€‹¬?'ƒEUõKÜ—
‚(ýtŠkʤÂèãx’ã¦e  ªg
£ƒi6¼^1iæ¾™çÜ„¨?ŒKHŠY—<å‚cÐy!áE4:_/Hþ
TqŽALÉe2A‘~9͵ ÞÝô¸KÝ¢ýï1Š2ìy>—΍g•J´)Öä‹e®s1v3öSLpÁgÄüLÓ#¡í“µWöh`¨WH$±®¥MOÁH19+œÙ…W DÄ,„“&$&M}ÇÙ§?ð#­6ïX[ÞCÐh5!Ý/0.Ž6ƒ)f½'|Ž¦B¥   ‘§MÞ’%·ïd(ŒRƒþ¿FQØ—•Át“À Ln ÃΛV :È6ðj"Ú'y"    ã톉u9ÍN–fr àP®ˆÁýPA˜[†3„&ñÊA¹ºì"¤T¶Ú%h™·—DÐÀO=ã²d2'C isٲؤdìÛI‚äñN”Aýй§FÌ9õGê ‡³mè>*XÓ ,Õê¡VØ”H{G)âæN²$ÎSKÁ„[[¡ ˆ…]J߈0&Fñ©aËé­Òc¿Ì’z< ŽÓ€Xˆ—Ư- ¢‰\ršõ&ûæÌk¡HAÐt*8)’Üo£ÎçM¿ÑžAÐ²˜Ðàiô7oLm¿$)u‚ð«óõ<'ØŠ㲇xx¥&¦Ý:¡QzhA<1¹¬Ñ%g²Žj•/æ¤c†~BF
£Ä ׶|ù0_Û7‘Èök0"iãh"sJ»uOè–Ð|c©øöí*UñnáœÂúð±d/rí¯å9šÕ4]j*™õ'©Ãµ_õ]ĮʠéÁ#Ál8€&‚Sž¤zpÀ®9r
Ÿ†ÜE5ña¸!éIJ™    ÁûßÄñD/6êa€Î£ö§~×£"#Áž®çï/ú硱n„¥¯]
O"ɐ„dÔ¡!»ÀÅ=)ŸR‘À>êSÍµÚAÒ¦ ©uk#ãXÅN$aœôs8OAiö§‚´áž†Iä˜]²iчÔkxó¦»þ|€\zpß[Ù¨Á¢„ròA`4ß¡À`k0*:}ØW²’ Ã3{V´YØ)OÐYõm›ìpJ&ÂÞuã!ã5k`b/RŒ[½ ~íX·(ÊÞPK    ‘V–BQ¹9Cþ  §  
   xl/styles.xml­TMoœ0½÷WX¾7ì®Ôª€¨„ÔC£ªÙJ½X+þ@ö°eóë;¶  °J¤^ÊÅ3oÆoìÇŒó»I+vç¥5ßßì8ÓØVš¾à¿ŽÕûOœy¦Ê(ø<¿+ßå/
O ȈáÖ¢¡èàÀƒ; ñ?!·Yæ›háoì †"uZ ¹®Ï<í­LZe‡Ýîc¦…4¼Ìͨ+ž5v4XðÏʼ³fEö<”*4°³P¿JÖN†\ÿœ ý>xur"‹ÜØc²
Ý.nB'Ÿ`o¬²Ž¹¾.xUíâà,Ž‹§cH¥–sxÊ|ˆàLE›íãe€¹eõ÷[úôÅÓ^6u7”¡|\þQ¬wâ²?|øõâB·®­k©u¶ÿ#Aaã$A©ÇÐ.¿»«Ô©cékƒÖ,Hùbÿl&šäÚ-[â~M;u¯)
this is the end
{_A¶÷@cÌq×ù £Øp+a!,1ú{)-'“™yýä¤ÞÔ$¶p˜(bW¶ÈÞÂã~ÝŸAIGÞ0&_X—Ý|£„}4b5&,Bïõ¢µ¸@e*•x˜giûÀæuE÷BOúhÌI·ÿèåPK      ‘V–BG’D²X  ð                   [Content_Types].xmlPK      ‘V–B¶78é   K               ‰  _rels/.relsPK      ‘V–B„$±Vé   ¹               ›  xl/_rels/workbook.xml.relsPK      ‘V–B.%0›  P               ¼  docProps/app.xmlPK      ‘V–B÷5?ҍ  B               …  docProps/core.xmlPK      ‘V–B­­áJ                 A  xl/theme/theme1.xmlPK      ‘V–Bz'´«Û  1               ¼  xl/sharedStrings.xmlPK      ‘V–BQ¹9Cþ  §  
             É  xl/styles.xmlPK      ‘V–B­¹Ä   ©  Ö               ò  xl/workbook.xmlPK      ‘V–B.¼üD`  @Ñ               È  xl/worksheets/sheet1.xmlPK      ‘V–BÍKR"x      #             ^2  xl/worksheets/_rels/sheet1.xml.relsPK      Ñ  3