Problem in Data format while Export Data to Excel using PHP

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
tkmanoj
Forum Newbie
Posts: 1
Joined: Mon Jun 23, 2008 4:55 am

Problem in Data format while Export Data to Excel using PHP

Post by tkmanoj »

I wanted to export a set of data from mySQL database to MS-Excel using PHP.

I have written a program in PHP, which is working well. But, I am having a trouble in the format in which the excel report is being generated.

Here are the details:
I have an Employee Table, where the data is as follows:

EmpID EmpName Dept
001122 Thomas Marketing
002222 David Production
000345 Joseph Finance

But in the excel, I am getting the report as follows:

Emp_ID Emp_Name Department
1122 Thomas Marketing
2222 David Production
345 Joseph Finance

In the database, the EmpID is a Varchar (6), where as in the Excel, it takes as integer. hence it looses all the zero's in the begining of the ID.

Here is the program I am using:

Code: Select all

<?php 
$filename = 'exportdata.xls'; 
header("Content-type: application/octet-stream"); 
header("Content-Disposition: attachment; filename=".$filename); 
header("Pragma: no-cache"); 
header("Expires: 0"); 
 
$out_put = "Emp ID" . "\t" . "Emp Name" . "\t" . "Department" . "\n";
 
$link = mysql_connect('localhost', 'user1', 'password')
    or die('Could not connect: ' . mysql_error());
 
mysql_select_db('test') or die('Could not select database');
 
$query_emp = 'SELECT * FROM EMPLOYEE';
 
$result_emp = mysql_query($query_emp) or die('Query failed: ' . mysql_error());
 
while ($row_emp = mysql_fetch_array($result_emp, MYSQL_ASSOC)) {
    $EmpID = $row_emp["EmpID"];
    $NAME = $row_emp["EMPNAME"];
    $DEPT = $row_emp["DEPT"];
    
    $rp_output .= $EmpID .  "\t" . $NAME. "\t" . $DEPT . "\n";
}
 
mysql_free_result($result_emp);
mysql_close($link);
 
print $out_put; 
exit; 
?>
Request your help in fixing this.
hyder_m29
Forum Newbie
Posts: 12
Joined: Mon Jun 23, 2008 9:36 am

Re: Problem in Data format while Export Data to Excel using PHP

Post by hyder_m29 »

Open Excel, design how you want the excel sheet to look like, ex. headers, borders, etc.
Save as an XML and use PHP to manipulate that XML file and save as Excel XML when done.
Post Reply