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.

#, #, #, #, #, #