hi to all ,

how to create a .xls file using php and mysql database.

thanks in advance
---------------------
Anil Kumar

hi ,
i got the solution for that script.


//EDIT YOUR MySQL Connection Info:
$DB_Server = "localhost"; //your MySQL Server
$DB_Username = "USER NAME"; //your MySQL User Name
$DB_Password = "PASSWORD"; //your MySQL Password
$DB_DBName = "DBNAME"; //your MySQL Database Name
$DB_TBLName = "TABLE NAME"; //your MySQL Table Name
//$DB_TBLName, $DB_DBName, may also be commented out & passed to the browser
//as parameters in a query string, so that this code may be easily reused for
//any MySQL table or any MySQL database on your server

//DEFINE SQL QUERY:
//you can use just about ANY kind of select statement you want -
//edit this to suit your needs!
$sql = "Select * from $DB_TBLName";

//Optional: print out title to top of Excel or Word file with Timestamp
//for when file was generated:
//set $Use_Titel = 1 to generate title, 0 not to use title
$Use_Title = 1;
//define date for title: EDIT this to create the time-format you need
$now_date = date('m-d-Y H:i');
//define title for .xls file: EDIT this if you want
$title = "Dump For Table $DB_TBLName from Database $DB_DBName on $now_date";
/*

Leave the connection info below as it is:
just edit the above.

(Editing of code past this point recommended only for advanced users.)
*/
//create MySQL connection
$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password)
or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno());
//select database
$Db = @mysql_select_db($DB_DBName, $Connect)
or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno());
//execute query
$result = @mysql_query($sql,$Connect)
or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno());


//define separator (defines columns in excel & tabs in word)
$sep = "\t"; //tabbed character
$fp = fopen('products1.xls', "w");
$schema_insert = "";
$schema_insert_rows = "";
//start of printing column names as names of MySQL fields
for ($i = 1; $i < mysql_num_fields($result); $i++)
{
$schema_insert_rows.=mysql_field_name($result,$i) . "\t";
}
$schema_insert_rows.="\n";
echo $schema_insert_rows;
fwrite($fp, $schema_insert_rows);
//end of printing column names



//start while loop to get data
while($row = mysql_fetch_row($result))
{
//set_time_limit(60); // HaRa
$schema_insert = "";
for($j=1; $j<mysql_num_fields($result);$j++)
{
if(!isset($row[$j]))
$schema_insert .= "NULL".$sep;
elseif ($row[$j] != "")
$schema_insert .= strip_tags("$row[$j]").$sep;
else
$schema_insert .= "".$sep;
}
$schema_insert = str_replace($sep."$", "", $schema_insert);
//following fix suggested by Josue (thanks, Josue!)
//this corrects output in excel when table fields contain \n or \r
//these two characters are now replaced with a space
$schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
$schema_insert .= "\n";
//$schema_insert = (trim($schema_insert));
//print $schema_insert .= "\n";
//print "\n";

fwrite($fp, $schema_insert);
}
fclose($fp);


thanks
-------------------------
Anil Kumar

Code works great but when open in Excel it says that products1.xls is different format from specified by file extension. Make sure not corrupt etc...

I continue and open and it does look fine.

Wonder if I way to fix so it doesnt show the error message.

(Also can columns widths be changed??)

So,yesterday I was at my friend's birthday and saw a lot of my friend. But one of it told me about an interesting tool -repair Microsoft Excel. As he told me it recovered some of his old corrupted xls files for seconds and absolutely free. Moreover I tried it and was wondered,reason of it helped me quite easy. And showed me how it exports recovered data into a new Microsoft Excel file.

Hi,

This code perfectly working, once check that file contains full permissions or not , if not then it will give the errors.

Once add these line of code for download that file :

header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=product1.xls");
header ('Content-Transfer-Encoding: binary');
header ('Content-Length: '.filesize('product1.xls'));
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT');
header ('Cache-Control: cache, must-revalidate');
header ('Pragma: public');

Hope that problem may be solved.

Thank you,

Anil Kumar