CakePHP – php-excel-reader

The next enhancement on the BHJS Alumni website is adding an import function such that a bench of graduates could be created in the Database without adding new member one by one. Importing the records from an excel file would probably the most common and straight forward way.

The following procedures would guide you to implement the php-excel-reader in your CakePHP project.


1. Download the php-excel-reader
Updated @ 2013-01-26: Mark has working on a the php-excel-reader and make it a more suitable PHP5 Class. For more information, please refer to this comment.

2. Copy the excel_reader2.php to your CakePHP project at /app/vendors/php-excel-reader

3. Put the example.xls in /app/webroot

4. At the controller where u want to read the excel add the App::import to import the php-excel-reader

<?php
App::import('Vendor', 'php-excel-reader/excel_reader2'); //import statement
class ReadExcelController extends AppController {
...

5. Create the function in the controller for displaying the excel content

    /**
     * Display the content of example.xls
     */
    function show_excel() {
		$data = new Spreadsheet_Excel_Reader('example.xls', true);
		$this->set('data', $data); 
    }

6. Create the view for displaying the excel content

<?php
	echo $data->dump(true,true);
?>

7. Deploy your app and verify the view in browser
excel-php-reader

Done =)

Update @ 2011-08-12: Fix the debug message problem by longinus

Advertisements

