Hi all

I'm a newie to PHP. I have a html form which will passed 6 value to a php file and from a php file, I would like to write these value on to 2 rows in the excel. Therefore each person submit the form, it will write 6 values into 2 rows in excel, how do I do it? Please need help.

My sample html form

<html>
<body>
  <form action="writeToExcel.php" method="post">
    <center><h1>Personal Learning Goals Form</h1></center>
    Student ID: &nbsp;&nbsp;&nbsp;<input type="text" name="StudID" /><br/>
    Student Name: &nbsp;&nbsp; <input type="text" name="StudName" /><br/> 
    Comment 1: &nbsp;&nbsp;<input type="text" name="Com1" /><br/>
    Comment 2: &nbsp;&nbsp;<input type="text" name="Com2" /><br/> 
    Comment 3: &nbsp;&nbsp;<input type="text" name="Com3" /><br/>
    Comment 4: &nbsp;&nbsp;<input type="text" name="Com4" /><br/> 
   <input type="submit" value="Submit Form">
  </form>
</body>
</html>

Thanks in advance

enz

I just spent the last several days trying to figure out how to write to an Excel spreadsheet with PHP.

There is a proprietary PHP library for sale at this link:
http://www.eephp.com/
I have not tried it so I don't know if it works or what...

You can also use some PHP libraries if you are only running on a Windows machine (using the COM object, and such stuff)...but Windows is not my server environment, so I didn't look too deeply here.

You can also read and write to an Excel file if you save it as an Excel XML file-type...but be warned that the Excel XML file format is different for Excel 2003 and Excel 2007 on Windows and the former (2003) version is not available on Mac Excel and the later (2007) requires an update for Mac Excel.

You can also always read and write to a CSV or tab-delimited file that is readable through PHP and also Excel. This is ultimately what I decided to do with it...but of course it's just the data, and none of the markup or formulas available in the Excel xls file-format.

Hope this helps, let me know if you want me to clarify what I've learned on any of these specific points.

Hi Langsor,

I just want to be able to write data to the spreadsheet. Really dont mind about csv or excel.

I found something yesterday but still cant figure out how it works.

http://devzone.zend.com/article/3336-Reading-and-Writing-Spreadsheets-with-php

The above link show how to write to an excel file but I was stuck with installing pear. "stupid me huh?" :) . Well this is my first time to write something on php.

And here is another one:
http://www.codeplex.com/PHPExcel

They also provide the class and library like the one you say but it's for free. I haven't try it, may give it a go today but not confident enough.

Langsor, if you know how to write it to the csv, please help me with that too, coz I'm happy as long as I'm able to obtain the data for my boss. No formula is needed as well.

My one is quite simple. I have the form, user enter the data and the form just write those data onto 2 rows and 3 column in the spreadsheet. :)

Thank you.
enz

An easy way to create an xls file from php is to create a tab delimited file with an xls extension. Here's an easy example. Create a folder named files and save the following php file in the same directory as the folder.

<?php
$file="files/test.xls";
$f = fopen($file, "w+");
fwrite($f,"A1\tB1\t\nA2\tB2\tC2\t\n\t\tC3");
echo(fwrite ? "File was written":"File write failed");
?>

Now open the file from the browser. If the browser says File was written, then you should now have an xls file inside the files folder created earlier. Open it up.
Note that the "\t" creates a tab and "\n" create a new row. Also note that the text A1 will be in cell A1.

Hi bubbylee17

I try your code but nothing is changed on test.xls. The file still empty even though I see the "File was written" on it.

I put more things on the third line as follow: $f = fopen($file, "w+") or die ("Could not open"); then I the output will be "Could not open".

I'm wondering whether it ables to open the .xls file or not. I doubt it.

thank you.
enz

Here is an example.

<?php
$file="test.xls";
$test="<table border=1><tr><td>Cell 1</td><td>Cell 2</td></tr></table>";
header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=$file");
echo $test;
?>

thanks Nav33n, your example is working but it pop up and ask the user to click on save.

Here is what my page should do:

On HTML file:
It has a form for the user to submit their data and click on submit. (POST method has been used)

On PHP:
- Obtain the info from HTML
- Open .xls file
- Go to the first empty row (coz the .xls already have some data on it)
- Add new info on that empty row
- Save the file
- Exit

