Reading/Parsing Excel Spreadsheet using PHP

June 15th, 2008 | Save to del.icio.us now(0)

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.

Are you satisfied with your knowledge? No, then spent 15 minutes every day on PHPCamp.net a knowledge sharing website for our own PHP community

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.

Wondering what to do next?


	Grab my feed
Catch Me On Twitter

Filed under php | Comments (42)

42 Responses to “Reading/Parsing Excel Spreadsheet using PHP”

June 16, 2008 5:15 pm Binny V A says
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.
June 16, 2008 11:15 pm roScripts - Webmaster resources and websites says
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…
July 9, 2008 10:22 am PHP : Dead Easy Excel Export | Edmonds Commerce Blog says
[...] to Excel by configuring the MIME Type Profile Option Power your PHP Business Logic with Excel Reading/Parsing Excel Spreadsheet using PHP Bookmark this [...]
July 10, 2008 2:28 pm Jasonwoon says
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!!
July 22, 2008 1:06 am | 8 Jam 27 Menit | Catatan says
[...] 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 [...]
July 23, 2008 5:23 am parvin gaikwad says
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
August 20, 2008 3:14 pm nhojnivron says
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
August 24, 2008 8:43 pm eltioloco says
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.
September 15, 2008 7:50 am Galder says
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!
October 7, 2008 7:48 am vineeth says
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
October 21, 2008 7:41 am Gauttam says
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
October 21, 2008 8:35 am Amit Kumar Singh says
@gauttam you need to set the file permissions properly.
October 21, 2008 9:30 am Gauttam says
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
October 21, 2008 9:36 am Gauttam says
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
October 21, 2008 9:39 am Gauttam says
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
October 21, 2008 10:59 pm Marc says
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.
December 9, 2008 6:31 am anson says
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
January 1, 2009 12:50 pm Rakesh Kumar Nautiyal says
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.
January 1, 2009 1:29 pm Amit Kumar Singh says
@rakesh please check that actual file name on the erver and file name you specified in the script are same(it is case-sensitive)
January 2, 2009 10:04 am Rakesh Kumar Nautiyal says
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
January 2, 2009 10:29 am Rakesh Kumar Nautiyal says
function str_database_name($str)
{
$str=strtolower(strtr($str,”*()!$’?: ,&+-/.ŠŒŽšœžŸ¥µÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖØÙÚÛÜÝßàáâãäåæçèéêëìíîïðñòóôõöøùúûüýÿ”,
“—————SOZsozYYuAAAAAAACEEEEIIIIDNOOOOOOUUUUYsaaaaaaaceeeeiiiionoooooouuuuyy”));
return $str;
}
what is this function
January 12, 2009 12:01 am PatrickMc says
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
January 16, 2009 12:11 pm pankaj kamble says
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
February 19, 2009 12:52 pm Srinivas says
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.
March 10, 2009 4:48 am thuclh says
@ 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’);
March 19, 2009 1:41 am TrialandError says
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
March 24, 2009 4:32 am Rakesh Kumar Nautiyal says
Please guide me how can improve my website for the users. As this is tutorial site.

Rakesh
May 23, 2009 12:41 am Deep says
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.
May 23, 2009 12:44 am somdip says
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.
May 23, 2009 12:45 am somdip says
Using Mac and Safari
May 26, 2009 3:47 pm Ameen Raza says
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,
May 26, 2009 5:49 pm Amit Kumar Singh says
well, without error message it is hard to help, but may be you can try to see if path to xls file is correct.
May 27, 2009 4:08 am somdip says
Still facing the problem. Has anybody got the solution??
June 12, 2009 3:22 pm Nishit.M.V says
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
June 23, 2009 9:04 pm Daniel Malikov says
Thank you very much for this article.

| I have removed the PEAR dependency: this is a bad practice

Why is it bad practice?
August 28, 2009 11:25 pm Fernando says
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
September 4, 2009 12:22 am shahil says
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.
September 28, 2009 9:40 am Vickykoko says
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 !!
October 1, 2009 5:33 am kalpan says
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????
December 15, 2009 10:27 am rochmadi says
I have a problem with the link cell, its data is not retrieved and displayed.

Please help.
January 6, 2010 5:14 pm Marco says
Thanks for sharing this.Right now, I have problems importing an excel file with keywords. I will try to execute this code.
March 13, 2010 8:01 pm Techie Talks says
Cool Coding, thank you for helping me out with this problem…