CakePHP

CakePHP – Read data from an excel file into an array

41 Comments

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
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.

    Like

    Reply

  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]

    Like

    Reply

  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.

    Like

    Reply

    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?

      Kit

      Like

      Reply

  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:”
    Add
    ” return true;”

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

    Now I don’t get the warnings.

    Like

    Reply

    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.

      Like

      Reply

  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!
    Thanks

    Like

    Reply

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

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

    To read some charactors properly.

    Like

    Reply

  8. 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

    Like

    Reply

    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?

      Kit

      Like

      Reply

  9. 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

    Like

    Reply

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

    thanks in advanced

    Like

    Reply

  14. 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
    Vipin

    Like

    Reply

  15. 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.

    Like

    Reply

  17. 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 …??

    Thanks,
    Vinod

    Like

    Reply

Leave a comment

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