wordpress hit counter
Converting a Facebook stream into an Open XML spreadsheet - OpenXML Developer - Blog - OpenXML Developer
Goodbye and Hello

OpenXmlDeveloper.org is Shutting Down

There is a time for all good things to come to an end, and the time has come to shut down OpenXmlDeveloper.org.

Screen-casts and blog posts: Content on OpenXmlDeveloper.org will be moving to EricWhite.com.

Forums: We are moving the forums to EricWhite.com and StackOverflow.com. Please do not post in the forums on OpenXmlDeveloper.org. Instead, please post in the forums at EricWhite.com or at StackOverflow.com.

Please see this blog post for more information about my plans moving forward.  Cheers, Eric

Converting a Facebook stream into an Open XML spreadsheet

Converting a Facebook stream into an Open XML spreadsheet

  • Comments 24



A Facebook stream is a way of easily accessing the posts on your Facebook wall, and programmatically putting them to good use. It has some similarities to a Twitter feed.


This sample is an example of an app which pulls down a stream and puts it into a nicely formatted Open XML spread sheet using PHPExcel. The spreadsheet reorganizes posts into a time log with rows showing the person’s photo, their name (with a link back to their profile), the actual message from the post and the time and date of the post.


Privacy considerations

The data in the stream is from your friends; who have given you permission to view their wall but not necessarily intending the public to do so. Due to this, and the way Facebook’s business model works, there are strict authentication requirements; more so than that of a twitter feed. Therefore it isn’t appropriate to give you access to my actual implementation - you need to build this sample app yourself!


Step 1: Register an app in Facebook

To develop an app for Facebook you will first need to add the developer app to your Facebook page http://facebook.com/developers  and register your new app by clicking the ‘Set Up New Application’ button and giving it an name. This will give you a set of keys: an API key and a Secret key, which you will need to use in your code later.


The instructions here http://developers.facebook.com/get_started.php are great for getting set up.


Go to your apps settings page. From http://www.facebook.com/developers/  => under ‘My Applications’ => Click the name of your app =>  click ‘Edit Settings’ => ‘Canvas’

Change the ‘Render Method’ to IFrame and under ‘Required URL’s’ set the canvas redirects to point back to your page. This enables you to access your app through Facebook by going to your app page, for example, http://apps.facebook.com/reallysnazzyapp/


Step 2: Hosting your app

While you register an app in Facebook and it appears as part of Facebook, you need to host the code separately yourself - somewhere on a public web site from where Facebook can surface it.


There are a number of free php hosting providers which you may try. Some are better than others. You will need one which has php5.2 with --enable-zip (for the PHPExcel library to work)


You can check these details by putting up a page with the following script: 



echo phpinfo();


This will display the info of the set up of your PHP install.


If you have the resources available, I would suggest having your own web server to develop on. Controlling the environment through these means will make the development process less complicated.



Step 3: Writing the app

Our app is not only displayed in Facebook, but the code also accesses Facebook data.  For an app to access Facebook data a security set up is required.


To authenticate with Facebook, you need to exchange tokens and session keys; generated from your API and secret  keys (that you would have got in step 1). The majority is handled by the PHP Facebook API libraries. However, you will need a public facing web server to develop on, so that you can  provide your half of the authentication with Facebook.



You will need to download the Facebook PHP API library from: http://svn.facebook.com/svnroot/platform/clients/packages/facebook-platform.tar.gz


There are four parts to the code:

·         Authentication with Facebook

·         Pulling down the data from Facebook

·         Processing/searching through the data

·         Inserting data into a spreadsheet with stylish formatting


Authentication with Facebook

In your code you will want to put the include for the Facebook API library:


include_once '../php/facebook.php';


Then insert the code to provide the authentication.

You will need to replace (with your own) the API, secret keys, and the addresses where to redirect; after authentication, or if the user cancels out:



$appapikey = “xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';

$appsecret = 'yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy';


$next = "http://SomeServer.com/spike/index.php";  //Where to redirect after authentication

$next_cancel = "http://SomeServer.com/spike/canceled.php"; // Where to redirect if the user cancels the authentication



//-----Login, authenticate and get permission to read the user’s stream-----



$facebook = new Facebook($appapikey, $appsecret);//Create a new facebook object



//Sometimes the session token has expired or is invalid, this throws an execption. If this happens catch it and reset the user and try again





      $user_id = $facebook->require_login();//get the user to login



      if (!$facebook->api_client->users_hasAppPermission('read_stream',$user_id))


            $facebook->redirect("http://www.facebook.com/authorize.php?api_key=" . $appapikey . "&v=1.0&ext_perm=read_stream&next=" . $next . "&next_cancel=" . $next_cancel );




catch (Exception $ex)


      //If the session token has expired or is invalid, this will effectivly reset it and then redirect to try again.


      $facebook->set_user(null, null);






