Serving MS Excel Documents From A Perl CGI Script

How can you serve a Microsoft Excel spreadsheet from a Perl CGI script?

It’s actually quite easy, and just a case of sending the right headers.

Assuming we have our raw binary Excel sheet in a variable called $excel, we can use this simple block of code to allow it to be downloaded from a web script with the filename of text.xls.

print "content-type: application/vnd.ms-exceln";
print "content-disposition: attachment; filename=text.xlsnn";
print $excel;

This will prompt the user with a file download box asking where to store text.xls.

The key here is the content-disposition tag. We could change the content-disposition to inline to try to force the browser to open the Excel document in browser itself, but that’s not really very friendly.

What Is An RDF Triple?

An RDF file should parse down to a list of triples.

A triple consists of a subject, a predicate, and an object. But what do these actually mean?

The subject is, well, the subject. It identifies what object the triple is describing.

The predicate defines the piece of data in the object we are giving a value to.

The object is the actual value.

Let’s take a quick look at my FOAF file to get an example triple.

I know Cal Henderson, and this is represented in my FOAF file as…

<foaf:knows>
<foaf:Person>
<foaf:nick>Cal</foaf:nick>
<foaf:name>Cal Henderson</foaf:name>
<foaf:mbox_sha1sum>2971b1c2fd1d4f0e8f99c167cd85d522a614b07b</foaf:mbox_sha1sum>
<rdfs:seeAlso rdf:resource="http://www.iamcal.com/foaf.xml"/>
</foaf:Person>
</foaf:knows>

Using the RDF validator we can get a the list of triples represented in this piece of RDF.


Triple Subject Predicate Object
1 genid:ARP40722 http://www.w3.org/1999/02/22-rdf-syntax-ns#type http://xmlns.com/foaf/0.1/Person
2 genid:ARP40722 http://xmlns.com/foaf/0.1/nick "Cal"
3 genid:ARP40722 http://xmlns.com/foaf/0.1/name "Cal Henderson"
4 genid:ARP40722 http://xmlns.com/foaf/0.1/mbox_sha1sum "2971b1c2fd1d4f0e8f99c167cd85d522a614b07b"
5 genid:ARP40722 http://www.w3.org/2000/01/rdf-schema#seeAlso http://www.iamcal.com/foaf.xml
6 genid:me http://xmlns.com/foaf/0.1/knows genid:ARP40722

But what does this actually mean? Lets go through it line by line.

Triple 1 – This has a subject of genid:ARP40722. You would have noticed that this didn’t appear in our original RDF, so where did it come from? It’s actually a bnode, and the value is generated by the RDF Validator. It’s purpose is to make sure we can identify the subject where it hasn’t been specifically named. In this case, we look to triple 6 and see that it has been generated there as the value of that object. The predicate of line 1 is the rdf:type, and the object is foaf:Person. This all corresponds to the code in <foaf:Person> in our FOAF extract.

Triple 2 – You’ll notice this has the same subject as triple 1. This isn’t a coincidence as the triple is part of the same foaf:Person. The predicate says we are defining the foaf:nick property, and the object is Cal. So we know that this foaf:Person has a nickname of Cal. This all corresponds to the line <foaf:nick>Cal</foaf:nick> in our FOAF extract.

Triple 3 – Yet again the subject is the same as triple 1. The predicate is foaf:name, and the object is Cal Henderson. So we know the name of the person in this foaf:Person is Cal Henderson. This represents the line <foaf:name>Cal Henderson</foaf:name>

Triple 4 – Yet again the subject is the same as triple 1. The predicate is foaf:mbox_sha1sum and the object is the SHA1 sum relating to Cal’s email address. This represents the line <foaf:mbox_sha1sum>2971b1c2fd1d4f0e8f99c167cd85d522a614b07b</foaf:mbox_sha1sum>

Triple 5 – Yet again the subject is the same. The predicate is rdfs:seeAlso and the object is http://www.iamcal.com/foaf.xml. We now know that if we want more information on Cal, we can see his FOAF file at that URL. This represents the line <rdfs:seeAlso rdf:resource="http://www.iamcal.com/foaf.xml"/>

Triple 6 – Here the subject is genid:me. This is because at the start of my FOAF file, in an area not show above I defined the id as me. The predicate is foaf:knows and the object is genid:ARP40722. This is saying that I know the person defined by the subject genid:ARP40722. In this case, it’s Cal, and his details have been shown in the previous 5 triples. This relates to the <foaf:knows> block.

