export to excel data 2

[OldSmelly]

I'm on fire now

I have searched this form and come up whith this litle script to create a excel-sheet. But what it does it actualy creates a file with the jpg in it from my header ?? very weird I think Anyone got a idea


function create_excel_file($querystring)
{
$file="test.xls";
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment;filename=".$file );
header('Pragma: no-cache');
header('Expires: 0');
$result_excel = mysql_query($querystring);
if($result_excel)
{
$columns=mysql_num_fields($result_excel);
for ($i = 0; $i < mysql_num_fields($result_excel); $i++)
{
print "\"".mysql_field_name($result_excel,$i)."\",";
}
echo "\n";
While ($myrow = mysql_fetch_array($result_excel))
{
for ($i = 0; $i < ($columns); $i++)
{
echo "\"".$myrow[$i]."\",";
}
echo "\n";
}
}
else
{
echo "No results";
}
}

 

[jpadie]

try this instead. make sure there is no html or anything else being sent to the browser.

$file="test.xls";
if (file_exists($file)):
  header("Content-Type: application/vnd.ms-excel");
  header("Content-Disposition: attachment;filename=".$file );
  header('Pragma: no-cache');
  header('Expires: 0');
  readfile($file);
endif;

i'm not sure what your query does. if you are using this to generate the excel file then i'm not sure you're going about it the right way - it looks to me that at best you will generate a csv.

 

[OldSmelly]

My query just does something like "select * from persons"

I do this to create a csv file

$file = "test.csv";
$result_csv=mysql_query($querystring);
$fh=fopen($file,"w+");
While($row=mysql_fetch_assoc($result_csv))
{
$string=implode(";",$row)."\n";
fwrite($fh,$string);
}
fclose($fh);

But how to put it on clients pc instead of on the server ?

 

[jpadie]

  $file = "test.csv";
  $temp_str = "";
  $result_csv=mysql_query($querystring);
  $fh=fopen($file,"w+") or die ("unable to open file"); //note that this APPENDS not overwrites
  while($row=mysql_fetch_assoc($result_csv)):
      foreach ($row as &$val):
         $val = str_replace('"', "'", $val);
      endforeach;
      fputcsv($fh, $row, ",", '"');
      $temp_str .= '"'.implode('","',$row).'"\n\r';//used for the non file based download
  endwhile;
  fclose($fh);      

// note that you do not need to save the file on the filesystem to force a download.
// use the commented code block to try this
/*
header("Content-disposition: attachment; filename=$file");
header("Content-Type: application/force-download");
header("Content-Transfer-Encoding: binary");
header("Content-Length: ".strlen($temp_str));
header("Pragma: no-cache");
header("Expires: 0");
echo $temp_str;
*/

header("Content-disposition: attachment; filename=$file");
header("Content-Type: application/force-download");
header("Content-Transfer-Encoding: binary");
header("Content-Length: ".filesize($file));
header("Pragma: no-cache");
header("Expires: 0");
readfile($file);

 

[OldSmelly]

Fatal error: Call to undefined function fputcsv() in /opt/lampp/htdocs/leas/functions.php on line 184

It doesn't know this function

 

[jpadie]

then i guess you don't have a very up to date installation of php.

 

[OldSmelly]

Version 5.05 on apache

 

[jpadie]

fputcsv is (PHP 5 >= 5.1.0RC1)

use the version that is in the comment block or if you want a filesystem version use the following in place of the existing while loop:

while($row=mysql_fetch_assoc($result_csv)):
      foreach ($row as &$val):
         $val = str_replace('"', "'", $val);
      endforeach;
