Get data from an online registration form and store the data and show it using Excel spreadsheet

[princesohrab]

Hi all.. I need help.I've been asked to create and online registration form and when the form is submitted, the data has to go on a spreadsheet (Excel). I'm assuming a database where the registration details is viewed in Excel Spreadsheet.I have done form to send data to email before but not to Excel spreadsheet..Also, I'm not sure if PHP can be used or ASP.Any help would be greatly appreciated.Many thanks

[jeffman]

You don't need a database to do this. That just adds an extra step. If you want to save the file in native Excel format, you'll need to license a special library. But you don't have to do that. Fortunately, Excel reads flat files and converts them into spreadsheet format. A flat file in this case contains a unique line for each "row". Each "field" in each row is separated from the previous field with a tab. So all you really need to do is read and write to a text file.

Edited June 11, 2013 by Deirdre's Dad

[niche]

Here's how php would do it: http://www.w3schools.com/php/php_file.asp

[princesohrab]

Thanks guys.. Do I still use the form action as usual??And is the script a separate file?Have any

[princesohrab]

Thanks guys.. Do I still use the form action as usual??And is the script a separate file?Have any of you got an example of how it's done?Please help

[niche]

Please post your code as far as you've got using the code tags.

[justsomeguy]

I've used PHPExcel to create native Excel files: http://www.google.com/search?client=opera&q=phpexcel&sourceid=opera&ie=utf-8&oe=utf-8&channel=suggest It's fairly heavyweight, but works fine. Keep in mind that there are limitations in Excel, a spreadsheet can have a maximum of 256 columns and 65536 rows. If you have more than that then you have to split your data up into multiple files or else Excel will just ignore the additional data.

Do I still use the form action as usual?? And is the script a separate file?

Conceptually it's no different than any other form handler. You submit the data to a PHP script, and it handles the data. In this case it's saving it to a file, it could be emailing it or adding it to a database or whatever else, it's the same setup. It doesn't really matter what the code does, it's just a form handler. Set it up like any other form handler. If you want to build a text file instead of a native Excel file, then put your data in an array and use a function like this to write it to the file: http://php.net/manual/en/function.fputcsv.php If the data contains UTF characters then you'll need to output a BOM at the start of the text file, and you'll need to make sure that every field is quoted, even if it doesn't contain the CSV delimiter. If you scroll down on the manual page for fputcsv you'll find several other functions that people have written that are similar, like converting an array to a CSV string and returning the string instead of writing to a file.

[princesohrab]

This is the html code for the form and below it is the PHP code I was thinking of using so the data can be sent to an email address.But they want it to be stored and be able to read on spreadsheet excel.

<form id="form1" method="post" action="w-hill-contactformprocess.php">				    <table width="90%" border="0" align="center" cellpadding="6" cellspacing="2" onfocus="MM_validateForm('name','','R','email','','RisEmail','telephone','','NisNum');return document.MM_returnValue">				   				   					  <tr>					    <td align="right"><label for="name">Name</label></td>					    <td align="left"><input name="name" type="text" id="name" size="45" maxlength="30" /></td>					  </tr>					 				   				   					  <tr>					    <td align="right"><label for="worklocation">Work Location</label></td>					    <td align="left"><input name="worklocation" type="text" id="worklocation" size="45" maxlength="30" /></td>					  </tr>					 					 					 					  <tr>					    <td align="right"><label for="worknumber">Work Contact Number</label></td>					    <td align="left"><input name="worknumber" type="text" id="worknumber" size="45" maxlength="90" /></td>					  </tr>					 					 					 					  <tr>					    <td align="right"><label for="mobile">Mobile Number</label></td>					    <td align="left"><input name="mobile" type="text" id="mobile" size="45" maxlength="12" /></td>					  </tr>					 					 					 					  <tr>					    <td align="right"><label for="email">Email Address</label></td>					    <td align="left"><input name="email" type="text" id="email" size="45"  /></td>					  </tr>					 					 					 					  <tr>					    <td align="right"><label for="abilitylevel">Ability Level</label></td>					    <td align="left">				    <select name="Ability">	 <option value="Strong">Strong</option>	 <option value="Medium">Medium</option>	 <option value="Weak">Weak</option>	 </select>					    </td>					  </tr>					 					 					 					 					  <tr>					    <td align="right"><label for="abilitylevel">Tennis Standard</label></td>					    <td align="left">				    <select name="Standard">	 <option value="Strong">School</option>	 <option value="Medium">Club</option>	 <option value="Weak">County</option>				    <option value="Weak">International</option>	 </select>					    </td>					  </tr>					 					 					 					 					  <tr>					    <td align="right"><label for="abilitylevel">Frequency of Play</label></td>					    <td align="left">				    <select name="Ability">	 <option value="Strong">Weekly</option>	 <option value="Medium">Monthly</option>	 <option value="Weak">Few Times a Year</option>	 </select>					    </td>					  </tr>					 					 					 					  <tr>					    <td align="right"><label for="comments">Anything else you would like to tell us</label></td>					    <td align="left"><textarea name="comments" id="comments" cols="35" rows="6"></textarea></td>					  </tr>					 					 					 					  <tr>					    <td align="right"> </td>					    <td align="left"><label for="submit"></label>					    <input name="submit" type="submit" id="submit" onclick="MM_validateForm('email','','RisEmail','telephone','','NisNum');return document.MM_returnValue" value="Submit" /></td>					  </tr>					 				  </table>	  </form>

