Excel export problem in IE

问题:

I have this script to export mysql data to excel. I have tried everything but I am not able to get this script to work for IE. The script downloads the data using FireFox or Chrome but IE fails and says:

Internet Explorer cannot download list_view.php from www.mysite.com. (this is the site on which the file resides).

Internet Explorer was not able to open this Internet site. The requested site is either unavailable or cannot be found. Please try again later.

here is the code that I am using:

$sql = "SELECT...."
     $result = mysql_query($sql) 
or die("\nError Retrieving Records.<hr />sql: $sql<hr />ERROR:".mysql_error()); 

if(mysql_num_rows($result) > 0){

    // build a filename that excel will use to save it as
    $filename=$name."_".dateFromDB($_GET['confDate']).".xls";

    // headers to force the open/save into Excel

    header("Content-Type: application/vnd.ms-excel");
    header("Content-Disposition: attachment; filename=$filename");
    header("Pragma: no-cache");
    header("Expires: 0");}?>
        <table>     
    <thead>
    <tr>
        <th>address</th>
        <th>phone</th>
        <th>email</th>  
        <th>status</th>
    </tr>
    </thead>
    <tbody>
    <?php // loop over items
    while ($row = mysql_fetch_array ($result, MYSQL_ASSOC)) { ?>
        <tr>
            <td><?=$row['address'];?></td>
            <td><?=$row['phone'];?></td>
            <td><?=$row['email'];?></td>
            <td><?=$row['status'];?></td>
        </tr><?php
    }?>
    </tbody>
    </table><?php 
    exit(); // exit or it will throw an error when it tries to continue        

I know there maybe some people suggesting not to use IE, but the person who is actually using the export function do not have access to different browser.

EDIT:

I forgot to mention that I am running a SSL(https). If i turn SSL off everything is working fine on IE, but the moment SSL is on the IE shows an error.

Any ideas how to make it work under SSL?

回答1:

Headers

Headers can be tricky with IE. Here's the thing, you should set it not to cache like this:

 ini_set('zlib.output_compression','Off');
        header('Pragma: public');
        header("Expires: Sat, 26 Jul 1997 05:00:00 GMT");                  // Date in the past
        //header('Last-Modified: '.gmdate('D, d M Y H:i:s') . ' GMT');
        header('Cache-Control: no-store, no-cache, must-revalidate');     // HTTP/1.1
        header('Cache-Control: pre-check=0, post-check=0, max-age=0');    // HTTP/1.1
        header ("Pragma: no-cache");
        header("Expires: 0");

Now there can be an issue with that if your server time is not set to the right timezone, this may actually have the opposite effect and force IE to cache it. Make sure your timezone is set for the correct timezone you are in. Is it a shared server? do you have ssh access??

Excel Headers

Now what you need is to provide the set of headers for IE

header('Content-Transfer-Encoding: none');
        header('Content-Type: application/vnd.ms-excel;');                 // This should work for IE & Opera
        header("Content-type: application/x-msexcel");                    // This should work for the rest
        header('Content-Disposition: attachment; filename="'.basename($filename).'"');

Try that, hopefully it should work.

回答2:

Instead of outputting everything as a table, can you just output it as CSV, either manually formatting it, or something like implode('","', $row)? Leave the content-type header the way it is. I've had success doing that, even in IE.