excel issue

ady01 · May 24, 2008

got my code 50% working here but its displaying the data in the HTML page not in excel like im asking my code to do, any idears what im doing wrong in the below code ?

<?php
require_once("config.php");

$db=mysql_connect($AddressBook_HOST,$AddressBook_Username,$AddressBook_Password);
mysql_select_db($AddressBook_DatabaseName,$db);

$query  = "SELECT * FROM Addresses";
$result = mysql_query($query) or die('Error, query failed: '.mysql_error());

$tsv  = array();

while($row = mysql_fetch_array($result, MYSQL_NUM))
{
   $tsv[]  = implode("\t", $row);
}

$tsv = implode("\r\n", $tsv);


$fileName = 'mysql-to-excel.xls';
header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=$fileName");

echo $tsv;


?>

AndyB · May 24, 2008

This works for me:

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=$filename");
header("Content-Transfer-Encoding: binary");
if(strpos($_SERVER['HTTP_USER_AGENT'], 'MSIE')){
    // IE cannot download from sessions without a cache
    header('Cache-Control: public');
}
echo $tsv;

ady01 · May 24, 2008

Well i changed the code to the below as you said but now i just get a blank page ? have i done something wrong with altering the code ?

<?php
require_once("config.php");

$db=mysql_connect($AddressBook_HOST,$AddressBook_Username,$AddressBook_Password);
mysql_select_db($AddressBook_DatabaseName,$db);

$query  = "SELECT * FROM Addresses";
$result = mysql_query($query) or die('Error, query failed: '.mysql_error());

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=$filename");
header("Content-Transfer-Encoding: binary");
if(strpos($_SERVER['HTTP_USER_AGENT'], 'MSIE')){

    header('Cache-Control: public');
}
echo $tsv;


?>

BlueSkyIS · May 24, 2008

you added his output code, but you deleted the part of your code that builds the output, $tsv

ady01 · May 24, 2008

OK i have now got the below but now its still outputting to html again ? well confused now !

<?php
require_once("config.php");

$db=mysql_connect($AddressBook_HOST,$AddressBook_Username,$AddressBook_Password);
mysql_select_db($AddressBook_DatabaseName,$db);

$query  = "SELECT * FROM Addresses";
$result = mysql_query($query) or die('Error, query failed: '.mysql_error());

$tsv  = array();

while($row = mysql_fetch_array($result, MYSQL_NUM))
{
   $tsv[]  = implode("\t", $row);
}

$tsv = implode("\r\n", $tsv);


header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=$filename");
header("Content-Transfer-Encoding: binary");
if(strpos($_SERVER['HTTP_USER_AGENT'], 'MSIE')){
    // IE cannot download from sessions without a cache
    header('Cache-Control: public');
}
echo $tsv;


?>

ady01 · May 25, 2008

**bump**

ady01 · May 25, 2008

**bump**

BlueSkyIS · May 25, 2008

what do you mean 'outputting to HTML'? you mean, it's showing up as text in your browser window, or something else?

by your trouble, i assume you're using IE on Windows?

ady01 · May 25, 2008

well im testing in firefox not IE (hate IE) & yes its just displaying in the browser window, not downloading to excel like i wanted it to....

AndyB · May 25, 2008

tested with Firefox, this produces the download dialog box with option to open and save. You should be able to adjust to suit your case and have it work:

<?php 
include("../includes/db-conn.php");
mysql_connect($db_host, $db_login, $db_pass) or die ("Can't connect!"); 
mysql_select_db($db_name) or die ("Can't open database!"); 

$CSV = "To,From,Car Price,4WD Price\n";
$file ="report_". date("Y-m-d"). ".txt";

$query = "SELECT * FROM ratesheet ORDER by id ASC";
$result = mysql_query($query) or die("Error: ". mysql_error(). " with query ". $query);
while($myrow = mysql_fetch_array($result)) {
    $CSV.= $myrow[city_to]. ",";
    $CSV.= $myrow[city_from]. ",";
    $CSV.= $myrow[price_car]. ",";
    $CSV.= $myrow[price_4wd]. "\n";
}
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=\"$file\"");
header("Content-Transfer-Encoding: binary");
if(strpos($_SERVER['HTTP_USER_AGENT'], 'MSIE')){
    // IE cannot download from sessions without a cache
    header('Cache-Control: public');
}
echo $CSV;
exit;
?>[code]

[/code]

ady01 · May 26, 2008

Sorry about this but still outputting to the browser !!!! I have changed my code as per last post to the below and my output is STILL in web browser not in excel !!! ahhh going mad now ! (sorry)

<?php

include("config.php");
mysql_connect($AddressBook_HOST, $AddressBook_Username, $AddressBook_Password) or die ("Can't connect!"); 
mysql_select_db($AddressBook_DatabaseName) or die ("Can't open database!"); 

$CSV = "ID,Name,DOB,HouseNumber,Street,City,Country,Telephone,Fax,Email,Remarks\n";
$file ="report_". date("Y-m-d"). ".txt";

