I have a website that I created for reports. There are basically 3 reports. I have a button on each page that calls another PHP page that will download the results of the query that fills the page into a CSV file. This works fine on one page, but on the other two it gives me an error when trying to open. It says:
The file format and extension of 'FileName' don't match. the file could be corrupted or unsafe. Unless you trust its source, don't open it. Do you want to open it anyway?
I click yes, then get this:
Excel has detected that 'FileName' is a SYLK file, but cannot load it. Either the file has errors or it is not a SYLK file format. Click OK to try to open the file in a different format.
I click OK and it opens fine.
Where as on the other page it just opens.
Here's most of the ExportToExcel.php
switch ($_POST['ExportToExcel'])
{
case "QDef":
$tsql = "select Id,QSrc,QName,QDef,isActive,RunReport,FilePath from pmdb.v_QDefs order by Id";
$hsql = "select Headings from TableHeadings where TableName = 'v_QDefs' and Headings != 'Edit' order by ID";
break;
case "TableUpdates":
$tsql = "select ID, TableName, UpdateDate from pmdb.TableUpdates order by UpdateDate";
$hsql = "select Headings from TableHeadings where TableName = 'TableUpdates' and Headings != 'Edit' order by ID";
break;
}
$filename = $_POST['ExportToExcel'];
header("Content-Type: application/x-csv");
header("Content-Disposition: attachment; filename=$filename.csv");
//define separators (defines columns in excel)
$sep = ",";
$br = "
"; //line break
$getHeadings = $conn->query($hsql);
$rHeadings = $getHeadings->fetchALL(PDO::FETCH_ASSOC);
$headings = array();
$NumHeadings = count($rHeadings);
for ($i = 0;$i < $NumHeadings; $i++)
{
$headings[] = $rHeadings[$i]["Headings"];
}
//start of printing column names as names of SQL fields
foreach($headings as $Heading => $value)
{
echo "$value" . $sep;
}
//end of printing column names
echo $br; //separate the headers and the data
foreach($conn->query($tsql) as $row)
{
for ($i = 0;$i < $NumHeadings;$i++)
{
$CommentPos = strpos($rHeadings[$i]["Headings"],"comment");
$NewLines = array("
","
","
","");
$UseValue = str_replace($NewLines, " ",$row[$i]);
$UseValue = str_replace('"', "'",$row[$i]);
$pos = strpos($UseValue,",");
if ($CommentPos === FALSE || $pos === FALSE || isset($UseValue))
{
echo '"' . $UseValue . '"' . $sep;
}
else
{
echo $UseValue . $sep . "Not quoted";
}
}
echo $br;
}
I have a include
at the top that has the connection string for connecting to my MSSQL DB, which does work, or there'd be nothing displayed on the page to begin with.
I just con't figure out why the page doesn't work the same way for all reports when they are all calling the page the same way.
EDIT again
I tried several ideas from below and now I have this:
$filename = $_POST['ExportToExcel'] . '.csv';
$Opened = fopen($filename,'w');
header("Content-Type: text/csv; charset=utf-8'");
header("Content-Disposition: attachment; filename=$filename");
fputcsv($Opened,$headings);
foreach($conn->query($tsql) as $row)
{
fputcsv($Opened,$row);
}
fclose($Opened);
Which still gives me a blank spreadsheet. So obviously I'm still doing something wrong?