Menu

Colin Devroe

Reverse Engineer. Blogger.

Building a single page web site using an Excel spreadsheet

Need something like this? Consider hiring me for your next project.

I was recently asked by a client if I could use an existing Excel spreadsheet and create a single-page web site from it. This way, their team would be able to use the work they’ve already done and the data they’ve already keyed in and wouldn’t need to do double the work.

I could have built an data import tool that used the Excel data and reformatted it for WordPress or another content management system, but after asking a few questions I figured the simplest and most cost-effect approach was to use the Excel spreadsheet as the datastore.

It sounded fun. So I gave it a shot.

Here is what I came up with. It is a bit simple, arguably a bit crude, and wouldn’t work for anything terribly complex or for a high-traffic site, but, it works for them and that is all that matters.

Let’s start out with a simple example spreadsheet. Let’s say you have the following data for your soccer team in Excel.

Excel Soccer Team Example

In this case you have four columns; Name, Age, Jersey Number, Goals. How can you get those to display in a web page? Using PHPExcel, which I’ve used on several client projects, you can read from and write to an Excel spreadsheet fairly easily using PHP. Using the above data as an example, the code might look like this.

// Load PHPExcel, then load Excel spreadsheet
include 'lib/PHPExcel/IOFactory.php';
$objPHPExcel = PHPExcel_IOFactory::load($file);

// Read the Sheet data, create Array
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);

$players = array(); // Array used to store player information

// Go through each row one-by-one
foreach( $sheetData as $row ) :

  // If Column A is not empty or null, or "Name" (this will avoid having a player with column names)
  if ( !empty($row[A]) && $row[A] != 'NULL' && $row[A] != 'Name' && $row[A] != '' ) :
    $players[] = array( 'name' => $row[A], 'age' => $row[B], 'jersey_number' => $row[C], 'goals' => $row[D] );
  endif;

endforeach;

Let’s walk through this code quickly. First, we load the PHPExcel IOFactory, which lets us read from Excel. Then we load the Excel file. $file, in this case, would be the full path to the Excel spreadsheet you’d like to load. Then, we create an array out of each row of data in the sheet. Once we have that, we simply loop through each row and create a “Player” out of each one.

You’ll notice the way I avoided adding a Player with the column names is to check to see if $row[A] == "Name". You could also skip the first row depending on where in the spreadsheet your soccer team’s information is. But this is just an example.

Now that you have this Player array, you could use this information anywhere on your page by creating a simple for loop to go through them and write the list of player and their information.

Let me give one more example. This represents what my client needed much more closely.

What if you had variable names in Column A and wanted the values of those variables to come from Column B? Here is an example spreadsheet where this might apply.

Excel spreadsheet example profile

Here is what the code may look like for this…

// Load PHPExcel, then load Excel spreadsheet
include 'lib/PHPExcel/IOFactory.php';
$objPHPExcel = PHPExcel_IOFactory::load($file);

// Read the Sheet data, create Array
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);

$person = array(); // Array used to store person information

// Go through each row one-by-one
foreach( $sheetData as $row ) :

  // If Column A is not empty or null
  if ( !empty($row[A]) && $row[A] != 'NULL' && $row[A] != '' ) :

    // Remove spaces in Column A to use as array key and value being Column B
    // Example A:'Person Country' B:'USA' becomes $person[person_country]='USA';
    if ( !empty($row[B]) && $row[B] != 'NULL' && $row[B] != '' ) :
      $person[str_replace( ' ', '_', strtolower(trim($row[A])) )] = trim($row[B]);
    endif;
    
  endif;

endforeach;

The subtle differences here, from the example above, is that $row[A] becomes the array key, and $row[B] becomes the value. By doing this, the person’s profile information is now easily printed anywhere on the page using <?php echo $person['age']; ?>

This simple Excel-powered approach becomes very powerful for someone with no web language skills. They can open an Excel spreadsheet, edit the values, and the web page changes.

Need something like this? Consider hiring me for your next project.

GoPro open sources Camera Toolkit

GoPro just updated their iOS app. In the release notes I noticed a link to their developer page wherein they’ve open sourced their Camera Toolkit for iOS and Android. Let’s hope a ton of bug fixes result so I can stop hacking mine.

Three Toe: A simple SMS autoresponder on top of the Twilio API

If you find yourself needing something like this, why not hire me to make it for you?

