Read Excel files in Perl and PHP

Relational databases that speak SQL are the data-storage backbone for most developers. Unfortunately, but most of the data that’s created outside the control of the technology caste at a typical workplace is in Excel format. Because of this, being able to procedurally read and write Excel documents with a familiar language can open up a whole world of possibilities for automation and data migration.

Assuming you’re attempting to read and write standard text (Ie. not binary/graphic) data from Excel worksheets, this is actually fairly doable in PHP and Perl.

A recent article by Mike Diehl at Linux Journal peaked my interest in this. He shows off some of the features of the Spreadsheet::ParseExcel Perl module, which can be used to pull data and even formatting information from cells in an Excel worksheet. Once you have your hands on the data, you can do what you want with it: output it to XML, toss it in a database for subsequent querying, or even convert it into other Excel documents (oh, the shame).

Perl Excel Libraries and Information
Spreadsheet:ParseExcel – Read from Excel 95/97/2000 documents
Spreadsheet:WriteExcel – Write to Excel 97/2000/2002/2003 documents
Linux Journal – Reading Native Excel Files in Perl

There are libraries for dealing with native Excel files in PHP as well. The following two seem to be the only options for binary Excel documents.

PHP Excel Libraries
PHP Excel_Reader – Read Excel 95 and 97 documents
Spreadsheet_Excel_Writer – Write Excel 5.0 documents
Reading and Writing Spreadsheets with PHP

With the most recent version of Excel, there is an XML file format option that will allow you to read and write data in a worksheet by directly interacting with the saved file’s DOM. IBM has a document that details doing this with PHP, and it would be straightforward to apply this technique to Perl as well.

Read/Write XML Excel Data in PHP

Finally, if all you need to do is output a document that can be read in Excel, a standard CSV-format file will usually do the trick. Escaping can be a bit tricky, however, and my preferred format has become a plain-old HTML table. Just create a file that contains a TABLE element (no BODY or HTML tags necessary), with any number of TR rows and html-escaped data in the TDs, and save it out. If you use the XLS file extension, it will open directly in Excel with a double-click and Excel never seems to mind reading in the data.

Do you have any other Excel programming hacks? Give us a shout in the comments.

Frank’s Workbench – Project of the Week

This week’s project comes from Frank (yep, the Woodcraft guy from Ep. 62). Let’s hear what he has to say:

Well, I finally completed my workbench. I would love to have a typical European style bench with a large twin screw vice on the side, but I have neither the space nor the funds. Furthermore, I needed a bench that I can easily move by myself, yet is strong enough to work with mortising chisels or hand planes. Also, because of my height, I prefer a bench that stands just a little higher than most.

This bench turned out to be a pretty good compromise. Its design revolves around the Zyliss vice and Veritas Bench Dogs / Wonder Dogs / Surface Clamp. My father purchased a Zyliss vice back in the 70’s and I have been very impressed with them ever since. New, they tend to be pretty expensive but if you keep your eyes open they are quite reasonable on eBay. I now have four of them.

The light wood is Alder and the darker is Eucalyptus. The Eucalyptus is not only beautiful but also tough as nails. I will say that if I were to do it again, I would replace the Alder with Hard Maple. The Alder is softer than I expected. I made the feet out of Ash to handle the abuse of being moved around. All of the joinery is either pairs of 10×50mm Dominos or Miller dowels. I finished it with Danish Oil and several coats of wax.

Episode 62- Gadget Station (Pt. 5)

Download Low Resolution
Download High Resolution

 

Road Trip! I was originally convinced that the Gadget Station would call for knife hinges. But just for fun I decided to take a trip to my local Woodcraft to see what they might suggest. Well, surprise surprise! I discovered a hinge that I had never noticed before. The Soss Invisible Hinge. I actually like this hinge better and I think it will suit this application perfectly. I hope you enjoy this little review of common hinges with our friend Frank Galloway, at the Chandler, AZ Woodcraft Store.

Links:

WOODSMITH: Adjustable Clamping Jig

Using heavy, bulky pipe or bar clamps to glue up small panels and assemblies is not a good match. You need a clamping method that’s a better fit to the job.

PhotoMy solution to this problem is the adjustable clamping jig you see in the photo above.The jig uses two parallel, pivoting arms to trap the workpieces. Clamping pressure is then applied by driving wedges between the arms and a pair of adjustable dowels. It’s both effective and very easy to use.

