Reading/Parsing Excel Spreadsheet using PHP
This is second article in the series of common PHP problems that a beginner faces. You can check the first article Warning: header data already sent.
In this tutorial we will see, how we can easily parse or read an Excel sheet and display the parsed data to user. Even though we are going to display the output, the other extension of this technique is that we can parse an excel sheet filled with data, in set format, and convert it into SQL statement and insert the data into database. Now this is an easy task on an windows based server, to do the same thing in Linux we need to use some other libraries.
This tutorial assumes that you know PHP.
So let’s get into the details, PHP-ExcelReader is used for parsing the excel sheet.
First of all download the PHP-ExcelReader from SourceForge, if you PEAR configured properly or you can download the files Excel Reader from which I have removed the PEAR dependency (note: this is a bad practice, I only use this in my DEV environment for simple parsing and testing, please do not use this file in production environment).
require_once 'reader.php';
I just included the class file.
Now we will use following function for parsing the file, This function basically returns the data parsed from the excel sheet in an associative array. Off-course you have to remember that it is just a quick function to get the job done in DEV environment and you should not use it in production environment, without suitable modifications.
function parseExcel($excel_file_name_with_path)
{
$data = new Spreadsheet_Excel_Reader();
$data->setOutputEncoding('CP1251');
$data->read($excel_file_name_with_path);
$colname=array('NAME','SKU','PROD GROUP','WAREHOUSE CODE','DATE','LENGTH','WIDTH','COLOR','WEIGHT LB','PACKAGING','COST','RETAIL','WHOLESALE','SHIPPING COST','QUANTITY','MIN INV','MAX INV','DESC');
$startloging=false;
$k=0;
for ($i = 1; $i <= $data->sheets[0]['numRows']; $i++) {
for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++) {
if($data->sheets[0]['cells'][$i][$j]=='NAME')
{
$startloging=true;
break;
}
if($startloging)
{
$product[$k-1][$j-1]=$data->sheets[0]['cells'][$i][$j];
$product[$k-1][$colname[$j-1]]=$data->sheets[0]['cells'][$i][$j];
}
}
if($startloging)
{
$k=$k+1;
}
}
return $product;
}
In first line we initialize the Spreadsheet_Excel_Reader, then we set the output encoding.
Finally we read the Excel file using read method of Spreadsheet_Excel_Reader class. This basically completes the process of reading the excel spreadsheet. Simple isn’t it?
Rest of the logic is basically parsing the output array of spreadsheet class, to more user friendly and easy to understand array.
The first FOR loop is counting the rows in spreadsheet, while second FOR loop is counting the number of columns in each row.
if($data->sheets[0]['cells'][$i][$j]=='NAME')
This condition is used to find the right row and column while parsing the data array. This ensures that we only return the relevant data from the excel sheet and not everything. In your case replace ‘NAME’ with the name of the first column in your spreadsheet.
Once we figure out the correct row( from where we need to pick the data) we save it in both the numeric array and also as associative array.
Here is a variation of this function, where our excel sheet does not have any column name mentioned.
function parseExcel($excel_file_name_with_path)
{
$data = new Spreadsheet_Excel_Reader();
// Set output Encoding.
$data->setOutputEncoding('CP1251');
$data->read($excel_file_name_with_path);
$colname=array('id','name');
for ($i = 1; $i <= $data->sheets[0]['numRows']; $i++) {
for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++) {
$product[$i-1][$j-1]=$data->sheets[0]['cells'][$i][$j];
$product[$i-1][$colname[$j-1]]=$data->sheets[0]['cells'][$i][$j];
}
}
return $product;
}
If you know some other alternatives please share.
Related posts
- Joomla 1.0.x : How it works
- ADS: Automatic Deployment Script
- akWpUploader: Alternative Wordpress Image Uploader
- Reading/Parsing Excel Spreadsheet using PHP
- Drupal Form API : An Introduction
Filed under php | Comments (42)
42 Responses to “Reading/Parsing Excel Spreadsheet using PHP”




