views:

19

answers:

1

I have a CSV export script (enrdata_arch.php) which calls information from an existing database and exports it into CSV format. For some reason however, the script returns the following error ONLY in Internet Explorer:

Microsoft Office Excel cannot access the file 'https://www.domain.com/admin/enrdata_arch.php'. There are several possible reasons:

  • the filename or path does not exist
  • the file is being used by another program
  • the workbook you are trying to save has the same name as currently open workbook

**

The original script is posted below:

**

<?php

$host = 'localhost';
$user = 'username';
$pass = 'password';
$db = 'db_name';
$table = 'table_name';
$archive = 'archive_name';
$file = 'export';

$link = mysql_connect($host, $user, $pass) or die("Can not connect.".mysql_error());
mysql_select_db($db) or die("Can not connect.");

$result = mysql_query("SHOW COLUMNS FROM ".$table."");
$i = 0;
if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_assoc($result)) {
$csv_output .= $row['Field'].", ";
$i++;
}
}
$csv_output .= "\n";

$values = mysql_query("SELECT * FROM ".$table."");
while ($rowr = mysql_fetch_row($values)) {
for ($j=0;$j<$i;$j++) {
$csv_output .= '"'.$rowr[$j].'",'; 
}
$csv_output .= "\n";
}

$filename = $file."_".date("Y-m-d",time());
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header( "Content-disposition: filename=".$filename.".csv");
print $csv_output;

//transfers old data to archive
$transfer = mysql_query('INSERT INTO '.$archive.' SELECT * FROM '.$table) or die(mysql_error());

//empties existing table
$query = mysql_query('TRUNCATE TABLE '.$table) or die(mysql_error());

mysql_close($link);
exit;
?>

It almost seems as if the script is trying to save and open a PHP file only when using IE. Any ideas?

+1  A: 

The headings for SSL is a known problem with IE

I use

header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0

and it's the 'Pragma: public that's the key for IE/ssl

Content type for a CSV file is

text/csv

or

text/plain

rather than

application/vnd.ms-excel

which is specifically MS Excel .xls speardsheets

Mark Baker
@Mark Baker - Thank you for your assistance. Would you mind explaining your header additions? What is the overall purpose of the expired date/last modified date?
JM4
Those date headers are caching headers. If the browser has previously sent a similar request to the server, then it may not send the request again because it believes it already has the file in cache. These headers ensure that the browser will not cache the request, so it will always make the request from the server.
Mark Baker