DrawingThe drawings at right and below show how the jig is constructed. The two arms are bolted into pivot holes near the outside edge of the 3/4? plywood base. An arc of 1/2?-dia. dowel holes radiates from each edge to provide the adjustability. Add a couple of dowels and a pair of wedges and you’re in business.

Drawing

Good woodworking,

Ted Raife
Online Editor, Woodsmith

Create Separate User Profiles in Google Chrome [How To]

The Digital Inspiration blog points out that new-browser-on-the-block Google Chrome shares one thing with Firefox 3 not mentioned in many press reports—a history-aware address bar, or "Awesome Bar," that can create some red-faced moments on a shared computer. If more than one person on your system is switching to Chrome, and you don't feel like staying in Incognito Mode all the time, creating a new profile in Chrome requires manually creating a folder in Chrome's application data folder, but that's all there is to it. Vista and XP users, hit the link below for help finding your Chrome profiles folder, and then creating a custom shortcut to launch your own Chrome without leaving tracks behind on others.

HOWTO – reset a lost Ubuntu password

I loaded one of my test Ubuntu virtual machines today (one that I hadn’t used for a month) and, surprise, I had forgotten the password. This sort of thing happens from time to time, and if you’re new to Linux, it can be a little disconcerting.

Loosing your root password isn’t the end of the world, though. You’ll just need to reboot into single user mode to reset it. Here’s how to do it on a typical Ubuntu machine with the GRUB bootloader:

Boot Linux into single-user mode

  1. Reboot the machine.
  2. Press the ESC key while GRUB is loading to enter the menu.
  3. If there is a ‘recovery mode’ option, select it and press ‘b’ to boot into single user mode.
  4. Otherwise, the default boot configuration should be selected. Press ‘e’ to edit it.
  5. Highlight the line that begins with ‘kernel’. Press ‘e’ again to edit this line.
  6. At the end of the line, add an additional parameter: ‘single’. Hit return to make the change and press ‘b’ to boot.

Change the admin password
The system should load into single user mode and you’ll be left at the command line automatically logged in as root. Type ‘passwd’ to change the root password or ‘passwd someuser’ to change the password for your “someuser” admin account.

Reboot
Once your done, give the three finger salute, or enter ‘reboot’ to restart into your machine’s normal configuration.

That’s all there is to it. Now just make sure to write your password down on a post-it and shove it somewhere safe like under your keyboard. :)

Marching Penguins: Monitoring Your HPC Cluster

Getting into Ganglia for a scalable and flexible solution to the problem of cluster monitoring.

Copy a Single File to Avoid Re-Activating Windows XP [Tutorial]

The Online Tech Tips site offers up a great tip for anyone reinstalling XP off something other than their original CD—such as a slipstreamed and automated installation—or lacking a net connection to run the activation. Before wiping your system clean, grab a file named WPA.DBL from the System32 directory and save it to a thumb drive or other external media. When you load up your new system, skip registration, enter “Safe Mode” from the boot menu, and drop it back into that System32 folder. Now you’re re-activated and free of nagging. Hit the link below for detailed explanation of each step.


ShopNotes: A smooth finish

I like to use MDF to make shop projects. It’s flat, smooth, and inexpensive. To give the MDF some protection and dress up its bland appearance, I usually paint it. Here are a few tips for getting the best results.

Continue reading “SHOPNOTES: A smooth finish”

Startup Delayer Staggers Your Startup Apps for Smoother Loading [Featured Windows Download]

startup-delayer.pngWindows only: Free application Startup Delayer staggers the applications that launch when you log in to Windows by user-defined increments. The reason: To mitigate the common startup bottleneck caused by all of your startup applications fighting to run at the same time. You’d never try launching eight different applications simultaneously under normal circumstances and expect your computer to handle it well, so why should your startup apps be any different? To use it, just drag applications to the delay bar at the bottom of the window. You can visualize the time between the launch of different apps and drag-and-drop the delays until you’ve got the perfect spacing.

Though you’ll likely be delaying app launches by seconds, you can delay a launch for up to 24 hours (though we’re not sure why you would). If Startup Delayer sounds familiar, that’s probably because we featured an identically named application a while back. This Startup Delayer, however, makes the process much simpler and more manageable. Startup Delayer is freeware, Windows only.

WP Like Button Plugin by Free WordPress Templates