My general advice to client is to use the CSV format instead of Excel files for import/export purposes. CSV has native PHP support.
But some clients insist on using Excel – in those cases, this will come in handy.
Reading/Parsing Excel Spreadsheet using PHP…
This is second article in the series of common PHP problems that a beginner faces You can check the first article Warning header data already sent In this…
[...] to Excel by configuring the MIME Type Profile Option Power your PHP Business Logic with Excel Reading/Parsing Excel Spreadsheet using PHP Bookmark this [...]
Anyhow i tried use the reader.php but i got this error
Fatal error: Allowed memory size of 8388608 bytes exhausted (tried to allocate 512 bytes)
any idea how to direct read it without puting the data into arrays? cos my excel file size is 70MB consist of 114 columns and 29,000 rows of datas. Pleaseeeee Help!!
[...] melakukan konversi membuat diri saya mampir ke blog salah satu sahabat saya di PHPUG, lalu mampir kemari dan akhirnya saya dapatkan Excel Reader yang sudah bersih dari PEAR Library. Setelah itu, saya buat [...]
i want to read excel esv file i am able to read it but
i have some formula’s in my excel csv file i want to read the data with
output of given formula but i can,t read it..
the output come with formula
guys anyone here have a fix on the date problem? coz the date output is like this 00/0606/08080808 instead of 08/21/2008… i tried different encodings though TIA
Was there a solution for the memory error…i have a small file 3.5 mb it will not work, but when i cut in half…runs fine.
I made the date updates and GetInt4 update but that did not help.
but my file will keep growing…i plan to have a 15 mb file when its all compile.
If you find a solution for the 70mb file please share the info.
Thank you very much for your advice, Binny VA. I have applied your advice, say no to excel, and it was working properly in 5 minutes with csv.
Thank you to everybody!
how to install php excel reader. i have xampp folder
so where i put this new packages inside of this xampp
anyone pls help me
I use this code for upload a excel file but in my code fisrt i give upload a master xls file on local machine then user add the data and then upload this save file so at that time it’ll give error this file is not readable.and i make another file on local machine it’ll upload so it’s not give an error readable.it’ll easly uploaded so what to do for that?
pls give reply as soon as possible
@gauttam you need to set the file permissions properly.
ya i set all the permission for the file but then also not able to upload that file i already chk my tmp folder rights and file also but not able to upload http://ganesh/fidility/sponsors/accountusers just go on this link user name & pwd gautampatel/passwd. I think tmp folder has full control otherwise it’ll not give to upload the file
When i download master file and then open that file and save as on my local machine so at that time also able to upload.Otherwise it’ll give error for the file is Tmp\php741.tmp is not readable
above link is on my local machine this is server link
http://www.giuliopia.com/winepoints/sponsors/accountusers
user_id and password both are same in above comment
MY problem with the phpexcelreader is that it can’t read the temporary file PHP uploads via a web form. I don’t want to save every dang file being uploaded. I just want the parsed content and then to move on. Fails.
MY problem with the php excel is that it can’t write picture into the appointed excel file via a PHP web form. And I don’t know how to convert excel into pdf via PHP,Pls help me !
mail:anson.wu@valence.com
I have downloaded the excel reader and putted it on my local web server
I is working properly but when I was uploaded this on webserer It is showing two type of err The filename Sql2Excel.xls is not readable
But reading and fetching the records on localhost server. How can Improve and solve the problem with the script.
@rakesh please check that actual file name on the erver and file name you specified in the script are same(it is case-sensitive)
Please Tell me which file name either it is excel file name or other
I am giving the path and here it the the script and the problem.
http://digital-product-retailer.com/excel/xls2mysql_step1.php
http://digital-product-retailer.com/excel/xls2mysql.php
function str_database_name($str)
{
$str=strtolower(strtr($str,”*()!$’?: ,&+-/.ŠŒŽšœžŸ¥µÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖØÙÚÛÜÝßàáâãäåæçèéêëìíîïðñòóôõöøùúûüýÿ”,
“—————SOZsozYYuAAAAAAACEEEEIIIIDNOOOOOOUUUUYsaaaaaaaceeeeiiiionoooooouuuuyy”));
return $str;
}
what is this function
biterscripting tends to be very usful when it comes to parsing CSV (Command separated values) files, or just parsing/reporting in general (web logs, excel files, html documents, xml documents, …). They have posted an excellent sample script at http://www.biterscripting.com/Download/SS_CSV.txt. You can download biterscripting free at http://www.biterscripting.com . Hope this helps.
Patrick Mc
hi sir
i know the how data show in excel but my problem is how i will show IMAGE in excel sheet.do u have any way
Hi i am creating a spreadsheet when client clicks on a button but when i try to upload the file for reading the contents using “reader.php” i get the following error:
“The file name template.xls is not readable” i think its going to line # 171 in reader.php page.
please help me out in this issue Thanks in advance.
@ Srinivas : I sure your directory of the xls file is not true.you need to set the directory again? Examples : in my applycation,the xls file in helpers folder in a com_sim component of Joomla.But the command is $data->read(‘components/com_sim/helpers/hello.xls’);
Any solutions to the Date Error.
My date also shows in the following way.
00/1818/0909 when it should be 3/18/09
i’ve tryed the updates, but no luck.
thanks
Please guide me how can improve my website for the users. As this is tutorial site.
Rakesh
While I am trying to upload a xls file after downloading and editing it, I am getting an error “The filename is not readable”. But if I copy the same content in a new xls and upload it,it’s getting uploaded successfully. I am using Mac and Safari.
I am getting a error “The filename is not readable” while uploading a exel file after downloading and editing it. But if a paste the content in a new excel the error doesn’t come. Please help.
Using Mac and Safari
Hi,
I am working on php 5 I tried the phpExcelReader,i also tried the example files with the downloaded directory and the above tutorial code but it simply didnt work..i dont see any error or any sort of info on the browser that why it didnt work..
any help is appreciated
Regards,
well, without error message it is hard to help, but may be you can try to see if path to xls file is correct.
Still facing the problem. Has anybody got the solution??
Fatal error: Allowed memory size of 33554432 bytes exhausted (tried to allocate 33030145 bytes) in D:\xampp\htdocs\testexcel\Excel\oleread.inc on line 172
Why do i get this when i run the example script
Thank you very much for this article.
| I have removed the PEAR dependency: this is a bad practice
Why is it bad practice?
Thanks for the article, is very usefuly. I have the “is not readable” error and i fix it in this way:
i have uploaded file in webform and when call to the Spreadsheet_Excel_Reader read method send this argument $_FILES["myFile"]["tmp_name"] and doesn’t work, i move the file into own directory in this way :
$path = “data/” .$_FILE["file"]["name"];
$data = new Spreadsheet_Excel_Reader();
$data->setOutputEncoding(‘CP1251′);
$data->read($path);
}
and works fine. I hope thats was we helpfully for you
Regards
hello people,
please use “tmp_name” as the second array parameter.
for example :-
$data->read($_FILES['excelfile']['tmp_name']);
so it will get the location of where the file is temporarily stored on server.
thanks.
Hi,
i have the same error. like this
“While I am trying to upload a xls file after downloading and editing it, I am getting an error “The filename is not readable”. But if I copy the same content in a new xls and upload it,it’s getting uploaded successfully.”
i use many method,but i can’t solution this question,i also use ” ‘tmp_name’ as the second array parameter”,but can’t solution.
please help !!
Thanks for sharing.
firstly I can’t switch to csv. My problem is in my excel file i have data in different languages (German, Italian, Spanish, Chinese, Japanese, Russian etc…) I am not able to read it all at once.
I tried using different encoding mechanism but didnt work.
Anyone????
I have a problem with the link cell, its data is not retrieved and displayed.
Please help.
Thanks for sharing this.Right now, I have problems importing an excel file with keywords. I will try to execute this code.
Cool Coding, thank you for helping me out with this problem…