?
Solved

PHP Excel stream not working in Firefox or IE

Posted on 2009-04-23
9
Medium Priority
?
638 Views
Last Modified: 2012-05-06
The following code is outputting to html but it is not streaming into Excel for download, what am i doing wrong? I've tried all kinds of headers that claim to work and none do! I don't want output to screen, I want Excel download!
<?
 
 
function dollar_format($amount) {
   $new_amount = "$".$amount;
   return $new_amount;
   }
 
$timestamp=date("Y-m-d");
 
$data = "
<style>
.header { font-size: 18px; font-weight: bold }
.date_range { font-size: 13px; font-weight: normal }
.field_name { font-size: 12px; font-weight: bold }
.data { font-size: 12px; font-weight: normal }
</style>
";
 
$data.="<table align=center border=1px style=\"font-family: arial, helvetica\">
<tr align=\"left\" class=\"header\"><td colspan=\"13\">Daily Fundings For XYZ Company</td></tr>
<tr align=\"left\" class=\"header\"><td colspan=\"13\">Fund Date: $fund_date</td></tr>";
 
$data.="
<tr><td colspan=\"16\">&nbsp;</td></tr>
<tr><td colspan=\"16\">&nbsp;</td></tr>
<tr height=\"40\" class=\"field_name\">
<td align=\"center\"><b>Acct #</b></td>
<td align=\"center\"><b>Patient</b></td>
<td align=\"center\"><b>Provider</b></td>
<td align=\"center\"><b>Charge Date</b></td>
<td align=\"center\"><b>Fund Date</b></td>
<td align=\"center\"><b>Charge Amt</b></td>
<td align=\"center\"><b>Promo</b></td>
<td align=\"center\"><b>Promo Rate</b></td>
<td align=\"center\"><b>Promo Amt</b></td>
<td align=\"center\"><b>Subtotal Amt</b></td>
<td align=\"center\"><b>Provider Discount</b></td>
<td align=\"center\"><b>Provider Payment</b></td>
<td align=\"center\"><b>Fund Method</b></td>
</tr>
";
		
	
$sql="SELECT
credit_line.amount as credit_line, 
contacts.contact_id as contact_id,
contacts.first_names as first_names,
contacts.last_name as last_name,
companies.company_id as company_id,
companies.company_name as company_name,
companies.rating_id as funding_method,
lenders.lender_short_name as lender_short_name,
charges.charge_id as charge_id,
charges.charge_auth_date as charge_auth_date,
charges.charge_auth_number as charge_auth_number,
charges.fund_date as fund_date,
charges.account_number as account_number,
charges.charge_auth_amount as charge_auth_amount,
charges.promo_term as promo_term,
charges.comment as comment,
charges.doctor_enrolled as doctor_enrolled,
charges.lender_amount as lender_amount,
charges.xyz_company_amount as xyz_company_amount,
charges.sub_total_amount as sub_total_amount,
charges.provider_discount as provider_discount,
charges.promo_rate as promo_rate,
charges.promo_amount as promo_amount,
charges.provider_payment as provider_payment,
charges.charge_auth_form as charge_auth_form,
charges.drivers_license_copy as drivers_license_copy,
charges.lender_id as lender_id,
charges.fund_notice_sent_time as fund_notice_sent_time,
charges.fund_notice_sent_via as fund_notice_sent_via,
charges.fund_notice_file as fund_notice_file,
charges.is_funded as is_funded,
users.username as username
FROM charges
LEFT JOIN contacts on contacts.contact_id = charges.contact_id
LEFT JOIN lenders on lenders.lender_id = charges.lender_id
LEFT JOIN opportunities on opportunities.opportunity_id = charges.opportunity_id
LEFT JOIN companies on companies.company_id = opportunities.company_id
LEFT JOIN users on users.user_id = charges.created_by
LEFT JOIN opportunity_amounts as credit_line on (credit_line.opportunity_id = opportunities.opportunity_id and credit_line.opportunity_amount_type_id = 2)
WHERE fund_date = '" . $fund_date . "'
AND charges.lender_id = '1'
AND is_funded = '1'
ORDER BY company_id ASC
"; 
 
$result = mysql_query($sql) or die("Failed Query of " . $sql); 
 