Hopefully this has shown how the RDF has been parsed into triples, and how they relate to each other.

Shelley Powers, in her excellent book Practical RDF, helpfully describes triples as the following.

  • Each RDF triple is made up of subject, predicate and object.
  • Each RDF triple is a complete and unique fact.
  • An RDF triple is a 3-tuple, which is made up of a subject, predicate and object – which are respectively a uriref or bnode; a uriref; and a uriref, bnode or literal.
  • Each RDF triple can be joined with other RDF triples, but it still retains its own unique meaning, regardless of the complexity of the models in which it is included.

My First CPAN Module

My first publicly released Perl module has reached CPAN.

Nokia::File::NFB allows the creation and manipulation of existing Nokia .nfb and .nfc files. These files are created by the Nokia PC Suite backup and restore program.

The perl module lets me go in and extract data from the backup files of my Nokia 7250 and Nokia 7610 phones. You still need to know how to actually handle that data, all the module does is extract, or allow you to add, new data.

Alternating Table Rows With Template Toolkit And CSS

Whilst looking at a potential recode of the Smash Hits Chart website, I came across the need to make each alternate row in the main chart table a different colour.

As the recode will be using Template Toolkit, this is simple.

The chart is passed as a list, so we have to iterate over it to get each entry out. I do this using a simple [% FOREACH %]. Template Toolkit thoughtfully provides us with a Template::Iterator object called loop inside the FOREACH block, so we can use the index() method to get the current elements list position. Using this, we just need to check if the value is odd or even to decide what colour to make the row.

Let’s see some sample code. To change the row’s colour I’m going to use two CSS classes called lite and dark.

<table>
[% FOREACH entry = chart %]
<tr class="[% IF loop.index % 2 %]lite[% ELSE %]dark[% END %]">
<td>[% entry.position %]</td>
<td>[% entry.artist %]</td>
<td>[% entry.track %]</td>
<tr>
[% END %]
</table>

The magic is in the line loop.index % 2. It takes the remainer (modulus) of loop.index divided by 2. This returns either 0 or 1, which is what Template Toolkit uses to determine truth, so can be used directly in the IF statement.

Supporting Moblogging From The Nokia 7610

I’ve had to make some changes to my homebrew MMS moblog software now I have a Nokia 7610 phone.

The 7610 has a megapixel camera in it, which is far better than the very low res on in my old Nokia 7250.

The downside is I can’t use the images it creates directly in my blog as they are far to high resolution. What I needed to do was to reduce the size of the image coming in before posting it to the blog. I achieved this using the Image::Magick Perl module.

This snippet of code takes an image from a filename, resizes it to a maximum of 450 wide and 350 high, and saves it back out with the original filename.

my $oldimage = new Image::Magick;
$oldimage->Read($full_filename);
$oldimage->Scale(geometry=>'450x350>');
$oldimage->Write(filename=>$full_filename);

The key to code is the use of geometry. Here I am saying what I would like the maximum boundries to be. Image::Magick does the rest, keeping the aspect ratio of the original image. The > at the end tells Image::Magick only to scale if the image is larger than that ratio, meaning smaller images are left untouched.

Great, so I have lovely scaled images. The next problem was that the software wasn’t picking up the attachment.

The Nokia 7250 attaches the image directly with the MIME type of image/jpeg, however the Nokia 7610 attaches it with the MIME type of application/octet stream. This means it’s hard to detect what the attachment is, however we are provided with a clue. We are given the recommended filename of the attachment, so scanning to see if it ends in .jpg lets us know if we have a JPEG image or not.

The code I use to detect the attachment in the octet-stream and put it into the variable $attachmentlooks something like this…

