I have created php coding to generate excel file. data are grab from SQL tables but the result are displayed with the HTML tags to the csv file. please see the attached image file which shows the output of below coding.
//genarate report
$out = '';
$filename_prefix = 'showroom_report';
$filename = $filename_prefix."_".$fromDate."-".$toDate;
$pay_array = array();
$showroom_array = array();
$location_id_array = array();
$pay_sql = "SELECT abans_crm.pay_mode.pay_mode,
Count(abans_crm.customers.purchase_type) AS mode_count,
abans_crm.customers.location_id
FROM
abans_crm.customers
INNER JOIN abans_crm.pay_mode ON abans_crm.customers.purchase_type = abans_crm.pay_mode.mode_id
WHERE
DATE(
abans_crm.customers.purchase_date
) >= '$fromDate'
AND DATE(
abans_crm.customers.purchase_date
) <= '$toDate'
GROUP BY
abans_crm.pay_mode.pay_mode";
$pay_result = mysql_query($pay_sql) or die(mysql_error());
if (mysql_num_rows($pay_result) > 0) {
while($row = mysql_fetch_assoc($pay_result)) {
array_push($pay_array, $row);
array_push($location_id_array, $row['location_id']);
}
}
$location_id_unique = array_unique($location_id_array);
$location_id_result = implode(", ", $location_id_unique);
//load showroom names
$showroom_sql = "SELECT
sh_sso.showrooms.showroom_id,
sh_sso.showrooms.showroom_name
FROM
sh_sso.showrooms
WHERE
sh_sso.showrooms.showroom_id IN ($location_id_result)";
$showroom_result = mysql_query($showroom_sql) or die(mysql_error());
while($row = mysql_fetch_assoc($showroom_result)){
array_push($showroom_array, $row);
}
echo "<table border=1 >\n";
echo "<thead><tr class=\"tableizer-firstrow\"><td colspan=6>Enquiries Report - $fromDate-to-$toDate</td></tr></thead><tbody>\n";
echo " <tr><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>\n";
echo " <tr><td>SHOWROOM</td><td colspan=4>PAYMENT METHOD</td><td rowspan=2>Total</td></tr>\n";
echo " <tr>"
. "<td> </td>"
. "<td>CASH</td>"
. "<td>CREDIT CARD</td>"
. "<td>HIRE PURCHASE</td>"
. "<td>LEASE</td>"
. "</tr>\n";
$cash_val = 0;
$credit_val = 0;
$hire_val = 0;
$lease_val = 0;
$row_total = 0;
for ($i=0; $i < count($showroom_array); $i++) {
$cash_val = searchArray($pay_array, $showroom_array[$i]['showroom_id'], 'Cash');
$credit_val = searchArray($pay_array, $showroom_array[$i]['showroom_id'], 'Credit Card');
$hire_val = searchArray($pay_array, $showroom_array[$i]['showroom_id'], 'Hire Purchase');
$lease_val = searchArray($pay_array, $showroom_array[$i]['showroom_id'], 'Lease');
include_once("../../../includes/sh_cms_functions.php");
$row_total = $row_total + $cash_val;
$row_total = $row_total + $credit_val;
$row_total = $row_total + $hire_val;
$row_total = $row_total + $lease_val;
echo "<tr>"
. "<td>".$showroom_array[$i]['showroom_name']."</td>"
. "<td>".$cash_val."</td>"
. "<td>".$credit_val."</td>"
. "<td>".$hire_val."</td>"
. "<td>".$lease_val."</td>"
. "<td>".$row_total."</td>"
. "</tr>\n";
$row_total = 0;
}
echo "</tbody></table>\n";
//Generate the CSV file header
header("Content-type: application/vnd.ms-excel");
header("Content-Encoding: UTF-8");
header("Content-type: text/csv; charset=UTF-8");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header("Content-disposition: filename=".$filename.".csv");
echo "\xEF\xBB\xBF"; // UTF-8 BOM
//Print the contents of out to the generated file.
print $out;
//Exit the script
exit;
mysql_close();
}
above coding will generate CSV file.