while($row=mysql_fetch_array($result)) {
 
$credit_line = $row["credit_line"];
$credit_line = number_format($credit_line,2);
$credit_line = dollar_format($credit_line,2);
if ($credit_line == "") { $credit_line = "<font color=\"red\"><b>UNKNOWN</b></font>"; }
 
$charge_auth_amount = $row["charge_auth_amount"];
$charge_auth_amount = number_format($charge_auth_amount,2);
$charge_auth_amount = dollar_format($charge_auth_amount,2);
 
$charge_auth_date = $row["charge_auth_date"];
$lender_short_name = $row["lender_short_name"];
$lender_id = $row["lender_id"];
$contact_id = $row["contact_id"];
$first_names = $row["first_names"];
$last_name = $row["last_name"];
$company_id = $row["company_id"];
$company_name = $row["company_name"];
$charge_id = $row["charge_id"];
$charge_auth_number = $row["charge_auth_number"];
$account_number = $row["account_number"];
$comment = $row["comment"];
$doctor_enrolled = $row["doctor_enrolled"];
$funding_method = $row["funding_method"];
$fund_notice_sent_time = $row["fund_notice_sent_time"];
$fund_notice_sent_via = $row["fund_notice_sent_via"];
$fund_notice_file = $row["fund_notice_file"];
 
$username = $row["username"];
$program = $row["program"];
 
$lender_amount = $row["lender_amount"];
$lender_amount = number_format($lender_amount,2);
$lender_amount = dollar_format($lender_amount,2);
 
$xyz_company_amount = $row["xyz_company_amount"];
$xyz_company_amount = number_format($xyz_company_amount,2);
$xyz_company_amount = dollar_format($xyz_company_amount,2);
 
$sub_total_amount = $row["sub_total_amount"];
$sub_total_amount = number_format($sub_total_amount,2);
$sub_total_amount = dollar_format($sub_total_amount,2);
 
$promo_amount = $row["promo_amount"];
$promo_amount = number_format($promo_amount,2);
$promo_amount = dollar_format($promo_amount,2);
 
$charge_auth_form = $row["charge_auth_form"];
if ($charge_auth_form == 0) { $charge_auth_form = "No"; } elseif ($charge_auth_form == 1) { $charge_auth_form = "Yes"; }
 
$drivers_license_copy = $row["drivers_license_copy"];
if ($drivers_license_copy == 0) { $drivers_license_copy = "No"; } elseif ($drivers_license_copy == 1) { $drivers_license_copy = "Yes"; }
 
$funding_method = $row["funding_method"];
if ($funding_method == 1) { $funding_method = "ACH"; } elseif ($funding_method == 2) { $funding_method = "CHECK"; } elseif ($funding_method == "" || $funding_method == 0) { $funding_method = "<font color=\"Red\"><b>No record</b></font>"; }
 
$provider_discount = $row["provider_discount"];
$provider_discount = number_format($provider_discount, 4, '.', '');
 
$provider_payment = $row["provider_payment"];
$provider_payment = number_format($provider_payment,2);
$provider_payment = dollar_format($provider_payment,2);
 
$fund_date = $row["fund_date"];
if ($fund_date == "") { $fund_date = "--"; }
 
$is_funded = $row["is_funded"];
if ($is_funded == 0) { $is_funded_notice = "<font color=\"Red\"><b>NO</b></font>"; } elseif ($is_funded == 1) { $is_funded_notice = "<font color=\"green\"><b>YES</b></font>"; }
 
$promo_term = $row["promo_term"];
if ($promo_term == 0 || $promo_term == "") { 
	  $promo_term = "None"; 
} elseif ($promo_term == 1) { 
	  $promo_term = "3 mos"; 
} elseif ($promo_term == 2) { 
	  $promo_term = "6 mos"; 
} elseif ($promo_term == 3) { 
	  $promo_term = "12 mos"; 
} elseif ($promo_term == 4) { 
	  $promo_term = "18 mos";
}
 
 
$promo_rate = $row["promo_rate"];
$promo_rate = number_format($promo_rate, 4, '.', '');
if ($promo_rate == 0 || $promo_rate == "") { $promo_rate = "0.0000"; }
 
$provider = "$company_name (#$company_id)";
$patient = "$first_names $last_name (#$contact_id)";
 
if ($provider_discount == "") { $provider_discount = "--"; }
 
 
$data.= "<tr class=\"data\">
<td align=\"center\" width=\"200\">#$account_number</td>
<td align=\"center\">$patient</td>
<td align=\"center\">$provider</td>
<td align=\"center\">$charge_auth_date</td>
<td align=\"center\">$fund_date</td>
<td align=\"right\">$charge_auth_amount</td>
<td align=\"center\">$promo_term</td>
<td align=\"right\">$promo_rate</td>
<td align=\"right\">$promo_amount</td>
<td align=\"right\">$sub_total_amount</td>
<td align=\"right\">$provider_discount</td>
<td align=\"right\">$provider_payment</td>
<td align=\"center\">$funding_method</td>
</tr>
";
 
}
 
 
$sql="
SELECT 
SUM(credit_line.amount) as total_credit_line,
SUM(charges.charge_auth_amount) as total_charge_auth_amount,
SUM(charges.sub_total_amount) as total_sub_total_amount,
SUM(charges.promo_amount) as total_promo_amount,
SUM(charges.provider_payment) as total_provider_payment
FROM charges
LEFT JOIN contacts on contacts.contact_id = charges.contact_id
LEFT JOIN lenders on lenders.lender_id = charges.lender_id
LEFT JOIN opportunities on opportunities.opportunity_id = charges.opportunity_id
LEFT JOIN companies on companies.company_id = opportunities.company_id
LEFT JOIN opportunity_amounts as credit_line on (credit_line.opportunity_id = opportunities.opportunity_id and credit_line.opportunity_amount_type_id = 2)
WHERE fund_date = '" . $fund_date . "'
AND charges.lender_id = '1'
AND is_funded = '1'
ORDER BY charges.company_id ASC
"; 
 