\\      fputcsv($fh, $row, ",", '"');
      $t_temp_str =  '"'.implode('","',$row).'"\n\r';
      $temp_str .= $t_temp_str; //used for the non file based download
      fwrite($fh, $t_temp_str, strlen($t_temp_str);
  endwhile;

 

[OldSmelly]

ok now my code is this

But I use a system with sessions and now I get the famous
"header output already sent"...... error

function create_csv_file($querystring)
{
$file = "test.csv";
$temp_str = "";
$result_csv=mysql_query($querystring);
$fh=fopen($file,"w+") or die ("unable to open file");

while($row=mysql_fetch_assoc($result_csv)):
foreach ($row as $val):
$val = str_replace('"', "'", $val);
endforeach;
$t_temp_str = '"'.implode('","',$row).'"\n\r';
$temp_str .= $t_temp_str;
fwrite($fh, $t_temp_str, strlen($t_temp_str));
endwhile;
fclose($fh);
if ($temp_str > "")
{
header("Content-disposition: attachment; filename=$file");
header("Content-Type: application/force-download");
header("Content-Transfer-Encoding: binary");
header("Content-Length: ".strlen($temp_str));
header("Pragma: no-cache");
header("Expires: 0");
echo $temp_str;
}

 

[jpadie]

it's not sessions that causes the problem. you are outputting something to the browser before the download. even a blank line will cause this problem.

often i see people including the doctype declaration etc before they start the php tags. this won't work.

 

[OldSmelly]

Yes there is output to the screen because this is a function that is called from another form so there is output.
No idea how to avoid that

 

[OldSmelly]

You were right (of course )

There was a blank line

 

[OldSmelly]

BTW Many thanks for helping me out this way much appreciated !!

The next problem occurs, The file is created nicely on my desktop BUT theire is onlu one records in it and excel reports that "The file is not completely loaded"

It's as if it's stil donwloading or such ?

I hope my english is understandable becouse i'm dutch speaking

 

[OldSmelly]

I just found out that the records are all there but excel doesn't know when to go a new row, it places everuthing into one row.

 

[jpadie]

your english is fine. my wife is dutch so we'll muddle by.

i think the problem is in line terminators.

change this line

$t_temp_str =  '"'.implode('","',$row).'"\n\r';

to

$t_temp_str =  '"'.implode('","',$row).'"'. chr(13) . chr(10);

 

[OldSmelly]

Slowly where getting there


Just 2 strange things are happening

1. Theire are some """" fields (I guess the empty ones)
2. The Value "#name" pops up in excel in some rows.

The last one is strange because when I view the file with
say wordpad it does not come up.

Any suggestions for the above ....

Thanks again

 

[jpadie]

1. not sure about this. may be an empty field but then there should a comma.

2. this might be because the underlying data is not a varchar or similar fieldtype but is instead a blob? if not, what does the underlying data look like?

 

[OldSmelly]

The underlying data consists only of int,char fields

This code " header("Content-disposition: attachment; filename=$file");
header("Content-Type: application/force-download");
header("Content-Transfer-Encoding: binary");
header("Content-Length: ".strlen($temp_str));
header("Pragma: no-cache");
header("Expires: 0"); "
is responsable for the transfer of the file to the clients pc am i right ?

Must it be "turned off" because after the file is send and I requery the database for output to the screen instead of the csv file I get someting like this before the display of the data ...

HTTP/1.1 200 OK Date: Wed, 01 Feb 2006 16:53:35 GMT Server: Apache/2.0.55 (Unix) mod_ssl/2.0.55 OpenSSL/0.9.8a PHP/5.0.5 DAV/2 mod_perl/2.0.1 Perl/v5.8.7 X-Powered-By: PHP/5.0.5 Set-Cookie: PHPSESSID=b11afe5f9f01f2fa6d02eec0d399e1d4; path=/ Expires: Thu, 19 Nov 1981 08:52:00 GMT Cache-Control: no-store, no-cache, must-revalidate, post-check=0, pre-check=0 Pragma: no-cache Keep-Alive: timeout=15, max=99 Connection: Keep-Alive Transfer-Encoding: chunked Content-Type: text/html; charset=ISO-8859-1 4dd8

 

[OldSmelly]

I found out how the #naam value appears in excel.

It pops up in field where there is a minus sign and a character in one field as in housnumber addition like in appartment 4 -B. It's what excel makes of it because it's not in the file itself (I checked with wordpad)..

Just thoughed I let you know

 

[jpadie]

thanks.

do you need assistance dealing with this exception or do you have a workaround already?