Export to xls

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
dreeves
Forum Commoner
Posts: 39
Joined: Thu Oct 22, 2009 8:53 am

Export to xls

Post 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;
 
?>
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: Export to xls

Post 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.
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
dreeves
Forum Commoner
Posts: 39
Joined: Thu Oct 22, 2009 8:53 am

Re: Export to xls

Post 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?
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: Export to xls

Post 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.
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
Post Reply