$result = mysql_query($sql) or die("Failed Query of " . $sql); 
$row = mysql_fetch_array($result);
	
$total_credit_line = $row['total_credit_line'];
$total_charge_auth_amount = $row['total_charge_auth_amount'];
$total_sub_total_amount = $row['total_sub_total_amount'];
$total_promo_amount = $row['total_promo_amount'];
$total_provider_payment = $row['total_provider_payment'];
 
 
$total_credit_line = number_format($total_credit_line,2);
$total_credit_line = dollar_format($total_credit_line,2);
$total_charge_auth_amount = number_format($total_charge_auth_amount,2);
$total_charge_auth_amount = dollar_format($total_charge_auth_amount,2);
$total_sub_total_amount = number_format($total_sub_total_amount,2);
$total_sub_total_amount = dollar_format($total_sub_total_amount,2);
$total_broker_amount = number_format($total_broker_amount,2);
$total_broker_amount = dollar_format($total_broker_amount,2);
$total_promo_amount = number_format($total_promo_amount,2);
$total_promo_amount = dollar_format($total_promo_amount,2);
$total_provider_payment = number_format($total_provider_payment,2);
$total_provider_payment = dollar_format($total_provider_payment,2);
 
 
$data.= "
<tr class=\"data\" style=\"background-color: #ccc\">
<td></td><td></td><td></td><td></td>
<td class=\"field_name\" align=\"right\">Totals*</td>
<td align=\"right\">$total_charge_auth_amount</td>
<td></td>
<td></td>
<td align=\"right\">$total_promo_amount</td>
<td align=\"right\">$total_sub_total_amount</td>
<td></td>
<td align=\"right\">$total_provider_payment</td>
<td></td>
</tr>
</table>
";
        		
$timestamp=date("Y-m-d");
 
header ("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
header ("Last-Modified: " . gmdate("D,d M YH:i:s") . " GMT");
header ("Cache-Control: no-cache, must-revalidate");    
header ("Pragma: no-cache");    
header ('Content-type: application/x-msexcel');
header ("Content-Disposition: attachment; filename=funding_report_$timestamp.csv" ); 
header ("Content-Description: PHP Generated Data" ); 
    
echo $data; 
    
mysql_close($conn);	
exit();
 
}
 
?>

Open in new window

0
Comment
Question by:pdheady
  • 6
  • 3
9 Comments
 
LVL 6

Accepted Solution

by:
basic612 earned 2000 total points
ID: 24220562
I use the following headers to output CSV for Excel.

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


Does this help?

I've attached modified script below.
<?
 
 
function dollar_format($amount) {
   $new_amount = "$".$amount;
   return $new_amount;
   }
 
$timestamp=date("Y-m-d");
 
$data = "
<style>
.header { font-size: 18px; font-weight: bold }
.date_range { font-size: 13px; font-weight: normal }
.field_name { font-size: 12px; font-weight: bold }
.data { font-size: 12px; font-weight: normal }
</style>
";
 
$data.="<table align=center border=1px style=\"font-family: arial, helvetica\">
<tr align=\"left\" class=\"header\"><td colspan=\"13\">Daily Fundings For XYZ Company</td></tr>
<tr align=\"left\" class=\"header\"><td colspan=\"13\">Fund Date: $fund_date</td></tr>";
 