In theory it suppose to work like that but I dont know how to write it on the php. Here is my temp php file, i'm able to echo all variables out but still dont know how to write it to an excel file:

<html>
	<body>
		<?php
		$StudentID = $_POST['StudID'];
		$StudentName = $_POST['StudName'];
		$Comment1 = $_POST['Com1'];
		$Comment2 = $_POST['Com2'];
		$Comment3 = $_POST['Com3'];
		$Comment4 = $_POST['Com4'];

		echo "Your Student ID is $StudentID <br />";
		echo "Your Name is $StudentName <br />";
		echo "Your Comment 1 is  $Comment1 <br />";
		echo "Your Comment 2 is  $Comment2 <br />";
		echo "Your Comment 3 is  $Comment3 <br />";
		echo "Your Comment 4 is  $Comment4 <br />";

		?>

	</body>
</html>

Please help me out. It quite urgent for me.

Thanks
enz

<?php
$value1 = $_POST['something1'];
$value2 = $_POST['something2'];
$value3 = $_POST['something3'];
$fp=fopen("example.xls","a+");
$test="$value1 \t $value2 \t $value3 \t \n";
//where $value1,$value2 and $value3 POSTED values to write to excel
fwrite($fp,$test);
fclose($fp);
?>

Hey,

I don't know if anyone is interested but I created a PHP class for MS Excel CSV file handling today ... see attachment to this posting

It's hot off the press and barely tested, just some basic testing done on the methods, but I plan to use it and you're welcome to too.

If you find any bugs or have any suggestions for improving it -- I would love to hear about them.

I'm sure I could have built it "better-stronger-faster" than I did, but it's just meant to get the job done.

It allows you to read, write, append and edit csv files.
*** Examples and documentation are in the header comments of the file itself

Yep, that's about it, let me know what you think or if you find it useful.

Thanks

Attachments
<?php

/*** CSV Class :: BETA VERSION :: 2008.08.05 :: Copyright - Kevin Douglas [kdouglas@satarah.com] ***/

/*** METHODS AVAILABLE ***
  
  // Requires PHP 5.x :: Built on PHP version 5.25 on Windows platform
  // This class does not check that existing csv files are in the correct
  // csv file format -- I created this to be compatible with my copy of
  // Microsoft Office 2003 Enterprise Ed -- Excel 2003 CSV file format
  
  $csv = new CSV( 'path/file.csv' );
  -or-
  $csv = new CSV();
  $csv->load_file( 'path/file.csv' );

  // When working with these methods, you must pass table data
  // in a 2-dimensional array of ROWS x COLS => FIELD-DATA
  // all table fields are indexed starting at zero (0) like an array
  // the write, append and edit methods will pad the table to fit new data
  
  $csv->parse_table();
    // returns 2-dimensional array of ROWS x COLS => FIELD-DATA
  
  $csv->write_table( $table_data_array [, 'path/to/new_file.csv'] );
    // will clobber existing loaded csv file, or pass new file path [optional]
    // expects 2-dimensional array of ROWS x COLS => FIELD-DATA
  
  $csv->append_table( $table_data_array );
    // will append values to existing loaded csv file, will not create new file
    // expects 2-dimensional array of ROWS x COLS => FIELD-DATA
  
  $csv->edit_table( $row_index, $col_index, $field_data );
    // pass ROW and COL indexes (zero based) and FIELD-DATA (string or number)
    // will add new field data or overwrite existing field data   

***/

/*** PROPERTIES AVAILABLE ***
  
  // already defined to default values ...
  $csv->row_delim // read-write, usually newline (\n)
  $csv->col_delim // read-write, usually comma (,)
  $csv->enclosure // read-write, usually quote (")
  
  // might be useful for interactive scripts ...
  $csv->rows // read-only, number of rows, index zero
  $csv->cols // read-only, number of cols, index zero
  
***/

