Importing an Excel file into CakePHP

Posted on 04/16/2009 at 02:42 pm by Kevin Wentworth
Viewed 33,277 times | 0 comments

I needed to be able to upload an excel file, parse the file, and then add each row as a separate database entry.  I had this working for a CSV file using the php built-in function fgetcsv(), but the client switched to using an excel file (instead of direct PHP access to the in-house database, we decided to have the client upload a CSV/Excel file). 

I did a little research and found the perfect solution- PHP-Excel Reader.  I saved this to my CakePHP vendors directory and had to make one modification: at line 31 in reader.php, I changed the require_once 'blah/blah/' to just require_once '' and made sure it was in the same directory as reader.php.

I had to adjust some server settings (because I was loading in a huge file- WARNING: this isn't a memory efficient way of doing things):

  1. set_time_limit(240);    //4minutes
  2. ini_set('memory_limit', '64M');

After I got the vendor library loaded into CakePHP:

  1. App::import('Vendor', 'Spreadsheet_Excel_Reader', array('file' => 'excelreader/reader.php'));

I initialized the Spreadsheet_Excel_Reader class (there is some examples that come with the download file)

  1. $data = new Spreadsheet_Excel_Reader();
  2. // Set output Encoding.
  3. $data->setOutputEncoding('CP1251');

Then, I read in my data posted from the form (make sure the form type is set to FILE)

  1. $data->read($this->data['ModelName']['csv']['tmp_name']);

This is the cool part.  Here is where you go line by line and change the data as you see fit, and then insert into the DB.  I had to manipulate the data that was in the spreadsheet to match up with my database fields, so I chose to take this route.  You may not need to.

  1. $headings = array();
  2. $xls_data = array();
  3. for ($i = 1; $i <= $data->sheets[0]['numRows']; $i++) {
  4.     $row_data = array();
  5.     for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++) {
  6.         if($i == 1) {
  7.             //this is the headings row, each column (j) is a header
  8.             $headings[$j] = $data->sheets[0]['cells'][$i][$j];
  9.         } else {
  10.             //column of data
  11.             $row_data[$headings[$j]] = isset($data->sheets[0]['cells'][$i][$j]) ? $data->sheets[0]['cells'][$i][$j] : '';
  12.         }
  13.     }
  15.     if($i > 1) {
  16.         $xls_data[] = array('ModelName' => $row_data);
  17.     }
  18. }
  20. if(isset($this->data['ModelName']['overwrite']) and $this->data['ModelName']['overwrite'] == 1) {
  21.     $this->ModelName->emptyTable();
  22. }
  24. if($this->ModelName->saveAll($xls_data, array('validate'=>false))) {
  25.     $this->Session->setFlash('Success. Imported '. count($xls_data) .' records.');
  26. } else {
  27.     $this->Session->setFlash('Error.  Unable to import records. Please try again.');
  28. }

You'll notice a few things- In my form I have a radio button named 'overwrite' that if selected, I will overwrite the data, rather than update it.  I found that CakePHP didn't have a good deleteAll function for my purpose.  You need to supply a condition, you can't just delete all.  When I supplied a condition of 'id !=' => null, it emptied the table but at the cost of the lookup, and then deleting where all IDs where in the 'WHERE id IN(x, xx, xxx)' it took quite a time hit.  

Instead, I wrote my own little function called emptyTable().  I think I'll move it up to my app_model.php file, because it is so simple and so powerful.  Here it is, in all it's wonder:

  1. function emptyTable() {
  2.     $table = $this->tablePrefix.$this->table;
  3.     return $this->query("TRUNCATE $table");
  4. }

I hope you found this article/tutorial useful.  It's my first one, so I'm sure it was hard to follow.  I promise to make them better.


-Kevin Wentworth

Bookmark and Share

Tags for Importing an Excel file into CakePHP

Cakephp | Web Programming | Site Avenger | Excel | Csv | Database | Saveall | Vendors | Import | Truncate | Empty Table

Comments for this Posting

No comments. Be the first to post a reply.

Sorry, comments are closed for this posting.

Please Email Kevin if you have any questions. Thanks!

Meet Site Avenger - Hosted Content Management System

Powered By: Site Avenger | Site Production: Saco Design