$data.="
<tr><td colspan=\"16\">&nbsp;</td></tr>
<tr><td colspan=\"16\">&nbsp;</td></tr>
<tr height=\"40\" class=\"field_name\">
<td align=\"center\"><b>Acct #</b></td>
<td align=\"center\"><b>Patient</b></td>
<td align=\"center\"><b>Provider</b></td>
<td align=\"center\"><b>Charge Date</b></td>
<td align=\"center\"><b>Fund Date</b></td>
<td align=\"center\"><b>Charge Amt</b></td>
<td align=\"center\"><b>Promo</b></td>
<td align=\"center\"><b>Promo Rate</b></td>
<td align=\"center\"><b>Promo Amt</b></td>
<td align=\"center\"><b>Subtotal Amt</b></td>
<td align=\"center\"><b>Provider Discount</b></td>
<td align=\"center\"><b>Provider Payment</b></td>
<td align=\"center\"><b>Fund Method</b></td>
</tr>
";
		
	
$sql="SELECT
credit_line.amount as credit_line, 
contacts.contact_id as contact_id,
contacts.first_names as first_names,
contacts.last_name as last_name,
companies.company_id as company_id,
companies.company_name as company_name,
companies.rating_id as funding_method,
lenders.lender_short_name as lender_short_name,
charges.charge_id as charge_id,
charges.charge_auth_date as charge_auth_date,
charges.charge_auth_number as charge_auth_number,
charges.fund_date as fund_date,
charges.account_number as account_number,
charges.charge_auth_amount as charge_auth_amount,
charges.promo_term as promo_term,
charges.comment as comment,
charges.doctor_enrolled as doctor_enrolled,
charges.lender_amount as lender_amount,
charges.xyz_company_amount as xyz_company_amount,
charges.sub_total_amount as sub_total_amount,
charges.provider_discount as provider_discount,
charges.promo_rate as promo_rate,
charges.promo_amount as promo_amount,
charges.provider_payment as provider_payment,
charges.charge_auth_form as charge_auth_form,
charges.drivers_license_copy as drivers_license_copy,
charges.lender_id as lender_id,
charges.fund_notice_sent_time as fund_notice_sent_time,
charges.fund_notice_sent_via as fund_notice_sent_via,
charges.fund_notice_file as fund_notice_file,
charges.is_funded as is_funded,
users.username as username
FROM charges
LEFT JOIN contacts on contacts.contact_id = charges.contact_id
LEFT JOIN lenders on lenders.lender_id = charges.lender_id
LEFT JOIN opportunities on opportunities.opportunity_id = charges.opportunity_id
LEFT JOIN companies on companies.company_id = opportunities.company_id
LEFT JOIN users on users.user_id = charges.created_by
LEFT JOIN opportunity_amounts as credit_line on (credit_line.opportunity_id = opportunities.opportunity_id and credit_line.opportunity_amount_type_id = 2)
WHERE fund_date = '" . $fund_date . "'
AND charges.lender_id = '1'
AND is_funded = '1'
ORDER BY company_id ASC
"; 
 
$result = mysql_query($sql) or die("Failed Query of " . $sql); 
 
while($row=mysql_fetch_array($result)) {
 
$credit_line = $row["credit_line"];
$credit_line = number_format($credit_line,2);
$credit_line = dollar_format($credit_line,2);
if ($credit_line == "") { $credit_line = "<font color=\"red\"><b>UNKNOWN</b></font>"; }
 
$charge_auth_amount = $row["charge_auth_amount"];
$charge_auth_amount = number_format($charge_auth_amount,2);
$charge_auth_amount = dollar_format($charge_auth_amount,2);
 
$charge_auth_date = $row["charge_auth_date"];
$lender_short_name = $row["lender_short_name"];
$lender_id = $row["lender_id"];
$contact_id = $row["contact_id"];
$first_names = $row["first_names"];
$last_name = $row["last_name"];
$company_id = $row["company_id"];
$company_name = $row["company_name"];
$charge_id = $row["charge_id"];
$charge_auth_number = $row["charge_auth_number"];
$account_number = $row["account_number"];
$comment = $row["comment"];
$doctor_enrolled = $row["doctor_enrolled"];
$funding_method = $row["funding_method"];
$fund_notice_sent_time = $row["fund_notice_sent_time"];
$fund_notice_sent_via = $row["fund_notice_sent_via"];
$fund_notice_file = $row["fund_notice_file"];
 
$username = $row["username"];
$program = $row["program"];
 
$lender_amount = $row["lender_amount"];
$lender_amount = number_format($lender_amount,2);
$lender_amount = dollar_format($lender_amount,2);
 
$xyz_company_amount = $row["xyz_company_amount"];
$xyz_company_amount = number_format($xyz_company_amount,2);
$xyz_company_amount = dollar_format($xyz_company_amount,2);
 
$sub_total_amount = $row["sub_total_amount"];
$sub_total_amount = number_format($sub_total_amount,2);
$sub_total_amount = dollar_format($sub_total_amount,2);
 
$promo_amount = $row["promo_amount"];
$promo_amount = number_format($promo_amount,2);
$promo_amount = dollar_format($promo_amount,2);
 
$charge_auth_form = $row["charge_auth_form"];
if ($charge_auth_form == 0) { $charge_auth_form = "No"; } elseif ($charge_auth_form == 1) { $charge_auth_form = "Yes"; }
 
$drivers_license_copy = $row["drivers_license_copy"];
if ($drivers_license_copy == 0) { $drivers_license_copy = "No"; } elseif ($drivers_license_copy == 1) { $drivers_license_copy = "Yes"; }
 
$funding_method = $row["funding_method"];
if ($funding_method == 1) { $funding_method = "ACH"; } elseif ($funding_method == 2) { $funding_method = "CHECK"; } elseif ($funding_method == "" || $funding_method == 0) { $funding_method = "<font color=\"Red\"><b>No record</b></font>"; }
 
$provider_discount = $row["provider_discount"];
$provider_discount = number_format($provider_discount, 4, '.', '');
 
$provider_payment = $row["provider_payment"];
$provider_payment = number_format($provider_payment,2);
$provider_payment = dollar_format($provider_payment,2);
 
$fund_date = $row["fund_date"];
if ($fund_date == "") { $fund_date = "--"; }
 
$is_funded = $row["is_funded"];
if ($is_funded == 0) { $is_funded_notice = "<font color=\"Red\"><b>NO</b></font>"; } elseif ($is_funded == 1) { $is_funded_notice = "<font color=\"green\"><b>YES</b></font>"; }
 
$promo_term = $row["promo_term"];
if ($promo_term == 0 || $promo_term == "") { 
	  $promo_term = "None"; 
} elseif ($promo_term == 1) { 
	  $promo_term = "3 mos"; 
} elseif ($promo_term == 2) { 
	  $promo_term = "6 mos"; 
} elseif ($promo_term == 3) { 
	  $promo_term = "12 mos"; 
} elseif ($promo_term == 4) { 
	  $promo_term = "18 mos";
}
 
 
$promo_rate = $row["promo_rate"];
$promo_rate = number_format($promo_rate, 4, '.', '');
if ($promo_rate == 0 || $promo_rate == "") { $promo_rate = "0.0000"; }
 
$provider = "$company_name (#$company_id)";
$patient = "$first_names $last_name (#$contact_id)";
 
if ($provider_discount == "") { $provider_discount = "--"; }
 
 
$data.= "<tr class=\"data\">
<td align=\"center\" width=\"200\">#$account_number</td>
<td align=\"center\">$patient</td>
<td align=\"center\">$provider</td>
<td align=\"center\">$charge_auth_date</td>
<td align=\"center\">$fund_date</td>
<td align=\"right\">$charge_auth_amount</td>
<td align=\"center\">$promo_term</td>
<td align=\"right\">$promo_rate</td>
<td align=\"right\">$promo_amount</td>
<td align=\"right\">$sub_total_amount</td>
<td align=\"right\">$provider_discount</td>
<td align=\"right\">$provider_payment</td>
<td align=\"center\">$funding_method</td>
</tr>
";
 
}
 
 
$sql="
SELECT 
SUM(credit_line.amount) as total_credit_line,
SUM(charges.charge_auth_amount) as total_charge_auth_amount,
SUM(charges.sub_total_amount) as total_sub_total_amount,
SUM(charges.promo_amount) as total_promo_amount,
SUM(charges.provider_payment) as total_provider_payment
FROM charges
LEFT JOIN contacts on contacts.contact_id = charges.contact_id
LEFT JOIN lenders on lenders.lender_id = charges.lender_id
LEFT JOIN opportunities on opportunities.opportunity_id = charges.opportunity_id
LEFT JOIN companies on companies.company_id = opportunities.company_id
LEFT JOIN opportunity_amounts as credit_line on (credit_line.opportunity_id = opportunities.opportunity_id and credit_line.opportunity_amount_type_id = 2)
WHERE fund_date = '" . $fund_date . "'
AND charges.lender_id = '1'
AND is_funded = '1'
ORDER BY charges.company_id ASC
"; 
 
$result = mysql_query($sql) or die("Failed Query of " . $sql); 
$row = mysql_fetch_array($result);
	
$total_credit_line = $row['total_credit_line'];
$total_charge_auth_amount = $row['total_charge_auth_amount'];
$total_sub_total_amount = $row['total_sub_total_amount'];
$total_promo_amount = $row['total_promo_amount'];
$total_provider_payment = $row['total_provider_payment'];
 
 
$total_credit_line = number_format($total_credit_line,2);
$total_credit_line = dollar_format($total_credit_line,2);
$total_charge_auth_amount = number_format($total_charge_auth_amount,2);
$total_charge_auth_amount = dollar_format($total_charge_auth_amount,2);
$total_sub_total_amount = number_format($total_sub_total_amount,2);
$total_sub_total_amount = dollar_format($total_sub_total_amount,2);
$total_broker_amount = number_format($total_broker_amount,2);
$total_broker_amount = dollar_format($total_broker_amount,2);
$total_promo_amount = number_format($total_promo_amount,2);
$total_promo_amount = dollar_format($total_promo_amount,2);
$total_provider_payment = number_format($total_provider_payment,2);
$total_provider_payment = dollar_format($total_provider_payment,2);
 
 
$data.= "
<tr class=\"data\" style=\"background-color: #ccc\">
<td></td><td></td><td></td><td></td>
<td class=\"field_name\" align=\"right\">Totals*</td>
<td align=\"right\">$total_charge_auth_amount</td>
<td></td>
<td></td>
<td align=\"right\">$total_promo_amount</td>
<td align=\"right\">$total_sub_total_amount</td>
<td></td>
<td align=\"right\">$total_provider_payment</td>
<td></td>
</tr>
</table>
";
        		
$timestamp=date("Y-m-d");
 
header ("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
header ("Last-Modified: " . gmdate("D,d M YH:i:s") . " GMT");
header ("Cache-Control: no-cache, must-revalidate");    
header ("Pragma: no-cache");    
// header ('Content-type: application/x-msexcel');
// header ("Content-Disposition: attachment; filename=funding_report_$timestamp.csv" ); 
header ("Content-Description: PHP Generated Data" ); 
header ( "Content-type: application/vnd.ms-excel" );
header ( "Content-disposition: csv" . date ( "Y-m-d" ) . ".csv" );
header ( "Content-disposition: filename=funding_report_$timestamp.csv" );
    
echo $data; 
    
mysql_close($conn);	
exit();
 
}
 
?>

Open in new window

0
 
LVL 1

Author Comment

by:pdheady
ID: 24220702
Doesn't work, still outputs to HTML to browser.
0
 
LVL 6

Expert Comment

by:basic612
ID: 24220733
All I have in my script is these headers:


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

Does the following code work?
<?
 
 
function dollar_format($amount) {
   $new_amount = "$".$amount;
   return $new_amount;
   }
 
$timestamp=date("Y-m-d");
 
$data = "
<style>
.header { font-size: 18px; font-weight: bold }
.date_range { font-size: 13px; font-weight: normal }
.field_name { font-size: 12px; font-weight: bold }
.data { font-size: 12px; font-weight: normal }
</style>
";
 
$data.="<table align=center border=1px style=\"font-family: arial, helvetica\">
<tr align=\"left\" class=\"header\"><td colspan=\"13\">Daily Fundings For XYZ Company</td></tr>
<tr align=\"left\" class=\"header\"><td colspan=\"13\">Fund Date: $fund_date</td></tr>";
 
$data.="
<tr><td colspan=\"16\">&nbsp;</td></tr>
<tr><td colspan=\"16\">&nbsp;</td></tr>
<tr height=\"40\" class=\"field_name\">
<td align=\"center\"><b>Acct #</b></td>
<td align=\"center\"><b>Patient</b></td>
<td align=\"center\"><b>Provider</b></td>
<td align=\"center\"><b>Charge Date</b></td>
<td align=\"center\"><b>Fund Date</b></td>
<td align=\"center\"><b>Charge Amt</b></td>
<td align=\"center\"><b>Promo</b></td>
<td align=\"center\"><b>Promo Rate</b></td>
<td align=\"center\"><b>Promo Amt</b></td>
<td align=\"center\"><b>Subtotal Amt</b></td>
<td align=\"center\"><b>Provider Discount</b></td>
<td align=\"center\"><b>Provider Payment</b></td>
<td align=\"center\"><b>Fund Method</b></td>
</tr>
";
		
	
$sql="SELECT
credit_line.amount as credit_line, 
contacts.contact_id as contact_id,
contacts.first_names as first_names,
contacts.last_name as last_name,
companies.company_id as company_id,
companies.company_name as company_name,
companies.rating_id as funding_method,
lenders.lender_short_name as lender_short_name,
charges.charge_id as charge_id,
charges.charge_auth_date as charge_auth_date,
charges.charge_auth_number as charge_auth_number,
charges.fund_date as fund_date,
charges.account_number as account_number,
charges.charge_auth_amount as charge_auth_amount,
charges.promo_term as promo_term,
charges.comment as comment,
charges.doctor_enrolled as doctor_enrolled,
charges.lender_amount as lender_amount,
charges.xyz_company_amount as xyz_company_amount,
charges.sub_total_amount as sub_total_amount,
charges.provider_discount as provider_discount,
charges.promo_rate as promo_rate,
charges.promo_amount as promo_amount,
charges.provider_payment as provider_payment,
charges.charge_auth_form as charge_auth_form,
charges.drivers_license_copy as drivers_license_copy,
charges.lender_id as lender_id,
charges.fund_notice_sent_time as fund_notice_sent_time,
charges.fund_notice_sent_via as fund_notice_sent_via,
charges.fund_notice_file as fund_notice_file,
charges.is_funded as is_funded,
users.username as username
FROM charges
LEFT JOIN contacts on contacts.contact_id = charges.contact_id
LEFT JOIN lenders on lenders.lender_id = charges.lender_id
LEFT JOIN opportunities on opportunities.opportunity_id = charges.opportunity_id
LEFT JOIN companies on companies.company_id = opportunities.company_id
LEFT JOIN users on users.user_id = charges.created_by
LEFT JOIN opportunity_amounts as credit_line on (credit_line.opportunity_id = opportunities.opportunity_id and credit_line.opportunity_amount_type_id = 2)
WHERE fund_date = '" . $fund_date . "'
AND charges.lender_id = '1'
AND is_funded = '1'
ORDER BY company_id ASC
"; 
 
$result = mysql_query($sql) or die("Failed Query of " . $sql); 
 
