Save PHP Form to Excel 1

[laurajones]

Ok I will keep this short and sweet. I am trying to create a PHP UserForm, that will open an existing Excel Workbook (on our server) and save the input from the form to it. The user never needs to see the Excel sheet. I have the html part of the form created but that's it. I'm not sure how to do the rest. Can someone please point me in the right direction? Thanks.

 

[jpadie]

are you sure that's a good idea? storing structured data in a spreadsheet does not seem optimal. perhaps it would be better to store the data in a database and then attach to the database via the spreadsheet?

anyway, if you are set on doing this then i'd advise not keeping the data in XLS format but in csv. that is easily written/read in php.

If it _must_ be in xls(x) format then i'd investigate the excel writer and excel reader packages within pear.

http://sourceforge.net/projects/phpexcelreader/

http://pear.php.net/package/Spreadsheet_Excel_Writer

 

[IPGuru]

I would have to second the advise of jpadie.

Data should be stored in a database not a spreadsheet (spreadsheets often get miss-used like this because they are quick & simple to set-up but are very inefficient when they become large & a security hole for sensitive data)

if it is not important enough to store if a proper database then it is better to use a CSV file rather than a proprietary format, it will make it easier to process the data in other applications should the need arise.



I do not Have A.D.D. im just easily, Hey look a Squirrel!

 

[laurajones]

No, I'm not deadset on it being stored directly to the Excel sheet, just as long as it can be pulled up in Excel when the bosses need it. I don't know how to save it to a database either, so can either of you provide some assistance?

 

[vacunita]

Sounds like you are in over your head.

To keep it easy for both you and the Excell happy bosses I would use a CSV format as well as mentioned by jpadie and IPGuru.

The easiest way to do this, is to simply create a file using fopen, and write to it your comma separated strings using fwrite or even fputcsv().

Take a look at the PHP online manual:

http://php.net/manual/en/function.fputcsv.php

http://php.net/manual/en/function.fopen.php

http://php.net/manual/en/function.fwrite.php\

If you need more help, we need more specific details of what you want to store, and how your Excel file should look.





----------------------------------
Phil AKA Vacunita
----------------------------------
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.

Behind the Web, Tips and Tricks for Web Development.

http://behindtheweb.blogspot.com/

 

[jpadie]

not tested but this might get you started. key thing is to ensure that each field in your form is listed (case sensitively) in the $fields array at the top of the script.

the field names MUST match the form names (case sensitive again).

to trigger a download point your browser at the script and add

&action=download

here is the code

<?php
ob_start();
$fields = array('name','emailaddress','message');
$host = 'localhost';
$dbname = 'mydb';
$user = 'myuser';
$pwd = 'mypwd';

if (isset($_REQUEST['action'])):
	switch (strtolower($_REQUEST['action'])):
		case 'save':
			savedata();
			showthankyoupage();
			break;
		case 'download':
			downloaddata();
			break;
	endswitch;	
else:
	displayform();
endif;

function connectdb(){
	global $host, $dbname, $user, $pwd;
	global $pdo;
	$pdo = new PDO( 
    	"mysql:host=$hose;dbname=$dbname", 
    	$user, 
    	$pwd 
    	array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8" 
	); 
}
function savedata(){
	global $fields;	
	foreach($fields as $field):
		$placeholder[] = '?';
		$params[] = empty($_REQUEST[$field]) ? '' : trim($_REQUEST[$field];
	endforeach;
	connectdb();
	global $pdo;
	$s = $pdo->prepare("Insert into formdata (". implode(',', $fields) . ") VALUES (". implode(',',$placeholder) .")");
	$pdo->execute($params);
	return true;
}

function showthankyoupage(){
	ob_end_clean();
	include 'thankyoupagetemplate.php';
	exit;
}

function downloaddata(){
	global $fields;
	connectdb();
	global $pdo;
	$s = $pdo->prepare("select * from formdata");
	$s->execute();
	$output = <<<OP
<table>
	<thead>
		<tr>
			<th>
OP;
	$output .= implode('</th><th>', $fields);
	$output .= '</th></tr></thead>';
	$output .= '<tbody>';
	while ($row = $s->fetchObject()):
		$inner = array();
		foreach($fields as $field):
			$inner[] = '<td>' . htmlspecialchars($row->$field) . '</td>';
		endforeach;
		$output .= '<tr>' . implode("\n", $inner) . '</tr>';
	endwhile;
	$output .= "</tbody></thead>";
	ob_end_clean();
	header("Content-Disposition: attachment; filename=\"formData.xls\"");
	header("Content-Type: application/vnd.ms-excel");
	echo $output;
}

function displayform(){
	include 'formpage.php';
}
?>

 

[ingresman]

Excel is used for some interesting things these days not just simple spreadsheets.
Have a look at

http://phpexcel.codeplex.com/

which might be of interest.

 

[stentorian]

Why Excel? And what about Linux users?

http://phpforms.net/

- online form builder

 

[ingresman]

The MS$ class should work on Linux as it's all writen in PHP and creates the XML format rather than the older binary format