dynamically generated excel files with php and mysql

[metazai]

Here's one . . . I have a client who has stumped me. Got a site built on PHP4/MySQL. The client wants a logged-in user to click on a button which will open an excel spreadsheet (all their clients have excel) that contains some information culled from the database . . . say 15-16 items of information, and allow them to edit it, click a button, and save it back to the database/file/whatever.

I don't even know where to start or if such a thing is possible. Any ideas?

[sde]

excel can work with tab delimited files. you can use headers in a php script to manipulate the file name and force a download.

PHP Code:

<?

$filename = "test.xls";

header("content-type: application/vnd.ms-excel");
header("Cache-Control: no-store, no-cache, must-revalidate"); // HTTP/1.1
header("Cache-Control: post-check=0, pre-check=0", false);
header("Pragma: no-cache"); // HTTP/1.0
header("Cache-control: private");
header("Content-Disposition: attachment; filename=$filename");
header("Content-Transfer-Encoding: ascii");

echo "Joe\tSmith\t123 B St.\tSpringfield\tMI\t12345\n";


?>

They can make edits, but they must save as tab delimited. Then you write an upload script and parse it with fgetcvs() line by line and do what you need to with the data.

[metazai]

Very clever. Let me show you the problem I'm having with it. I've got a page with a link that will transfer the username over to this, i.e.

<a href="includes/census_xls.php?customername=<? echo($row->username); ?>">Edit This as an Excel Spreadsheet </a>

The entire "census_xls.php" page looks like this:

PHP Code:

<?
$dbhost = '*************';
$dbuser = '*************';
$dbpass = '*************';
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die                      ('Error connecting to mysql');
$db="benef007";
mysql_select_db($db)
or die("Select DB Error: ".mysql_error());
$sql="SELECT * FROM customers WHERE username='$customername'";
$result=mysql_query($sql);
$row = mysql_fetch_object($result);
$filename = "be_census.xls";
header("content-type: application/vnd.ms-excel");
header("Cache-Control: no-store, no-cache, must-revalidate"); // HTTP/1.1
header("Cache-Control: post-check=0, pre-check=0", false);
header("Pragma: no-cache"); // HTTP/1.0
header("Cache-control: private");
header("Content-Disposition: attachment; filename=$filename");
header("Content-Transfer-Encoding: ascii");
echo ("NAME OF PLAN: 401(k) Plan\t\t\t\t\tDATA CERTIFIED BY:\t".$row->certby."\n");
echo ("\t\t\t\t\t\tDATE:\t".$row->formdate."\n");
echo ("\t\t\t\t\t\t\tHOURS WORKED CODE\n");
echo ("\t\t\t\t\t\t\t1= 1,000 HOURS OR MORE\n");
echo ("\t\t\t\t\t\t\t2= 501 to 999\n");
echo ("\t\t\t\t\t\t\t3= 500 HOURS OR LESS\n");
echo ("NAME\tSOC. SEC. NUMBER\tSEX\tDATE OF BIRTH\tDATE OF HIRE\tDATE OF TERM\tCOMPENSATION 10/1/2005-9/30/2006\tHOURS WORKED CODE\tOWNER %\tOFFICER Y/N\n");
echo ($row->lastname.", ".$row->lastname."\t".$row->social."\t".$row->sex."\t".$row->dob."\t".$row->doh."\t".$row->dot."\t".$row->comp."\t".$row->hw."\t".$row->owner."\t".$row->officer."\n"); ?>

This brings up a download dialogue, but all it gives is "Internet Explorer cannot download from census_xls.php?customername=blahblahblah"

Trying to work it through. Any ideas?

[sde]

i'm not sure, i tested in firefox.

try changing the content type to this:

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