This requires the user to login and then check if they have permission for this app to read their stream. If not, it redirects them to the authorization page. You need to insert the address of where you want the user to be redirected to once authorized, and where to go if they cancel out. I opted for the simple solution of redirecting back to the current page. I also have a plain, simple page in place that the user will be diverted to if they cancel.


canceled.php :



echo "No love?";



Sometimes you will find a session key is retained in your app that has actually expired. This is what the try catch is for. If there is a problem, it will catch it and reset settings and then bring the user back to try again.


Displaying a welcome message prior to downloading the file

Once the authentication has been completed, we check if this is a first time load. If it is we give the user a message with the option to download the spreadsheet. Clicking on the submit button will now cause the data to be pulled and converted into a spreadsheet:


//the first time the user arives at this page $_REQUEST['mode'] will != 1 so just display the intro

// if the user clicks the submit then $_REQUEST['mode'] == 1 and the file will be created and presented to the user

if ($_REQUEST['mode'] != 1)


      echo '<form action="index.php" method="get">';

      echo 'Download posts from your wall, as an Open XML Spreadsheet <br/>';

      echo '<input type="hidden" name="mode" value="1"/><input type="submit" /></form>';




// ...create file and present to the user


This is necessary because if you redirect to another page the session key will be changed and the authentication will fail. For a more robust solution, you could have a switch case which will include different PHP file depending on the mode.


Pulling down the data from Facebook:


Pulling down the results is easy enough

$feed = $facebook->api_client->stream_get();

This will pull down by default the last 30 wall post by your friends.

The $feed is a nested array of ‘posts’, ‘profiles’ and ‘albums’.

In this example, I have only used the ‘posts’ and ‘profiles’ fields.

You can also expand the results which stream_get() returns by setting some parameters

$facebook->api_client->stream_get(viewer_id, source_ids, start_time, end_time, limit, filter_key);

See http://wiki.developers.facebook.com/index.php/Stream.get for more information.


Processing/searching through the data

Next we take the stream ($feed) and extract the information we want before putting it into an array we can use:

// get the revelent infomation out the stream ($feed) and put into an array


$posts = $feed['posts'];

$profiles = $feed['profiles'];


//Put in the names for the row headings

$listMessageDetails  = array







  "time"=>"Time created"




  // Put in the rest of the details for each message

$i =1;


foreach ($posts as $post)


      $message = $post['message'];

      //If the post is auto generated by some other Facebook app (i.e., xyz quiz),  it normally doesnt have a message

      //so we dont add the details of these posts 

      if( !empty($message))


            $userId = $post['actor_id'];

            $created_time = $post['created_time'];  // this is in'unix time' format




            $details = getDetails($userId, $profiles);

            $image = $details['pic_square'];

            $name = $details['name'];

            $url = $details['url'];



            $MessageDetails['name'] = $name;

            $MessageDetails['message'] = $message;

            $MessageDetails['time'] =  date("g:i a, F j, Y", $created_time ); // convert 'unix time' to 5:16 pm, March 10, 2001

            $MessageDetails['id'] = $userId;

            $MessageDetails['image'] = $image;

            $MessageDetails['url'] = $url;


            $listMessageDetails[$i] = $MessageDetails;





The data in the ‘post’ array has a lot of information, most of which is not relevant to what we are interested in for this app (e.g., ‘post_id’  , ‘permalink’ etc. ) This pulls the data we are interested in and puts it into an array. (Who said something, and their details, and what they said)

A post only has the post’s user id (i.e., actor_id) not their name, link to their profile pic etc. So the getDetails() function pulls that data out of the ‘profile’ array of the stream ($feed).

The spreadsheet columns will also need some heading so easy to just put them into this array at this point



Putting that data into a spreadsheet with stylish formatting

Through the PHPExcel library you can take the processed data and put it into a spreadsheet and apply some formatting. You will need to download the PHPExcel library from http://phpexcel.codeplex.com/ , put the library folder on to the web server and put an include at the top of your script.


include 'PHPExcel/IOFactory.php';


The documentation which comes with PHPExcel is very useful and has lots of examples; make sure you have a look through it.



Then create a new PHPExcel object and load the data in.



//-----Take the data in $listMessageDetails and put into a spreadsheet using the PHPExcel library


$objPHPExcel = new PHPExcel(); //create an new PHPExcel spreadsheet object

$sheet = $objPHPExcel->getActiveSheet();




foreach ($listMessageDetails as $row => $MessageDetails)



      //put the values from each message into a new row, in the correct columns


      //note: columns are 0-based indexed but rows are 1-based. So ‘A1’ and an index of (0,1) would refer to the same cell.





      $sheet->getCellByColumnAndRow(1 , $row + 1)->setValueExplicit($MessageDetails['name'], PHPExcel_Cell_DataType::TYPE_STRING);




      $sheet->getCellByColumnAndRow(2 , $row + 1)->setValueExplicit($MessageDetails['message'], PHPExcel_Cell_DataType::TYPE_STRING);

      $sheet->getStyle('C' . $row)->getAlignment()->setWrapText(true);



      $sheet->getCellByColumnAndRow(3 , $row + 1)->setValueExplicit($MessageDetails['time'], PHPExcel_Cell_DataType::TYPE_STRING);





This code takes everything in the $listMessageDetails array and loads it into $objPHPExcel ready for formatting.

This is a convenient place to set the column widths, so do that at the same time. There is a gotcha that you need to be aware of, is that: columns are 0-based indexed but rows are 1-based. So ‘A1’ and an index of (0,1) refer to the same cell.


Most of the formatting used here was copied from an earlier PHP/Open XML article I wrote at http://openxmldeveloper.org/archive/2009/05/06/4606.aspx Refer to this article for further details on how to use the PHPExcel library for formatting. Most of what is used here is quite self explanatory; so I won’t go into huge detail.



Set some basic layout properties:


//Set Print properties

$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE); //Set printing orentation



// set autofilter on the columns

$objPHPExcel->getActiveSheet()->setAutoFilter('A1:' . $objPHPExcel->getActiveSheet()->getHighestColumn() . $objPHPExcel->getActiveSheet()->getHighestRow() );



Make the headings bold, put in some borders and alternating row colors for visual clarity:




//-----Put in some formatting to the table data to make it easer to read-----


$highestRow = $objPHPExcel->getActiveSheet()->getHighestRow();

$highestColumn = $objPHPExcel->getActiveSheet()->getHighestColumn();

$objPHPExcel->getActiveSheet()->insertNewRowBefore($highestRow + 1, 1);//Add one more row as a footer to the table


//put a border on the top and bottom rows and make the title row bold



$objPHPExcel->getActiveSheet()->getStyle('A' . ($highestRow + 1) )->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);


//Loop through all of the rows and put in fill and borders on the edges

for($row =1; $row<$highestRow + 2; $row++) //remember that


      //Set the colors, mid blue/grey for the top and bottom rows, with alternating white and light blue/grey

      if ($row == 1 || $row ==$highestRow + 1) $color = 'FFCFDAE7';

      else if ($row%2==0) $color = 'FFFFFFFF';

      else $color = 'FFE7EDF5';


      // set the fill type and apply the color

      $objPHPExcel->getActiveSheet()->getStyle('A' . $row)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);

      $objPHPExcel->getActiveSheet()->getStyle('A' . $row)->getFill()->getStartColor()->setARGB($color);


      //duplcate the first cells style (fill plus the top and bottom borders) across the whole row

      $objPHPExcel->getActiveSheet()->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle('A' . $row), 'B' . $row . ':'. $highestColumn . $row); //copy style set in first column to the rest of the row


      //Put some borders on the far left and right cells of the row

      $objPHPExcel->getActiveSheet()->getStyle('A' . $row )->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

      $objPHPExcel->getActiveSheet()->getStyle($highestColumn . $row )->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);



Put the profile images into the spread sheet by grabbing the image and saving it to a temp file. Then use PHPExcel to insert it into the spreadsheet:


//-----put the profile image into the streadsheet in column 'A' -----



$highestRow = $objPHPExcel->getActiveSheet()->getHighestRow();


for($row =1; $row<$highestRow; $row++)


//set the row height to 50 for all but the first row

if ($row !=1)     $objPHPExcel->getActiveSheet()->getRowDimension($row)->setRowHeight(50);



      $objDrawing = new PHPExcel_Worksheet_Drawing();

      $objDrawing->setName('Profile Image');

      $objDrawing->setDescription('Profile Image');


      $pathURL = $listMessageDetails[$row - 1]['image']; 




      $path = tempnam(sys_get_temp_dir() , 'path' ); //creates a  temp file  to put the image in

      if( !empty($pathURL) && $row != 1) // if the image is inaccessable or doesnt exist (e.g., the first row) then skip


      copy($pathURL, $path); // Copy the image from its url location to the temporary file, ready to be loaded in the the spreadsheet



      $objDrawing->setPath($path);// the path of where to find the image to insert

      $objDrawing->setHeight(50); //size you want the image to be displayed as


      $objDrawing->setCoordinates('A' . $row);// set where you want to put the image





Now we have the url of the Facebook profile of the author of each message. We can make their name a clickable hyperlink:




// Put in a link on the profile name back to the users Facebook page

for($row =2; $row<$highestRow ; $row++)


      $url = $listMessageDetails[$row - 1]['url'];

      $objPHPExcel->getActiveSheet()->getCell('B' . $row)->getHyperlink()->setUrl($url);




To conclude, we can return the spreadsheet as a file to the user. To stream the file to a browser you will need to include the headers:



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

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

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


Now we can output the stream. This takes PHPExcel object we have been working with puts it into a writer before writing to the output:



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




There you have it. Authenticating with, and taking data, from Facebook. Converting that data and presenting it as a formatted spreadsheet.


The complete PHP app is attached. Enjoy!

Attachment: FacebookStreamToOpenXMLSpreadsheet.zip
Page 1 of 2 (24 items) 12