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 Code:
<?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 Code:
//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 Code:
//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 Code:
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 Code:
$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 Code:
$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 Code:
$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 Code:
<?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>
LinkBack URL
About LinkBacks
[PHP Tut] Creating Excel XLS for download
Reply With Quote
