0

I am generating a excel file my php using the following script.it gives me the following error.What may be the reason for this-

Warning: Cannot modify header information - headers already sent by (output started at C:\xampp\htdocs\geochronology\library\admin\generate-excel.php:45) in C:\xampp\htdocs\geochronology\library\admin\generate-excel.php on line 56

<?php
// Database Connection file
include('includes/config.php');
?>
<table border="1">
<thead>
<tr>
<th>Sr.</th>
<th>User Name</th>
<th>Affiliation</th>
<th>Sample Type</th>
<th>Sample ID</th>
<th>Date</th>
</tr>
</thead>
<?php
// File name
$filename="Sample-summary";
// Fetching data from data base
        $search=$_POST['search2'];
        $option=$_POST['option2'];
        $period=$_POST['period2'];
        $datefrom=$_POST['datefrom2'];
        $dateto=$_POST['dateto2'];
        $dbh = new PDO("mysql:host=".DB_HOST.";dbname=".DB_NAME,DB_USER, DB_PASS,array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'"));


            if($period ==null){
            $sql = "SELECT tbluser.user,tbluser.affiliation,tblfacility.type,tblfacility.sampleid,tblfacility.time,DATE_FORMAT(tblfacility.time, '%d-%m-%y') AS formatted_date 
            FROM tblfacility 
            JOIN tbluser on tbluser.id=tblfacility.user 
            where ".$search." ='".$option."' ";}
            else{
            $sql="SELECT * FROM tblfacility JOIN tbluser on tbluser.id=tblfacility.user where ".$search." ='".$option."' AND time between '".$datefrom."' and '".$dateto."' ";
            }
            $query = $dbh->prepare($sql);
            $query->execute();
            $results=$query->fetchAll(PDO::FETCH_OBJ);
            //echo "<prep>";
            //echo "this is the final";
            print_r($sql);
            $cnt=1;         
            foreach($results as $result){
?>
            <tr>
                <td><?php echo $cnt;?></td>
                <td><?php echo $row['user'];?></td>
                <td><?php echo $row['affiliation'];?></td>
                <td><?php echo $row['type'];?></td>
                <td><?php echo $row['sampleid'];?></td>
                <td><?php echo $row['time'];?></td>
            </tr>
<?php
$cnt++;
// Genrating Execel  filess
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=".$filename."-Report.xls");
header("Pragma: no-cache");
header("Expires: 0");
            }
            ?>
</table>
M. Eriksson
  • 13,450
  • 4
  • 29
  • 40
  • @MagnusEriksson But answer given in that does not clear . –  Nov 21 '19 at 06:15
  • 1
    What is not clear? The accepted answer starts with: _"No output before sending headers!"_, which is what you're doing. You're outputting the table before you use `headers()`. Just move the headers to be set _before_ you output anything. – M. Eriksson Nov 21 '19 at 06:18
  • Your code is vulnerable to SQL injection. Please read https://stackoverflow.com/a/60496/1839439 – Dharman Nov 21 '19 at 06:28

3 Answers3

1

To directly answer your question, headers must come first in your file. Move all the header settings to be first, before everything else (Except possibly config.php, depending on what you do in that include)

The exact error is caused by your output in line 5. Headers are sent (automatically) as soon as you output anything at all. Once you send output, it is too late to send more headers.

Also note the other comments you've received though, your code is vulnerable and there are better ways to do this.

Gerdofal
  • 91
  • 7
0

easiest way to generate excel file in php is

Put your table in a variable and paste this code at end of file 

//File Name you want to save as
$file_name ="file_name.xls";
header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=$file_name");
//Echo table as below
echo $table_variable;
Abhishek Sharma
  • 135
  • 12
  • The easiest way would probably be to just move the `headers()` to the top of the file. – M. Eriksson Nov 21 '19 at 06:33
  • If you need excel file you should use this method. – Abhishek Sharma Nov 21 '19 at 06:41
  • It doesn't matter what you're outputting. The only thing needed here is to make sure the headers are set before any output. At least that's the issue the OP asks about. To be honest, if they really need an Excel file, they should rather use [PHPSpreadsheet](https://github.com/PHPOffice/PhpSpreadsheet). – M. Eriksson Nov 21 '19 at 06:50
-1

I understand you want to generate an excel file. There is a very simple way to do it using this PHP function: https://www.php.net/manual/en/function.fputcsv.php

So, as you can see you'd need to open the file: $fp = fopen('file.csv', 'w');

Then write row by row, which, in your code is the foreach that goes through results:

...
foreach ($row in $rows) {
   fputcsv($fp, $row);
}

And finally, expose that file to the user, note it won't be an xls file, but it will be a .csv file, which can be opened in excel anyways.

This might help: How to create and download a csv file from php script?

Bruno Smaldone
  • 208
  • 3
  • 11