0

I've created a program to dynamically create a file. It works fine. When I add in code to prompt the user about where to save the file, it saves, but when I try to open the file I get the error 'Cannot open the file because the file format or file extention is not valid'. When I save it without prompt it opens fine.

The relevant parts of the code are:

if(isset($_POST['esd']))
{
$sqlcust = "SELECT * FROM compliance_customers WHERE Customer_ref = " . $_REQUEST['cust'];
$sqlcustresult = mysqli_query($conn, $sqlcust);
while($row_sqlcustresult = mysqli_fetch_array($sqlcustresult))
{
    $name = $row_sqlcustresult['Customer_name'];
}
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$name.'_'.$date.'.xlsx"');
}



#use php excel to create a spreadsheet of the customers current subscription
if( (isset($_REQUEST['esd'])) && (!isset($noEsd)) )
{
// Set properties for the excel file
$objPHPExcel = new PHPExcel();

$objPHPExcel->getProperties()->setCreator($_SESSION['username']);
$objPHPExcel->getProperties()->setLastModifiedBy($_SESSION['username']);
$objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Customer export_" . $customername);
$objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Customer export_" . $customername);
$objPHPExcel->getProperties()->setDescription($customername . "_" . $date);

$cnt_current_record = 2;

$objPHPExcel->getActiveSheet()->SetCellValue('A1', 'Content Name');
$objPHPExcel->getActiveSheet()->SetCellValue('B1', 'Content Type');
$objPHPExcel->getActiveSheet()->SetCellValue('C1', 'Content Description');

$sql_excelfile = "SELECT * FROM compliance_changes ORDER BY change_type";
$result_sql_excelfile = mysqli_query($conn, $sql_excelfile);
while($row_sql_excelfile = mysqli_fetch_array($result_sql_excelfile))
{
    if(in_array($row_sql_excelfile['change_name'], $excel_array))
    {
        #echo  "<tr class='subscriptiondetails'><td>" .$row_sql_excel['change_name']."</td><td>"   .$row_sql_excel['change_type']."</td><td>". $row_sql_excel['change_description']."</td></tr>";
        #$objPHPExcel->setActiveSheetIndex($cnt_current_record);
        $objPHPExcel->getActiveSheet()->SetCellValue('A' . $cnt_current_record, $row_sql_excelfile['change_name']);
        $objPHPExcel->getActiveSheet()->SetCellValue('B' . $cnt_current_record, $row_sql_excelfile['change_type']);
        $objPHPExcel->getActiveSheet()->SetCellValue('C' . $cnt_current_record, $row_sql_excelfile['change_description']);
        $cnt_current_record++;
    }
}
#rename sheet
$sheet_title = $customername;
$objPHPExcel->getActiveSheet()->setTitle($sheet_title);

#write the xls file
#$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$filename = $customername . "_" . $date . ".xlsx";
#$objWriter->save(str_replace('customerdetail2.php', $filename, __FILE__));

$objWriter->save('php://output');

#echo the file has been written
echo $filename . " has been created";

I'm not overly familiar with PHPexcel, not sure why if I just save it, the files open fine, but when I use $objWriter->save('php://output'); to output the code, it fails. Can anyone help?

Stephen Kennedy
  • 529
  • 5
  • 18
  • http://stackoverflow.com/questions/14252465/phpexcel-file-cannot-open-file-because-the-file-format-or-file-extension-is-not – Len_D Nov 25 '14 at 15:34
  • Hi @Len_D, Thanks for posting that. I had already tried that solution, gave me the same error. – Stephen Kennedy Nov 25 '14 at 15:39
  • Whenever you see this error, the first thing you should do is open the file in a text editor and look for whitespace characters (space, tab, newlines, etc) at the beginning or end of file, or a BOM marker, or any obvious PHP human-readable error messages in the content of the file – Mark Baker Nov 26 '14 at 08:00
  • @MarkBaker Hi Mark, thanks very much for the comment you have certainly highlighted the problem. But I don't know how to fix it. I've realised the export for some reason isn't outputting data, its exporting the webpage. Is this something wrong in my headers? – Stephen Kennedy Nov 26 '14 at 08:46
  • No, it probably means that you're also outputting the page markup elsewhere in your script.... simply changing the headers won't affect that, you need to make sure that nothing else is output/echoed/printed when you're trying to send a file to the client browser – Mark Baker Nov 26 '14 at 10:35
  • That includes __not__ doing `echo $filename . " has been created";` after saving the file to `php://output` – Mark Baker Nov 26 '14 at 10:36
  • Thanks @MarkBaker for the answer, that worked. I'll post this as the answer, I bow to your superior knowledge of all things php. – Stephen Kennedy Dec 05 '14 at 14:26

1 Answers1

0

Thanks to @markbaker for the solution.

I had PHP code echoing code on the same page as php://output. This causes the stream to be overwritten with the HTML/PHP from the page which I hadn't been aware of. A good way to spot this if you encounter the same issue, is open the file with a text editor, if your stream contains html instead of your output information, this could be your issue too.

I changed this so the form when clicked posted to another page, containing only the code for the stream I wanted to output, and this worked perfectly fine.

I hope this is helpful, Stephen.

Stephen Kennedy
  • 529
  • 5
  • 18