/*** EXAMPLES OF USE ***

require_once 'CSV.php';

$file = 'test.csv';
$csv = new CSV( $file );

print $csv->rows."\n";
print $csv->cols."\n";

$table = $csv->parse_table();
print_r( $table );

$data = array(
  array( 'one','two','three','"four",five' ),
  array( 1,2,3 ),
  array( 'uno','dos','tres','quatro','cinco' )
);

$csv->write_table( $data, 'pizza.csv' ); // auto-loads new file
$csv->append_table( array( 'shrimp','anchovies','mozzarella' ) );
$csv->edit_table( 12, 7, 'java-beans' );
print_r( $csv->parse_table() );

***/

class CSV {

  public function __construct ( $csv_file = FALSE ) {
    $this->row_delim = '/(\r\n|\r|\n)/';
    $this->col_delim = ',';
    $this->enclosure = '"';
    if ( $csv_file ) {
      $this->load_file( $csv_file );
    }
  }
  
  public function load_file ( $csv_file ) {
    if ( is_file( $csv_file ) ) {
      $this->csv_file = $csv_file;
      $this->table = file_get_contents( $csv_file );
    } else {
      die( 'UNABLE TO READ FROM CSV FILE' );
    }
    $this->lines = $this->split_lines();
    $this->fields = $this->count_fields();
    $this->rows = count( $this->lines ) - 1;
    $this->cols = $this->fields - 1;
  }
  
  protected function split_lines () {
    return preg_split( $this->row_delim, $this->table );
  }
  
  protected function count_fields () {
    foreach ( $this->lines as $line ) {
      if ( strlen( $line ) ) {
        if ( strpos( $line, $this->enclosure ) === FALSE ) {
          return count( split( $this->col_delim, $line ) );
        }
      }
    }
    $table_data = $this->parse_table();
    return count( $table_data[0] );
  }
  
  public function parse_table () {
    if ( !$this->lines ) {
      die( 'NO CSV FILE LOADED TO PARSE' );
    }
    foreach ( $this->lines as $line ) {
      if ( $len = strlen( $line ) ) {
        $cols = array();
        $field = '';
        $pos = 0;
        $esc = 0;
        while ( $pos < $len ) {
          $char = substr( $line, $pos, 1 );
          if ( $esc % 2 ) {
            if ( $char == $this->enclosure ) {
              $next = substr( $line, $pos + 1, 1 );
              if ( $next == $this->enclosure ) {
                $field .= $char;
                $pos ++;
              } else {
                $esc ++;
              }
            } else {
              $field .= $char;
            }
          } else {
            if ( $char == $this->enclosure ) {
              $esc ++;
            } elseif ( $char == $this->col_delim ) {
              $cols[] = $field;
              $field = '';
            } else {
              $field .= $char;
            }
          }
          $pos ++;
        }
        $cols[] = $field;
        $table[] = $cols;
      }
    }
    return $table;
  }
  
  public function write_table ( $table_data, $csv_file = FALSE ) {
    if ( $csv_file ) {
      if ( is_file( $csv_file ) || file_put_contents( $csv_file, '' ) ) {
        $file_path = $csv_file;
      }
    } elseif ( $this->csv_file ) {
      $file_path = $this->csv_file;
    } else {
      die( 'UNABLE TO WRITE TO CSV FILE' );
    }
    foreach ( $table_data as $row ) {
      $fields = ( ( $cnt = count( $row ) ) > $fields ) ? $cnt : $fields;
    }
    $this->publish_table( $file_path, $table_data, $fields );
  }
  
  public function append_table ( $table_data ) {
    if ( $this->csv_file ) {
      $file_path = $this->csv_file;
    } else {
      die( 'UNABLE TO APPEND TO CSV FILE' );
    }
    foreach ( $table_data as $row ) {
      $fields = ( ( $cnt = count( $row ) ) > $fields ) ? $cnt : $fields;
    }
    $fields = ( $fields > $this->fields ) ? $fields : $this->fields;
    $this->publish_table( $file_path, $table_data, $fields, TRUE );
  }
  
  public function edit_table ( $row_idx, $col_idx, $field_data ) {
    if ( $this->csv_file ) {
      $file_path = $this->csv_file;
    } else {
      die( 'UNABLE TO EDIT CSV FILE' );
    }
    $fields = ( $col_idx + 1 > $this->fields ) ? $col_idx + 1 : $this->fields;
    $table_data = $this->parse_table();
    if ( $row_idx + 1 > count( $this->lines ) ) {
      $table_data = array_pad( $table_data, $row_idx + 1, array_pad( array(), $fields, '' ) );
    }
    $table_data[$row_idx][$col_idx] = $field_data;
    $this->publish_table( $file_path, $table_data, $fields );
  }
  
