Archive for December, 2008

Posted by Matt at 24 December 2008

Category: Programming, utils

I recently created a project on Google Code for PHP Excel Reader. This is a PHP library for reading MS Office Excel files that has existed on Sourceforge for quite a while but was no longer being maintained or enhanced. I needed to use it for a work project, so I put in some time to fix it and enhance it to make it more usable. I decided to put it up on Google Code in the hopes that others would find it useful.

[ PHP Excel Reader Project Home ]

Example

Microsoft Excel

<?php
require_once 'excel_reader2.php';
$xls = new Spreadsheet_Excel_Reader("example.xls");

echo '<table border="1">';
  for ($row=1;$row<=$xls->rowcount();$row++) {
  echo '<tr>';
  for ($col=1;$col<=$xls->colcount();$col++) {
    echo '<td>' . $xls->val($row,$col) . ' </td>';
  }
  echo '</tr>';
}
echo '</table>';
?>
HTML Output

Usage

$data = new Spreadsheet_Excel_Reader("test.xls"); 

Retrieve formatted value of cell (first or only sheet): 

  $data->val($row,$col) 

Or using column names: 

  $data->val(10,'AZ') 

From a sheet other than the first: 

  $data->val($row,$col,$sheet_index) 

Retrieve cell info: 

  $data->type($row,$col);
  $data->raw($row,$col);
  $data->format($row,$col);
  $data->formatIndex($row,$col); 

Get sheet size: 

  $data->rowcount();
  $data->colcount(); 

$data->sheets[0]['cells'][$i][$j] - data from $i-row $j-column 

$data->sheets[0]['numRows'] - count rows
$data->sheets[0]['numCols'] - count columns 

$data->sheets[0]['cellsInfo'][$i][$j] - extended info about cell
$data->sheets[0]['cellsInfo'][$i][$j]['type'] = "date" | "number" | "unknown"
$data->sheets[0]['cellsInfo'][$i][$j]['raw'] = value if cell without format
$data->sheets[0]['cellsInfo'][$i][$j]['format'] = Excel-style Format string of cell
$data->sheets[0]['cellsInfo'][$i][$j]['formatIndex'] = The internal Excel index of format 

$data->sheets[0]['cellsInfo'][$i][$j]['colspan']
$data->sheets[0]['cellsInfo'][$i][$j]['rowspan']

Posted by Matt at 9 December 2008

Category: igoogle gadgets, web

Tags: , , ,

If you are a gadget author, you’re probably interested in knowing how many users have your gadgets installed. There are a few ways to deduce this information, but none are perfect. I’m going to show you one way to generate useful information that is probably something you’re looking for.

Gadget Directory Details

The first approach is to just look at the iGoogle Gadget Directory entry for your gadget. It should show how many users you have. In fact, my Gadget Monitor gadget retrieves its information from here for user counts. Unfortunately, if you look at different internationalized versions of the directory, you’ll see different numbers based on how many of those users have it installed. There is no place to get a single consolidated number.

Analytics

A better approach is to use Google Analytics. In the gadget developer documentation there are instructions on how to use analytics with gadgets. But using it in the default way – recording one hit every time your gadget is loaded – is not all that useful because:

  • Users may load your gadget many times in one day, and this fact isn’t really relevant like page views on a web site are
  • If you record multiple hits per person using your gadget, you still don’t know how many distinct users are using your gadget
  • Even looking at the “unique pageviews” data in Analytics can be misleading, because if the user has multiple sessions in the same day, it still gets counted more than once

New Goals

The approach I use takes advantage of Analytics, but in a different way. The goals are:

  • Get as accurate a count as possible of how many unique users interacted with your gadget in a day
  • Get a count of how many times your gadget was added by new users in a given day

This approach, unfortunately, will not get you any closer to knowing how many people have your gadget on their iGoogle page and didn’t hit it one day, or maybe have it on a tab that they didn’t look at one day.

A Different Approach

I’ll explain this new approach by example.

First, in the ModulePrefs declaration of your gadget, you need to require two features:

<Require feature="analytics"/>
<Require feature="setprefs" />

Second, you need a hidden user preference to store some data:

<UserPref name="today" datatype="hidden" defaultvalue="" />

Finally, some javascript code in your Content section:

// Get a reference to user preferences
var prefs = new _IG_Prefs();
// Get the user's current date/time
var now = new Date();
// Create a date string of the form yyyy-m-d
var today = (now.getFullYear?now.getFullYear():now.getYear())+'-'+(now.getMonth()+1)+'-'+now.getDate();
// Get the stored value of the "today" user preference.
var prefs_today = prefs.getString('today');

// If the "today" user preference is blank, then the user has never loaded
// this gadget. They must have just added it!
if (prefs_today=='') {
    _IG_Analytics("UA-0000000-0", "/addgadget/mygadget");
}
// If the generated date string doesn't match the one stored in the
// preferences, then this is the first time the user has loaded the
// gadget today. Record a hit and change the preferernce to be
// today's date string so it won't get recorded again.
if (prefs_today!=today) {
    prefs.set('today',today);
    _IG_Analytics("UA-0000000-0", "/gadget/mygadget");
}

(Note: in the above code, replace UA-0000000-0 with your Analytic account’s unique ID, and ‘mygadget’ with a unique identifier for your specific gadget)

The concept here is simple. Every time the gadget loads, it will get a string representing today’s date. If the date doesn’t match the value stores in the user’s preferences (for example, if the gadget was last loaded yesterday), then record a hit for today and update the preference.

This way, a single user will only record one hit a day to the Analytics counter. You get a pretty accurate count of how many unique users are using your gadget in a single day.

Since the preference gets updated every day the user loads the gadget, if it is blank that means this must be the first time it’s being loaded. That effectively means they have just added it. In that case, record a hit to a separate url starting with /addgadget to separate that from the other url.

An advantage of putting each hit in a “subdirectory” in analytics is so you can effectively use the drill-down report. By looking at the numbers for the entire /addgadget directory you can get a count of gadgets added in a day across all your different gadgets. Looking at the numbers for /gadget as a whole gives you a user count of all your gadgets combined.

I hope this approach proves to be as useful to you as it has been for me!

Posted by Matt at 3 December 2008

Category: Uncategorized

If you’re like me, you know you should back up your important data but you don’t. I’ve experienced the panic of thinking files are gone several times, and at those moments I swore to myself that I would start backing up my stuff so this never happened again. But I didn’t. Until now.

I discovered mozy.com from an article in Newsweek. It’s an online backup solution, so you can get rid of your tapes, thumb drives, and external hard drives. Create an account at mozy.com, install the client on your computer, and start backing up! It schedules backups to run automatically when your computer is idle and you have sufficient bandwidth. You can choose what to backup, and I like the concept of “backup sets” from types of files, rather than picking from directory structures (which you can also do).

Some nice features I’ve found already:

  • Incremental backups save bandwidth after the first run
  • You can view and restore your backed up files from any computer via their web site
  • 2GB free data storage – unlimited for only $4.95/month
  • Data is stored encrypted, transmitted encrypted

Give it a try. It might just be convenient enough to finally get you backing up your stuff!

Disclaimer: The links to mozy.com from this post include my referral ID. If you sign up using these links I will get extra space for free. Thanks ;)