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 ) . "
";
}
/*if($code=="M"||$code=='m'){
$value="
Total \t $total \t";
$data .=trim($value)."
";
}*/
$data = str_replace("" , "" , $data);
if ($data == "")
{
$data = "
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
$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.
Your code produces a tab seperated ascii file - not an XLS file as you are claiming in the header.
Return the correct mimetype ("Content-Type: text/csv") and an appropriate file extension (e.g. ${label}.txt) and MSEscel will stop complaining.
(NB I'd stay well away from anything which writes native MSExcel files - even using MS's "open" xaml formats - sooner or later you'll run into horrible compatability problems).