Page 1 of 1

Problem in Data format while Export Data to Excel using PHP

Posted: Mon Jun 23, 2008 5:09 am
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.

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

Posted: Mon Jun 23, 2008 10:07 am
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.