A friend of mine somewhat recently became a licensed realtor. One thing that surprised him was the lack of technology being used in his workplace. Though not a technologist himself, he enjoys an efficient workflow such as using a tablet and managing documents digitally. So whenever he’s asked to complete a transaction using piles of paper and copy machines and so forth, it obviously gets under his skin.

For the last several months we’ve been trying to find ways that I can use my various talents (finite though they may be) to make his work easier or even improve the chances he’ll be successful in a sale. We’ve had a few conversations and personally I’ve been brainstorming and keeping notes and scratching out ideas.

Some of the ideas I’ve had are more expensive and involved while others are more simple and can be done inexpensively. One more expensive and involved idea is shooting drone footage of lakeside homes to help differentiate the listing. I hope to get a chance to shoot some drone footage soon. (hire me?)

One idea that, thanks to Twilio, is very easy and inexpensive to do is writing a SMS autoresponder to send property information to a would-be-buyer. And since I’ve wanted an excuse to play with Twilio this was my chance.

Here is the idea; A buyer pulls up in front of a house that they may be interested in. In the front yard, the listing agency’s sign would have a phone number that the buyer could send a code to and get information about the property.

It turns out that writing this on top of the Twilio API is very simple. (Coincidentally they IPO’d yesterday, their stock spiked 90%, and they wrote code live from the NYSE trading floor. Pretty good day for them yesterday.)

Here is what you need:

  • A Twilio account (you can use a free trial account to get started)
  • A phone number (you can purchase one from Twilio for $1/month or a short code for $1,000/month)
  • A server to host Three Toe

Let me just state right up front; using Twilio will cost money but it is very inexpensive. You get charged for each message received and each message sent. So in our example of a buyer texting a code and getting a response that is 1 round trip that costs just about a penny and a half (at current prices). A penny to send a home buyer a link to a beautiful web site of the home they want is a no-brainer.

Here is how Twilio works:

  1. Potential buyer sends a text message to your Twilio-powered number
  2. Twilio receives that text, creates a “POST” with general information about the sender (phone number, location, and contents of message, etc.)
  3. Twilio then sends that information to your app via a “webhook”
  4. Your app parses the response, and can do anything you want. In our case, we’re using Twilio to send a message back to the sender with information based on the code they sent

Theoretically you could use this simple service to do just about anything. You could ask for their email address and subscribe them to your newsletter. You could send back a YouTube video. You could keep a log of their phone number in a database and an agent could follow up with them at a later date. You could call the sender and read an audio message back to them with background music.  Or, you could first call the agent in charge of the property and then call the sender back and connect the two together.

But I didn’t do any of that. All I wanted to do for this service was set up an easy way to allow my friend the realtor to create an auto response for the code the potential buyer sent.

Here is the app works:

  1. Twilio sends a webhook POST and it is received by Three Toe.
  2. Using the contents of the message (e.g. “T0001”) it looks in a “responses” directory for a TXT file with that same name.
    1. If that TXT file is found, it responds to the sender with its contents.
    2. If that TXT file is not found, it responds with something like “There is no information available for this property.”

Very, very simple.

To add a new “code” that the application will respond to my friend the realtor just needs to add a new TXT file to the “responses” directory. Or, alternatively, I plan on creating a very simple form that will allow him to list and edit the responses and add new ones.

Here is the code: I’ve shared the basic application on GitHub in hopes that someone else can put it to use or even help improve it.

I’m fully aware that there are thousands of open source versions of this same sort of thing. In fact, this is basically what Twilio would likely use as their example code for others to learn from. I made this simply to scratch our itch and to get familiar with the Twilio API. It was fun to hack around with.

If you find yourself needing something like this, why not hire me to make it for you?

Update on June 28th, 2016: I’ve tagged version 0.2.1 on Github which includes a new configuration area to make it dead simple to create, edit, and delete codes and responses.

Update on June 29th, 2016: I’ve tagged version 0.3.0 on Github which includes a Bootstrap-style theme for the configuration area. This makes it easy to update codes and responses on mobile devices.

I have a few planned features for Three Toe that I’ll likely be able to build as extensions. I’ve already sold three copies of this simple app (with slight modifications) and some of the features needed by those customers will be making their way back into the repo. Fun app to hack away on.

PHP is pretty bad

Since I wrote “I’m perfectly happy using PHP” last week I figured I’d show the other side’s viewpoint as well. There are those out there that loathe the language. Evee goes off on PHP like no one else could:

