CakePHP – Read data from an excel file into an array

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



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


41 thoughts on “CakePHP – Read data from an excel file into an array”

  1. You can make more simple, see:

    $reader = new PHPExcel_Reader_Excel5();
    $reader->setReadDataOnly(true); // Not read styles
    $excel = $reader->load(‘file.xls’);
    return $excel->getActiveSheet()->toArray();

    See you.


  2. I fallowed your steps it works but i got few warings like below please suggest me
    Undefined offset: 0 [APP\vendors\php-excel-reader\excel_reader2.php, line 397]

    Notice (8): Undefined offset: 0 [APP\vendors\php-excel-reader\excel_reader2.php, line 400]

    Notice (8): Undefined offset: 1 [APP\vendors\php-excel-reader\excel_reader2.php, line 403]

    Notice (8): Undefined offset: 1 [APP\vendors\php-excel-reader\excel_reader2.php, line 406]

    Notice (8): Undefined offset: 1 [APP\vendors\php-excel-reader\excel_reader2.php, line 626]

    Notice (8): Undefined index: [APP\vendors\php-excel-reader\excel_reader2.php, line 521]

    Notice (8): Undefined index: [APP\vendors\php-excel-reader\excel_reader2.php, line 524]

    Notice (8): Undefined index: [APP\vendors\php-excel-reader\excel_reader2.php, line 527]

    Notice (8): Undefined index: [APP\vendors\php-excel-reader\excel_reader2.php, line 530]

    Notice (8): Undefined offset: 2 [APP\vendors\php-excel-reader\excel_reader2.php, line 403]

    Notice (8): Undefined offset: 2 [APP\vendors\php-excel-reader\excel_reader2.php, line 406]

    Notice (8): Undefined offset: 2 [APP\vendors\php-excel-reader\excel_reader2.php, line 626]

    Notice (8): Undefined index: dontprint [APP\vendors\php-excel-reader\excel_reader2.php, line 626]

    Notice (8): Undefined index: hyperlink [APP\vendors\php-excel-reader\excel_reader2.php, line 383]


  3. I am testing this in Cake PHP. I am pretty new at this and am getting a missing database table error.

    I have the library installed and had the example.php files working reading an non-corrupt .xls file.

    Issue now is I get an missing database tables error with the standard cake error view.

    How do I get this working without adding a model for the show excel action in my controller?

    Thanks for any tips.


    1. Hi Dave,

      I think the missing database table error is independent of the excel library in this post. The controller should work even thought it is not associated with any model.

      So if u remove the changes about the post. you should still get the error, rite? could you please more details about the code and the error u get?



  4. Ya, the error is not related to the library. I got around it but more by trial and error and trying different things. Now the library is working fine.

    Thanks for all your work on this.

    PS: on the warnings issue I followed the tip about changing the line for the case statement
    After this line
    “case E_NOTICE:”
    ” return true;”

    in the file C:\wamp\www\app\cake\libs\debugger.php

    Now I don’t get the warnings.


    1. Good to know that u have solved the problem and thanks for providing the solution of removing those warning message =D.
      i will update the post about your fix later tonite.


  5. Thanks you. i’m upload sucess file excell . But i can’t read file with format unif8. Now, i want read file with format font unif8. can you help me!


  6. For dumptoarray() you might need to add following line

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

    To read some charactors properly.


  7. I got some problem about read excel file into an array when my excel file have about 26 columns. Did you limit the column it can read? If yes, how many columns? I can’t fix that problem, please help me. Thank you so much


    1. Hi HT,

      i haven’t tried reading a .xls file with so many columns. could you tell me more about the error u got? and does it still happen if you reduce the number of columns?



  8. I fixed that problem, it’s my mistake :(, because I tried to ignore some null rows, but my code was not exactly.
    P/s: another idea, according to me, I remove htmlentities() function, because that function make my excel file can’t show vietnamese clearly


  9. Anyone can help me to read unicode data from excel file?
    I got something like this á� á�¸á�� á��á��á��á��á��á��á��

    thanks in advanced


  10. When I try the code, every time I am getting “The filename cj_report.xls is not readable”. I gave full permission to file and folder. Still same issue. Do you know why?
    I am using cakephp.

    Please help


  11. Hi guys. Very new to cakephp. Working with cakephp 2.1. Very new at Cakephp. I have workied through this over and over again, must be 12 times now. Keep on getting errors on class “Spreadsheet_Excel_Reader” not found, fatal error.

    I am really needing a simple import into a table (mysql), for cakephp 2.1 that’s it and I have looked all over the show.

    Please anyone who can help desperately needed.

    Thank you in advance.


  12. i followed your steps and can be able to import data ..!!
    but my excel sheet contain some images how to upload them to the server ,and save the path in database ..please help me with this …??



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.