+9 votes
349 views
asked in Programming by (9.7k points)

how can I export my mysql database to XLS with PHP?

1 Answer

+7 votes
answered by (9.7k points)

<?php
function export_excel_csv()
{
    $conn = mysql_connect("localhost","root","");
    $db = mysql_select_db("database",$conn);
    
    $sql = "SELECT * FROM table";
    $rec = mysql_query($sql) or die (mysql_error());
    
    $num_fields = mysql_num_fields($rec);
    
    for($i = 0; $i < $num_fields; $i++ )
    {
        $header .= mysql_field_name($rec,$i)."\t";
    }
    
    while($row = mysql_fetch_row($rec))
    {
        $line = '';
        foreach($row as $value)
        {                                            
            if((!isset($value)) || ($value == ""))
            {
                $value = "\t";
            }
            else
            {
                $value = str_replace( '"' , '""' , $value );
                $value = '"' . $value . '"' . "\t";
            }
            $line .= $value;
        }
        $data .= trim( $line ) . "\n";
    }
    
    $data = str_replace("\r" , "" , $data);
    
    if ($data == "")
    {
        $data = "\n No Record Found!\n";                        
    }
    
    header("Content-type: application/octet-stream");
    header("Content-Disposition: attachment; filename=reports.xls");
    header("Pragma: no-cache");
    header("Expires: 0");
echo chr(255).chr(254).iconv("UTF-8", "UTF-16LE//IGNORE", $data); //special charachter (turkish vs..)
    print "$header\n$data";
}
?>

Ask a Question
Welcome to WikiTechSolutions where you can ask questions and receive answers from other members of the community.

You can ask a question without registration.

Categories

...