while($row=mysql_fetch_array($result)) {
 
$credit_line = $row["credit_line"];
$credit_line = number_format($credit_line,2);
$credit_line = dollar_format($credit_line,2);
if ($credit_line == "") { $credit_line = "<font color=\"red\"><b>UNKNOWN</b></font>"; }
 
$charge_auth_amount = $row["charge_auth_amount"];
$charge_auth_amount = number_format($charge_auth_amount,2);
$charge_auth_amount = dollar_format($charge_auth_amount,2);
 
$charge_auth_date = $row["charge_auth_date"];
$lender_short_name = $row["lender_short_name"];
$lender_id = $row["lender_id"];
$contact_id = $row["contact_id"];
$first_names = $row["first_names"];
$last_name = $row["last_name"];
$company_id = $row["company_id"];
$company_name = $row["company_name"];
$charge_id = $row["charge_id"];
$charge_auth_number = $row["charge_auth_number"];
$account_number = $row["account_number"];
$comment = $row["comment"];
$doctor_enrolled = $row["doctor_enrolled"];
$funding_method = $row["funding_method"];
$fund_notice_sent_time = $row["fund_notice_sent_time"];
$fund_notice_sent_via = $row["fund_notice_sent_via"];
$fund_notice_file = $row["fund_notice_file"];
 
$username = $row["username"];
$program = $row["program"];
 
$lender_amount = $row["lender_amount"];
$lender_amount = number_format($lender_amount,2);
$lender_amount = dollar_format($lender_amount,2);
 
$xyz_company_amount = $row["xyz_company_amount"];
$xyz_company_amount = number_format($xyz_company_amount,2);
$xyz_company_amount = dollar_format($xyz_company_amount,2);
 
$sub_total_amount = $row["sub_total_amount"];
$sub_total_amount = number_format($sub_total_amount,2);
$sub_total_amount = dollar_format($sub_total_amount,2);
 
$promo_amount = $row["promo_amount"];
$promo_amount = number_format($promo_amount,2);
$promo_amount = dollar_format($promo_amount,2);
 
$charge_auth_form = $row["charge_auth_form"];
if ($charge_auth_form == 0) { $charge_auth_form = "No"; } elseif ($charge_auth_form == 1) { $charge_auth_form = "Yes"; }
 
$drivers_license_copy = $row["drivers_license_copy"];
if ($drivers_license_copy == 0) { $drivers_license_copy = "No"; } elseif ($drivers_license_copy == 1) { $drivers_license_copy = "Yes"; }
 
$funding_method = $row["funding_method"];
if ($funding_method == 1) { $funding_method = "ACH"; } elseif ($funding_method == 2) { $funding_method = "CHECK"; } elseif ($funding_method == "" || $funding_method == 0) { $funding_method = "<font color=\"Red\"><b>No record</b></font>"; }
 
$provider_discount = $row["provider_discount"];
$provider_discount = number_format($provider_discount, 4, '.', '');
 
$provider_payment = $row["provider_payment"];
$provider_payment = number_format($provider_payment,2);
$provider_payment = dollar_format($provider_payment,2);
 
$fund_date = $row["fund_date"];
if ($fund_date == "") { $fund_date = "--"; }
 
$is_funded = $row["is_funded"];
if ($is_funded == 0) { $is_funded_notice = "<font color=\"Red\"><b>NO</b></font>"; } elseif ($is_funded == 1) { $is_funded_notice = "<font color=\"green\"><b>YES</b></font>"; }
 
$promo_term = $row["promo_term"];
if ($promo_term == 0 || $promo_term == "") { 
	  $promo_term = "None"; 
} elseif ($promo_term == 1) { 
	  $promo_term = "3 mos"; 
} elseif ($promo_term == 2) { 
	  $promo_term = "6 mos"; 
} elseif ($promo_term == 3) { 
	  $promo_term = "12 mos"; 
} elseif ($promo_term == 4) { 
	  $promo_term = "18 mos";
}
 
 
$promo_rate = $row["promo_rate"];
$promo_rate = number_format($promo_rate, 4, '.', '');
if ($promo_rate == 0 || $promo_rate == "") { $promo_rate = "0.0000"; }
 
$provider = "$company_name (#$company_id)";
$patient = "$first_names $last_name (#$contact_id)";
 
if ($provider_discount == "") { $provider_discount = "--"; }
 
 
$data.= "<tr class=\"data\">
<td align=\"center\" width=\"200\">#$account_number</td>
<td align=\"center\">$patient</td>
<td align=\"center\">$provider</td>
<td align=\"center\">$charge_auth_date</td>
<td align=\"center\">$fund_date</td>
<td align=\"right\">$charge_auth_amount</td>
<td align=\"center\">$promo_term</td>
<td align=\"right\">$promo_rate</td>
<td align=\"right\">$promo_amount</td>
<td align=\"right\">$sub_total_amount</td>
<td align=\"right\">$provider_discount</td>
<td align=\"right\">$provider_payment</td>
<td align=\"center\">$funding_method</td>
</tr>
";
 
}
 
 
$sql="
SELECT 
SUM(credit_line.amount) as total_credit_line,
SUM(charges.charge_auth_amount) as total_charge_auth_amount,
SUM(charges.sub_total_amount) as total_sub_total_amount,
SUM(charges.promo_amount) as total_promo_amount,
SUM(charges.provider_payment) as total_provider_payment
FROM charges
LEFT JOIN contacts on contacts.contact_id = charges.contact_id
LEFT JOIN lenders on lenders.lender_id = charges.lender_id
LEFT JOIN opportunities on opportunities.opportunity_id = charges.opportunity_id
LEFT JOIN companies on companies.company_id = opportunities.company_id
LEFT JOIN opportunity_amounts as credit_line on (credit_line.opportunity_id = opportunities.opportunity_id and credit_line.opportunity_amount_type_id = 2)
WHERE fund_date = '" . $fund_date . "'
AND charges.lender_id = '1'
AND is_funded = '1'
ORDER BY charges.company_id ASC
"; 
 