  protected function publish_table ( $file_path, $table_data, $fields, $append = FALSE ) {
    foreach ( $table_data as &$row ) {
      if ( count( $row ) && is_array( $row ) ) {
        foreach ( $row as &$field ) {
          if ( strpos( $field, $this->enclosure ) !== FALSE ) {
            $field = preg_replace( "/($this->enclosure)/", "\\1\\1", $field );
            $enclose = TRUE;
          }
          if ( strpos( $field, $this->col_delim ) !== FALSE ) {
            $enclose = TRUE;
          }
          if ( $enclose ) {
            $field = $this->enclosure.$field.$this->enclosure;
            $enclose = FALSE;
          }
        }
        $cols = array_pad( $row, $fields, '' );
        $line = implode( $this->col_delim, $cols );
        $table[] = $line;
      }
    }
    $append ? 
      file_put_contents( $file_path, "\n".implode( "\n", $table ), FILE_APPEND ) :
      file_put_contents( $file_path, implode( "\n", $table ) );
    $this->load_file( $file_path );
  }
}

?>

enzogoy,

Here is what you can do for your form...

Your html file:

<html>
<body>
  <form action="writeToExcel.php" method="post">
    <center><h1>Personal Learning Goals Form</h1></center>
    Student ID: &nbsp;&nbsp;&nbsp;<input type="text" name="StudID" /><br/>
    Student Name: &nbsp;&nbsp; <input type="text" name="StudName" /><br/> 
    Comment 1: &nbsp;&nbsp;<input type="text" name="Com1" /><br/>
    Comment 2: &nbsp;&nbsp;<input type="text" name="Com2" /><br/> 
    Comment 3: &nbsp;&nbsp;<input type="text" name="Com3" /><br/>
    Comment 4: &nbsp;&nbsp;<input type="text" name="Com4" /><br/> 
   <input type="submit" value="Submit Form">
  </form>
</body>
</html>

Your php file [writeToExcel.php]

<?php

require_once 'CSV.php';
$out_file = 'learning_goals.csv';

$csv = new CSV();

$headers = array ( // one row
  array (          // six columns
    'StudID',
    'StudName',
    'Com1',
    'Com2',
    'Com3',
    'Com4'
  )
);

if ( !is_file( $out_file ) ) {
  $csv->write_table( $headers, $out_file );
}

$data = array ( // one row
  array (       // six columns
    $_POST['StudID'],
    $_POST['StudName'],
    $_POST['Com1'],
    $_POST['Com2'],
    $_POST['Com3'],
    $_POST['Com4']
  )
);

$csv->append_table( $data );

// now you have a csv file in the same directory as this script
// you can read and edit it with excel, or also this class
// ... for demonstration purposes ...
// comment out the following for production

$table = $csv->parse_table();
print_r ( $table );

?>

Hope maybe this helps some.

Let me know if you have any questions or run into any bugs...

Thanks

Hi Langsor

I saved the csv.php file in the same directory with the .php file. I copy the writetoexcel.php you gave to me too. When I run it, it didn't write to the csv. But I can see on the webpage an 'array'. Is the code missing something?

Thanks
enz

Hi,

if your array looks something like this then everything is okay ...

Array
(
    [0] => Array
        (
            [0] => StudID
            [1] => StudName
            [2] => Com1
            [3] => Com2
            [4] => Com3
            [5] => Com4
        )

    [1] => Array
        (
            [0] => 588
            [1] => langsor
            [2] => something
            [3] => sometihng else
            [4] => 
            [5] => 
        )

)

If it is an empty array or does not have the header fields or does not have the data you entered into the form, then get back to me.

Look in the folder with the CVS.php script, you should find a file named learning_goals.csv, open it and see that everything is in there.

If it's all working you can then comment out the two lines printing the array to the web browser ... change

$table = $csv->parse_table();
print_r ( $table );

... to ...

//$table = $csv->parse_table();
//print_r ( $table );

