It is very simple to create a excel file (xls) file with the content we have and providing it for download.
In other words we can easily add mysql table data in to excel and provide it for download.
The following steps will do that
a)Create a file specific to do this
example: excel.php
b)The header we are going to set is the only important thing that specifies the document as a excel document. Header should be in the first lines of the page before any other content.
header("Content-type: application/x-msdownload");
i.e. Content-type: application/x-msdownload is used to specify the document as xls document.
c)Add further headers to specify it as an attachment at give it a name
header("Content-Disposition: attachment; filename=test-excel.xls");
header("Pragma: no-cache");
header("Expires: 0");
d)Now what ever you print (using php print function) after '\t' will be saved in new column in xls
what ever you print after '\n' will be saved in new row in xls
i.e. \t acts as a column separator and \n as a row separator
print "row1 col1 \t row1 col2 \t row1 col3";
print "\n row2 col1 \t row2 col2 \t row2 col3";
This will do - when ever you visit the page excel.php you will be prompted to save the test-excel.xls file..
To add mysql data in to the excel file, just print the data in step four with each row seperated by \n and each column value seperated by \t
example:
<?php
header("Content-type: application/x-msdownload");
header("Content-Disposition: attachment; filename=extraction.xls");
header("Pragma: no-cache");
header("Expires: 0");
define(db_host, "localhost");
define(db_user, "root");
define(db_pass, "");
define(db_link, mysql_connect(db_host,db_user,db_pass));
define(db_name, "testdb");
mysql_select_db(db_name);
$select = "SELECT * FROM tablename";
$result = mysql_query($select);
$fields = mysql_num_fields($result);
for ($i = 0; $i < $fields; $i++) {
$header .= mysql_field_name($result, $i) . "\t";
}
while($row = mysql_fetch_row($result)) {
$line = '';
foreach($row as $value) {
if ((!isset($value)) OR ($value == "")) {
$value = "\t";
} else {
$value = str_replace('"', '""', $value);
$value = '"' . $value . '"' . "\t";
}
$line .= $value;
}
$data .= trim($line)."\n";
}
$data = str_replace("\r","",$data);
print "$header\n$data";
?>
