Export WordPress Content to Excel

I recently built a Customer Relationship Management system (CRM) for myself using WordPress. I had experimented with a few existing solutions like Salesforce and Sugar, but none worked for me. I spent about 10 minutes creating this solution (screenshot) with custom taxonomies and custom meta boxes. In a few months I might review it in more detail after using it for a while.

One feature I’d like to share now is my method of exporting to a spreadsheet. This technique should work for all of the major spreadsheet programs (Microsoft Excel, Google Docs, OpenOffice).

I knew I wanted a way to get my data into Excel for analyzing it, but didn’t want to figure out how to dynamically generate an .xls file. So an easy workaround is to render your content in a simple table, then copy/paste it into your spreadsheet.

Create the Template

First you’ll need to build a template page for the table (if you’re using Thesis see my note below). Here’s a start:

Instead of calling get_header(), which would’ve rendered the actual header, I called wp_head(), which is the hook that runs some essential WordPress code in the header. The goal here is to only display the table so you don’t get any extra text coming across to your spreadsheet.

Create a page called “Download”, set it to Private, and set the page template to Download. Now you’ll be able to see the results of your work on this page.

Before we build the loop that renders all the content, I like to put in the table headers. Inside <table> put the titles of all the fields you’d like exported like this:

Now for the code that actually lists the data. I’m going to do a simple query that just lists all posts. You could customize this query to do whatever you’d like (see query_posts in the Codex for options). You could even make a custom field on your Download page to define the query variables if you will be changing it often and don’t want to edit code.

Inside the <tr> you’ll put <td>’s for each of your variables. Here’s some tips:

  • To list the value of a custom field, use <td><?php echo get_post_meta($post->ID,'field_name',true);?></td> where field_name is the actual name of the field.
  • To display the publish date, use <td><?php the_time('m.d.y');?></td>
  • To list the values of a custom taxonomy in a comma-separated list and without having it link to the taxonomy page, use <td><?php $poc = get_the_terms( $post->ID, 'poc', '', ', ', '' ); $list = ''; if ($poc) { foreach ($poc as $data) $list .= $data->name.', '; echo $list; } ?></td> where ‘poc’ is the name of the taxonomy. You could just use the_terms() but then you’ll have links in your spreadsheet.
  • To list the categories as a comma-separated list and without links, use <td><?php $cats = get_the_category(', '); $list = ''; if ($cats) { foreach ($cats as $data) $list .= $data->cat_name.', '; echo $list; } ?></td>
  • To display the post content without having it break your table (which the_content() would do), use <td><?php echo get_the_content();?></td>

That covers all the major types of data that I used. Let me know if I missed anything.

Using it with Thesis

Thesis is a little different, in that you can’t create template pages like normal themes. Based on Kristarella’s great post on Creating Thesis Squeeze and Landing Pages, I used filters to remove the header, sidebars, footer and headline area. I then created a function that rendered the table and attached it after the post content. Here’s the code to put in custom_functions.php (note that I excluded everything inside the table – that’s the same as the code above).

chat9 Comments

    • Bill Erickson says

      For this specific implementation I use More Fields to create the boxes, but I’m getting ready to write a post on how to code them. I prefer coding them for client projects and plugins so it just shows up when they install the theme/plugin, rather than saying “now download this plugin and do these steps.”

  1. Lisa says

    Hi, this is exactly the type of plugins that I am looking for. Can you please email me and let me know how much will you charge if you were to help me implement this? Sorry I am an amateur.

    • Bill Erickson says

      Yes you can. This was designed for clients who don’t know (or want to know) mysql. Another benefit of this method is being able to limit it just to the content you want to see. For instance, If I’m only interested in names and email address, I just set those as the two fields. I’ve also used this to list all post matching a certain criteria so I can do something with it. For instance, I want to go all the Prospects in my CRM and close out the old ones. I’ll add a query_posts(‘category=prospects&posts_per_page=-1′); to the top, then make the post id link to the single post. Now I have a dynamic list that I can work through as I determine if a prospect should be classified as closed.

  2. says

    This is exactly the functionality that i need for a client to be able to view their available stock in an online table. Followed your instructions and it worked like a dream. Thanks for the advice!

  3. Richard says

    I tried you’re example, but it didn’t download anything. It printed to screen. I’m using the Genesis framework, so I’m wondering if I have to do something else for that?

    • Bill Erickson says

      It’s not supposed to download anything. It displays as a table on the screen. You then select all, copy, then paste in Excel.

      From the article: “I knew I wanted a way to get my data into Excel for analyzing it, but didn’t want to figure out how to dynamically generate an .xls file. So an easy workaround is to render your content in a simple table, then copy/paste it into your spreadsheet.”

Leave a Reply

If you'd like to include code in your post, please post it to http://gist.github.com and include a link.