68 thoughts on “CakePHP – php-excel-reader”

  1. instead of


    // controller
    $this->set('data', $data);

    // view
    echo $data->dump(true,true);

    in the view, would it not be more MVC to have


    //controller
    $this->set( 'data', $data->dump( true,true ) );

    //view
    echo $data;

    either way… looks cool

    Like

  2. really nice one
    not yet used it, but sounds fanatastic
    most DAUs dont know how to save .csv with excel, this will make the whole thing easier.. (for the user at least)

    thank you 🙂

    Like

    1. When I am in production mode (Debug level 0) the errors do not show (by design) and the spreadsheet is displayed quite nicely. However when I display the errors (Debug level 2) there are a lot, all coming from the excel_reader2.php file. Is there another file that I need to include?

      Like

  3. It works fine just in the webserver. Might try it in another controller a bit later. I don’t think the errors are having any impact on it’s working, but obviously something’s not right.

    Like

    1. O, sorry, i just realized that i got the same warnings after i have set the debug level to 2.

      so you are not the only one who got those warnings =P

      Like

      1. First, make sure the folder containing the excel is writable.

        Second, make sure u have specified the correct path when instantiate the Spreadsheet_Excel_Reader. Take a look @ this comment

        Like

  4. How can I get the value of a cell?
    I tried to debug $data using a simple excel file containing a 3×3 table, and I found a HUGE result in the view. I couldnt event copy and paste it (it is more than 16000 lines).

    So, I cant event figure out what to search for inside $data. Can any of you help me with this?

    Like

  5. Whats the procedure if you are working with a very large .xls file, lets say 40MB+, so should I save this file and split it by sheets and then work with every independent sheet as a single file.

    This is because I am geting an PHP Memory Exhaust error, because of the size of the document.

    As far as i can tell, theres no problem with your class, what happens here is that PHP has an amount of memory assigned to it by .ini configuration. And this is usually 32MB.

    Any suggestions?

    Like

  6. good post!, but i have a question…

    how you read or load de path of the xls file in the controller???

    i’m working with PHPExcel… this is my controller

    ‘excel/PHPExcel.php’));
    App::import(‘Vendor’,’PHPExcelWriter’,array(‘file’ => ‘excel/PHPExcel/Reader/Excel5.php’));
    class ExcelsController extends AppController{
    var $name = “Excels”;
    var $helpers = array(‘Javascript’,’Ajax’);
    var $components = array(‘RequestHandler’);
    var $uses = array (‘Resultado’,’Accion’,’Unidad’,’Tiporesultado’);

    function test(){
    $this->autoRender = false;
    $objReader = new PHPExcel_Reader_Excel5();
    // $objReader->setReadFilter( new MyReadFilter() );
    $objPHPExcel = $objReader->load($_POST[‘url’]);<—ERROR

    … and url post it's something like this
    http://localhost/cake2/webroot/upload/files/plantilla_resultados.xls

    This is the error from server

    Fatal error: Uncaught exception 'Exception' with message 'Could not open http://localhost/cake2/webroot/upload/files/plantilla_resultados.xls for reading! File does not exist, or it is not readable

    I use CKFinder for upload files, and I set chmod a+xrw to the xls file, but it's dosn't work.

    I hope you can help me!!!! 🙂

    Like

  7. I’m getting the ‘is not readable’ error also. things is, i have been developing my scripts in one hosting’s server and moved it over to a new server with the same hosting company, and now i am getting the error. literally the only thing i can see that’s different is the file is getting stored in temp directory c:\windows\Temp\php5\tempfilename.tmp (working) vs c:\windows\Temp\tempfilename.tmp (not working).
    i have no idea what would cause this on the scriptside…i’m beginning to wonder if it’s something with the server….
    any help would be greatly appreciated!

    Like

      1. i used $_FILES[“file”][“tmp_name”] because i’m only processing the data from the xls that is getting uploaded with php

        thx for your help btw!

        Like

      2. have you ever tried to specify the file path when instantiate the Spreadsheet_Excel_Reader object?

        $data = new Spreadsheet_Excel_Reader('example.xls', true);
        

        Like

  8. Here is the solution for Memory Exhaust issue:

    Probably one server has a 64 bit processor. The GetInt4d bit shift doesn’t work with 64 bit processors.

    Andreas Rehm hacked it to ensure correct result of the <=128)
    $_ord_24 = -abs((256-$_or_24) << 24);
    else
    $_ord_24 = ($_or_24&127) << 24;
    return ord($data[$pos]) | (ord($data[$pos+1]) << 8 | (ord($data[$pos+2]) << 16) | $_ord_24;

    Like

    1. Thanks for your comment Sandy, could u elaborate more on how to solve the Memory Exhausted issue which Pavel met?

      Which file did u hack?

      Thanks =)

      Like

  9. Hi,

    the not readable error happens when you miss the file path or file name. It cant be read so that is why it happens..

    I had same error and realized that instead of folder files/… I typed file/…

    Hope it helps to somebody

    Like

  10. I want to read the file in folder in webroot, I tried to change the url in function show_excel:
    $data = new Spreadsheet_Excel_Reader(‘files\upload\\’.$idata[‘name], true);
    instead of $data = new Spreadsheet_Excel_Reader(‘example.xls’, true);
    but it can read my file, do you have any idea?

    Like

    1. The example in this post will read the file in app/webroot. so for new Spreadsheet_Excel_Reader(‘example.xls’, true), it will read app/webroot/example.xls.

      say if you want to read the file app/example.xls. you could try new Spreadsheet_Excel_Reader(‘../example.xls’, true)

      And you also need to check if the file is writable as replied by Milos below.

      hope it work. =)

      Like

  11. I changed to ‘files/upload/’, in code
    $data = new Spreadsheet_Excel_Reader(‘files/upload/’.$idata[‘name], true);
    and it work, thanks so much 🙂

    Like

  12. I discover another problem :D, for example, when I have many rows in my excel file, and I try to delete the content of some rows, and after I show that file on my view, it’s show all rows include empty rows.
    I have modified some code in excel_reader2.php like this:
    – after line 631, I added some code to find empty cell, and stop to read that content
    if($this->val($row,1,$sheet)==”){ break;}
    – I removed html entities function to show vietnamese
    //$val = htmlentities($val); (about line 637)
    An my excel file will show the content that not empt to view, but I can’t stop it to show the number of rows (header).
    Do you know how to fix that problem? thanks you so much 🙂

    Like

    1. i haven’t tried to hack the excel_reader2.php. Is it possible to exclude those empty rows by manipulating the $data array in show_excel() function?

      Like

      1. I don’t know, in excel_reader2.php I don’t know how to control the data to make it show right not empty data. I just break to show it when read null data

        Like

      2. i suggest you print the $data and remove those entities which are empty instead of hacking the excel_reader2.php.

        print_r($data);
        

         

        can you show me the printed text?

        Like

  13. I want to save excel file data by row wise in a database table.How can i do it?Please give the answer with source code.Example:I have excel file which contained a class 10 subject wise marks.

    Like

  14. Hello y trayin to do this example but i have a problem, it throw a mistake: Notice (8): iconv() [function.iconv]: Detected an incomplete multibyte character in input string [APP\vendors\excel_reader2.php, line 1718]. I dont know what to do if u can help me i’d appreciate it!.

    Thank you in advance.

    Regards.

    Like

      1. The problem is with utf16encode this function is in excel_reader function _encodeUTF16($string)

        Like

  15. Error: The application is trying to load a file from the PhpExcel plugin
    Error: Make sure your plugin PhpExcel is in the app\Plugin directory and was loaded
    can you help me?

    Like

  16. Do i need an excel version to run the example? I download the test.xls and put it in my webroot folder.
    After starting the action show_exel() i get “The filename test.xls is not readable”

    Like

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