Create a Excel File for a user in PHP

Question

I have data in a MySQL database. I am sending the user a URL to get their data out as a excel file.

How can I, when they click the link, have a pop-up to download a excel with the record from MySQL?

I have all the information to get the record already. I just don't see how to have PHP create the excel file and let them download a file

include("con.php");

// Qry to fetch all records against date
$dateonedayback=strtotime('-1 day',strtotime(date("Y/m/d")));
$one_day=date("Y-m-d",$dateonedayback);


$qry_user_points = "SELECT u.points,u.db_add_date,u.user_email_id FROM tbl_user AS u WHERE u.points != '0' AND date(u.db_add_date)='".$one_day."' ";

$query_result= $con->db_query($qry_user_points);

header('Content-Type: application/vnd.ms-excel');   //define header info for browser
header('Content-Disposition: attachment; filename=Users-Points-Report-'.date('Ymd').'.xls');

$excel_header = array("Email","Points","Date");

for ($i = 0; $i < count($excel_header); $i++)    
{
echo $excel_header[$i]."\t";
}
print("\n");


$j=0;
while($rowValue = $con->db_fetch_array($query_result)) 
{
$points = $rowValue['points'];
$emailID = $rowValue['user_email_id'];
$addedDate = $rowValue['db_add_date'];

// create an array to insert in excel with url encoding
$final_array = array(urlencode($points),urlencode($emailID),urlencode($addedDate));

$result_count =$con->db_num_rows($query_result);

$output = '';
for($k=0; $k < $con->db_num_rows($query_result); $k++)
{
    if(!isset($final_array[$k]))
        $output .= "NULL\t";
    else
        $output .= "$final_array[$k]\t";
}
$output = preg_replace("/\r\n|\n\r|\n|\r/", ' ', $output);
print(trim($output))."\t\n";

$j++;
} // while close

When i hit link it creates excel file but data not coming properly

it writes only points in excel file not email & date

please help me on this.

Consider using the PHP built-in [fputcsv()](http://www.php.net/manual/en/function.fputcsv.php) function when creating a CSV file, or even the [SPL version](http://www.php.net/manual/en/splfileobject.fputcsv.php) — Mark Baker, Jan 08 '14 at 09:43
Note that you aren't actually creating an Excel file (either BIFF or OfficeOpenXML format), just a CSV file with a .xls extension — Mark Baker, Jan 08 '14 at 09:45
possible duplicate of [Export data to Excel with PHP](http://stackoverflow.com/q/13085269/), [phpexcel to download](http://stackoverflow.com/q/8566196/), [Download Excel From Server Using PHP](http://stackoverflow.com/q/17044580/). See also [Alternative for PHP_excel](http://stackoverflow.com/q/3930975/). — outis, Jan 08 '14 at 10:14

Victor Perov · Accepted Answer · 2014-01-08T10:01:53.500

For example, you can use PEAR Spreadsheet lib http://pear.php.net/package/Spreadsheet_Excel_Writer

This lib creates excel file and than you should send content to user with setting right headers

via mail:

        $sid = md5(uniqid(time()));
        $header = "From: Automatic report system<admin@example.com>\nReply-to: admin@example.com\nMIME-Version: 1.0\nContent-Type: multipart/mixed; boundary=\"$sid\"\n\n";
        $header .= "This is multi-part message in MIME format.\n--$sid\n";
        $header .= "Content-type: text/plain; charset=utf-8\n\n";
        $length = filesize($filePath);
        $header .= "--$sid\nContent-type: application/octet-stream; name=\"$fileTitle.xls\"\n";
        $header .= "Content-Transfer-Encoding: base64\n";
        $header .= "Content-Disposition: attachment; filename=\"$fileTitle.xls\"\nContent-Length: $length\n\n";
        $header .= chunk_split(base64_encode(file_get_contents($filePath))) . "\n\n\n\n";
        mail('recepient@example.com', 'New report', null, $header);

via browser:

        $length = filesize($filePath);
        header("Content-type: application/octet-stream");
        header("Content-Disposition: attachment; filename=\"$fileTitle.xls\"");
        header("Content-Length: $length");
        readfile($filePath);
        exit;

Create a Excel File for a user in PHP

1 Answers1