If you need to access the learning_goals.csv file and print the data out to the web or place it in a database you can do so with the code we just commented out $table = $csv->parse_table(); where $table is a 2-dimensional array of the data in your csv file ...

Let me know how it goes

<?php
$value1 = $_POST['something1'];
$value2 = $_POST['something2'];
$value3 = $_POST['something3'];
$fp=fopen("example.xls","a+");
$test="$value1 \t $value2 \t $value3 \t \n";
//where $value1,$value2 and $value3 POSTED values to write to excel
fwrite($fp,$test);
fclose($fp);
?>

Hi Nav33n,

I tried that code, open file seems to be alright but the fwrite doesn't seem to work. I put the xls file in the same folder with the .php file. I run the .php file a few times but nothing write to the .xls file.

I google the command and saw that they also used the same with the one you provided, dont know where i'm doing wrong. :(

enz

Okay, I knew there would be some debugging needed ...
In the file that your form is submitting to you will find a line that looks like this ... $csv->append_table( $data ); In order for you to add further data after the very first file-write you will need to add this line in front of it ...

$csv->load_file( $out_file );
$csv->append_table( $data );

Let me know if you have any troubles

Hi Langsor

This is what appear on the page: Array ( [0] => Array ( [0] => StudID [1] => StudName [2] => Com1 [3] => Com2 [4] => Com3 [5] => Com4 ) ) It only has the header without any data I have entered.

enz

Okay, have you tried it with the extra line in my last post added?

$csv->load_file( $out_file );

Let me know and we can figure this out I'm sure ...

Quick question -- what version of PHP are you using ?

If its 4.x then I know what the problem is. If it's 5.x then take a look at the zip file I've attached and give those files a shot on your server ... let me know if they do or don't work.

I can fix it for an earlier version of PHP (I'm pretty sure) ... so let me know. If you don't know your version of PHP I've also included a file called env.php, go there in your web browser on your PHP server and it will tell you the version at the top.

Getting closer

Hi Nav33n,

I tried that code, open file seems to be alright but the fwrite doesn't seem to work. I put the xls file in the same folder with the .php file. I run the .php file a few times but nothing write to the .xls file.

I google the command and saw that they also used the same with the one you provided, dont know where i'm doing wrong. :(

enz

Umm.. Can you read data from the excel file ? Try that ! also check file permissions. :)

Quick question -- what version of PHP are you using ?

If its 4.x then I know what the problem is. If it's 5.x then take a look at the zip file I've attached and give those files a shot on your server ... let me know if they do or don't work.

