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
Done =)
Update @ 2011-08-12: Fix the debug message problem by longinus
Awesome, i will check with my app later.
Thanks
LikeLike
u are welcome =)
LikeLike
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
LikeLike
haha~ ya, u are rite =)
LikeLike
Temen minta tutorialnya sie phpexcel reader…, Attach yah ke saia
LikeLike
Hi Andri, what does it mean?
LikeLike
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 🙂
LikeLike
you are welcome =)
LikeLike
Does anyone else get loads of errors using this?
LikeLike
what error did u get?
LikeLike
I get loads. Have taken a screen shot:
LikeLike
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?
LikeLike
no idea. have u tried the php-excel-reader directly in the web server?
LikeLike
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.
LikeLike
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
LikeLike
Hi, i get an error “The filename example.xls is not readable”. Did anyone experience this?
LikeLike
What MS Office version are u using?
LikeLike
I am using MS version 2003. I get the same message.
What is the problem?
LikeLike
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
LikeLike
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?
LikeLike
Just saw you other post, sorry!
LikeLike
i guess u are talking about this
CakePHP – Read data from an excel file into an array
hope it can solve your problem =)
LikeLike
Thank you, it really helped me
LikeLike
u are welcome. =)
LikeLike
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?
LikeLike
You can use a for loop to manipulate the $data.
for more details you can refer to the following link.
[RESOLVED] Excel-reader for PHP
LikeLike
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!!!! 🙂
LikeLike
Hi Gustavo,
for the load method in PHPExcel, the input parameter has to be a file path instead of an url. that’s why it prompts you file not found.
so try to load the excel file by providing the file path.
hope this help.
Kit
Reference: Docs For Class PHPExcel_Reader_Excel5
LikeLike
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!
LikeLike
How did you specific the file path for loading the excel from the c:\windows\Temp directory?
LikeLike
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!
LikeLike
have you ever tried to specify the file path when instantiate the Spreadsheet_Excel_Reader object?
LikeLike
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;
LikeLike
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 =)
LikeLike
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
LikeLike
Thanks MILOS =)
LikeLike
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?
LikeLike
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. =)
LikeLike
You need to check is your folder writable .. Also it goes files/upload/…
LikeLike
Thanks =D
LikeLike
I changed to ‘files/upload/’, in code
$data = new Spreadsheet_Excel_Reader(‘files/upload/’.$idata[‘name], true);
and it work, thanks so much 🙂
LikeLike
good to know that you have solved the problem =)
LikeLike
Great 🙂 Good luck with the rest of the project 🙂
LikeLike
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 🙂
LikeLike
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?
LikeLike
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
LikeLike
i suggest you print the $data and remove those entities which are empty instead of hacking the excel_reader2.php.
can you show me the printed text?
LikeLike
Hi everyone, not sure if that helps but i slightyl modified the code so taht it didn’t throw errors in debug mode 2. Also I changed the first column width to 20px for it was very very wide with default cakephp css template.
you can find the file here: http://dl.dropbox.com/u/1240928/excel_reader2.php
regards
longinus
LikeLike
Thanks very much~ i have updated the post. =D
LikeLike
thanks man!
LikeLike
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.
LikeLike
If you want to save data in database, they you need to setup a Model instance. Read thru the CakePHP cookbook and you could find the answer.
LikeLike
I refactored the class into a more suitable PHP5 class. Not completely finished, but now working with it is a little bit less painful: https://github.com/dereuromark/tools/blob/master/Vendor/SpreadsheetExcelReader/SpreadsheetExcelReader.php
Especially using CakePHP (I made a wrapper class for App::uses lazy loading).
LikeLike
Hi Mark,
Thanks for your comment. Please let me know once you have finished the class. And it would be great if you could write a brief usage example post of your SpreadsheetExcelReader.php and i could redirect the users to your blog,
Happy blogging! =)
LikeLike
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.
LikeLike
Is your source file saved in UTF-8 encoding?
Reference: StackOverflow – PHP: Dealing special characters with iconv
LikeLike
The problem is with utf16encode this function is in excel_reader function _encodeUTF16($string)
LikeLike
so you have fixed the problem?
LikeLike
Not yet, the problem persist!
LikeLike
I guess you need to convert the string before passing it to the helper. the following post should help.
StackOverflow – How to detect malformed utf-8 string in PHP?
LikeLike
thank you..
it’s work.!!
LikeLike
good to know that. =)
LikeLike
how to view show_excel.ctp what is the path plz replay asap…
LikeLike
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?
LikeLike
See if the following post helps
StackOverflow – Using PHPExcel in CakePHP
LikeLike
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”
LikeLike
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.
LikeLike
App::import(‘Vendor’, ‘php-excel-reader/excel_reader2’);
this id giving me following sytax error
syntax error, unexpected ‘new’ (T_NEW)
LikeLike
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.
LikeLike
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.
LikeLike