Sending Query Result to Excel

[coool]

Hi guys,

How can I send query result to EXCEL in PHP pages?

is there such a function in PHP ? any code example !

[pbmods]

Heya, coool.

You could generate a CSV file and then import it into Excel....

[coool]

what's the difference between an excel file and a CSV file ?

can't i just export to an excel file !! .. what's the benefits of exporting first to CSV file, then insert this file into EXCEL !?

thanks in advance,

coool : )

[pbmods]

Heya, coool.

The benefit to using CSV ('Comma-Separated Values') format is that there is already a built-in PHP function that can handle this:

http://php.net/manual/en/function.fputcsv.php

Writing in Excel format would require a third-party class, of which I admittedly don't know any. However, you might find what you are looking for at PHP Classes.

[coool]

alright..... I've thought of an easier way to do this

when you have a txt file that have rows saparated by \n and columns saparated by \t

and then you change .txt TO .xls

you get a table in EXCEL

can you see how easy is this : )

now .. here's the code.. there's something that doesn't work ! can you check it please
[php]
<?
$sql = "SELECT field1,field2,f ield3 FROM MyTable WHERE field3='3'";

$result = mysql_query($sq l) or die(mysql_error ());
$count = mysql_num_field s($result);

for($i=0;$i<$co unt;$i++)
{
$header .= mysql_field_nam e($result, $i)."\t";
}

while($row = mysql_fetch_row ($result))
{
$line = '';
foreach($row as $value)
{
if(!isset($valu e) || $value == "")
$value = "\t";
else
{
$value = str_replace('"' , '""', $value);
$value = '"' . $value . '"' . "\t";
}
$line .= $value;
}
$data .= trim($line)."\n ";
}

$data = str_replace("\r ", "", $data);

if ($data=="")
$data = "\nno matching records found\n";

header("Content-type: application/x-msdownload");
header("Content-Disposition: attachment; filename=excelF ile.xls");
header("Pragma: no-cache");
header("Expires : 0");

print "$header\n$data ";

?>
[/php]

I'm not getting a box asks me whether I want to open or save EXCEL file !!!!
I'm just getting the data displayed as this:
field1 field2 field3 "a" "b" "3" "c" "d" "3" "e" "f" "3"
with no \t and with no \n --- just spaces !!

what's wrong :( ?

[pbmods]

Heya, Coool.

You just reinvented the fputcsv() function.

[coool]

oh okay ! ... does that look better?

[php]
<?
$sql = "SELECT field1,field2,f ield3 FROM MyTable WHERE field3='3'";

$result = mysql_query($sq l) or die(mysql_error ());

$fp = fopen('file.xls ', 'w');

foreach ($result as $line)
{
fputcsv($fp, split('\t', $line));
}

fclose($fp);

header("Content-type: application/x-msdownload");
header("Content-Disposition: attachment; filename=excelF ile.xls");
header("Pragma: no-cache");
header("Expires : 0");
[/php]

but I'm not getting anything when I open the page

[pbmods]

Heya, Coool.

Ah. I see what you're trying to do now.

Add this line right after fclose();
[code=php]
readfile('file. xls');
[/code]

[coool]

