[SOLVED] PHP export to excel

[244863]

When using the below code to take sql results and export to excel it has a prompt for save or open. is there a way I can modify this code to autosave to a specific location instead of asking??

 

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

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

 

# replace excelfile.xls with whatever you want the filename to default to

header("Content-Disposition: attachment; filename=register.xls");

header("Pragma: no-cache");

header("Expires: 0");

 

thanks,

[MadTechie]

You can't "auto save" to the clients PC (thats a security risk), but you could save it on the server!

[244863]

sorry that is what I wanted to do is save it to the server, do you have any examples??

[MadTechie]

Erm.. file_put_contents();

[Mark Baker]

For sending to the client browser

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

Not "Content's", but "Content-Type"

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

and if we're being technical, it's "application/vnd.ms-excel" for xls and "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" for xlsx rather than simply "application/octet-stream"

 

You might also use:

header ('Content-Transfer-Encoding: binary');
header ('Content-Length: '.$fileSize);

 

[244863]

I have that working. what about if I had a table that gained its results from php for example below, how would I get that to export to excel and save a copy on the local machine automatically so that I did not need any user interaction.

 

<div class="informationArea" style="width: 600px;">

<table cellspacing="0" cellpadding="0"

style="width: 600px; font-size: 85%;" class="sortable">

<tr>

<th>Start Date</th>

<th>Forename</th>

<th>Surname</th>

</tr>

<?

$sql = 'select * from ' . oracle_database . '."VRELIENCEREG"';

$sql = OCI_Parse ( $conn2, $sql );

OCI_Execute ( $sql );

$counter = 0;

 

while ( OCI_Fetch ( $sql ) ) {

 

    $header .= oci_field_name($sql)."\t";

   

$row = oci_fetch_row($sql);

  $line = '';

  foreach($row as $value){

    if(!isset($value) || $value == ""){

      $value = "\t";

    }else{

# important to escape any quotes to preserve them in the data.

      $value = str_replace('"', '""', $value);

# needed to encapsulate data in quotes because some data might be multi line.

# the good news is that numbers remain numbers in Excel even though quoted.

      $value = '"' . $value . '"' . "\t";

    }

    $line .= $value;

  }

  $data .= trim($line)."\n";

?>

 

    <tr>

    <td><?php echo oci_result ( $sql, "Start_Time" );?></td>

    <td><?php echo oci_result ( $sql, "Forenames" );?></td>

    <td><?php echo oci_result ( $sql, "Surname" );?></td>

</tr>

<?php

}

?>

</table>

</div>

<?php  $data = str_replace("\r", "", $data);

 

 

# Nice to let someone know that the search came up empty.

# Otherwise only the column name headers will be output to Excel.

if ($data == "") {

  $data = "\nno matching records found\n";

}

?>

</div>

[Mark Baker]

I have that working. what about if I had a table that gained its results from php for example below, how would I get that to export to excel and save a copy on the local machine automatically so that I did not need any user interaction.

You wouldn't, not without the need for any user interaction, unless you have direct access to that local machine from your server through (for example) FTP or shared network drives.

 

Consider the situation: If I have a script that creates an executable which I can send to your local machine automatically without any need for user interaction; and that executable reformats your hard disk when it runs... the only difference between that and what you want is the "payload" of the file. If it was possible to do what you're asking, then it would be possible for more malevolent hackers to do whatever they wanted with no security barrier to stop them.

[244863]

What I want to do is on a daily basis I want an event on the server to run this page in the background so that it grabs database records from oracle / sql and automatically creates an excel file in a folder on the same server. And I dont know how to do it, the code above shows you how i am printing out the table in php and the getting the data from oracle. but I thought there must be a way of codeing a solution to my problem...

[Mark Baker]

To run a task on the server on a daily basis, use cron