Page 1 of 1

problem allowing users to download as excel from mysql

Posted: Tue Oct 11, 2011 11:07 pm
by thechinmaster
Hi

I have created a page that populates an HTML table based on mysql/php. Now I am trying to create a link on the same page so that logged in users are able to download the same information in excel format.

I have used the code pasted below, and it does display the information but not in columns; it just shows as one massive line of text. Where have I gone wrong? :banghead:

Thanks.

Code: Select all

<?php
	require_once("models/config.php");
	if(!isUserLoggedIn()) { header("Location: login.php"); die(); }
?>
<?PHP 
mysql_connect('****', '*********', '**********') or die (mysql_error());
mysql_select_db('**********') or die (mysql_error());

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) . '"'; 
} 
header("Content-Disposition: attachment; filename=\"$filename\""); 
header("Content-Type: application/vnd.ms-excel;"); 
$out = fopen("php://output", 'w'); 

$flag = false; 
$result = mysql_query("SELECT * from Customer$loggedInUser->user_id") or die('Query failed!'); 
while(false != ($row = mysql_fetch_assoc($result))) { 
	if(!$flag) { 
	// display field/column names as first row 
	fputcsv($out, array_keys($row), ',', '"'); 
	$flag = true; 
} 
	array_walk($row, 'cleanData'); 
	fputcsv($out, array_values($row), ',', '"'); 
} 

fclose($out); 
?>

Re: problem allowing users to download as excel from mysql

Posted: Wed Oct 12, 2011 11:07 am
by ouchiko
Try adding the following headers

Code: Select all

header("Cache-Control: public");
header("Content-Description: File Transfer");
header("Content-Transfer-Encoding: binary");
Don't forget that you are pushing out CSV [Comma Seperated Data] which Excel can read but it is not a native excel format. What you need to do is instruct the browser than this file is NOT for viewing in the browser.

Also, in the code I note that you do not seem to define $filename - do this - it will inform the system what type of file and thus what application to load.

Re: problem allowing users to download as excel from mysql

Posted: Wed Oct 12, 2011 11:02 pm
by Eric!
I haven't tried your technique, but perhaps you need to add an end-of-line character to each row like \n or \r\n.

You might run into browser specific problems with your solution. If you want to go a little more deeper there are some good PHP classes for Excel that I've used to create complicated dynamic excel files, instead of just pumping out CSV data.

You can do formulas, formatting and almost everything that the Excel program offers via php. I think it is pretty easy to learn from the examples.

http://excelwriterxml.sourceforge.net/docs/

Re: problem allowing users to download as excel from mysql

Posted: Thu Oct 13, 2011 1:30 am
by thechinmaster
Thanks for both suggestions - sorry Eric! but I dont have much experience with PHP so would struggle to tailor your example to my specific needs.

ouchiko - thanks for your ideas. Unfortunately I still have issues. Please look at the code I have pasted below:

Code: Select all

<?php
	require_once("models/config.php");
	if(!isUserLoggedIn()) { header("Location: login.php"); die(); }
?>
<?PHP 
mysql_connect('***', '****', '*****') or die (mysql_error());
mysql_select_db(******') or die (mysql_error());
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) . '"'; 
} 
header("Content-Disposition: attachment; filename=\"$filename\""); 
header("Content-Type: application/vnd.ms-excel;"); 
header("Cache-Control: public");
header("Content-Description: File Transfer");
header("Content-Transfer-Encoding: binary");

$out = fopen("php://output", 'w'); 
$filename = 'echo date("Y.m.d").csv'; 
$flag = false; 
$result = mysql_query("SELECT * from Customer$loggedInUser->user_id") or die('Query failed!'); 
while(false != ($row = mysql_fetch_assoc($result))) { 
	if(!$flag) { 
	// display field/column names as first row 
	fputcsv($out, array_keys($row), ',', '"'); 
	$flag = true; 
} 
	array_walk($row, 'cleanData'); 
	fputcsv($out, array_values($row), ',', '"'); 
} 

fclose($out); 
?>
Have I done this correctly? If so, when I click the link in my site, it comes up with the following: "The file you are trying to open, 'example.php.xls' is in a different format than specified by the file extention. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?" The information displayed is still the same as before. Thanks again for your help!

Re: problem allowing users to download as excel from mysql

Posted: Thu Oct 13, 2011 4:43 am
by ouchiko
No

You need to define $filename before the headers, otherwise filename is empty...

.. $filename = date('Y-m-d').".csv";

Anyway, I ran your code , minus the SQL bits and it delivers an option to download a file for Excel.. [in firefox]

Re: problem allowing users to download as excel from mysql

Posted: Sat Oct 15, 2011 4:10 am
by thechinmaster
Brilliant - what a plonker to miss something so simple. Thanks for all your help - works like a charm.