Page 1 of 1

Export to xls

Posted: Mon Mar 15, 2010 12:20 pm
by dreeves
I am having trouble using PHP to export some data to a table. After I am prompted if I want to Open or Save the file (and click open) I receive an error message stating:"The file you are trying to open is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?"
I click "yes" and the file opens just fine.

How can I prevent this error message from coming up? What is causing it in the first place? I'm guessing it is something in the header.

Code: Select all

<?php
include 'dbinfo.inc.php';
$link= mysql_connect('localhost',$username,$password);
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
@mysql_select_db($grant_database) or die( "Unable to select database");
 
$Result = $pDate = null;
 
    $Query = "SELECT * from main LIMIT 0,20";
 
    $Result = mysql_query($Query);
 
$TableData = ''; 
 
// For Headings in Excel
$contents="Project Number\tProject Name\tGrant Year\tProject Description\n";       
 
// Put data records from mysql by while loop.
if($Result)
{
    while($Row=mysql_fetch_array($Result)){
 
        $contents.=$Row['project_number']."\t";
        $contents.= str_replace(',', '\,', $Row['project_name'])."\t";
        $contents.=$Row['grant_year']."\t";      
        $contents.= str_replace(',', '\,', $Row['project_description'])."\n";       
        
        
        $TableData .= '<tr><td>'.$Row['project_number'].'</td><td>'.$Row['project_name'].'</td><td>'.$Row['grant_year'].'</td><td>'.$Row['project_description'].'</td></tr>';  
    } 
}
// This one executes only if you click Export button 
 
    header("Pragma: public");
    header("Expires: 0");
    header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); 
    header("Content-Type: application/force-download");
    header("Content-Type: application/octet-stream"); //I tried vnd.ms-excel and it still didn't work
    header("Content-Type: application/download");;
    header("Content-Disposition: attachment;filename=orderlist.xls "); 
    header("Content-Transfer-Encoding: binary ");    
    
    print_r($contents);
    exit;
 
?>

Re: Export to xls

Posted: Mon Mar 15, 2010 12:23 pm
by AbraCadaver
This must be an IE feature. It sees that the file is not Excel, it is text/plain, but the extension is .xls, so it complains. You either need to construct a valid Excel file, or try an extension like .csv.

Re: Export to xls

Posted: Mon Mar 15, 2010 2:48 pm
by dreeves
The error is generated by Excel, and pops up using both IE and Firefox. I've tried numerous ways to create an Excel file using PHP and I get this same error message every time. Could it be fixed with something in the header? Could it be caused by an older version of PHP I might be using?

Re: Export to xls

Posted: Mon Mar 15, 2010 3:18 pm
by AbraCadaver
dreeves wrote:The error is generated by Excel, and pops up using both IE and Firefox. I've tried numerous ways to create an Excel file using PHP and I get this same error message every time. Could it be fixed with something in the header? Could it be caused by an older version of PHP I might be using?
It is do to the fact that excel can tell that it is not an Excel native format but it has the xls extension. Even though Excel supports opening a csv (what you are creating), it is not an Excel file. Try changing the extension to .csv.