i got this code from the internet to export mysql query to excel using php..i just modify it to meet my need. i need some help on this..thanks..

$DB_Server = "localhost"; 
$DB_Username = "root";    
$DB_Password = "";              
$DB_DBName = "dcde";        
$DB_TBLName = "po"; 
$filename = "filename";        

$sql = "Select poNo,date,supplier,tAmount,code from $DB_TBLName WHERE  code='2'";
$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password) or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno());

$Db = @mysql_select_db($DB_DBName, $Connect) or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno());   

$result = @mysql_query($sql,$Connect) or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno());    
$file_ending = "xls";

header("Content-Type: application/xls");    
header("Content-Disposition: attachment; filename=$filename.xls");  
header("Pragma: no-cache"); 
header("Expires: 0");

$sep = "\t"; 

for ($i = 0; $i < mysql_num_fields($result); $i++) {
echo mysql_field_name($result,$i) . "\t";

    while($row = mysql_fetch_row($result))
        $schema_insert = "";
        for($j=0; $j<mysql_num_fields($result);$j++)
                $schema_insert .= "NULL".$sep;
            elseif ($row[$j] != "")
                $schema_insert .= "$row[$j]".$sep;
                $schema_insert .= "".$sep;
        $schema_insert = str_replace($sep."$", "", $schema_insert);
        $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
        $schema_insert .= "\t";
        print "\n";

attached are the two images..1st is the the output of this code..and the 2nd file is my desired output which is already formatted.. like 1) the name of the field is change, bold..2) column should be automatically adjusted to fit the entry 3)..additional entry like PREPARED: NAME

thanks a lot..

Recommended Answers

All 2 Replies

You are outputting a CSV file. You can only do formatting if you create an actual Excel file. You can do this for example with PHPExcel.


Use PEAR Spreadsheet Excel library. it is really cool. You can set the colour and width of the columns, add formulas etc.

It is easy to use and very versitle

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.