$query = "SELECT * FROM Addresses ORDER by id ASC";
$result = mysql_query($query) or die("Error: ". mysql_error(). " with query ". $query);
while($myrow = mysql_fetch_array($result)) {
    $CSV.= $myrow[iD]. ",";
    $CSV.= $myrow[Name]. ",";
    $CSV.= $myrow[DOB]. ",";
$CSV.= $myrow[HouseNumber]. ",";
$CSV.= $myrow[street]. ",";
$CSV.= $myrow[City]. ",";
$CSV.= $myrow[Country]. ",";
$CSV.= $myrow[Telephone]. ",";
$CSV.= $myrow[Fax]. ",";
$CSV.= $myrow[Email]. ",";
    $CSV.= $myrow[Remarks]. "\n";
}
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=\"$file\"");
header("Content-Transfer-Encoding: binary");
if(strpos($_SERVER['HTTP_USER_AGENT'], 'MSIE')){
    header('Cache-Control: public');
}
echo $CSV;
exit;
?>

My output from this is :_ (FYI the datat is just test data from MYSQL so that part is right, its taking the data... just not diplaying in excel)

ID,Name,DOB,HouseNumber,Street,City,Country,Telephone,Fax,Email,Remarks 50,522380,Ptechnology group,alivemore,tgoater,tgoater@example.com,example,example,Joanne A,jahern@example.com,None 51,358225,eservices,person a,Person 1,example@example.com,example,example,Robert,Rd@example.com,Place order Via EDI link 54,test,test,test,test,test,test,test,test,test,testing server 57,new,new,new,new,new,new,new,new,new,new testing

BlueSkyIS · May 26, 2008

you might want to try replacing the comma separators with tabs instead:

$CSV.= $myrow[iD]. "\t";

ady01 · May 26, 2008

**bump**

BlueSkyIS · May 26, 2008

so my tab idea didn't work?

everything i do with excel output in php, i do with this pear package: http://pear.php.net/package/Spreadsheet_Excel_Writer

it handles all of the output formatting and browser stuff.

ady01 · May 26, 2008

No, tabs didnt work either sorry, I have no experience with pearl and am using a hosted linux solution so i dont think this will be possible.... it must be possible with just php to download to excel ?

BlueSkyIS · May 26, 2008

how an excel file is handled is somewhat browser-dependent. here is some code that i used at one time before i was able to install the pear module. it's pretty messy, but you see how i use tabs, and a variation on the header()s. maybe see if any of this helps:

<?php
// At this point, we've already gotten the data from mysql, in $result and we're going to the excel output.....

$header = "Band\tDate\tVenue\tCity\tState\tMedia\tCDs\tSHNs\tComplete\tComments\tSetlist\t";

while($row = mysql_fetch_array($result, MYSQL_ASSOC)){
	$line = '';

	if ($row['first_name'] > "") {
		$band_name = $row['first_name']." ".$row['last_name'];
	} else {
		$band_name = $row['last_name'];
	}

	// clean up band name
	$band_name = '"'.str_replace('"', '""', $band_name).'"'."\t";
	$date = '"'.$row['date'].'"'."\t";
	$venue = ($row['venue'] > "")?'"'.str_replace('"', '""',$row['venue']).'"'."\t":"\t";
	$city = ($row['city'] > "")?'"'.str_replace('"', '""',$row['city']).'"'."\t":"\t";
	$state = ($row['state'] > "")?'"'.str_replace('"', '""',$row['state']).'"'."\t":"\t";
	$media = '"'.$row['media'].'"'."\t";
	$cds = '"'.$row['cds'].'"'."\t";
	$shns = '"'.$row['shns'].'"'."\t";
	$complete = '"'.$row['complete'].'"'."\t";
	$comments = ($row['comments'] > "")?'"'.str_replace('"', '""',$row['comments']).'"'."\t":"\t";
	$setlist = ($row['setlist'] > "")?'"'.str_replace('"', '""',$row['setlist']).'"'."\t":"\t";

	$line .= $band_name.$date.$venue.$city.$state.$media.$cds.$shns.$complete.$comments.$setlist;

	$line = str_replace("\r", "", $line);
	$line = str_replace("\n", "", $line);

	$data .= trim($line)."\n";
}
# this line is needed because returns embedded in the data have "\r"
# and this looks like a "box character" in Excel
$data = str_replace("\r", "", $data);

# Nice to let someone know that the search came up empty.
# Otherwise only the column name headers will be output to Excel.
if ($data == "") {
	$data = "\nno matching records found\n";
}

# This line will stream the file to the user rather than spray it across the screen
header("Content-type: application/octet-stream");

# replace excelfile.xls with whatever you want the filename to default to
header("Content-Disposition: attachment; filename=excelfile.xls");
header("Pragma: no-cache");
header("Expires: 0");

echo $header."\n".$data; 
?>

AndyB · May 27, 2008

It's not clear why my most recent code works for me with Firefox (2.0.0.14) on a PC but fails for you. What's your version? What's your OS?

ady01 · May 27, 2008

