CartThrob Forums | How to create an Excel file template

1 of 2

How to create an Excel file template

Pete Eveleigh

Posted: 06 May 2010 12:53 PM

[ Ignore ]

Administrator

Total Posts: 152

Joined 2010-01-19

The following code will let you create a template that spits out an Excel file.
I’ve kept it pretty simple, you may want to change the ordering of the fields etc.

The first part uses PHP to set the header information so you will need to enable PHP for this particular template. You’ll also probably want to set it so only store administrators can see it.

<?php
    header("Content-type: application/vnd.ms-excel");
    header("Content-Disposition: attachment; filename=orders.xls");
    header("Pragma: no-cache");
    header("Expires: 0");
?>
    <table border="1">
    <thead>
    <tr>
        <th>Order No.</th>
        <th>Date</th>
        <th>Customer name</th>
        <th>Customer email</th>
        <th>Customer phone</th>
        <th>Items</th>
        <th>Discount coupon</th>
        <th>Discount value</th>
        <th>Tax</th>
        <th>Total</th>
        <th>Notes</th>
        <th>Billing address</th>
        <th>Shipping address</th>
        <th>Status</th>
        <th>Transaction ID</th>
        <th>Customer IP</th>
        <th>Error message</th>
    </tr>
    </thead>
    <tbody>
    {exp:weblog:entries weblog="orders" status="not closed|closed" allow_future_entries="yes" orderby="entry_id" sort="asc"}
    <tr>
    <td>{title}</td>
    <td>{entry_date format="%d %M %Y"}</td>
    <td>{order_customer_name}</td>
    <td>{order_customer_email}</td>
    <td>{order_customer_phone}</td>
    <td>
        {order_items}
        {item:quantity} x {item:title} @ {item:price} each<br />
        {/order_items}
    </td>
    <td>{order_coupon}</td>
    <td>{order_discount}</td>
    <td>{order_tax}</td>
    <td>{order_total}</td>
    <td>{order_notes}</td>
    <td>{order_full_billing_address}</td>
    <td>{order_full_shipping_address}</td>
    <td>{status}</td>
    <td>{order_transaction_id}</td>
    <td>{order_customer_ip}</td>
    <td>{order_error}</td>
    </tr>
    {/exp:weblog:entries}
    </tbody>
    </table>
<?php exit; ?> 

[ Edited: 01 August 2011 09:39 AM by Chris Newton ]

Signature

Pete Eveleigh | Fantastic Machine | Gloucester, UK | vCard | Twitter

Profile

Chris Newton

Posted: 06 May 2010 12:59 PM

