PHP Excel Exporter
A few times a year a client needs to export something from a database table to Excel. There's a simple hack to do it in most any language. There are actually a few, but having come up as a web developer, my preferred trick is to just build an HTML table and serve it as Excel by setting the mime type header. Having done this dozens of times, I finally formalized this into a simple PHP class tonight to save myself some time and figured I might as well share it.
The bad news: because I am lazy, it relies on an old data connection class I wrote years ago when I was even less bright than I am now. The thing's so ugly I posted it somewhere else because I am too ashamed to host it here. You can rip that out and use whatever you prefer by just changing the logic in _get_table() below. If you do choose to use my old data-class.php, be aware it expects 4 constants, DB_SERVER, DB_USER, DB_USER_PASS, DB_NAME to create a connection to the database.
Here's the exporter code itself Update: I moved the code to snipplr because this Wordpress plugin doesn't handle newline characters very well.
The simplest use is to instantiate an object, tell the exporter what you want to appear in the header row in the spreadsheet (by setting column_heads to an array of values) and then calling export(), passing it the SQL query that gets the data. If the number of fields in your query doesn't match the number of heads in column_heads, the resulting HTML will be a mess. You will understand if the code assumes you never make such mistakes. Here's a code example:
$e = new ExcelExporter(); $e->column_heads = array("First Name", "Last Name"); echo $e->export("SELECT first_name, last_name FROM table");
Quick notes:
- Control the Excel filename in my example by setting $e->filename("something-else.xls")
- Add a timestamp to every file (useful for making sure the filename is always unique) by setting $e->timestamp_file = true
- When you're trying to implement this and it's not working and having to say yes to the popup and let the file open in Excel is driving you crazy, set $e->debug = true and it will skip the Excel headers, sending the output to the browser
The big gotcha that works well for me but might not for you: there's a hook in the code that passes every data column through _format_field(). In my current class, this looks for any field with "_date" in the column name, assumes that field is a Unix timestamp and transforms the value into a m/d/y date. If you live in the other 99% of the world where people format their dates un-Americanly, well, you can do that like this: $e->date_format("d/m/y") or whatever other crazy date/ time format you like.
If you think that behavior stinks, rip it out. Alternatively, you can modify it or subclass this code (like "client-xyz-exporter extends ExcelExporter" for every client who lives in Excel) and change _format_field() to do whatever you want in a one-off sort of way. This is not high art, it's just a faster way of making someone happy (if you can imagine the kind of person whose life is improved by additional spreadsheets).
Windows GPG Front-End
I'm doing some work with GPG encryption and I always like to have a visual/ gui front-end to use to make sure I haven't screwed something up in my command line adventures. I came across Cryptophane today and it seems like a nice way to keep track of my particular Alice and Bob. The only problem I ran across was that my GPG install was in a non-standard place and Cryptophane doesn't look in the registry (I'm pretty sure GPG writes to it). The error wasn't immediately clear and there's no online help (though a .chm is provided), so I thought I'd post this for anyone else who runs into a similar problem. My shortcut target now looks like this:
"G:\Program Files\Cryptophane\Cryptophane.exe" --gpg-path "G:\Program Files\GNU\GnuPG\gpg.exe"
Your paths may vary, etc.
Morning Stress
I want English Muffin insurance, for those times when you tear the thing all wrong and one side is basically not there and the thing toasts completely unevenly because of the difference in girth. And the pressure's on then, because the damn things come in six-packs, which makes no sense. It doesn't correlate with anything: there are five weekdays, seven days in a week if you're eating them every day (and it's 10/14 if there's two of you in the house). Every once in a while I'll come upon a four-pack and think, "Two of those might work better", but then I get up close and realize it's one of those massive "SANDWICH SIZE!" affairs and I can't do it. Sure I've eaten sandwiches made from English Muffins, even hamburgers when we ran out of buns, but there's something depressing about intentionally buying those. And if you try to eat one for breakfast, you're going to feel like a real fatty. So that doubles the pressure and I wind up in front of the toaster like some bomb disposal technician whose decided on which wire to cut and now it's just a question of death or glory. This is why there are Eggos.
Nothing’s Coming Out!
You want to know why recycling isn't keeping pace, why the universe is going to suffer heat death and run out of energy a few billion years from now? I just opened a new container of bay leaves-- if you don't know what those are, they're pretty much what you thought they were. The salient point is their size; go ahead and look, I'll wait.
The container had a shaker top on it. You know, the kind of thing you'd expect if the contents were ground up seeds and not say, vegetation off a tree. So I have to break a goddamn thumbnail because some halfwit in Sandusky, Ohio can't figure out whether or not to slap a shaker lid (with two options, not that either one would work) on top without calling someone back at HQ. Can't you see him starting to take out that 25' yellow Stanley tape he's carried around all his life for no good reason, thinking, "This is finally it" when he realizes everyone's staring a little more than normal? Isn't there something on the shop wall next to the OSHA posters, some suggested guideline on when not to include the shaker cap? "If it's bigger than the tip of your pinkie," with a red crossout circle.
Of course not, because the job's long since been turned over to the one reliable employee, some robot who happily slaps caps all day. When Skylab comes on line and the Terminators get rid of us, it won't be because of world wars or violence or inhumanity, it'll be because some computer figured out it just spent the last 10 years making the world worse off.
Anatomy of a Late Night
Posted by Tom in Entertainment, Movies, Music, Personal on January 10th, 2009
New Year's Eve has totally thrown us off schedule. For some time (the formula for which is: NOW - # of years we've had a dog) we've been consistently in bed by 11, 12 on weekend nights, with 1am being a notably late night. Head hit the pillow at 3 again last night and I'm delineating Why for myself so I can find the error
- 8pm: Watched Tropic Thunder which turned out to be exactly what I thought it was, a decent three-and-a-half star comedy, something that's not so easy to find nowadays. Perhaps the comedy palate has grown more sophisticated since the days of Meatballs, though the trailer we caught beforehand, Van Wilder: Freshman Year, suggests otherwise. Amazing the amount of work shitty comedy writers ("And then her tits pop out!") can still get while more talented writers ("It was a surprise for all when her breasts sprang free of the bespoke bustier she was wearing.") go hungry. Movie opens well with fake trailers, then nose dives for a bit until it sets up the story. Would be worth sitting through for Robert Downey, Jr. alone. Tom Cruise was better than I want to admit.
- 10pm: Rock Band. As my poor X-Box can attest, I'd jumped off the Rock Band train last fall. I'd gotten good enough to play some on Expert but was still way overmatched by certain songs on Hard (and always songs like "Foreplay/ Longtime" that take half a goddamn hour to point out you're going to fail for the fiftieth time, never some Ramones ditty that's over in 1:58). A light clicked on and I realized "playing" a "game" doesn't involve nearly popping a vessel in your head and throwing things after the age of 5 or so. I was fine with no more Rock Band, there was no hole in my life.
Then I heard The Gaslight Anthem's new album. After playing it non-stop for a week, the damn drummer(1) hooked me back into Rock Band. Thankfully things have gone a lot better since. Even managed to skid through "Don't Fear the Reaper" last night, failing just late enough to make it to the end. - 1am: Bill Burr, Why Do I Do This? via Netflix. Discovered him last week on Comedy Central, causing another late night. The set was so good I was ready to sit through the repeat airing until smarter heads prevailed. I've been obsessivley listening to his old podcast episodes this week. Can't believe he can spitball an hour worth of funny on a weekly basis.
- 2:30am: This is where things really fell off the track. It started to go wrong in the 1am hour when we re-watched something we'd seen a week before and wound up cracking the emergency PBR 12 pack in the frige, but I could have just shut down the X-Box, finished the beer and gone to bed. Instead I have to page through the Netflix Instant Queue, even though if anything in there was worthy of watching at 3am I'd have watched it already. Nope, a half hour later it's 3am, we're only halfway through the pilot episode of Macgyver, the beer's all gone and I realize the level of tension wasn't high enough to keep anyone but me awake. The possible rocket explosion will have to wait until we have more beer. Actually, to honor the spirit of the show, I should really brew some Pruno in the toilet. How Richard Dean Anderson isn't starring in anything more than my dreams nowadays is a mystery.
Postscript, 8am: dog gets up in bed, throws an absolute shitfit, leaving me wide awake with the better part of 12 beers still coursing through my veins. I think it's some sort of Temperance thing with her, trying to get us off the Devil's Brew.
1. Brush with fame: the drummer went to elementary school with a friend of mine. The amazing part is my friend went to elementary school in New Jersey and still wound up bright.
“Life’s Good”, Indeed
I am providing this post-mortem both as a public service and so I don't punch in the screen of my brand-new LG monitor. I picked up a new 22" monitor at Best Buy yesterday after losing a monitor to the power surges from the ice storm (what I ought to have done is replace the APC unit that's now cost me an external drive enclosure and a monitor, but that's another gripe). Almost 24 hours later, the monitor is actually running as expected. Let me preface this by saying I appreciate my setup might be a little different from what the folks at LG bothered to test on:
- Mac hardware running Windows
- it's a second monitor
- NVidia and UltraMon are both fighting for control of the setup
All the same, this was a more painful hardware process than I remember going through. It's a goddamn display, not an ultrasound machine. Nothing should be more plug-and-play. And yet, when I plugged it all in with the existing VGA connection from the old monitor, none of the native resolutions were available. I installed the software and drivers, but it still couldn't figure itself out. The LG software could identify my primary monitor, but the software would not allow me to use any of the features because it could only run on an LG device. Even when it figured out there was an LG display somewhere, no dice. I'm assuming it has to be the primary display. Also, for a company that's done such a good job of becoming an international player, it's disappointing the English was as broken as the software displaying it.
I gave up on LG and updated the NVidia Control Panel, hoping newer versions would have the widescreen resolutions. Playing with the new control panel only blew things up worse: reversed the primary and the secondary and then screwed up Ultramon so badly the taskbar was displaying a box for every background process running on the box under my user account. It also saw fit to snap the resolution on both monitors to something fun. So, reboot.
My last gasp, and it included a fair bit of gasping as I've been screwing off work due to a wrenched back, was to find a DVI cable and try that. Nothing. No second monitor, no avowed knowledge of a second monitor in the control panel. For no good reason except I'm my own IT department, rebooted. Success! Sort of. Both monitors are working during bootup, both are correctly identified in the control panel, both have their proper native resolutions dialed in and programs are being popped up on the LG. Just one issue: it's power light is in orange standby mode, the screen is off and no amount of pressing the power button will convince it to turn on or off. This is when one has to say, "I can tell a convincing lie to the returns desk if need be" and show inanimate objects just who the hell runs the show around here. Out comes the power cable. The funny little capacitor manages to keep the light orange for five brave seconds before giving up the ghost. I yell, "Clear", jam the power cable back in as painfully as possible and here we are, all of us with a better understanding of the pecking order. And then I removed the startup item LG installed ("forte display manager") to keep it from doing it's infinite loop dance with NVidia for control of the display.
So, the public service announcement: if you get a new wide-screen LG monitor, get a DVI cable with it. Hook them up. Don't install the LG software (though you'll most likely need the drivers). I suppose if you just have one monitor, you'll be fine either way, but you'll only find this post if something goes wrong.
Xmas Flick Tradition Continues for Hicks in Stix
Posted by Tom in Uncategorized on December 4th, 2008
I bought myself a couple of early Xmas/ Birthday presents this week, Going My Way and The Bells of St. Mary's. I bought those two old weepers in order to maintain the family tradition (my mother's) of watching at least TBoSM every Christmas. It won't be the same without her (and I'll probably fall apart when that dumb little kid calls Bing "faddah"), but it'll be something.
I would tell you neither movie is an Oscar winner, but Going My Way took home 7 (!), including Best Picture. Cinema has come a long way in the interim: there's more dramatic tension reading the phone book ("Will the Zs really make it at the end?"). The movies exist as frames for musical numbers, a bit of feel-good holiday cheer and not much else. All the same, I will assert (based on nothing more than hope) there are worse ways to spend 2 hours in front of the TV at Christmas.
I've seen both enough they run together, so much so I was surprised to see Barry Fitzgerald isn't in the sequel, The Bells of St. Mary's. It's a solo tour de force for Bing Crosby's Super Priest, who could kick the ass out of Ayn Rand's Architect and bed any woman he wanted, except he's so cool he's into the absitence thing decades before it became cool. The film overcomes two glaring issues:
- Allowing Bing Crosby anywhere near other people's children
- Employing Ingrid Bergman's nun as an educator, given the church could theoretically reassign her to a high school class full of pubescent males
Aside #1
To emphasize what an issue #2 is, I present a full list of all the women in the world my father ever suggested were attractive
- My mother (1,000,006 times)
- Ingrid Bergman (1 time)
. . . to get you to a conclusion that might as well appear in the opening credits. SPOILER ALERT: The school is saved! Like any holiday movie, it's not about the story but about the season and some feeling of continuity in life. Much as I'm making fun of the film, it'll be a mess 'round here when they get to singing "The Bells of St. Mary's": the last time we heard it, it was being sung by the girls' choir from St. Mary's Bay View at the funeral.
Like any good Spoil Yourself purchase on Amazon, I wound up with more than just what I set out to buy, adding a 3rd movie we used to watch together, The Grapes of Wrath after running across a post on the New York Times,
It was the kind of movie we'd watch if it was on TV on a Sunday afternoon when there was ironing to be done. The populism and underdog-nature of the story appealed to my mom, but we knew what really got her was the mom. She saw her own mother in her and, of course, I see mine (don't think the "Oh, Tom!" doesn't catch my ear). The final, famous scene ("Where ever there's a fight . . . ") always resonates. When I was young, close to my parents and just wanting to stay home, egotism made it easy to see myself as Heroic Tom Joad, leaving family and friends, purposely striding out the door to make the world A Better Place. Now that the roles are reversed, that I'm home and my mom is gone forever, the scene reminds me she's not exactly gone. She might not show up if you're getting trounced by a cop, but she's there in my relationship with Michelle, she's there in anything I do just for someone else, she's there in just about anything I do right. The idea that time is a coping mechanism, a way of perceiving ourselves in the physical world, it'd be nice to think you could step outside, take a hard right and see everyone that's left behind.
Aside #2
After a dozen viewings of Going My Way (and having seen The Quiet Man), it was disconcerting to run across Barry Fitzgerald as a bad guy in The Sea Wolf. I conveniently came across it one Saturday night on PBS and watched because Jack London's book had just been assigned in class. It was even worse than the time I saw Harry Morgan as a low-down, dirty ranchhand in Bend of the River; at least by that point I knew he was the kind of guy that would push his wife down a flight of stairs.
You can spare me the emails, I'm well aware (old movie on PBS + in high school + Saturday night) = LOSER.
Making .NET 2.0 Spit JSON
I just finished translating a set of PHP web services into C# for a national car insurer and came across two things worth mentioning, if only for my future reference.
Each service needed an XML and a JSON version. I knew I didn't want to write code to hand-roll duplicate sets of responses, but I also didn't think it was worth knocking up a formatter class for two response formats. I created the XML responses and decided I'd transform them to JSON via XSL. Just before I got started on that, I remembered anything halfway smart I think of has already been done. Thus, xml2json.
Sinfully proud of myself, I wired up the JSON responses and pointed the pre-existing test site at my new services. And: nothing. Knowing I'm perfect, I fiddled with the site's JavaScript for an embarrassingly long time before looking at the output of my JSON services. I'd created a bunch of web services that returned a string type and the JSON looked gorgeous in the unit tests, so where was the problem? Oh yeah:
<string>{json:"help, i'm trapped in here"}</string>
There's a lot I love about .NET's web services, mainly how the busywork is taken care of behind the scenes. Except there's no easy way to get back there and change the plumbing (to mix a metaphor) so it spits out strings instead of XML. .NET 3.5 has native JSON serialization, but this had to be 2.0-compatible. I found a number of possible solutions, most of which relied on the Ajax toolkit. Having worked with it, I have two objections to the toolkit:
- It's really heavy, so you'd better need it
- It feels like black magic: include the proper fake files, say the incantations just right, things may work
The toolkit was too much of an elephant gun for this ant. But the alternative was to create my own custom response handler, which felt both egotistical and like a good way to make a mess. While weighing the various options, all of them ugly, it hit me:
[WebMethod] public void MyJSONService() { Context.Response.Write(myJsonString); Context.Response.Flush(); Context.Response.End(); }
A hack can be something that works even though you don't understand it, something you do because you're too lazy to do it correctly or something less than beautiful that solves the problem with a minimum of fuss. I'd like to think this was a #3. It's not ideal and it's not a good solution for a large system, but if you're just trying to get .NET 2.0 to send back strings without it wrapping everything in an XML safety envelope, this works without requiring two tons of library or chicken blood + a full moon.
Twitter Updates for 2008-10-20
- Earplugs didn't prevent much. #redsox #
- Hate when home plate umps make a strike call on a close checked swing. Can't we get the 2nd opinion? #redsox #
- Found my enemy for the night: overly-demonstrative older woman, front row, just up the 3rd base line (over RH batter's shoulder) #redsox #
- McCain's ads make me feel like I missed an episode. Seem to have been edited with a blender. #
- @ericwyman She's right above the New Era sign with a Petula Clark bob, between two old guys. #
- Why would Varitek need to tell the catching coach what he's going to do? #redsox #
- @ericwyman good to hear. Make up for lost torrent time. #
- Carl Crawford comes out to MIA? 1. Sounds anti-Amerikun to me. 2. Good luck signing a guy who's theme music says he just wants your money. #
- @savetherobot Heart of Dorkness #
- Need to know what it says on Papi's non-standard, non-team issued shirt. Something dirty, no doubt. #redsox #
- Buck Martinez's Can't-catch-up-to-a-sliding-runner theory sounded like it was a new Law of Thermodynamics. #
- Cialis, if your boner pills are so great, why do they require two soaking tubs? #
- @mikesusz I'd have thought the roof, Field Turf and different colored walls would have been a tip off. And the empty lounge chairs. #
- Dunno, how big are 2 out hits? There are 0 outs right now. #redsox #
- @ericwyman looked like a Very Special Fan. #
- TBS, you claim Frank Caliendo makes a big impression, I'd like to prove it. Have him meet me on top of the Sears Tower. #
- "Hi, this is Matt Garza for Mucinex." #
- Anyone else catching a romantic subtext to Ron & Buck's discussion of pitchers & catchers? #redsox #
- @paulkelley the caught stealing? 3-2 and 1 out is a hit and run situation. Sucks they get the momentum from it. #
- If that was an "easy" 1-2-3 inning, I don't want to see a hard one. Too much solid contact. #
- @paulkelley yeah, it was definitely a risky move, but Tito seems to send the runner in that situation about 80% of the time. Sucked. #
- PINCH HIT #redsox #
- Dog's snoring soundly. Don't know how she does it. Nerves of steel. #redsox #
- @ericwyman Thank you. Get that kid to an ENT, stat. #garzasnot #
- Someone in the front row order something to make those weird-ass munchkin waitresses go away #redsox #
- Now is that Animal or Hawk wearing the sweatshirt in the Tampa dugout? #redsox #
- @ericwyman How quick does Price go in our draft next year? #
- Ah well, great season, fun playoffs and Tampa's a damn good team. Could do without the fanbase. #redsox #
- What is that song? Where can I pay to have it not played? #redsox #
- @CinemaSuicide Uf, thanks for giving me someone to block. #
- Please tell the dog I don't negotiate with terrorists. #
- Good post on the value of defense and the price of wins in MLB: http://is.gd/4qPK #
- @adarowski @SoxyLady I was sorry to hear it too, but I think it's only "out of character" for the person he's portrayed as in the media. #
Powered by Twitter Tools.