problem allowing users to download as excel from mysql

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
thechinmaster
Forum Newbie
Posts: 14
Joined: Thu Sep 15, 2011 7:21 pm

problem allowing users to download as excel from mysql

Post 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); 
?>
ouchiko
Forum Commoner
Posts: 35
Joined: Sun Oct 09, 2011 6:54 pm
Location: London

Re: problem allowing users to download as excel from mysql

Post 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.
Eric!
DevNet Resident
Posts: 1146
Joined: Sun Jun 14, 2009 3:13 pm

Re: problem allowing users to download as excel from mysql

Post 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/
thechinmaster
Forum Newbie
Posts: 14
Joined: Thu Sep 15, 2011 7:21 pm

Re: problem allowing users to download as excel from mysql

Post 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!
ouchiko
Forum Commoner
Posts: 35
Joined: Sun Oct 09, 2011 6:54 pm
Location: London

Re: problem allowing users to download as excel from mysql

Post 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]
thechinmaster
Forum Newbie
Posts: 14
Joined: Thu Sep 15, 2011 7:21 pm

Re: problem allowing users to download as excel from mysql

Post by thechinmaster »

Brilliant - what a plonker to miss something so simple. Thanks for all your help - works like a charm.
Post Reply