PHP is an embarrassment, a blight upon my craft. It’s so broken, but so lauded by every empowered amateur who’s yet to learn anything else, as to be maddening. It has paltry few redeeming qualities and I would prefer to forget it exists at all.

Her analogy to a toolbox full of tools that you can’t really use properly is apt. I do feel like I write a lot of workarounds for things. I’ve always thought it was due to the depth of my knowledge of the language. Perhaps it isn’t. Perhaps it is indicative of it.

Some of the things pointed out are rather baffling, such as why the functions names are inconsistently styled such as using underscores or not, etc. But those are a matter of taste really. A language can throw out a style-guide and still be very useful if those functions do things that are of value. As pointed out there, it just gets worse from there.

If you’re into programming you might as well read the entire post. It is good.

Use what works, play with the new

I had Unmark’d Kyle Slattery’s post on his company site (which I think is rather good looking; here is why) regarding why his company uses Ruby on Rails. It is a good post. Notice this bit:

It’s easy to get caught up in the newest trend, and there are lots of great technologies being developed, but at the end of the day, just because something’s new and shiny doesn’t mean it will move the needle for your business.

Bingo. By the time I finish editing this post seventeen more frameworks, libraries, or pseudo-languages will have been released. And honestly, that is fantastic. Because out of those a few will take off, be well supported, and become great utilities for future projects to benefit from.

However, this doesn’t mean we need to use them in live projects immediately. Or, that we should jump from one framework to the next because you like the way the method names use camelCase.

Kyle goes on:

When it comes down to it, I’m most productive when I’m writing Rails code. Sure, I could build out my next project in Node.js, or Go, or whatever else is out there, but I’m going to be able to crank out the best, most productive code in Rails. I know it best. I’ve been working with it for almost as long as it’s been out, so I know all of its ins and outs.

This piece, as he explains, is a big reason to decide to use one language or framework over another. Not just your own productivity, as he states, but also for those that may touch the code in the future. If the framework is widely known, actively maintained, and many people use it in live projects, chances are you can add more people to the project and not have to teach them much about your project. They can likely dive right in and be productive very early on.

Lastly, this bit:

A mature framework, while it may not be as exciting, has had thousands (maybe tens of thousands) of hours of developer time spent getting it to where it is, which generally results in a more stable and secure platform.

If the project your working on ever hits any type of scale, you will want to know that hundreds if not thousands of other projects that run on the same technology has done so too. Early on, many people pointed to Twitter as a project that helped Ruby on Rails mature. And also to Facebook as one that helped PHP get a lot faster. Do a bit of digging when selecting what to use in your project, if a “big boy” is using and supporting it then you will likely get more sleep.

The very same reasons Kyle uses Ruby on Rails is why I use PHP. I do like the way Ruby looks far better than PHP. (Insert GIF of DHH saying Ruby is gorgeous here) I also think that the Rails framework is well structured for web applications. I do think Go looks succinct and interesting. And Node is likely better for some of the things I’m trying to accomplish. However, I’m faster with PHP, a lot of people know it, it is very fast and stable, and has been used in large-scale projects. So I’m perfectly happy using PHP.

I try my very best to stay completely agnostic when it comes to choices like these. (Kyle and I still get along great even though he’s a Ruby fascist.) In fact, longtime readers of my little blog here have likely seen my opinion of Microsoft technologies change dramatically since 2012 or so. Recently I saw someone’s C# and was all o_0! It looked very, very nice.

I love playing around with new things in order to see what else is out there. I can’t tell you how many times I’ve built a new Ruby on Rails application or built something with React or this or that. You’ve gotta keep the juices flowing and one way is to push your abilities into new territory. I liken it to a guitar player that mainly plays rock music riffing with a classical pianist. Expand your horizons from time-to-time and it will only make you a better guitar player.

None of this is to say that you shouldn’t switch to something new when the time is right. In fact, one very good reason to play with new languages and frameworks from time-to-time is that when something you’re using needs to get the axe you’re likely better prepared than if you had your head in the sand. There may be a time to switch from PHP to something else and when that happens I hope I have an open mind and make a good decision. I’ll likely refer back to Kyle’s post to help me make that decision too.

Stripe’s API documentation

Stripe has had best-of-breed API documentation for the last several years. They’ve just made it even better with a “quickstart tour” that works wonderfully.

Anyone documenting an API should look very closely at what Stripe has done. Much of their success is very likely due to them caring about their docs.

/via John Collison on Twitter.