[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 223
  • Last Modified:

PHP Script not triggering Excel Export as expected

I am attempting to build a user defined/initiated export to excel.  The script is working to the point of reflecting the data on the page when run, but it does not trigger the Excel document.

If I place the header information anywhere else I get a


Warning: Cannot modify header information - headers already sent by (output started at /home/loanmod1/domains/ermqc.com/private_html/mgmtSummary_Export.php:21) in /home/loanmod1/domains/ermqc.com/private_html/include/forms/mgmtSummaryExportMarkup.php on line 8


 I'm sure I am doing something silly.  Thank you.
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN http: //www.w3.org/TR/html4/loose.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">
<head>
 <?php
 $filename = "FindingExport" . date('Ymd') . ".xls";

  header("Content-Disposition: attachment; filename=\"$filename\"");
  header("Content-Type: application/vnd.ms-excel");
  ?>
</head>
<body>
    <?php error_reporting(E_ALL);
require_once ("include/userConnect.php"); ?>
 <form enctype="multipart/form-data" method="POST" action="<?php echo $_SERVER['PHP_SELF'].'?'.$_SERVER['QUERY_STRING']; ?>">

 <br/><br/>
        <h3 align="LEFT"><b><u>Management Summary Report</u></b></h3>
    <br/><br/>

     <table id="auditSel" cellspacing="1" cellpadding="0" width="75%" border="2" >
         <tr>
             <th>Select Audit Type</th>
             <th>Select Client</th>
             <th>Period Begin Date</th>
             <th>Period End Date</th>
         </tr>
         <tr>
             <td><select  id="type"  name="type">
 <?php
        $auditQuery = "SELECT * FROM auditType WHERE active='1' ORDER BY auditTypeName ";
        $auditList = mysql_query($auditQuery) or die(mysql_error());
        while($auditRow = mysql_fetch_array($auditList))
            {
            $auditTypeKey = $auditRow['auditTypeKey'];
            $auditTypeName = $auditRow['auditTypeName'];
            echo "<option value=\"$auditTypeKey\">$auditTypeName</option>\n";
            }
            ?>
                 </select>
             </td>
              <td><select id="br" name="br" >
                    <?php
                    $branch = "SELECT * FROM  branch WHERE active='1'
                        ORDER BY branchName ASC";
                $branchList = mysql_query($branch) or die(mysql_error());
                while ($branchRow = mysql_fetch_array($branchList))
                    {
                    $branchKey = $branchRow['branchKey'];
                    $branchName = $branchRow['branchName'];
                    echo "<option value=\"$branchKey\">$branchName</option>\n";

                    }
                    
                        ?>
                </select></td>

             <td> <input class="mainText" id="currbegin" name="currbegin"  type="date" value="<?php if(isset($_POST['currbegin'])) echo $_POST['currbegin'];?>"/></td>
             <td><input class="mainText"  id="currend" name="currend" type="date" value="<?php if(isset($_POST['currend'])) echo $_POST['currend'];?>"/></td>
             
             </tr>
     </table>
    <br/>
    <br/>
    <input type="submit" name="create" value="Export">
   <?php
 
  if(isset($_POST['create'])){

    $br = $_POST['br'];
    $type = $_POST['type'];
    $currbegin = $_POST['currbegin'];
    $currend = $_POST['currend'];
  }
   function cleanData(&$str)
  {
    $str = preg_replace("/\t/", "\\t", $str);
    $str = preg_replace("/\r?\n/", "\\n", $str);
    if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';
  }

  // file name for download


  $flag = false;
  $result = "SELECT loanKey,branchName,trackingDate,lastName,firstName, loanNumber,loanTypeName,
   loanFindingKey,findingCategoryName,CONCAT(findingCategoryNumber,'-',findingKey) AS Code,
CASE WHEN cuS.severitydesc IS NULL THEN stS.severitydesc ELSE cuS.severitydesc END AS currSev,
CASE WHEN findingVerbiage='' THEN verbiageCustom ELSE CONCAT(findingVerbiage,' ',verbiageCustom) END AS red,
CASE WHEN findingVerbiage='' THEN verbiageCustom ELSE CONCAT(findingVerbiage,' ',verbiageCustom) END AS finding
FROM loan
JOIN borrower USING (loanKey)
JOIN audit USING (loanKey)
LEFT JOIN auditType USING (auditTypeKey)
LEFT JOIN occupancy USING (occupancyKey)
LEFT JOIN loanType USING (loanTypeKey)
JOIN branch USING (branchKey)
JOIN loanFinding USING (auditID)
JOIN severity stS ON stS.severityKey=loanFinding.stSeverity
LEFT JOIN severity cuS ON cuS.severityKey=loanFinding.customSeverity
JOIN finding USING (findingKey)
JOIN findingCategory USING (findingCategoryKey)
LEFT JOIN propertyType USING (propertyTypeKey)
WHERE branchKey='63'
AND borrower.type='1'
AND borrower.active='1'
AND loan.active='1'
AND audit.auditTypeKey='10'
AND loanFinding.active='1'
AND audit.trackingDate
BETWEEN '2011-10-03' AND '2011-10-07'
ORDER BY findingCategoryName, findingKey ASC";

  $resultList = mysql_query($result) or die(mysql_error());


  while(false !== ($row = mysql_fetch_assoc($resultList))) {
    if(!$flag) {
      // display field/column names as first row
      echo implode("\t", array_keys($row)) . "\r\n";
      $flag = true;
    }
    array_walk($row, 'cleanData');
    echo implode("\t", array_values($row)) . "\r\n";
  }
?>
   

 
 </form>
</body>

Open in new window

0
rcowen00
Asked:
rcowen00
1 Solution
 
hernst42Commented:
You need to split up the script in two. One Script where the user can enter the data and the second script which sends the header, takes the form-data an generates the excel output.
the action of the form in the first script is the name of the second script.
0
 
rcowen00Author Commented:
That triggered Excel, but I get

 Access denied for user 'xxxxxxxxxxxx' (using password: NO)
0
 
dsmileCommented:
Just check whether your mysql connection is provided with correct host, username, password and DB name
0
 
Ray PaseurCommented:
It's a law of HTTP that all headers must come first and be complete before any browser output.  HTML is browser output. so this construct is doomed.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN http: //www.w3.org/TR/html4/loose.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">
<head>
 <?php
 $filename = "FindingExport" . date('Ymd') . ".xls";

  header("Content-Disposition: attachment; filename=\"$filename\"");
  header("Content-Type: application/vnd.ms-excel");
  ?>
</head>
<body>
...

Here is how I might go about the task.  Forget about triggering Excel directly.   Instead, just create a CSV file (almost universally associated with Excel) and write it to disk.  Then give the client a clickable link to the file.  Easy!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now