I can fix it for an earlier version of PHP (I'm pretty sure) ... so let me know. If you don't know your version of PHP I've also included a file called env.php, go there in your web browser on your PHP server and it will tell you the version at the top.

Getting closer

my PHP version is 5.2.6. I just install it last week or a week before that.

I use your file but the same result happen. I think there should be something wrong on my server if your files working at your place :(

Langsor,

It's working now. I think the permission is the problem.

Now i'm trying with the .xls file from nav33n.

Thank you so much.

enz

ps: could I ask you another question about the array.
For example in my file have student ID, Name and 4 comments. Whenever student submit the form, is it possible it gonna write to 4 rows. Like :

StudID Name Comment 1
StudID Name Comment 2
StudID Name Comment 3
StudID Name Comment 4

Am I just need to create 4 array isn't it?

Umm.. Can you read data from the excel file ? Try that ! also check file permissions. :)

Hi Nav33n,

I still not able to make it work. I can see the .xls file is open and save base on the time of modify. But when I open the file, it's empty. Do you know what's wrong with it.

Thank you
enz

You will need to grab the form data and make it into four arrays ...

something like

<?php

require_once 'CSV.php';
$out_file = 'learning_goals.csv';

$csv = new CSV();

$headers = array ( // one row
  array (          // three columns
    'StudID',
    'StudName',
    'Comment',
  )
);

if ( !is_file( $out_file ) ) {
  $csv->write_table( $headers, $out_file );
}

$studID =  $_POST['StudID'];
$studName =  $_POST['StudName'];
$comments[] = $_POST['Com1'];
$comments[] = $_POST['Com2'];
$comments[] = $_POST['Com3'];
$comments[] = $_POST['Com4'];

$csv->load_file( $out_file );

foreach ( $comments as $comment ) {
  if ( $comment ) { // not NULL value
    $data = array ( // one row
      array (       // three columns
        $studID,
        $studName,
        $comment,
      )
    );
    $csv->append_table( $data );
  }
}

?>

I didn't test the above code, but it looks right

....

Hi Nav33n,

I still not able to make it work. I can see the .xls file is open and save base on the time of modify. But when I open the file, it's empty. Do you know what's wrong with it.

Thank you
enz

There are only 2 possible reasons.
1. The permission on .xls file.
2. You aren't writing any data to the file. Maybe the variable is empty ?
Try writing a string to the file and see if it works. That is,

<?php
$fp = fopen("test.xls","a+");
fwrite($fp,"This is some string");
fclose($fp);
?>

See if it works!

There are only 2 possible reasons.
1. The permission on .xls file.
2. You aren't writing any data to the file. Maybe the variable is empty ?
Try writing a string to the file and see if it works. That is,

<?php
$fp = fopen("test.xls","a+");
fwrite($fp,"This is some string");
fclose($fp);
?>

See if it works!

1. I gave everyone full permission to that file so there is no problem with the permission.
2. I used your sample code and the same thing happen. The file open and save but without any data on it. The Date modify of .xls is changed base on the time I run the .php file. On the server, I have office 2003 on it too so there shouldn't be a problem of writing to an excel file. Do we need to include something?

Nope. You dont have to include anything. I don't understand why it isn't working for you, but its writing the contents to the excel file when I execute the script :-/

Nope. You dont have to include anything. I don't understand why it isn't working for you, but its writing the contents to the excel file when I execute the script :-/

:) , may be something wrong at my server side. I'll try to figure it out when i'm free. At least at the moment the .csv file is working.


Langsor and nav33n, Thank you very much for you time and efford to help me with my basic php. You guys provide great help. Thanks.

Langsor,

Sorry for trouble you again.
I use your csv.php file since the day you gave it to me. It works well.

I plan to upgrade our server up to 2008 and use IIS 7. And then error pop up.

This is partly of my php code:

// Create the header of the array
$headers = array ( // one row
  array (          // fourteen columns
    'DateOfEvent',
	'StudName',
	'HG',
	'HGT',
	'YrLevel',
	'Location',	
  )
);


if ( !is_file( $out_file ) ) {
  $csv->write_table( $headers, $out_file );
}


//load file

$csv->load_file( $out_file );


// Add data to an array and then save that array to the csv file
// Using for loop to create 1 rows and 4 columns of data

for ($i=0;$i<1;$i++){
	$data = array ( // one row
	      array (       // fourteen columns
	        $DateOfEvent,
			$StudName,
			$HG,
			$HGT,
			$YrLevel,
			$Location,			
	        )
      );
	  $csv->append_table( $data );
 }
print_r ( "Your Report has been submitted.  Thank you." );

Error happen after that red line. It writes to the CSV file but after that it pop up the error.
500 - Internal server error.
There is a problem with the resource you are looking for, and it cannot be displayed.

No problem with writing, but after the writing there is an issue and I don't know what's wrong with it. Do a few testing but couldn't figure it out.

Hope you can help me once again.

Thanks.
enz

Wow, I'm excited that this has been working for you all this time. I have to say that I try to avoid Windows Server environments so might not be much help in this instance, but I'll do my best.

Can you put these lines at the top of the page where this code is running and see if we can get more error information?

error_reporting(E_ALL);
ini_set("display_errors", 1);

You say that there is no problem writing but that the error happens "after" the append table line. You might try commenting out the print_r line

//print_r ( "Your Report has been submitted.  Thank you." );

to see if that changes anything. I know it's a shot in the dark, but you've got to start somewhere.

Also, what code is following this error line?

Thanks,
langsor

Edited 5 Years Ago by langsor: n/a

Hi langsor,

It still show the same error.

500 - Internal server error.
There is a problem with the resource you are looking for, and it cannot be displayed.

I think something is change on the server side. The dif from the new server and the old one is: the new server use fastcgi while the old one use isapi.

Regards,
enzogoy

This question has already been answered. Start a new discussion instead.