if ($entity->mime_type =~ m/application/octet-stream/i) {
if (($head->recommended_filename =~ /.jpg$/i) || ($head->mime_attr("content-type.name") =~ /.jpg/i)) {
my $bh = $entity->bodyhandle;
my $attachment = $bh->as_string;

Where $entity is a MIME::Entity object and $head is a MIME::Head object from the entity.

I now have a tool that can take MMS email messages from various phones and post remotely to my blog.

Comparing Email Addresses In JavaScript

I needed to write a quick JavaScript that could check if two email addresses were the same or not today. As it’s so simple, I thought I’d share it.

Firstly we put the following JavaScript into a script tag.

function compareEmails(myForm) {
  if (myForm.email.value != myForm.email2.value) {
    alert("Your email addresses don't match. Please double check");
    return false;
  } else {
    return true;
  }
}

It takes a form, and compares the values of the fields called email and email2. If they are the same it returns true which lets the form submit normally.

However, if email and email2 are different it brings up an alert box telling our submitter the values are different, and returns false. Returning false means the script won’t submit to the script specified in the action parameter so the user has to confirm their email address again.

The following HTML code gives an example of the scripts use.

<form action="dosomething.pl" onSubmit="compareEmails(this);">
Email :<input type="text" name="email" /> <br />
Verify Email: <input type="text" name="email2" /> <br />
<input type="submit" />
</form>

Of course the code can be used to compare any fields and not just email addresses. All you need to do is to change the field names in the JavaScript.

Enjoy!

CSS Overflow Tag

My new favourite CSS tag is overflow.

It provides the ability for blocks to scroll if they overflow their bounding box. It’s part of the CSS2 specification, but most modern browsers should support it.

I have added it to the my code blocks as they tend to overrun the most often using preformatted text for the display.

For more information have a look at the CSS Overflow Guide.

Copying Selections With JavaScript

One thing I’ve started to do for web based admin systems is a far greater use of JavaScript.

One task I find I do quite frequently is copying from a list of options to a sublist of selected options. For example, a list of all blog entries to a sublist of related blog entries.

I do this by using a version of the following JavaScript. It takes a <select> HTML element with the property multiple="multiple" set, and copies to another <select> HTML element, also with multiple="multiple" set. I have a button set to execute the JavaScript via an onClick handler.

Lets have a quick look at the code…

First we need to define our function. Here I’ve called it doCopy and it takes two parameters which are the two <select> elements from the DOM, eg document.formname.selectname.

function doCopy(list1, list2) {

Now we need to iterate over the first list to check each <option> to see if it has been selected.

for (var i=0; i< list1.length; i++) {
if (list1.options[i].selected == true) {

If the current <option> is selected, we need to iterate over the second list, making sure we don’t already have it in the list. I’m setting a temporary variable, cancopy that is set to false if we find we already have it in the second list.

var cancopy = true;
for (var j=0; j< list2.length; j++) {
if (list1.options[i].value == list2.options[j].value) {
cancopy = false;
break;
}
}

If cancopy is true, then we’re OK to copy the item over to the second list. To do this we have to create a new Option object. Let’s have a quick look at the Option class in JavaScript. We can constuct a new Option like this…

new Option(text, value, defaultSelected, selected)

Where text is the text to show, value is value, defaultSelected is a boolean defining if we want the defaultSelected property set, and selected is a boolean defining if we want the selected property set.

We need to initialise our new Option with the value and text of our original option from list one, and set it as selected. We insert this into the secondlist by adding it to the very of its array. This will grow the list automatically.

if (cancopy == true) {
list2.options[list2.options.length] = new Option(list1.options[i].text, list1.options[i].value, false, true);
}

Lets put this all together for our final code…

function doCopy(list1, list2) {
for (var i=0; i< list1.length; i++) {
if (list1.options[i].selected == true) {
var cancopy = true;
for (var j=0; j< list2.length; j++) {
if (list1.options[i].value == list2.options[j].value) {
cancopy = false;
break;
}
}
if (cancopy == true) {
list2.options[list2.options.length] = new Option(list1.options[i].text, list1.options[i].value, false, true);
}
}
}
}

Here is some example HTML that uses the JavaScript to copy from list1 to list2.

<form name="mytestform">
<select name="list1" multiple="multiple">
<option value="1">One</option>
<option value="2">Two</option>
<option value="3">Three</option>
<option value="4">Four</option>
</select>
<input type="button" value="Copy" onClick="doCopy(document.mytestform.list1,document.mytestform.list2);" />
<select name="list2" multiple="multiple">
</select>
</form>

We should now be able to copy between the lists when we click on the Copy button.

Checking Variables Exist In JavaScript

I came across a problem with JavaScript earlier trying to see if a variable actually exists or not. For example…

if (x) {
// x exists.
} else {
// x doesn't exist.
}

Unfortunately this causes an error if x hasn’t been defined, eg…

var x = "robs test";

The solution came after a read of JavaScript: The Definitive Guide chapter 4. It explains about the Global Object, and that all global variables are located there. For client side JavaScript this is the Window object. So to see if x has been defined we need to check window.x, eg…

if (window.x) {
// x exists.
} else {
// x doesn't exist.
}

This means if I forget to define x it will default to the block of code saying that x doesn’t exist. Of course, I shouldn’t really be in a situation where variables haven’t been defined, but sanity checking is always a good idea, especially with development code.