Planet CakePHP

In the last article – CakePHP – php-excel-reader, we have implement the php-excel-reader. Now i would like to read the excel content for processing instead of just displaying it in the view.

This article assume that you have already implemented the php-excel-reader. Please refer to CakePHP – php-excel-reader.
I have searched on google and find a good post about reading the excel content into an array using the php-excel-reader.
Reference: Alexander Makhno’s Blog » php-excel-reader
The author added the following function in the excel_reader2.php

/**
* Read the excel content into an array
*
* @param $sheet - Sheet number in the excel file
* @return $arr - array containing the excel content
*/
function dumptoarray($sheet = 0) {
$arr = array();

for($row=1; $row <= $this->rowcount($sheet); $row++) {
for($col=1; $col <= $this->colcount($sheet); $col++) {
$arr[$row][$col] = htmlentities($this->val($row,$col,$sheet));
}
}

return $arr;
}

The following function in the controller read the excel and print it in the debug.log.

/**
* Read the excel content into an $arr and
* print it to debug.log
*/
function read_excel() {
$data = new Spreadsheet_Excel_Reader('test.xls', true);
$temp = $data->dumptoarray();
$this->log($temp, 'debug');
}

Here shows u the content of the test.xls
test.xls
 
You can find the array content in the debug.log.

2009-09-26 10:50:07 Debug: Array
(
[1] => Array
(
[1] => Name
[2] => Age
[3] => Gender
[4] => Nationality
[5] => Occupation
)

[2] => Array
(
[1] => Alan
[2] => 26
[3] => M
[4] => HKSAR
[5] => Analyst Programmer
)

[3] => Array
(
[1] => Bob
[2] => 30
[3] => M
[4] => USA
[5] => Project Manager
)

[4] => Array
(
[1] => Chris
[2] => 41
[3] => M
[4] => Indian
[5] => CTO
)

)

Done =)
Update @ 2011-07-07: Update the dumptoarray() so it can read the characters correctly. Thanks Rizwan! =D

...
//$arr[$row][$col] = $this->val($row,$col,$sheet);
$arr[$row][$col] = htmlentities($this->val($row,$col,$sheet));
...

 
Update @ 2011-07-26: The above htmlentities() may not work for vietnamese. Thanks HT! =D
Posted in CakePHP Tagged: CakePHP