Sending Query Result to Excel

[jx2]

forget all use one of those :
[php]
$filename="exce l.xls";

$sql = "xxxx";
$result = mysql_query($sq l);

while($line = mysql_fetch_ass oc($result))
{
if(empty($cols) )
{
$data .= implode("\t", array_keys($lin e))."\r\n";
$cols = true;
}
$data .= implode("\t",$l ine)."\r\n";
}



header("Content-type: application/x-msdownload");
header("Content-Disposition: attachment; filename=$filen ame");
header("Pragma: no-cache");
header("Expires : 0");
print "$data";

[/php]or this one if you want to create file [php]
<?php
$filename = "excel-m.xls";
$sql = "xxxxxxxx";

$result = mysql_query($sq l);
if(file_exists( 4filename)unlin k($filename);
$fp = fopen($filename ,'w');
$result = mysql_query($sq l);
while($line = mysql_fetch_ass oc($result))
{
if(empty($cols) )
{
fwrite($fp,impl ode("\t", array_keys($lin e))."\r\n");
$cols = true;
}
fwrite($fp,impl ode("\t",$line) ."\r\n");
}
fclose($fp);

header("Locatio n: $filename"); //coment it if you dont want to redirect
?>
[/php]
first one is great to generate dynamically
second if you want to print to file and use it later

regards
jx2

[coool]

I've used the second code

but

same problem !

when i use 2 or 3 different sql using the exportToExcel function, while i'm still in the same window(browser) / i always get the first sql excel file as a result

now when i look inside my directory.. i see that the content of the excel file has been changed

and when i open a new window(browser) for each sql , i get their correct results

so the problem is from the cookie/memory i guess

I have no clue how to solve this problem :(

this is the code I'm using:

[php]
$filename = "excelFile.xls" ;

$sql = "......";
$sqlRes = mysql_query($sq l) or die(mysql_error ());

if(file_exists( $filename))
{
unlink($filenam e);
}
$fp = fopen($filename ,'w');

while($line = mysql_fetch_ass oc($sqlRes))
{
if(empty($cols) )
{
fwrite($fp,impl ode("\t", array_keys($lin e))."\n");
$cols = true;
}
fwrite($fp,impl ode("\t",$line) ."\n");
}
fclose($fp);

header("Content-type: application/x-msdownload");
header("Content-Disposition: attachment; filename=$filen ame");
header("Pragma: no-cache");
header("Expires : 0");

[/php]

[pbmods]

Heya, Coool.

Have a look at this document.

[coool]

I'm still getting the problem :(

here's my code:

[php]
$filename = "excelFile.xls" ;

$sql = ".............. .....";
$sqlRes = mysql_query($sq l) or die(mysql_error ());

if(file_exists( $filename))
{
unlink($filenam e);
}
$fp = fopen($filename ,'w');

while($line = mysql_fetch_ass oc($sqlRes))
{
if(empty($cols) )
{
fwrite($fp,impl ode("\t", array_keys($lin e))."\n");
$cols = true;
}
fwrite($fp,impl ode("\t",$line) ."\n");
}
fclose($fp);

header("Expires : Sat, 01 Jan 2000 00:00:00 GMT");
header("Last-Modified: ".gmdate("D , d M Y H:i:s")." GMT");
header("Pragma: public");
header("Expires : 0");
header("cache-Control: must-revalidate, post-check=0, pre-check=0");
header("cache-Control: public");
header("Content-Description: File Transfer");

session_cache_l imiter("must-revalidate");
header("Content-Type: application/vnd.ms-excel");
header('Content-Disposition: attachment; filename="'.$fi lename.'"');
[/php]

am I missing something ??

[jx2]

my problems ends when i replaced those lines:[php]
header("Content-Type: application/vnd.ms-excel");
header('Content-Disposition: attachment; filename="'.$fi lename.'"');

//with this line:
header("Locatio n: $filename");
[/php]
or you can try this one insted as i sugested before[php]
$filename="exce l.xls";

$sql = "xxxx";
$result = mysql_query($sq l);

while($line = mysql_fetch_ass oc($result))
{
if(empty($cols) )
{
$data .= implode("\t", array_keys($lin e))."\r\n";
$cols = true;
}
$data .= implode("\t",$l ine)."\r\n";
}
header("Content-type: application/x-msdownload");
header("Content-Disposition: attachment; filename=$filen ame");
header("Pragma: no-cache");
header("Expires : 0");
print "$data";[/php]

and your link:
[html]<a href="yourphpsc ript.php?<?php echo "rand=".ran d(); ?>" >download</a>[/html]
it adds random number it will force browser/server to reload page

try it ...

[coool]

Okay I guess I didn't explain well what's going on...

I have several wepages
each have different SQL statement
each call exportToExcel function
each after calling, they have a link to the excelFile.xls
so when user click on this link - a save/open box of an excelFile.xls pop up

so i need to use fopen/fwrite/fclose, right !

here's a sample SQL page:
[php]
$sqlRes = mysql_query($_G ET['sql']);

exportExcel($sq lRes);

echo '<a href="excelFile .xls">Export to Excel<a/>';
[/php]

here's a function page:
[php]
function exportExcel($sq lRes)
{
$filename = "excelFile.xls" ;

if(file_exists( $filename))
{
unlink($filenam e);
}
$fp = fopen($filename ,'w');

while($line = mysql_fetch_ass oc($sqlRes))
{
if(empty($cols) )
{
fwrite($fp,impl ode("\t", array_keys($lin e))."\n");
$cols = true;
}
fwrite($fp,impl ode("\t",$line) ."\n");
}
fclose($fp);

header("Expires : Sat, 01 Jan 2000 00:00:00 GMT");
header("Last-Modified: ".gmdate("D , d M Y H:i:s")." GMT");
header("Pragma: public");
header("Expires : 0");
header("cache-Control: must-revalidate, post-check=0, pre-check=0");
header("cache-Control: public");
header("Content-Description: File Transfer");

session_cache_l imiter("must-revalidate");

header("Content-Type: application/vnd.ms-excel");
header('Content-Disposition: attachment; filename="'.$fi lename.'"');
}
[/php]

[jx2]

Okay I guess I didn't explain well what's going on...

I have several wepages
each have different SQL statement
each call exportToExcel function
each after calling, they have a link to the excelFile.xls
so when user click on this link - a save/open box of an excelFile.xls pop up

so i need to use fopen/fwrite/fclose, right !

if i understand you correctly - NO YOU ARE NOT RIGHT

and i tried explain you that allready

i try once more
[php]
//at the begining of your script
header("Cache-Control: no-cache, must-revalidate"); // HTTP/1.1
header("Expires : Mon, 26 Jul 1997 05:00:00 GMT"); // Date in the past


$filename = "excel.xls" ;
function exportExcel($sq lRes,$filename)
{
//generating filename- this prevent it from caching
$a = explode(".",$fi lename);
$filename = $a[0].date("Y-m-d_H-i-s").$a[1];

$fp = fopen($filename ,'w');
while($line = mysql_fetch_ass oc($sqlRes))
{
if(empty($cols) )
{
fwrite($fp,impl ode("\t", array_keys($lin e))."\n");
$cols = true;
}
fwrite($fp,impl ode("\t",$line) ."\n");
}
fclose($fp);

//generating link
$link = "<a href='$filename '>$filename</a>";
//displays link
echo $link;
return $link;
}
[/php]
this function will display the link to your file (each time new file and different link)

but... you dont need to do that if you going to use it only once

use this function insted:
[php]
//excel.php -in separate file safe it as excel.php use it like excel.xls
<?php
$filename="exce l.xls";
mysql_connect() ;
mysql_select_db ("your_db");
$sql = "xxxx";
$result = mysql_query($sq l);

while($line = mysql_fetch_ass oc($result))
{
if(empty($cols) )
{
$data .= implode("\t", array_keys($lin e))."\r\n";
$cols = true;
}
$data .= implode("\t",$l ine)."\r\n";
}
header("Content-type: application/x-msdownload");
header("Content-Disposition: attachment; filename=$filen ame");
header("Pragma: no-cache");
header("Expires : 0");
print "$data";
?>
[/php]

and in your page
[php]
//this function will display link to excel.xls (wich never existed and never will be)
function generateLink(){
$rand = rand();
echo"
<a href=\"excel.ph p?rand=$rand\"> download</a>
";
}
[/php]

jx2

[coool]

Alright,

when I've used your first way:
I got a new file for each call to the function

and that's not what I want because that will make many many files

as I need to use one file only

I tried to use the second way you've suggested

but !!! I was getting the data displayed in the browser as text
what I need is to get a box where it allows the user to OPEN/SAVE the data

so ! .. with the second way you've suggested.. how can I do that ?

assuming i'm calling a function and passing an new/same sql with a file name

[coool]

hmmm.. see this...

i try it - it's not working... kinda missing something !

in the page that should have the specified sql with a link to excel[php]
$result = mysql_query($_G ET['sql']);

generateLink($r esult);

function generateLink($r esult)
{
$rand = rand();
echo"
<a href=\"excel.ph p?result=$resul t&rand=$rand\"> download</a>
";
}
[/php]

in the excel.php page:
[php]
$filename="exce l.xls";

$result = $_GET['result'];

while($line = mysql_fetch_ass oc($result))
{
if(empty($cols) )
{
$data .= implode("\t", array_keys($lin e))."\r\n";
$cols = true;
}
$data .= implode("\t",$l ine)."\r\n";
}
header("Content-type: application/x-msdownload");
header("Content-Disposition: attachment; filename=$filen ame");
header("Pragma: no-cache");
header("Expires : 0");
print "$data";
[/php]

[jx2]

check PM and try link i send you every thing is working properly for me so it have to work for you as well

[coool]

alright, I've replied to your PM... please read it

I think there's just a little thing that is wrong with my codes !