array to excel problem!

Topics: User Forum
Jul 30, 2012 at 4:48 PM
Edited Jul 30, 2012 at 4:50 PM

Hey guys,

I'm using PHPExcel 1.7.7 and I'm having a problem exporting an array to an excel spreadsheet.

First, here's the array I'm trying to export:

 

Array ( [0] => Array ( [0] => Response ID [1] => Date Submitted [2] => "Tester" [3] => "Video Link" [4] => Yes Or No? ) [1] => Array ( [0] => 1 [1] => 2012-07-05 16:52:05 [2] => "tester1" [3] => "=HYPERLINK(""http://leviathan.edu.yorku.ca/OpenVULabUI/files/1341521543768/1341521543768.avi"",""1341521543768.avi"")" [4] => Yes ) [2] => Array ( [0] => 3 [1] => 2012-07-10 14:44:54 [2] => "tester2" [3] => "=HYPERLINK(""http://leviathan.edu.yorku.ca/OpenVULabUI/files/1341945911217/1341945911217.avi"",""1341945911217.avi"")" [4] => No ) [3] => Array ( [0] => 5 [1] => 2012-07-10 14:45:30 [2] => "tester2" [3] => "=HYPERLINK(""http://leviathan.edu.yorku.ca/OpenVULabUI/files/1341945938105/1341945938105.avi"",""1341945938105.avi"")" [4] => Yes ) [4] => Array ( [0] => 7 [1] => 2012-07-10 14:46:03 [2] => "tester3" [3] => "=HYPERLINK(""http://leviathan.edu.yorku.ca/OpenVULabUI/files/1341945966388/1341945966388.avi"",""1341945966388.avi"")" [4] => No ) [5] => Array ( [0] => 9 [1] => 2012-07-10 14:46:32 [2] => "tester4" [3] => "=HYPERLINK(""http://leviathan.edu.yorku.ca/OpenVULabUI/files/1341945995206/1341945995206.avi"",""1341945995206.avi"")" [4] => Yes ) )

 

Second, here's the code I'm using to export the data. I initially had header code so the user can download the data but i took it out for debugging purposes.

     $output = survey_generate_results($sid);
     $objPHPExcel = new PHPExcel();
     $objPHPExcel->getActiveSheet()->fromArray($output);
     $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
     $objWriter->save("php://output");

 

Lastly, here's the output it gives me:

ÐÏࡱá;þÿ  þÿÿÿ ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿþÿÿÿ  !"#$%&'()*+,-./0123456þÿÿÿ89:þÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿþÿà…ŸòùOh«‘+'³Ù0¸@Hh€ ˜ ¤°äUntitled SpreadsheetUnknown CreatorUnknown Creator@J¨ƒnÍ@J¨ƒnÍ » ÌÑB°=¼%r8X"1ܐCalibriàõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À à À “€ÿ’â8ÿÿÿÿÿÿÿÿÿÿÿÿ€€€€€€€€€ÀÀÀ€€€™™ÿ™3fÿÿÌÌÿÿffÿ€€fÌÌÌÿ€ÿÿÿÿÿÿ€€€€€ÿÌÿÌÿÿÌÿÌÿÿ™™Ìÿÿ™ÌÌ™ÿÿÌ™3fÿ3ÌÌ™ÌÿÌÿ™ÿfff™–––3f3™f333™3™3f33™333…w WorksheetÁÁgæ®ük Response IDDate Submitted"Tester" "Video Link" Yes Or No?2012-07-05 16:52:05 "tester1"w"=HYPERLINK(""http://leviathan.edu.yorku.ca/OpenVULabUI/files/1341521543768/1341521543768.avi"",""1341521543768.avi"")"Yes2012-07-10 14:44:54 "tester2"w"=HYPERLINK(""http://leviathan.edu.yorku.ca/OpenVULabUI/files/1341945911217/1341945911217.avi"",""1341945911217.avi"")"No2012-07-10 14:45:30w"=HYPERLINK(""http://leviathan.edu.yorku.ca/OpenVULabUI/files/1341945938105/1341945938105.avi"",""1341945938105.avi"")"2012-07-10 14:46:03 "tester3"w"=HYPERLINK(""http://leviathan.edu.yorku.ca/OpenVULabUI/files/1341945966388/1341945966388.avi"",""1341945966388.avi"")"2012-07-10 14:46:32 "tester4"w"=HYPERLINK(""http://leviathan.edu.yorku.ca/OpenVULabUI/files/1341945995206/1341945995206.avi"",""1341945995206.avi"")" » ÌÑ*+‚€Áƒ„&ffffffæ?'ffffffæ?(è?)è?¡"dXX333333Ó?333333Ó?U} $ } $ } $ } $ } $ ý ý ý ý ý ð?ý ý ý ý @ý  ý  ý  ý  @ý  ý  ý ý @ý ý ý ý  "@ý ý ý ý >¶@ggÿÿÿÿÿ þÿÕÍÕœ.“—+,ù®0¼HP X`hp x Žä  Worksheet Feuilles de calculRoot Entryÿÿÿÿÿÿÿÿ ÀFJ¨ƒnÍJ¨ƒnÍÀSummaryInformation(ÿÿÿÿ ÀFèWorkbookÿÿÿÿÿÿÿÿÿÿÿÿ ÀF‡ DocumentSummaryInformation8ÿÿÿÿÿÿÿÿÿÿÿÿ ÀF7ìþÿÿÿþÿÿÿþÿÿÿýÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ

Thanks for your help!

Coordinator
Jul 30, 2012 at 10:44 PM

Yes, the output is a binary file that you're streaming directly to your browser via php://output. That's what a BIFF8 binary excel file looks like if you it in a text editor.

You need to send the appropriate headers to tell the browser that this is an Excel file, as shown in /Tests/01simple-download-xls.php

 

Jul 31, 2012 at 3:05 PM
Edited Jul 31, 2012 at 3:16 PM

the thing is that's what shows up in the excel file also :S

Here's the file that downloads:

http://phpexcel.codeplex.com/Download/AttachmentDownload.ashx?ProjectName=phpexcel&WorkItemId=18428&FileAttachmentId=6533

Coordinator
Jul 31, 2012 at 3:33 PM
Edited Jul 31, 2012 at 3:38 PM

There are two TAB characters and a CR/LF at the beginning of this file (hex characters 09 0D 0A 09). Make sure that your script doesn't echo anything other than the actual file output rendered by the save, not even blank lines

Jul 31, 2012 at 4:31 PM

My script just outputs at array that you see in my question. It doesn't have any echos. If you print_r the variable I'm using in the fromArray function you will see that array.

Coordinator
Jul 31, 2012 at 4:53 PM

It needn't be an echo to generate output in PHP: switching in and out of PHP like:

?>

<?php

will also generate output

I haven't seen your script, but somewhere it's outputting a tab, a carriage return, a line feed, and another tab.

Sep 1, 2014 at 10:04 AM
I had the same problem... I solved it by adding
ob_end_clean();
right before the code that generates the excel file!

I know.. its better to search your files for the generated output whitespace but in some projects, you include over 100+ files and then my solution will save you a lot of time!

Barry