Sending Query Result to Excel

[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($sq l) 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.x ls");
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(especia lly 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($sq l) 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.x ls");
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($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]

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:
[code=php]
$sql = "SELECT * FROM session1";
$result = mysql_query($sq l);

$fp = fopen("exelFile .xls",'w');
while($line = mysql_fetch_ass oc($result) )
{
if(empty($cols) )
{
fwrite($fp, implode(',', array_keys($lin e)) . "\n");
$cols = true;
}

fwrite( $fp, implode(',',$li ne)."\n");
}
fclose($fp);
[/code]

Note that I also changed mysql_fetch_row () to mysql_fetch_ass oc().

HOWEVER

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

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

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

while($line = mysql_fetch_ass oc($result) )
{
if(empty($cols) )
{
echo implode(',', array_keys($lin e)), "\n";
$cols = true;
}

echo implode(',',$li ne), "\n";
}
[/code]

[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($sq l);

$fp = fopen("file.xls ",'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)))."\n";
$cols = true;
}
fwrite($fp,impl ode("\t",$line) )."\n";
}

fclose($fp);

header("Content-type: application/x-msdownload");
header("Content-Disposition: attachment; filename=file.x ls");
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($sq l);

$fp = fopen("file.xls ",'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))."\n");
$cols = true;
}
fwrite($fp,impl ode("\t",$line) ."\n");
}

fclose($fp);

header("Content-type: application/x-msdownload");
header("Content-Disposition: attachment; filename=file.x ls");
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,impl ode("\t", array_keys($lin e)))."\n";[/php]

correct: [php]fwrite($fp,impl ode("\t", array_keys($lin e))."\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("excelFi le.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 :) ?