Monday 3 September 2018

Exporting data from mysql to ms excel

I have the following code which I am using to export data from mysql database to microsoft excel

    $result = $this->db->query($sql);
$num_fields = mysql_num_fields($result);
    $header = "";
    for($i = 0; $i < $num_fields; $i++ )
    {
      $header .= mysql_field_name($result,$i)."\t";
    }
    $data = "";
    while($row = mysql_fetch_row($result))
   {
    $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";
}
/*if($code=="M"||$code=='m'){
    $value="\n Total \t $total \t";
    $data .=trim($value)."\n";
}*/

$data = str_replace("\r" , "" , $data);

if ($data == "")
{
    $data = "\n No Record Found!n";
}
header("Cache-Control: ");
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=\"$label.xls\"");
header("Pragma: ");
header("Expires: 0");
print "$header\n$data";

where $result is a mysql resource.
When I run the program, before opening, excel issues a warning "the file you are trying to open, filename.xls, is in different format than specified by the extension, verify that the file is not corrupted and is from a trusted source before opening. Do you want to open the file now?"
I am stranded because after accepting to open the file, I see the data that I require, what I want is a way of making the format of data sent match the xls extension format. What should I do?. I am using ms office 2007

If you want to produce a real Excel file, use PHPExcel.

0 comments:

Post a Comment