Php – write form data to xls file

PHPphpexcel

I just wanted to know if this is possible and if so, can i get a starting point.

Right now, I am looking to create an html form, and POST the data to an excel spreadsheet. I will be using PHP to POST the form data. The form will be set up as 2 tables. The 2 tables will be labeled as a Shortage table, and the other as an Overage table. I have a drop down menu with the names of people. what i am trying to accomplish is have the data write to an excel spreadsheet and separate the values from the shortage table and the values from the overage table. For example, if the shortage table contains 4 entries, and the overage contains 2 entries, in the excel spreadsheet i would like for cell A1 to say overage, row 2 will contain the column headers (i.e. date, description, size, etc…), and then have row 8 start off Overage, row 9 will have the headers, and row 10 on will contain the values inputted. Granted this will not always be the case, just an example.

My major goal is to have it set up this way. Each person will have a worksheet created specifically for them. This form will be used on a daily basis, so the filename will have the date on there. So each day the form will opened, and once the submit button has been pressed, i would like to have a worksheet created for that person containing the data from the 2 tables. For example, Bill Smith is the first entry today. the information from the 2 tables has been entered in, I select the submit button. Within the directory, there is an excel file labeled as, data03/29/16.xls. Within that excel workbook, a worksheet will contain the label/tab Bill Smith.

Is this at all possible, or would it be easier and efficient to write the form data to a CSV file?

Edit:

<!DOCTYPE HTML>

<html>

<head>
<title> Drive Check In </title>

<script type = "text/javascript">

function reset()
{
  var driver = document.getElementById("drivers");
  var date = docuemt.getElementById("dates");
  var custnum = document.getElementById("customernum");
  var invnum = document.getElementById("invoicenum");
  var proddesc = document.getElementById("proddes");
  var sz = document.getElementById("size");
  var cs = document.getElementById("cs");
  var btls = document.getElementById("btls");
  var chkint = document.getElementById("chkitls");

  driver.value.reset();
  date.value.reset();
  custnum.value.reset();

}

</script>

</head>

<body>

<form action = '' method = "post">
<table id = "shortages" >
<h1> Shortages </h1>
<thead>
<tr>
  <th> Driver </th>
  <th> Date </th> 
  <th> Customer# </th>
  <th> Invoice# </th>
  <th> Product Description </th>
  <th> Size </th>
  <th> CS </th>
  <th> Btls </th>
  <th> CHK Itls </th>
</tr>
</thead>
<tr>
  <td>
  <select id = "drivers" name = 'drivers'>
    <option value = " "> </option>
    <option value = "Driver1"> Driver1 </option>
    <option value = "Driver2"> Driver2 </option>
    <option value = "Driver3"> Driver3 </option>

  </select></td>

  <td> <input type = "text" id = "dates"size = "10" name = "dates"> </input> </td>
  <td> <input type = "number" min = "1" max = "99999999" id = "customernum" name = "customernum"> </input> </td>
  <td> <input type = "number" min = "1" max = "99999999" id = "invoicenum" name = "invoicenum"> </input> </td>
  <td> <input type = "text" id = "proddes" name = "proddes"> </input> </td>
  <td> <input type = "number" id = "size" name = "size"> </input> </td>
  <td> <input type = "number" id = "cs" name = "cs"> </input> </td>
  <td> <input type = "number" id = "btls" name = "btls"> </input> </td>
  <td> <input type = "text" id = "chkitls" name = "chkitls"> </input> </td>
</tr>

</table>

<input type = "submit" value = "submit" name = "mydrivers>

<?php

function myfputcsv($handle, $array, $delimiter = ',', $enclosure = '"', $eol = "\n") {
    $return = fputcsv($handle, $array, $delimiter, $enclosure);
    if($return !== FALSE && "\n" != $eol && 0 === fseek($handle, -1, SEEK_CUR)) {
        fwrite($handle, $eol);
    }
    return $return;
}

if(isset($_POST['mydrivers']))  {  
$header=array();
$data=array();
 foreach (array_slice($_POST,0,count($_POST)-1) as $key => $value) {    
     //$header[]=$key;
     $data[]=$value;
 }  
$fp = fopen('driver.csv', 'a+');
    //fputcsv($fp, $header);
    myfputcsv($fp, $data);
fclose($fp);
}

?>
</form>

</html>

This is the php file that I have so far. I was using this without implementing your suggestions, but I will implement them.

This is my main output goal. After the cvs file is opened and formatted, this is the main goal.

Main output goal

Edit 2:
After implementing your code, this is the output I am receiving. Output screen

Best Answer

The first thing you need to do is learn PHPEXCEL. The second and quicker way is to use header() and print your expected output in a normal html table:

<?php

$col1row = $_POST['col1row1'];//Getting data from form

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename="16.xls"');
header('Pragma: no-cache');
header('Expires: 0');
/*Saving purpose
**$output= @fopen('filename', 'w'); //error turn off
**$output = fopen('php://output', 'w');
 */
//HTML table will go here
   $table = '<table><tr>';
   $table .= '<th>Column 1</th>';
   $table .= '<th>Column 2</th>';
   $table .= '</tr>';
   $table .= '<tr>';
   $table .= '<td>'.$col1row1.'</td>';
   $table .= '<td>'.$col2row1.'</td>';
   $table .= '</tr>';
   $table .= '<tr>';
   $table .= '<td>'.$col1row2.'</td>';
   $table .= '<td>'.$col2row2.'</td>';
   $table .= '</tr>';
   $table .= '</table>';
   echo $table;
?>

Save this code as excelout.php and run it. You will get your table in excel. Hope this may help for starting.

The CSV updated and working version:

function myfputcsv($handle, $array, $delimiter = ',', $enclosure = '"', $eol = "\n") {
    $return = fputcsv($handle, $array, $delimiter, $enclosure);
    if($return !== FALSE && "\n" != $eol && 0 === fseek($handle, -1, SEEK_CUR)) {
        fwrite($handle, $eol);
    }
    return $return;
}
if(isset($_POST['mydrivers']))  {  
$header=array();
$data=array();
 foreach (array_slice($_POST,0,count($_POST)-1) as $key => $value) {    
     //$header[]=$key;
     $data[]=$value;
 }  
$fp = fopen('driver.csv', 'a+');
    //fputcsv($fp, $header);
    myfputcsv($fp, $data);
fclose($fp);
}

I don't change much thing in your form except the following:

<form action = 'driver.php' target = 'driver.php' method = "post">

to

<form action=""  method ="post">

and

<input type = "submit" value = "submit" onsubmit = "this.reset()">

to

<input type="submit" value ="submit" name="mydrivers">

You can check if $_POST if empty or not using array_filter():

$valid= array_filter($_POST);

if (!empty($valid)) {
   //all fields are furnished and can be saved.
}

Demo

Related Topic