[PHP Tut] Creating Excel XLS for download

mattura

Member
Messages
570
Reaction score
2
Points
18
This is for those who want to offer an XLS download, generated on the fly from your database(s).

First, you need to write the code at the top of a page of php, so the headers may be sent. Your file 'this.php' might look something like this:
PHP:
<?php
if ($_REQUEST['download']=="xls") {
 //the link below will cause this code to run
 //CODE HERE
 exit; //this is  important! It stops the rest of the page being appended to the file
}
?>
<html>
<body>
HTML here perhaps<br/>
<a link="this.php?download=xls">Download xls</a>
</body>
</html>

So...what goes in the 'CODE HERE' gap? We shall see...

Now XLS files are similar to other files in that they must have some kind of 'header' to mark the Beginning Of the File (BOF). This also shows your operating system how to open the file. This is the first thing that must be written to the XLS. This is in binary, and can be generated by the following (look up pack for more details):
PHP:
//this is the XLS header:
$xlshead=pack("s*", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);

Similarly, the XLS must have an End Of File marker:
PHP:
//this is the XLS footer:
$xlsfoot=pack("s*", 0x0A, 0x00);

We can write to cells by various methods, but here we shall use the cell reference method. This consists of a row/column reference, followed by a value. It is a good idea to wrap this in a function:
PHP:
function xlsCell($row,$col,$val) {
 $len=strlen($val);
 return pack("s*",0x204,8+$len,$row,$col,0x0,$len).$val;
}

So now it is time to create the bulk of your excel file using the function above. Here we will store it in a variable called $data. Let's say you have a database of users (name, phone, email):
PHP:
$data=xlsCell(0,0,"Name").xlsCell(0,1,"Phone").xlsCell(0,2,"Email");
Note that XLS co-ordinates start at [0,0]

Now you need to loop through your database using your favourite method, and simply add the values to $data, something like this:
PHP:
$rowNumber=0;
$q="SELECT * FROM `users` ORDER BY `name` ASC";
$r=mysql_query($q);
while ($row=mysql_fetch_assoc($r)) {
 $rowNumber=$rowNumber+1;
 $name=$row['name'];
 $phone=$row['phone'];
 $email=$row['email'];
 $data.=xlsCell($rowNumber,0,$name) . xlsCell($rowNumber,1,$phone) . xlsCell($rowNumber,2,$email);
}

Good! Now you have created an XLS file! But wait, you need to make sure you can serve it up properly, so that users/computers know what to do with it! This is accomplished by means of a few headers, sent before the data:
PHP:
$filename="users.xls";
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");;
header("Content-Disposition: attachment;filename=$filename"); 
header("Content-Transfer-Encoding: binary ");

So now we can put it all together:
PHP:
<?php
if ($_REQUEST['download']=="xls") {
 $xlshead=pack("s*", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);
 $xlsfoot=pack("s*", 0x0A, 0x00);
 function xlsCell($row,$col,$val) {
  $len=strlen($val);
  return pack("s*",0x204,8+$len,$row,$col,0x0,$len).$val;
 }

 $data=xlsCell(0,0,"Name") . xlsCell(0,1,"Phone") . xlsCell(0,2,"Email");
 $rowNumber=0;
 $q="SELECT * FROM `users` ORDER BY `name` ASC";
 $r=mysql_query($q);
 while ($row=mysql_fetch_assoc($r)) {
  $rowNumber=$rowNumber+1;
  $name=$row['name'];
  $phone=$row['phone'];
  $email=$row['email'];
  $data.=xlsCell($rowNumber,0,$name) . xlsCell($rowNumber,1,$phone) . xlsCell($rowNumber,2,$email);
 }
 $filename="users.xls";
 header("Content-Type: application/force-download");
 header("Content-Type: application/octet-stream");
 header("Content-Type: application/download");;
 header("Content-Disposition: attachment;filename=$filename"); 
 header("Content-Transfer-Encoding: binary ");
 echo $xlshead . $data . $xlsfoot;
 exit; //this is  important!
}
?>
<html>
<body>
HTML here perhaps<br/>
<a link="this.php?download=xls">Download xls</a>
</body>
</html>
 
Last edited:

mattura

Member
Messages
570
Reaction score
2
Points
18
C'mon guys and gals...I'd love to know what you think of this tut. Is it useful for anyone at all? Has anyone even tried it?
 

woiwky

New Member
Messages
390
Reaction score
0
Points
0
I haven't tried this code in particular, but it looks fine. Although I would recommend using an OOP style or at least procedural so it would be easier to use in other scripts as well.

This is a very good tut, though. Perhaps it's just that it's beyond the understanding of a lot of people around here. The explanations you provide are solid, but they do require intermediate php knowledge and basic mysql knowledge.

Regardless, generating xls files can prove to be quite useful in many situations. Nice tut, +rep.
 

sunils

New Member
Messages
2,266
Reaction score
0
Points
0
Its a good tutorial. Many of the people would want to offer its user some reports in excel format and they could use this tutorial to generate one on the fly. Good job.
 

mattura

Member
Messages
570
Reaction score
2
Points
18
Thanks! Just wanted to know if it's useful.
It would be a simple matter to change the style of programming to whatever you prefer, but I wrote it like that for explanatory clarity.
Also it is meant to be a copy-patse tutorial, so hopefully those with less complete php/sql knowledge can at least test it and make simple changes to taste (that's how we learn anyway!)

Let me know if you use it/like it/hate it etc or have any problems with it.
 
Top