exporting data from mysql to ms excel

Question

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

I've always found it works better to echo out a table of data, rather than relying on new lines. — Nick Pyett
– Nick Pyett, Commented Jun 13, 2011 at 11:56

alex · Accepted Answer · 2011-06-13 11:51:30Z

3

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

answered Jun 13, 2011 at 11:51

alex

492k205 gold badges890 silver badges991 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tamseyc Over a year ago

looking at the code I have given, isnt there any other way out?

Mark Baker Over a year ago

@Tamseyc - If you want to create a file to be read by MS Excel, whose format actually matches that of the xls extension, then you need to create real BIFF xls file... not a tab-separated file; and if you want to create a tab-separated file instead, use PHP's built in fputcsv() function rather than building it manually... it's so much easier and cleaner

symcbean · Accepted Answer · 2011-06-13 12:26:02Z

2

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).

answered Jun 13, 2011 at 12:26

symcbean

48.4k6 gold badges63 silver badges99 bronze badges

1 Comment

Mark Baker Over a year ago

Can you please explain what you mean by "horrible compatability problems"... as the developer of one of those libraries that writes native MSExcel files, it would be useful for me to know so that I can rectify the situation.

Collectives™ on Stack Overflow

exporting data from mysql to ms excel

2 Answers 2

2 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Related