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 Forums!
  • 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.

export to excel data

export to excel data

(OP)
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";
         }
}

RE: export to excel data

2
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.

RE: export to excel data

(OP)
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 ?

RE: export to excel data

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);

RE: export to excel data

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

It doesn't know this function

RE: export to excel data

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

RE: export to excel data

(OP)
Version 5.05 on apache

RE: export to excel data

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;

RE: export to excel data

(OP)
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;
     }

RE: export to excel data

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.

RE: export to excel data

(OP)
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

RE: export to excel data

(OP)
You were right (of course  :D )

There was a blank line

RE: export to excel data

(OP)
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

RE: export to excel data

(OP)
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.

RE: export to excel data

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);

RE: export to excel data

(OP)
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

RE: export to excel data

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?

RE: export to excel data

(OP)
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



  

RE: export to excel data

(OP)
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

RE: export to excel data

thanks.

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

RE: export to excel data

(OP)
No workaround ready at the moment :D

I'm uploading about 2 milion records into my mysql database at the moment......  smile

RE: export to excel data

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.

RE: export to excel data

(OP)
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!

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