I did ------------ got nothing displayed and no box poped up asking me if i wonna open or save the file :(

[php]
$sql = "SELECT field1,field2,f ield3 FROM MyTable WHERE field3='3'";

$result = mysql_query($sq l) or die(mysql_error ());

$fp = fopen('file.xls ', 'w');

foreach ($result as $line)
{
fputcsv($fp, split('\t', $line));
}

fclose($fp);

readfile('file. xls');

header("Content-type: application/x-msdownload");
header("Content-Disposition: attachment; filename=file.x ls");
header("Pragma: no-cache");
header("Expires : 0");
?>
[/php]

[coool]

and I found a file in my directory called "file.xls"

but it's empty !!!

(i'm sure sql query have data in it !! )

[coool]

and when I've tried to open file.xls itself from the browswer

www.websiteName .com/file.xls

I have the box to open or save this file

and when i've opened it !!! i got it empty !!!!

[jx2]

oh okay ! ... does that look better?

[php]
<?
$sql = "SELECT field1,field2,f ield3 FROM MyTable WHERE field3='3'";

$result = mysql_query($sq l) or die(mysql_error ());

$fp = fopen('file.xls ', 'w');

foreach ($result as $line)
{
fputcsv($fp, split('\t', $line));
}

fclose($fp);

header("Content-type: application/x-msdownload");
header("Content-Disposition: attachment; filename=excelF ile.xls");
header("Pragma: no-cache");
header("Expires : 0");
[/php]

but I'm not getting anything when I open the page

this confuse me
youve just wrote file to hardrive but you want to send it to the client
i think you want to redirect to that file now
or you want to print the results
[php]
$myArray = file("file.xls" );
foreach($myArra y as $var){
echo $var
}
[/php]
add that at the end of your script
it should work (let me know cos i'm going to do something like that for .svg)

regards
jx2

[jx2]

[php]<?php
header("Content-type: application/x-msdownload");
header("Content-Disposition: attachment; filename=excelF ile.xls");
header("Pragma: no-cache");
header("Expires : 0");
$sql = "SELECT field1,field2,f ield3 FROM MyTable WHERE field3='3'";

$result = mysql_query($sq l) or die(mysql_error ());

$fp = fopen('file.xls ', 'w');
//
while($line = mysql_fetch_row ($result) )
{
fputcsv($fp, split('\t', $line));
}

fclose($fp);
[/php]

try that and read that

but if i were you i would try that:
[php]

header("Content-type: application/x-msdownload");
header("Content-Disposition: attachment; filename=excelF ile.xls");
header("Pragma: no-cache");
header("Expires : 0");
$sql = "SELECT field1,field2,f ield3 FROM MyTable WHERE field3='3'";

$result = mysql_query($sq l) or die(echo mysql_error());

while($line = mysql_fetch_row ($result) )
{
echo implode(",",$li ne)."\n";
}
[/php]

i think that is what you looking for

let me know pls

regards
jx2

[pbmods]

Heya, Coool.

I did ------------ got nothing displayed and no box poped up asking me if i wonna open or save the file :(

[php]
$sql = "SELECT field1,field2,f ield3 FROM MyTable WHERE field3='3'";

$result = mysql_query($sq l) or die(mysql_error ());

$fp = fopen('file.xls ', 'w');

foreach ($result as $line)
{
fputcsv($fp, split('\t', $line));
}

fclose($fp);

readfile('file. xls');

header("Content-type: application/x-msdownload");
header("Content-Disposition: attachment; filename=file.x ls");
header("Pragma: no-cache");
header("Expires : 0");
?>
[/php]

My apologies. You have to put the header() statements BEFORE you call readfile().

Try this instead:

[php]
$sql = "SELECT field1,field2,f ield3 FROM MyTable WHERE field3='3'";

$result = mysql_query($sq l) or die(mysql_error ());

$fp = fopen('file.xls ', 'w');

foreach ($result as $line)
{
fputcsv($fp, split('\t', $line));
}

fclose($fp);

header("Content-type: application/x-msdownload");
header("Content-Disposition: attachment; filename=file.x ls");
header("Pragma: no-cache");
header("Expires : 0");

readfile('file. xls');
?>
[/php]

[jx2]

:-)
[php]<?php

$sql = "SELECT * FROM session1";
$result = mysql_query($sq l);

$fp = fopen("exelFile .xls",'w');
while($line = mysql_fetch_row ($result) )
{
fwrite( $fp, implode(',',$li ne)."\n");
}
fclose($fp);


header("Content-type: application/x-msdownload");
header("Content-Disposition: attachment; filename=excelF ile.xls");
header("Pragma: no-cache");
header("Expires : 0");[/php]
okey youve helped me accidentaly

THX

above is working script (i tried it)

regards
jx2