Recent Posts
- (09/10) Fixing Warning: the ECDSA host key for 'github.com' differs from the key for the IP addressTAGS:Web Server Admin
- (12/26) CakePHP 3 - Getting List of Column Definitions from a Table (like schema())TAGS:CakephpCake3
- (09/14) Change Order of Loaded Behaviors in CakePHP 3TAGS:Cake3CakephpWeb ProgrammingPhp
- (05/29) CakePHP 3 - Accept JSON Header Only Working When Debug FalseTAGS:Web ProgrammingCakephpCake3
- (05/23) Remove All Events from Google Calendar (Reset Calendar)TAGS:Web ProgrammingPhp
- (11/08) Google Tag Manager (GTM) Not Firing Default PageView EventTAGS:Web ProgrammingJavascriptGoogle Tag Manager
- (10/13) In PHP, how do you get __toString() Magic Method Result without calling echo?TAGS:CakephpCake3Cakephp 13PhpWeb Programming
- (11/14) Getting output from shell_exec() at all timesTAGS:Web ProgrammingWeb Server Admin
Subscribe to my feed
MainelyDesign.com Blog
Importing an Excel file into CakePHP
Posted on 04/16/2009 at 02:42 pm by Kevin Wentworth
Viewed 33,715 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/oleread.inc' to just require_once 'oleread.inc' 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):
After I got the vendor library loaded into CakePHP:
I initialized the Spreadsheet_Excel_Reader class (there is some examples that come with the download file)
- $data = new Spreadsheet_Excel_Reader();
- // Set output Encoding.
- $data->setOutputEncoding('CP1251');
Then, I read in my data posted from the form (make sure the form type is set to FILE)
- $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.
- for ($i = 1; $i <= $data->sheets[0]['numRows']; $i++) {
- for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++) {
- if($i == 1) {
- //this is the headings row, each column (j) is a header
- $headings[$j] = $data->sheets[0]['cells'][$i][$j];
- } else {
- //column of data
- $row_data[$headings[$j]] = isset($data->sheets[0]['cells'][$i][$j]) ? $data->sheets[0]['cells'][$i][$j] : '';
- }
- }
- if($i > 1) {
- }
- }
- $this->ModelName->emptyTable();
- }
- } else {
- $this->Session->setFlash('Error. Unable to import records. Please try again.');
- }
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:
- function emptyTable() {
- $table = $this->tablePrefix.$this->table;
- return $this->query("TRUNCATE $table");
- }
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.
Cheers,
-Kevin Wentworth
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!