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

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

About these ads

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

  1. Juan Basso

    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.

    Reply
  2. mahadevaprasad

    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]

    Reply
    1. ykyuen Post author

      ya, i got the same warnings too. sorry that i couldn’t find any way to solve this problem.

      Reply
  3. dave

    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.

    Reply
    1. ykyuen Post author

      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

      Reply
  4. dave

    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.

    Reply
    1. ykyuen Post author

      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.

      Reply
  5. leeseawuyhs

    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

    Reply
  6. Rizwan

    For dumptoarray() you might need to add following line

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

    To read some charactors properly.

    Reply
  7. HT

    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

    Reply
    1. ykyuen Post author

      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

      Reply
  8. HT

    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

    Reply
  9. sophea

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

    thanks in advanced

    Reply
  10. Vipin

    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

    Reply
  11. Philip

    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.

    Reply
  12. Vinod

    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

    Reply

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s