php output to excel 1

[StuartBombay]

I used code authored by jpadie that I found on this forum, but I can't find the thread again.
I took his code and made a function, and created a page to send a request to the function. It works, accept the spreadsheet that opens also has the HTML header and footer in it. All I want are the results of the query.
The problem lies in my unclear understanding, but I've tried such things as adding a header that relocates to a blank page but I end up with, well, a blank page!
If someone could point out my error I would appreciate it!

The function:

function excel_output($table) {
	global $conn;
echo $table;
	  $file = "test.csv";
	  $temp_str = "";
	  $querystring = "SELECT * from " . $table ."\n";
	  $result_csv=mysqli_query($conn, $querystring);
	  $fh=fopen($file,"w+") or die ("unable to open file"); //note that this APPENDS not overwrites
	  while($row=mysqli_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);      

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

The call:

<?php
$page_title = 'PPS Site Summary  ';
include('header.html');
include('functions/form_functions.inc');
require('../../includes/configdb.inc');
require('../../includes/opendb.inc');
$display_block = "";
$display_name = "<h2>Export Stuff</h2><br>";
global $conn;
	if (!$_POST) {
		//if a selection has not yet been made then show the selection form
		$display_block .= "
		<form method=\"POST\" action\"".$_SERVER["PHP_SELF"]."\">
    Export: 
    <Select name=\"view\">
    <Option value=\"v_program_admin_asst\">Vice Principals</option>
    <Option value=\"v_program_admin\" selected=\"selected\">Principals</option>
    <Option value=\"v_program_details\">Departments</option>
    <Option value=\"v_program_stc\">STCs</option>
    </Select>
    &nbsp&nbsp
    <input type=\"submit\" name=\"submit\" value=\"Export\" />
    </form>";
		$display_block .="</h2>";
		
	} else if ($_POST) {
		//find out how to redirect to a new page so headers don't show on the spreadsheet
		//After a selection has been made open the spreadsheet
			$result = excel_output($_POST['view']);
	} //end else if

echo $page_title = $display_name;
echo $display_block;
include('footer.html');
//Close the connection
require('../../includes/closedb.inc');
?>

 

[jpadie]

get rid of the echo $table and add an exit at the end of the exceloutput. basically you cannot output anything other than the excel file as http accepts only a single content type at a time.

 

[StuartBombay]

Doh!, of course! I never thought of 'exit()'. I also had to move the header into !POST part of the if statement.
I have about 5 blank rows at the beginning of the spreadsheet, I don't have empty records in my results... so I'll have to hunt that one down.

Thanks again-

 

[StuartBombay]

Sadly, when I move from localhost to the linux box it cannot open excel.

 

[jpadie]

excel is a windows program? why would you expect to be able to open it on a linux box?

and just to be obvious, you're not _opening_ excel when you create the excel like file. You're just creating a schema that Excel will be OK with.

 

[StuartBombay]

I was expecting it to open excel locally, not attempt to open it on the server.

 

[southbeach]

Isn't this a good time to use COM? There is also this

http://tek-tips.com/viewthread.cfm?qid=1496594&page=1

Using activeX through javascript.



--
SouthBeach
The good thing about not knowing is the opportunity to learn - Yours truly, 2008.

 

[StuartBombay]

COM is a good thought, and I may go that way.
Oddly, in at attempt to see if I could get a more descriptive error with the php function on the linux server, I commented out "or die" on the file open command.
I now get the Open dialog and can open an instance of Excel, however nothing is written to the spreadsheet. Progress, but also food for further puzzlement.
I've not enough experience to know what I should be troubleshooting, the headers, the file open command or php.ini

 

[vacunita]

Oddly, in at attempt to see if I could get a more descriptive error with the php function on the linux server, I commented out "or die" on the file open command.

Ths to me points to the fopen command. The "or die" section there terminates execution of the PHP script if it encounters a problem opening the file. When removed keeps the script going even though there was a problem at that point.

It signals a possible permissions problem when trying to fopen the file in question. I would start by making sure the you have correct permissions to the directory where the file resides. You may have them in the Windows box. But not in the Linux box.




----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[southbeach]

Take a look at this class

[URL unfurl="true"]http://www.fpgroups.com/index.php?Target=code&Action=read&Code=31[/URL]

I found it some time ago and have been using it with great success so far. That said, I am running off WIN 2003 server not Linux.

I suspect it should work just fine for you ...

Good luck!


--
SouthBeach
The good thing about not knowing is the opportunity to learn - Yours truly, 2008.

 

[ingresman]

I havn't looked at this

http://www.codeplex.com/PHPExcel

but I wonder if it would port to *nix ?