Print out contents of to Excel

[travelkind]

I have got this script that gathers all the data that I need but I need it to send it to excel instead  back to the screen in the web browser.  How can I do this? Should I use something like:

 

$csv_output .= "\n";

 

Here is my code:

 

// Make a MySQL Connection

// Construct our join query

$query = "SELECT sunocoimport.duns_num, custmast.gl_num, custmast.division_num, custmast.customer_num, sunocoimport.summary_date, sunocoimport.cc_fees, custmast.fee_percent, custmast.clear FROM sunocoimport, custmast WHERE sunocoimport.duns_num = custmast.duns_num AND (sunocoimport.cc_fees * custmast.fee_percent / 100) <> 0";

 

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

 

// Print out the contents of each row into a table

while($row = mysql_fetch_array($result)){

          echo $row['division_num'].",".$row['customer_num'].",".$row['summary_date'].",".round($row['cc_fees'] * $row['fee_percent'] /100 , 2) .",".$row['clear'].",".$row['gl_num'];

          echo "<br />";

}

?>

 

Thanks for your help!

[Mark Baker]

user the headers() function to set the appropriate header information to tell the browser that it's getting a csv file rather than html.... then echo out your csv content

 

PS get rid of '<br />'. That's purely html. For a csv file you want a line feed "\n" instead

[travelkind]

I am not familiar with the header() function but I looked it up and apparently it "must be called before any actual output is sent, either by normal HTML tags, blank lines in a file, or from PHP".  Here is an example I found of the header() function:

 

<?php

header("Location: http://www.example.com/"); /* Redirect browser */

 

/* Make sure that code below does not get executed when we redirect. */

exit;

?>

 

I guess I need to change the web address to be a csv output.  What would I need to change on the code and where exactly would I place it?  Thanks again for your help!

 

[ScotDiddle]

travelkind,

 

    Something like the following...

 

    The full script can be found here: http://www.stargeek.com/php_scripts.php?script=2

 

Scot L. Diddle, Richmond VA

 

<?php


# This line will stream the file to the user rather than display it across the screen
header("Content-type: application/octet-stream");

# replace $ExcelFileName. with whatever you want the filename to default to
header("Content-Disposition: attachment; filename=" . $ExcelFileName . "");
header("Pragma: no-cache");
header("Expires: 0");


echo $fileOutContents;

?>

[travelkind]

Scot,

Thanks for your help and for the location of all those additional scripts.  Where do I need to place your code snipet?  Here is my code:

 

// Make a MySQL Connection

// Construct our join query

$query = "SELECT sunocoimport.duns_num, custmast.gl_num, custmast.division_num, custmast.customer_num, sunocoimport.summary_date, sunocoimport.cc_fees, custmast.fee_percent, custmast.clear FROM sunocoimport, custmast WHERE sunocoimport.duns_num = custmast.duns_num AND (sunocoimport.cc_fees * custmast.fee_percent / 100) <> 0";

 

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

 

// Print out the contents of each row into a table

while($row = mysql_fetch_array($result)){

          echo $row['division_num'].",".$row['customer_num'].",".$row['summary_date'].",".round($row['cc_fees'] * $row['fee_percent'] /100 , 2) .",".$row['clear'].",".$row['gl_num'];

          echo "<br />";

}

?>

 

 

Thanks again!

[ScotDiddle]

travelkind,

 

Paste:

 

<?php	

# This line will stream the file to the user rather than display it across the screen	
header("Content-type: application/octet-stream");	

# replace $ExcelFileName. with whatever you want the filename to default to	
header("Content-Disposition: attachment; filename=" . $ExcelFileName . "");	
header("Pragma: no-cache");	
header("Expires: 0");

 

at the top of your script, then:

 

Change:

 

 

// Print out the contents of each row into a table
while($row = mysql_fetch_array($result)){
          echo $row['division_num'].",".$row['customer_num'].",".$row['summary_date'].",".round($row['cc_fees'] * $row['fee_percent'] /100 , 2) .",".$row['clear'].",".$row['gl_num'];
          echo "<br />";
}
?>

 

To:

 

// Print out the contents of each row into a table

$spreadSheetContent = '';

while($row = mysql_fetch_array($result)){
                    $spreadSheetContent .= $row['division_num'].",".$row['customer_num'].",".$row['summary_date'].",".round($row['cc_fees'] * $row['fee_percent'] /100 , 2) .",".$row['clear'].",".$row['gl_num'];
                    $spreadSheetContent .=  "<br />";
}

 

Then change echo $fileOutContents; to $spreadSheetContent

[travelkind]

Scot,

I made all the changes except: "Then change echo $fileOutContents; to $spreadSheetContent"  I am not sure where echo $fileOutContents is located.

 

The changes basically opened a text editor with out any data.  I assume I am not getting data because of the echo out statement.  In case you need it, here is my code:

 

<?php

 

# This line will stream the file to the user rather than display it across the screen

header("Content-type: application/octet-stream");

 

# replace $ExcelFileName. with whatever you want the filename to default to

header("Content-Disposition: attachment; filename=" . $ExcelFileName . "");

header("Pragma: no-cache");

header("Expires: 0");

 

$dbhost = "";

$dbuser = "";

$dbpassword = "";

 

$dbdatabase = "";

 

$db = mysql_connect($dbhost, $dbuser, $dbpassword);

mysql_select_db($dbdatabase, $db);

 

// Make a MySQL Connection

// Construct our join query

$query = "SELECT sunocoimport.duns_num, custmast.gl_num, custmast.division_num, custmast.customer_num, sunocoimport.summary_date, sunocoimport.cc_fees, custmast.fee_percent, custmast.clear FROM sunocoimport, custmast WHERE sunocoimport.duns_num = custmast.duns_num AND (sunocoimport.cc_fees * custmast.fee_percent / 100) <> 0";

 

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

 

// Print out the contents of each row into a table

 

$spreadSheetContent = '';

 

while($row = mysql_fetch_array($result)){$spreadSheetContent .= $row['division_num'].",".$row['customer_num'].",".$row['summary_date'].",".round($row['cc_fees'] * $row['fee_percent'] /100 , 2) .",".$row['clear'].",".$row['gl_num'];                   

$spreadSheetContent .=  "<br />";

 

}

?>