$result = mysql_query($sql) or die("Failed Query of " . $sql); 
$row = mysql_fetch_array($result);
	
$total_credit_line = $row['total_credit_line'];
$total_charge_auth_amount = $row['total_charge_auth_amount'];
$total_sub_total_amount = $row['total_sub_total_amount'];
$total_promo_amount = $row['total_promo_amount'];
$total_provider_payment = $row['total_provider_payment'];
 
 
$total_credit_line = number_format($total_credit_line,2);
$total_credit_line = dollar_format($total_credit_line,2);
$total_charge_auth_amount = number_format($total_charge_auth_amount,2);
$total_charge_auth_amount = dollar_format($total_charge_auth_amount,2);
$total_sub_total_amount = number_format($total_sub_total_amount,2);
$total_sub_total_amount = dollar_format($total_sub_total_amount,2);
$total_broker_amount = number_format($total_broker_amount,2);
$total_broker_amount = dollar_format($total_broker_amount,2);
$total_promo_amount = number_format($total_promo_amount,2);
$total_promo_amount = dollar_format($total_promo_amount,2);
$total_provider_payment = number_format($total_provider_payment,2);
$total_provider_payment = dollar_format($total_provider_payment,2);
 
 
$data.= "
<tr class=\"data\" style=\"background-color: #ccc\">
<td></td><td></td><td></td><td></td>
<td class=\"field_name\" align=\"right\">Totals*</td>
<td align=\"right\">$total_charge_auth_amount</td>
<td></td>
<td></td>
<td align=\"right\">$total_promo_amount</td>
<td align=\"right\">$total_sub_total_amount</td>
<td></td>
<td align=\"right\">$total_provider_payment</td>
<td></td>
</tr>
</table>
";
        		
$timestamp=date("Y-m-d");
 
// header ("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
// header ("Last-Modified: " . gmdate("D,d M YH:i:s") . " GMT");
// header ("Cache-Control: no-cache, must-revalidate");    
// header ("Pragma: no-cache");    
// header ('Content-type: application/x-msexcel');
// header ("Content-Disposition: attachment; filename=funding_report_$timestamp.csv" ); 
header ( "Content-type: application/vnd.ms-excel" );
header ( "Content-disposition: csv" . date ( "Y-m-d" ) . ".csv" );
header ( "Content-disposition: filename=funding_report_$timestamp.csv" );
    
echo $data; 
    
mysql_close($conn);	
exit();
 
}
 
?>

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Expert Comment

by:basic612
ID: 24220739
Also can I suggest you clear your cache / restart your browser before trying again.

Another good thing to test in Firefox is to use the web developer toolbar to disable your cache:

https://addons.mozilla.org/en-US/firefox/addon/60
0
 
LVL 1

Author Comment

by:pdheady
ID: 24220835
Did that, no luck.

Still outputting to browser.

:(
0
 
LVL 6

Expert Comment

by:basic612
ID: 24220862
Do you have error reporting on? I wonder if your script is outputting something to the browser that is preventing your file type headers from working.

Try this at the top of your script:

 error_reporting(E_ALL);
 ini_set("display_errors", 1);
0
 
LVL 1

Author Comment

by:pdheady
ID: 24220864
Got it working. Made a silly mistake.

I had an include before the excel headers that was loading the html headers.

I moved above and it works.

Thanks for assistance.

Your solution based on the question asked is correct answer because I did not include that piece of info.
0
 
LVL 6

Expert Comment

by:basic612
ID: 24220874
You could also try this test script to see if your headers / file type settings are the problem, or if the problem is somewhere else in the script. Be sure that there are no blank lines at the end or beginning of your script also - these can interfere with your headers.
<?php 
 
error_reporting(E_ALL);
ini_set("display_errors", 1);
 
header ( "Content-type: application/vnd.ms-excel" );
header ( "Content-disposition: csv" . date ( "Y-m-d" ) . ".csv" );
header ( "Content-disposition: filename=testfile.csv" );
 
echo "test csv,test two\n";
exit();
 
 
?>

Open in new window

0
 
LVL 6

Expert Comment

by:basic612
ID: 24220880
:)

Thanks, appreciated!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.
Suggested Courses
Course of the Month16 days, 11 hours left to enroll

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question