Smart questions
Smart answers
Smart people
Join Tek-Tips Forums
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

OldSmelly (Programmer) (OP)
1 Feb 06 4:27
I'm on fire now :D

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";
         }
}
Helpful Member!(2)  jpadie (TechnicalUser)
1 Feb 06 5:03
try this instead.  make sure there is no html or anything else being sent to the browser.

CODE

$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 (Programmer) (OP)
1 Feb 06 5:15
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 (TechnicalUser)
1 Feb 06 6:02

CODE

  $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 (Programmer) (OP)
1 Feb 06 6:31
Fatal error: Call to undefined function fputcsv() in /opt/lampp/htdocs/leas/functions.php on line 184

It doesn't know this function
jpadie (TechnicalUser)
1 Feb 06 6:45
then i guess you don't have a very up to date installation of php.
OldSmelly (Programmer) (OP)
1 Feb 06 6:59
Version 5.05 on apache
jpadie (TechnicalUser)
1 Feb 06 7:18
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:

CODE

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 (Programmer) (OP)
1 Feb 06 8:17
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 (TechnicalUser)
1 Feb 06 8:41
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 (Programmer) (OP)
1 Feb 06 8:45
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 (Programmer) (OP)
1 Feb 06 8:48
You were right (of course  :D )

There was a blank line
OldSmelly (Programmer) (OP)
1 Feb 06 8:54
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 :D
OldSmelly (Programmer) (OP)
1 Feb 06 9:08
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 (TechnicalUser)
1 Feb 06 9:29
your english is fine.  my wife is dutch so we'll muddle by.

i think the problem is in line terminators.

change this line

CODE

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

to

CODE

$t_temp_str =  '"'.implode('","',$row).'"'. chr(13) . chr(10);
OldSmelly (Programmer) (OP)
1 Feb 06 11:38
Slowly where getting there smile


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 (TechnicalUser)
1 Feb 06 11:45
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 (Programmer) (OP)
1 Feb 06 11:52
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 (Programmer) (OP)
2 Feb 06 0:41
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 (TechnicalUser)
2 Feb 06 1:16
thanks.

do you need assistance dealing with this exception or do you have a workaround already?
OldSmelly (Programmer) (OP)
2 Feb 06 1:32
No workaround ready at the moment :D

I'm uploading about 2 milion records into my mysql database at the moment......  smile
Vragabond (Programmer)
2 Feb 06 2:14
Excel clearly thinks that is a reference of some sort and is trying to look for it, but gets confused.  Best way to avoid things like that is to precede all your fields with a single quote (').  That is not printed in the field itself but it will tell Excel that a field is a regular text field and nothing else.
OldSmelly (Programmer) (OP)
2 Feb 06 2:50
Thanks for the input

Im gonna try this later this day, now I'm, off to work...

Hans

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close