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:
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):
Similarly, the XLS must have an End Of File marker:
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:
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):
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:
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:
So now we can put it all together:
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");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: