CakePHP

CakePHP – php-excel-reader

71 Comments

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

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

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

    Reply

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

    Reply

    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

      Reply

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

    Reply

    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

      Reply

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

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

    Reply

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

    Reply

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

    Reply

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

    Reply

      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

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

    Reply

    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

      Reply

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

    Reply

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

    Reply

    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

      Reply

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

    Like

    Reply

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

    Reply

    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

      Reply

      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

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

    Reply
  22. Pingback: CakePHP – Read data from an excel file into an array | hassancseku

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

    Reply

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

        Like

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

    Reply

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

    Reply

    1. There are other ppl who also came across the file not readable error. Please check the .xls file path and see if there is the root cause.

      Like

      Reply

  29. App::import(‘Vendor’, ‘php-excel-reader/excel_reader2’);
    this id giving me following sytax error

    syntax error, unexpected ‘new’ (T_NEW)

    Like

    Reply

    1. The same for me.
      Change the row: $this->_ole =& new OLERead();
      into: $this->_ole = new OLERead();

      From PHP 5 object are passed by references and & is not yet necessary.

      Like

      Reply

  30. The same for me.
    Change the row: $this->_ole =& new OLERead();
    into: $this->_ole = new OLERead();

    From PHP 5 object are passed by references and & is not yet necessary.

    Like

    Reply

Leave a comment

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