Hi all,
This is an extension from a project I came for help with and one of the problems was on click of an export button the correct data was exported to an excel file, which we got working using the code below.
Now i have a slightly different take on it for a different project.
This time they want to data to be added to the same excel file that sits on the server, so building it up rather than creating a fresh one each time, and still having the option once the data has been saved of saving the file locally.
error_reporting(E_ALL);
ini_set('display_errors','Off');
session_start();
header("Pragma: public"); // required
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Cache-Control: private",false); // required for certain browsers
header("Content-Transfer-Encoding: binary");
header("Content-Type: application/csv");
require_once('config.php');
$connectionInfo = array("Database"=>$databaseName, "UID"=>$username, "PWD"=>$password);
$conn = sqlsrv_connect($hostName , $connectionInfo);
$username=$_SESSION['user1'];
if ($_SESSION['flaglog']!=1)
{
header("location:index.php");
}
else
{
$query = "SELECT * FROM Users WHERE Username='$username'";
$res = sqlsrv_query($conn, $query);
while ($result = sqlsrv_fetch_array($res, SQLSRV_FETCH_ASSOC)) {
$name = $result["First_Name"];
$security = $result["Admin"];
//echo $security;
}
$contractLike = substr($username, 0, 3);
//echo $contractLike;
}
$query = $_SESSION['currentQuery'];
//var_dump($query);
$query = str_replace('WITH LIMIT AS( ', '', $query);
$query = str_replace(", ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber'", '', $query);
$query = str_replace(", ROW_NUMBER() OVER (ORDER BY Contract_Number) AS 'RowNumber'", '', $query);
$query = str_replace('select * from LIMIT WHERE {limit clause}', '', $query);
$query = str_replace('))', ')', $query);
$query = str_replace('SELECT * FROM Intranet)', 'SELECT * FROM Intranet', $query);
$query = str_replace("%')", "%'", $query);
//var_dump($query);
function cleanData(&$str)
{
if($str == 't') $str = 'TRUE';
if($str == 'f') $str = 'FALSE';
if(preg_match("/^0/", $str) || preg_match("/^\\+?\\d{8,}$/", $str) || preg_match("/^\\d{4}.\\d{1,2}.\\d{1,2}/", $str)) {
$str = "'$str";
}
if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';
$str = mb_convert_encoding($str, 'UTF-16LE', 'UTF-8');
}
// filename for download
$filename = "website_data_" . date('Ymd') . ".csv";
header("Content-Disposition: attachment; filename=\\"$filename\\"");
header("Content-Type: text/csv; charset=UTF-16LE");
$out = fopen("php://output", 'w');
$flag = false;
$result = sqlsrv_query($conn, $query) or die('Query failed!');
while(false !== ($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC))) {
if(!$flag) {
// display field/column names as first row
fputcsv($out, array_keys($row), ',', '"');
$flag = true;
}
//array_walk($row, 'cleanData');
//start
foreach ($row as $key => $value)
{
if ($value instanceof DateTime)
{
$row[$key] = date_format($value, 'd/m/y');
}
}
//end
$row = str_replace(",", ",", $row);
$row = str_replace(".", ".", $row);
fputcsv($out, array_values($row), ',', '"');
}
fclose($out);
exit;