<?php    $emailSubject = 'Tennis Registration!';$webMaster = 'sohrab.goodar@yahoo.com';$nameField = $_POST['name'];$emailField = $_POST['email'];$telephoneField = $_POST['telephone'];$commentsField = $_POST['comments'];$body = <<<EOD<br><hr><br>Name: $nameField <br>Email: $emailField <br>Telephone: $telephoneField <br>Comments: $commentsField <br>EOD;$headers = "From: $email\r\n";$headers .= "Content-type: text/html\r\n";$success = mail($webMaster, $emailSubject, $body, $headers);$theResults = <<<EOD<p>Thank you for your Message! You will be redirected to the home page in a moment</p><script type="text/javascript" >setTimeout ('ourRedirect()', 3000)function ourRedirect(){  location.href='index.html'}</script>EOD;echo "$theResults";?>

[princesohrab]

I have never worked with spreadsheet before and not sure how to proceed.

<form id="form1" method="post" action="w-hill-contactformprocess.php">

[justsomeguy]

Yes, you already explained what you want. Like we said, you can use PHPExcel to create XLS files, or you can create a CSV file, whatever you would rather do. If you want to email the file as an attachment I would recommend using the Pear Mail package for that instead of the built-in mail function.

[justsomeguy]

I have never worked with spreadsheet before and not sure how to proceed.

Start by figuring out if you want to create a XLS or CSV file, and then look at the examples. There are plenty of examples online. http://www.google.com/search?client=opera&q=create+spreadsheet+with+php&sourceid=opera&ie=utf-8&oe=utf-8&channel=suggest

[princesohrab]

Im overwhelmed.. :sorry:This is what i found on how to start it in php.

<?phpheader( "Content-Type: application/vnd.ms-excel" );header( "Content-disposition: attachment; filename=spreadsheet.xls" );// print your data here. note the following:// - cells/columns are separated by tabs ("\t")// - rows are separated by newlines ("\n")// for example:echo 'First Name' . "\t" . 'Last Name' . "\t" . 'Phone' . "\n";echo 'John' . "\t" . 'Doe' . "\t" . '555-5555' . "\n";?>

Do I declare the variable the same way as I did with the PHP file i pasted earlier?

[princesohrab]

how do I name the file so that I can call it from the contact form? ie: form action

[princesohrab]

This is where I got the snippet http://www.lateralcode.com/excel-spreadsheets-php/

[princesohrab]

Remember i nned to grab only the data that the person registering input

[justsomeguy]

That example is going to prompt the user to save or open the file, that won't email anything if that's what you're trying to do. Sending headers is for downloading.

Do I declare the variable the same way as I did with the PHP file i pasted earlier?

What variable? You only declare variables one way.

how do I name the file so that I can call it from the contact form?

The same way you name any other PHP file.

[thescientist]

it's pretty straightforward. 1) You create the form that will pass all the user data2) the action of the form, is the PHP filename that will do all this work3) the PHP page the form submits to collects all the user data (form GET or POST)4) and does whatever you want with it* writes to a CSV files or* writes to an XLS file or* emails you or the user* whatever else you want it to do step 4 is up to you, but the first three are the same regardless, as JSG said, it is just basic form handling until you get to step 4.

Edited June 11, 2013 by thescientist

[princesohrab]

ok.. I want it to write to an XLS file. How do i set up that file on the server andWhat are the functions on the PHP file?Have you got an example please? Im in real trouble as I need to get that done by tomorrow morning. this task is part of a recruitment process. Basically they told me: (Set up an “apply online” functionality – so that the person who review the data can get all the information he needs to get a team together. This should be provided on an excel spreadsheet) They also said: (It is not useful for members of the management team to receive emails every time someone applies in these circumstances so we usually have functionality which transfers the information into an excel spreadsheet so once the closing date) has passed we return the necessary data on this.

[justsomeguy]

It sounds like they want to save things in a database and periodically dump them to files. If you want to use PHPExcel there are examples here: http://phpexcel.codeplex.com/wikipage?title=Examples&referringTitle=Home The code that I've written that uses that is too complex to use as an example to learn how to do it. You're going to have to learn how to do this yourself, if this is part of a test for getting a job then obviously they expect you to be able to do your own research and figure out how to do what they need you to. If you don't get the job, then you're not who they're looking for. When I needed to start with PHPExcel I read the documentation and examples for it, and I played around with it to see how it works. That's what you need to do.