techfolks php tutorials logo
Excel Using PHP Excel Using PHP and Mysql Mysql PHP Sample Programs

Export to Excel Using PHP and Mysql

Below simple program will export mysql data values to excel sheet without using any PHP libraries.

Export to Excel Using PHP and Mysql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
<!--?php &lt;/p&gt;
&lt;p&gt;//write your db connection&lt;/p&gt;
&lt;p&gt;$xlsFile= 'file_'.date('Y-m-d').'.xls'; &lt;br ?-->$separator = "t";</pre>
$sql = "Select * from emp";
$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password) or die("Failed to connect:
" . mysql_error() . "
");
 
$Db = @mysql_select_db($DB_DBName, $Connect) or die("Failed to select db:
" . mysql_error(). "
" );
 
$res = @mysql_query($sql,$Connect) or die("Failed to execute :
" . mysql_error(). "
" );
 
header("Content-Type: application/xls");
header("Content-Disposition: attachment; filename=$xlsFile");
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Cache-Control: private", false);
header("Content-Transfer-Encoding: binary");
 
for ($i = 0; $i&lt;mysql_num_fields($res); $i++) {
echo mysql_field_name($res, $i) . "t";
}
print("n");
 
while($row = mysql_fetch_row($res))
{
 
$insert = "";
 
for($j=0; $j&lt;mysql_num_fields($res); $j++)
 
{
 
if(!isset($row[$j])) {
 
$insert .= "NULL".$separator;
 
}
 
elseif ($row[$j] != "") {
 
$insert .= "$row[$j]".$separator;
 
}
 
else {
 
$insert .= "".$separator;
 
}
}
$insert = str_replace($separator."$", "", $insert);
$insert = preg_replace("/rn|nr|n|r/", " ", $insert);
$insert .= "t";
print(trim($insert));
print "n";
}
?&gt;

Note: IF you get errors or in excel all the data is in one column, then instead t tab space use, comma ‘,’.

Related Articles
HTML Javascript Sample Programs Tips&Tricks

How to Detect a Adblock on Website or Blog and Show Pop up Message

viveik

Many people these are installing plugins like Adblock to block ads on the websites/blog and for few People earning from Ads are the main income stream, so today I found this below script where it can detect whether the user has installed Adblock any other plugins which are not displaying Ads on the website and […]

techfolks mysql tutorials
Mysql Tips&Tricks

How to Get MySql Query Result as an Email

viveik

If you are working on a big mysql query and want to get the mysql query as email when it finishes use the below trick. Go to mysql command:Set pager directive: mysql> pager mail -s “subject” emailme@domain.comPAGER set to ‘mail’ -s “subject” emailme@domain.com The trick uses pager directive to redirect the query output result to […]
Javascript Mysql PHP

PHP Radio Button Example With Mysql DB

viveik

Simple PHP Radio button enable and disable textarea textbox example in php using Mysql