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,field3 FROM MyTable WHERE field3='3'";

$result = mysql_query($sql) or die(mysql_error());
$count = mysql_num_fields($result);

for($i=0;$i<$count;$i++)
{
$header .= mysql_field_name($result, $i)."\t";
}

while($row = mysql_fetch_row($result))
{
$line = '';
foreach($row as $value)
{
if(!isset($value) || $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=excelFile.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,field3 FROM MyTable WHERE field3='3'";

$result = mysql_query($sql) 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=excelFile.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();

readfile('file.xls');
 

[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,field3 FROM MyTable WHERE field3='3'";

$result = mysql_query($sql) 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.xls");
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,field3 FROM MyTable WHERE field3='3'";

$result = mysql_query($sql) 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=excelFile.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($myArray 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=excelFile.xls");
header("Pragma: no-cache");
header("Expires: 0");
$sql = "SELECT field1,field2,field3 FROM MyTable WHERE field3='3'";

$result = mysql_query($sql) 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=excelFile.xls");
header("Pragma: no-cache");
header("Expires: 0");
$sql = "SELECT field1,field2,field3 FROM MyTable WHERE field3='3'";

$result = mysql_query($sql) or die(echo mysql_error());

while($line = mysql_fetch_row($result) )
{
echo implode(",",$line)."\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,field3 FROM MyTable WHERE field3='3'";

$result = mysql_query($sql) 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.xls");
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,field3 FROM MyTable WHERE field3='3'";

$result = mysql_query($sql) 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.xls");
header("Pragma: no-cache");
header("Expires: 0");

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

[jx2]

:-)
[php]<?php

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

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


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

THX

above is working script (i tried it)

regards
jx2

[jx2]

pbmods - is there any way to do it without saving it? i mean print it directly.(i cant save anything except .php .html .txt
:-(

regards
jx2

[pbmods]

Heya, JX2.

Well, you could save the file with a .txt extension. It really doesn't matter to PHP.

[coool]

lloolllz long discusion :)


okay with this code:
[php]
<?

$sql = "SELECT field1,field2 FROM MyTable Where field2>1";

$result = mysql_query($sql) or die(mysql_error());

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

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

fclose($fp);

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

readfile('file.xls');

?>
[/php]

when i've opened file.xls

I've got:

column A
a 3
b 4
c 2

I didn't get each field in saparated column
:(

but there's a tab between the two values inside the excel column

[jx2]

Heya, JX2.

Well, you could save the file with a .txt extension. It really doesn't matter to PHP.

yeah not for php i know that but it matter for unaware user and for browser(especially that i want to send svg or xml created in php)
i need to to some tests :-)
i'll be back

jx2

[jx2]

but there's a tab between the two values inside the excel column

cose you need to use coma(thats why its called csv coma separaded values )
(or you need to change the separator sign in excel)

regards
jx2

[coool]

i did that

no commas show up .. and same output ( fields saparated by a tab inside one excel column)

[php]
<?
$sql = "SELECT field1,field2 FROM MyTable Where field2>1";

$result = mysql_query($sql) or die(mysql_error());

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


while($line = mysql_fetch_row($result))
{
foreach($line as $column)
{
fputcsv($fp, split(',', $column));
}
}

fclose($fp);

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

readfile('file.xls');

?>
[/php]

[jx2]

try as i sugested before [php]<?php

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

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


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

if you have commas in this values u need to use \t but as i said u have to change it in excel

[coool]

thanks :)

the fields i'm using doesn't have commas.. although it didn't work with a coma ------ it was showing in excel column A this: a,3

anyway.. when I've used a tab ... THINGS WORKED .......that's because it considered it as text... when u have a table inside a text saparated by tabs and return charachters, and change the extension to xls... you'll find the table inserted perfectly inside excel

I have a little proble,,,, the fields name aren't displayed in my excel, why ?

[coool]

i mean i need the first row to be the field names

[pbmods]

Heya, Coool.

You'll need to output the field names as the first row to make this work properly (I believe that there is also a box you need to tick when importing the file in Excel).

Try something like this:

$sql = "SELECT * FROM session1";
$result = mysql_query($sql);
 
$fp = fopen("exelFile.xls",'w');
while($line = mysql_fetch_assoc($result) )
{
    if(empty($cols))
    {
        fwrite($fp, implode(',', array_keys($line)) . "\n");
        $cols = true;
    }
 
    fwrite( $fp, implode(',',$line)."\n");
}
fclose($fp);
 

Note that I also changed mysql_fetch_row() to mysql_fetch_assoc().

HOWEVER

Since you're not using fputcsv() anymore, there's no need to use any file functions at all. Try this instead:

header("Content-type: application/x-msdownload");
header("Content-Disposition: attachment; filename=excelFile.xls");
header("Pragma: no-cache");
header("Expires: 0");
 
$sql = "SELECT * FROM session1";
$result = mysql_query($sql);
 
while($line = mysql_fetch_assoc($result) )
{
    if(empty($cols))
    {
       echo implode(',', array_keys($line)), "\n";
        $cols = true;
    }
 
    echo implode(',',$line), "\n";
}
 

[coool]

PBmods, I've tried the last code in your previous post -- it didn't work !

instead i did this:
[php]
$sql="........";
$result = mysql_query($sql);

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

$result = mysql_query($sql);

while($line = mysql_fetch_assoc($result))
{
if(empty($cols))
{
fwrite($fp,implode("\t", array_keys($line)))."\n";
$cols = true;
}
fwrite($fp,implode("\t",$line))."\n";
}

fclose($fp);

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

then when i tried to open the file
they said
the file is not loaded correctly/completely - somthing like that

and then i saw the portion of the data in different columns but in one excel row

[coool]

I got it working :D

as simple as this:

[php]

$sql="....";
$result = mysql_query($sql);

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

$result = mysql_query($sql);

while($line = mysql_fetch_assoc($result))
{
if(empty($cols))
{
fwrite($fp,implode("\t", array_keys($line))."\n");
$cols = true;
}
fwrite($fp,implode("\t",$line)."\n");
}

fclose($fp);

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

now how can i change the color of the excel columns and the width size -- using PHP !

[jx2]

and then i saw the portion of the data in different columns but in one excel row

hmm sounds strange did you tried to use \r\n insted of \n ?

just a guess

[coool]

hmm sounds strange did you tried to use \r\n insted of \n ?

just a guess

it was just... a mistake

wrong: [php]fwrite($fp,implode("\t", array_keys($line)))."\n";[/php]

correct: [php]fwrite($fp,implode("\t", array_keys($line))."\n");[/php]

do you know how to change colors of the text and size... bold the fields name... etc ... in php .. that reflex EXCEL file !!

[coool]

alright, there's something strange here !

I have something wrong with memories ! or cookies !

if i run this exportToExcel function on SQL_1

i get a result ---- great !

but when i change SQL_1 to SQL_2

and run the function

i get the same file of SQL_1 !!!!!

i don't get the SQL_2 file until i delete manually the excelFile.xls

-----------

i tried these solutions:

use this code at the beginnig of my exportToExcel function
[php]
if(file_exists("excelFile.xls"))
{
unlink("excelFile.xls");
}
[/php]

but still... i'm not getting SQL_2 file until i delete SQL_1 file manually

by the way...i get a good result if I closed the browser and opened a new browser

so.. what's wrong ? how can i solve this ! do you have any clue :) ?

[jx2]

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

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

while($line = mysql_fetch_assoc($result))
{
if(empty($cols))
{
$data .= implode("\t", array_keys($line))."\r\n";
$cols = true;
}
$data .= implode("\t",$line)."\r\n";
}



header("Content-type: application/x-msdownload");
header("Content-Disposition: attachment; filename=$filename");
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($sql);
if(file_exists(4filename)unlink($filename);
$fp = fopen($filename,'w');
$result = mysql_query($sql);
while($line = mysql_fetch_assoc($result))
{
if(empty($cols))
{
fwrite($fp,implode("\t", array_keys($line))."\r\n");
$cols = true;
}
fwrite($fp,implode("\t",$line)."\r\n");
}
fclose($fp);

header("Location: $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($sql) or die(mysql_error());

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

while($line = mysql_fetch_assoc($sqlRes))
{
if(empty($cols))
{
fwrite($fp,implode("\t", array_keys($line))."\n");
$cols = true;
}
fwrite($fp,implode("\t",$line)."\n");
}
fclose($fp);

header("Content-type: application/x-msdownload");
header("Content-Disposition: attachment; filename=$filename");
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($sql) or die(mysql_error());

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

while($line = mysql_fetch_assoc($sqlRes))
{
if(empty($cols))
{
fwrite($fp,implode("\t", array_keys($line))."\n");
$cols = true;
}
fwrite($fp,implode("\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_limiter("must-revalidate");
header("Content-Type: application/vnd.ms-excel");
header('Content-Disposition: attachment; filename="'.$filename.'"');
[/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="'.$filename.'"');

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

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

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

and your link:
[html]<a href="yourphpscript.php?<?php echo "rand=".rand(); ?>" >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($_GET['sql']);

exportExcel($sqlRes);

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

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

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

while($line = mysql_fetch_assoc($sqlRes))
{
if(empty($cols))
{
fwrite($fp,implode("\t", array_keys($line))."\n");
$cols = true;
}
fwrite($fp,implode("\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_limiter("must-revalidate");

header("Content-Type: application/vnd.ms-excel");
header('Content-Disposition: attachment; filename="'.$filename.'"');
}
[/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($sqlRes,$filename)
{
//generating filename- this prevent it from caching
$a = explode(".",$filename);
$filename = $a[0].date("Y-m-d_H-i-s").$a[1];

$fp = fopen($filename,'w');
while($line = mysql_fetch_assoc($sqlRes))
{
if(empty($cols))
{
fwrite($fp,implode("\t", array_keys($line))."\n");
$cols = true;
}
fwrite($fp,implode("\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="excel.xls";
mysql_connect();
mysql_select_db("your_db");
$sql = "xxxx";
$result = mysql_query($sql);

while($line = mysql_fetch_assoc($result))
{
if(empty($cols))
{
$data .= implode("\t", array_keys($line))."\r\n";
$cols = true;
}
$data .= implode("\t",$line)."\r\n";
}
header("Content-type: application/x-msdownload");
header("Content-Disposition: attachment; filename=$filename");
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.php?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($_GET['sql']);

generateLink($result);

function generateLink($result)
{
$rand = rand();
echo"
<a href=\"excel.php?result=$result&rand=$rand\">downl oad</a>
";
}
[/php]

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

$result = $_GET['result'];

while($line = mysql_fetch_assoc($result))
{
if(empty($cols))
{
$data .= implode("\t", array_keys($line))."\r\n";
$cols = true;
}
$data .= implode("\t",$line)."\r\n";
}
header("Content-type: application/x-msdownload");
header("Content-Disposition: attachment; filename=$filename");
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 !