Andy, really weird why its not working for me, it should work on all browsers really (using firefox 2.0.0.14 and XP Pro OS) - There was an example code on http://www.php-mysql-tutorial.com/php-mysql-select.php which i started to create the code with - the example one they did works fine on http://www.php-mysql-tutorial.com/examples/convert.php - just wont work chen i change it for my needs !

ady01 · May 27, 2008

Its only a thought here but my hosting provider run linux servers, would this be a problem as I know Excel is not a linux app ? I realise the excel is not directley running on their end and would only have to be on the clients end but would this be a problem ?

They also have a list of functions that are disabled but from the below this would not be one of them :

# highlight_file

# diskfreespace

# exec

# passthru

# system

# popen,

# pfsockpen

# show_source

# php_uname

# ini_alter

# ini_restore

# ini_set

# getrusage

# mysql_list_dbs

# get_current_user

# set_time_limit

# getmyuid

# getmypid

# dl

# leak

# listen

# chgrp

AndyB · May 27, 2008

Andy, really weird why its not working for me, it should work on all browsers really (using firefox 2.0.0.14 and XP Pro OS) - There was an example code on http://www.php-mysql-tutorial.com/php-mysql-select.php which i started to create the code with - the example one they did works fine on http://www.php-mysql-tutorial.com/examples/convert.php - just wont work chen i change it for my needs !

Curious and curiouser. I'm running FF 2.0.0.14 and XP Pro locally. The php script is running on a *nix server so it appears we have identical environments. I don't see that the disabled functions would have any effect at all. One difference between the code I have working and the code you're having trouble with is that mine generates a .txt file whereas you're trying to generate a .xls file. Needless to say, my .txt file opens perfectly with Excel.

BlueSkyIS · May 27, 2008

i develop lamp (linux/apache/mysql/php) exclusively and run excel code on various linux servers without this problem, testing on Mac Firefox and Safari, plus Win XP Pro IE and Firefox.

if you believe that the problem may be host-specific, i would take a look at the raw headers being sent to you to see if there is anything in there that may be causing the problem. may be ignoring or rewriting your headers.

ady01 · May 27, 2008

Well Im now thinking the problem is here somewhere :

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=excelfile.xls");
header("Pragma: no-cache");
header("Expires: 0");

echo $header."\n".$data; 
?> 
[code]

My reason for this is (and i could be wrong) is I can change the extension to what ever I want (XLS, RFT , TXT etc) & it makes no diffence to how the browser responds, Im thinking its almost paying no attention to this last part of the code... 

I Think for some reason its almost stopping before it gets to this part of the PHP, and just generating the result as text in a window... EG I could remove most of the code and get the same output E.g i could run the below and propabley get the same result  ? am i making sence here. ?

[code]
<?php
include('config.php');

mysql_connect($AddressBook_HOST, $AddressBook_Username, $AddressBook_Password) or die ("Can't connect!"); 
mysql_select_db($AddressBook_DatabaseName) or die ("Can't open database!");

$result = mysql_query('select * from Addresses');
$count = mysql_num_fields($result);
?>

[/code][/code]

ady01 · May 27, 2008

Amended my code guys on some advise from a mate, this seems to have stopped it displaying in the browser it now displays the following error when run :

Parse error: syntax error, unexpected $end in /data/members/paid/a/d/******/htdocs/smb1buddylist/download.php on line 43

<?php

include("config.php");
mysql_connect($AddressBook_HOST, $AddressBook_Username, $AddressBook_Password) or die ("Can't connect!"); 
mysql_select_db($AddressBook_DatabaseName) or die ("Can't open database!"); 

$CSV = "ID,Name,DOB,HouseNumber,Street,City,Country,Telephone,Fax,Email,Remarks\n";
$file ="report_". date("Y-m-d"). ".TXT";

$query = "SELECT * FROM Addresses ORDER by ID ASC";
$result = mysql_query($query) or die("Error: ". mysql_error(). " with query ". $query);
while($myrow = mysql_fetch_array($result)) {
    $CSV.= $myrow[iD]. "\t";
    $CSV.= $myrow[Name]. "\t";
    $CSV.= $myrow[DOB]. "\t";
$CSV.= $myrow[HouseNumber]. "\t";
$CSV.= $myrow[street]. "\t";
$CSV.= $myrow[City]. "\t";
$CSV.= $myrow[Country]. "\t";
$CSV.= $myrow[Telephone]. "\t";
$CSV.= $myrow[Fax]. "\t";
$CSV.= $myrow[Email]. "\t";
    $CSV.= $myrow[Remarks]. "\n";

header("Content-Type: application/vnd.ms-excel");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");;
header("Content-Disposition: attachment;filename=download.xls ")
?>

freakus_maximus · May 27, 2008

Any chance my post http://www.phpfreaks.com/forums/index.php/topic,198320.msg895554.html#msg895554 might help ya out?

It will take a query and open the results in Excel.

Note - As user on that post pointed out, don't pull the ID and it works fine. Again, not my code, but I have found it useful as hell.

BlueSkyIS · May 27, 2008

Parse error: syntax error, unexpected $end in /data/members/paid/a/d/******/htdocs/smb1buddylist/download.php on line 43

you didn't close your while() {

Sign In

excel issue

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information