For those PHPers out there who are doing an export to Excel, I thought I'd share the solution I came up with. I realize there are already a gabillion examples out there, but I merged some of the better approaches from a few of them that made it fairly elegant, I think (such as leveraging the implode, array_keys, and array_values functions).

Without further adieux...

Alright boys and girls, here is an issue and its solution that had me going NUTS for a couple of hours. I actually wrote and implemented three DIFFERENT solutions trying to find a way around the problem, only to find out in the end that I kept carrying the true issue with me.

The Scenario

My user clicks a link and a window pops up allowing them to save or open some exported data as an Excel spreadsheet. Of course, it all worked fine on my machine (two of them, actually), but when my boss tried it on his, he got an error. Not cool. Back to the drawing board.

The Challenge

My first attempt (which worked fine for me) was to create a true excel spreadsheet by streaming the data in binary to a file (it's a bit complicated, but cool). Excel 2007 was able to open it just fine, but my boss' older 2003 could not. So, I decided I'd just fall back on the old reliable CSV type file rather than try and troubleshoot the first approach in too much depth. I coded my CSV export and for the life of me could NOT get my Excel to open it without first flashing me with two nasty error messages, paraphrased here:

"The file you are trying to open '[file name here]' is in a different format than specified by the file extension."

"Excel has detected that '[filename here]' is a SYLK file, but cannot load it."

What the heck??? All we're talking about is some text separated by a comma. And Excel doesn't recognize the format??? Content headers are right; file extension is right; Open it up in a text editor and it sure looks fine to me; What's going on?

I reworked the code to use tabs as delimiters; same symptoms.

The Solution

After dicking with it for a couple of hours, comparing known working CSVs with the one I generated, comparing non printable character strings such as line feeds (which is what I suspected the issue to possibly be), I finally figured out that the TRUE problem was (and I grit my teeth in frustration when saying this): the name of the first column in my data.

NEVER allow the first column in your data to be called "ID". AUGH!!!! (had to scream). Apparently, using "ID" is a trigger to Excel that your file contains a symbolic link to another spreadsheet somewhere. I know, I know, for every OTHER type of file that Excel opens it relies on the file's EXTENSION to determine the type; but not in this case.

Moral of the story: NEVER allow the first column in your CSV data to be called "ID".

Lesson learned, I'm now one small step closer to Nirvana.

okay, I had a major wrestling match today when attempting to allow my users to automatically save or download a dynamically generated Excel spreadsheet, so thought I'd share the details in case it helps someone else save some hair pulling.

The Scenario

You have a page with a link on it. Clicking this link produces a popup window, the code behind it querying a database, writing an excel file to disk, setting the content headers, and then prompting the user to open or save the file.

The Symptoms

In IE (i'm using version 8), your user clicks the link to "Export Data", popup window appears, and then promptly closes without so much as a "how do you do". (in other browsers, such as Chrome and Firefox, everything behaves just like it should; user clicks link, popup appears, user is immediately prompted to either open or save the file)

The Solution

After trying all MANNER of values for headers, it turns out the magic combo for making it work (in my case, anyway) had two parts to it:

1. DO cache the content. This is accomplished by making sure you DO NOT have a "Pragma: no-cache" header, and including a header like this: Expires: Thu, 01 Dec 2010 16:00:00 GMT (or any other future date...perhaps you could automatically calculate it to be one minute in the future or something). Seems that IE, if you have said NOT to cache content, can't find the file by the time it gets to the dialog because it already uncached it. Strange behavior, but true.

2. Check your browser settings to ensure that you have "Automatic prompting for file downloads" ENABLED. By default it is DISABLED. Here's a screenshot of that setting to help you locate it:

After I did those two things, my download link behaved as it did in the other browsers I tested.

Just for clarification, here are the headers and values I ended up with (this is PHP code, so just deal with it):

My wife and I have recently been involved in the search for and procurement of a new home. It started one Saturday this past April when we were driving down the hiway and on a whim decided to stop in at the Centex office in a newly developed subdivision, just to "check it out". By Sunday afternoon we had already selected the model and all of the upgrades for our new home, and were embarked upon the journey of having a house built from the ground up.

It's now nearly July. The house is framed, roofed, wired, plumbed, and is on the verge of having the sheet rock installed. The whole experience thus far has had its ups and downs, but the majority have all been "ups", and I attribute this to two people: my realtor (Terri Russo), and the sales person for Centex (Linda Wilkins).

My wife and I didn't know it, but when you are having a home built, you can and should still have a realtor to represent you during all phases. The initial deterrent to doing so is the thought of adding an extra expense to a probably already tight budget, but (at least with Centex), the realtor's fees are paid by the builder! So with that in mind, I called up a long time friend Terri Russo, a realtor with Russo Group here in San Antonio. Terri is a woman of infinite energy and charisma, with such a thorough knowledge of the home buying/building process that she can (and will) easily translate it into terms that a new home buyer can comprehend. She has accompanied my wife and I on almost every meeting we've had with the builder and has been invaluable when it comes to ensuring that no detail is overlooked or goes unexplained. She always answers her phone (even when on vacation visiting family in Chicago), proactively communicates, and is an invaluable resource when it comes to answering questions like, "Hey, know anybody who does new construction inspections? Tiling? Sells appliances? Gives a good massage?" Terri Russo knows at least one person in nearly every profession there is, I'd say, and will never steer you wrong.

Now let me talk about our Centex sales rep, Linda Wilkins. Linda is THE sweetest woman I've ever met (next to my grandma ). She was recommended to me by a co worker who also had a Centex home built, and from the first time I spoke with her on the phone I was immediately set at ease by her gentle voice and absolute desire to simply help us fulfill our home needs. Not for one single moment in all the months now that we have been working with her have I ever felt like I was talking to a sales person. In fact, I would say that the title doesn't even fit her, as she doesn't "sell" anything; she merely educates you on what your options are and then lets you decide. She, like Terri Russo, is always responsive, caring, empathizing, and leaves no doubt in your mind that your concerns are just as much her own. My wife and I...well, I will admit that I think we have been a wee bit more challenging to work with than probably most new home buyers. But even when we were in her office expressing (in a not so controlled manner) an issue we had regarding the trees in our new yard, she never once reacted negatively and only made it a point to relate to our point of view and then get us a speedy and acceptable resolution. Linda Wilkins, you rock! You can contact Linda on her cell at (210) 336-4522 or by email at linda.wilkins@centex.com .

At this point, I'd like to interject just a few things my wife and I have learned that I feel may be useful to anybody reading this who is considering buying a new home.

Buying a home and moving to a new location is a major event, tantamount to giving birth to a new baby (though hopefully you won't require Demerol to get through this process). So, celebrate it as a family before, during, and after! And, if you happen to be wanting to buy a new home in the San Antonio area, you should definitely look up my friends Terri Russo and Linda Wilkins. They will not steer you wrong.

Doug out.

Continue following our new home saga! "Closing Day" post here...

Yesterday I had the privilege of providing my first consultation job as an instructor on the subject of Coldbox. The team that hired me are at the stage in their project where they're ready to start writing code (database is designed, UML and usecase diagrams are complete, mockups have been created), and they wanted to "experience" my thought processes, the questions I ask myself, my approaches to implementing functionality, and how I troubleshoot as I make my way from an idea to a working app in Coldbox. So, we spent the entire day together building an app from scratch, using Coldbox.

In preparation for the task, I spent the past week investing a LOT of time re-reading the Coldbox docs, experimenting, building out a small sample application that the team and I scoped out beforehand, and making notes about my thought/decision-making processes along the way.

Taking notes about your own personal development process is actually quite interesting and insightful, and since my audience found it useful and enlightening, I thought I'd share a high level view of it with the rest of the community as well. What follows is a bullet point list of how I personally generally go from idea to app.

Let's take a walk in my head, shall we? Don't be afraid....

1. let's get the current version of the coldbox framework (www.coldboxframework.com)

6. Okay, now I'll take a first pass through my config.xml.cfm file and alter the settings I know about at this point.

7. At this point, I feel the need to go ahead and create my database table structure and get that to a solid starting point.

8. Let's tweek the default layout and make placeholders for the different components it must organize (header, footer, nav, content, messages, dynamic content area, etc.).

In a nutshell, this is my general thought process as I build an app, with emphasis on building a Coldbox app. How does my "to do" list match up with everybody else's? I'd be interested to know!