[ Ignore ] [ # 1 ]

Administrator

Total Posts: 8342

Joined 2008-09-29

Wow, that’s pretty cool. People ask for this all the time. Heck, I could even use this.

Signature

NOTE: If I say “I will look into x” and you PM me information, please do not hesitate to contact me again about it, if I do not respond in several hours, or at most a day. Please feel free to remind me as you see fit

Profile

Pete Eveleigh

Posted: 06 May 2010 01:21 PM

[ Ignore ] [ # 2 ]

Administrator

Total Posts: 152

Joined 2010-01-19

That’s just a really basic implementation.

I guess if you used something like PHPExcel you could make the document much better, perhaps include charts etc.

I’m currently working on some “Store Reports” templates and plan to make use of Google charts etc. As I manage to get each bit done I’ll post them in here.

Signature

Pete Eveleigh | Fantastic Machine | Gloucester, UK | vCard | Twitter

Profile

shinemktg

Posted: 01 June 2010 08:26 AM

[ Ignore ] [ # 3 ]

Is a Really Great Dancer

Total Posts: 69

Joined 2010-04-20

Thanks for that Pete, I’ve just used it in a completely different site, it saved me a load of work in generating some essentially useless report screens.
Now I can just set some parameters like start and end dates and pump out the data in to excel which is what the client uses anyway.

One tip I just discovered, using the following line in the header:

header(“Content-type: application/octet-stream”);

my mac doesn’t recognise the document type in order to suggest an application to open it with, so I have to download, then open in excel

by changing it to the following

header(“Content-type: application/vnd.ms-excel”);

(beware the smart quotes if copying this)

I now get the option to open the document directly in Excel.

I don’t know if there’s an even better way that will make it recogniseable as something that could be opened in openoffice also, but I’ll post that if I find it.

Profile

Pete Eveleigh

Posted: 01 June 2010 08:33 AM

[ Ignore ] [ # 4 ]

Administrator

Total Posts: 152

Joined 2010-01-19

Good tip. I use a Mac myself but rarely open stuff directly in an application so I never noticed that!

Signature

Pete Eveleigh | Fantastic Machine | Gloucester, UK | vCard | Twitter

Profile

Chris Newton

Posted: 01 June 2010 02:34 PM

[ Ignore ] [ # 5 ]

Administrator

Total Posts: 8342

Joined 2008-09-29

Yeah, the application/octet-stream’s a real generic type.

Signature

Profile

larissa

Posted: 19 October 2010 08:09 PM

[ Ignore ] [ # 6 ]

Wallflower

Total Posts: 4

Joined 2010-05-04

Pete - this order report is really useful - thanks so much.

I implemented it, and also changed the header, per shinemktg’s suggestion, to have it work as an excel doc.

But I think I’m missing something obvious - I created it as a template in EE, and so can see the report as a rendered template. But how do you then take that rendered template, and turn it into a downloadable form, that can then be opened in excel?

I tried just copying the content from the browser and pasting into excel, but the table formatting was lost, for obvious reasons….

Profile

Pete Eveleigh

Posted: 20 October 2010 05:06 AM

[ Ignore ] [ # 7 ]

Administrator

Total Posts: 152

Joined 2010-01-19

If you enable PHP for that template then the header() function should cause it to be downloaded by your browser (or opened by a helper application).

Signature

Pete Eveleigh | Fantastic Machine | Gloucester, UK | vCard | Twitter

Profile

larissa

Posted: 20 October 2010 02:07 PM

[ Ignore ] [ # 8 ]

Wallflower

Total Posts: 4

Joined 2010-05-04

Got it! Thanks so much!!

Profile

MadeByMayo

Posted: 16 January 2011 08:20 PM

[ Ignore ] [ # 9 ]

Has a Nice Profile

Total Posts: 127

Joined 2010-05-24

WOW

This is brilliant. I’ve been asked about this from a few clients who want to use Cart Throb, but because i don’t have great PHP knowledge (im more front end) it’s pretty difficult for me to achieve stuff like this. Definetely going to be giving this a bash this week.

How easy would it be to make this dynamic? By having a page which allows you to choose/enter a date range, and it outputs the results as the excel? I’ve found out from a few shop owners / distributers that having this sort of feature on a shopping cms, is helpful to keep track of their income/profit.

Profile

Pete Eveleigh

Posted: 17 January 2011 07:11 AM

[ Ignore ] [ # 10 ]

Administrator

Total Posts: 152

Joined 2010-01-19

Wouldn’t be too difficult really.

You’d just need to create a form that gets the date range you want then feed the resulting POST variables into the weblog:entries tag

Create some kind of conditional that outputs the form if the POST variables aren’t present and the Excel file if they are.

Signature

Pete Eveleigh | Fantastic Machine | Gloucester, UK | vCard | Twitter

Profile

Rob Sanchez

Posted: 17 January 2011 11:46 AM

[ Ignore ] [ # 11 ]

Administrator

Total Posts: 3369

Joined 2009-05-29

Also, have a look at this: http://expressionengine.com/legacy_docs/modules/weblog/archive_month_links.html

This will print month-by-month links, you can use these urls to filter by month/date.

Profile

ccarter

Posted: 04 February 2011 02:03 AM

[ Ignore ] [ # 12 ]

Wallflower

Total Posts: 9

Joined 2010-06-29

This is a great solution…except that I can’t get it to work…
I have allowed PHP on both input or output but when I go to the template in the browser, the file I get is named orders.xls.html and doesn’t pull in the order information. It seems to place EE code in the cells.
Any thoughts?
Thanks.

Profile

Chris Newton

Posted: 04 February 2011 02:52 AM

[ Ignore ] [ # 13 ]

Administrator

Total Posts: 8342

Joined 2008-09-29

Make sure it outputs correctly for you without the PHP first.

Signature

Profile

ccarter

Posted: 04 February 2011 12:15 PM

[ Ignore ] [ # 14 ]

Wallflower

Total Posts: 9

Joined 2010-06-29

Thanks for that suggestion. I should have thought of that. But in fact, it doesn’t work without the php either and I can’t, for the life of me, see why! It’s weird, I can retrieve the order number and date and a few other fields but many of the fields come in as EE tags like {order_customer_name} and then some following will retrieve correctly. I can’t see the problem even without the php code in there. Here’s the code I used, maybe on of you kind people can see my mistake. Thanks.

<!DOCTYPE HTML>
<html lang="en">
<head>
<meta charset="utf-8" />
<title>Title</title>
<link rel="stylesheet" href="style.css" />
</head>
<body>
    <header>
        <nav>
        </nav>
    </header>
    <table border="1">
    <thead>
    <tr>
        <th>Order No.</th>
        <th>Date</th>
        <th>Customer name</th>
        <th>Customer email</th>
        <th>Customer phone</th>
        <th>Items</th>
        <th>Discount coupon</th>
        <th>Discount value</th>
        <th>Tax</th>
        <th>Total</th>
        <th>Notes</th>
        <th>Billing address</th>
        <th>Shipping address</th>
        <th>Status</th>
        <th>Transaction ID</th>
        <th>Customer IP</th>
        <th>Error message</th>
    </tr>
    </thead>
    <tbody>
    {exp:weblog:entries weblog="orders" status="not closed|closed" allow_future_entries="yes" orderby="entry_id" sort="asc"}
    <tr>
    <td>{title}</td>
    <td>{entry_date format="%d %M %Y"}</td>
    <td>{order_customer_name}</td>
    <td>{order_customer_email}</td>
    <td>{order_customer_phone}</td>
    <td>
        {order_items}
        {item:quantity} x {item:title} @ {item:price} each<br />
        {/order_items}
    </td>
    <td>{order_coupon}</td>
    <td>{order_discount}</td>
    <td>{order_tax}</td>
    <td>{order_total}</td>
    <td>{order_notes}</td>
    <td>{order_full_billing_address}</td>
    <td>{order_full_shipping_address}</td>
    <td>{status}</td>
    <td>{order_transaction_id}</td>
    <td>{order_customer_ip}</td>
    <td>{order_error}</td>
    </tr>
    {/exp:weblog:entries}
    </tbody>
    </table> 
    <footer>
</footer>
</body>
</html> 

Profile

Chris Newton

Posted: 04 February 2011 01:05 PM

[ Ignore ] [ # 15 ]

Administrator

Total Posts: 8342

Joined 2008-09-29

Do you actually have similar ee field names? Do you have a custom field in the order weblog with the shortname: order_customer_name?

Signature

Profile

1 of 2

‹‹ Sample Email Templates Ajax Add to Cart ››