Sunday, April 3, 2011

Read Excel Sheet (.xls) file with PHP

Hi,

If you need to do large data migration from an excel sheet to your MySQL database, the most conventional way is to convert the .xls or .xlsx files to simple CSV(comma seperated file) and read it using PHP file read function. However we can also read a .xls file without converting it to a csv file. We can even navigate through the various worksheets in a single file. Click here to download the entire source code. I have used couple of external class files to achieve this.

Lets have a look at the spreadsheet I am using as example:

Code and Explanation:

First we need to include the main class file in our PHP page and initialise an object for it as follows:

include 'reader.php';
$excel = new Spreadsheet_Excel_Reader();

Now we are loading the excel file using the above created object as:
$excel->read('sample.xls');

Now we navigate through the rows and columns of the first worksheet in the excel file and display it as a simple HTML table in the browser:

$x=1;
    while($x<=$excel->sheets[0]['numRows']) {
      echo "\t<tr>\n";
      $y=1;
      while($y<=$excel->sheets[0]['numCols']) {
        $cell = isset($excel->sheets[0]['cells'][$x][$y]) ? $excel->sheets[0]['cells'][$x][$y] : '';
        echo "\t\t<td>$cell</td>\n";
        $y++;
      }
      echo "\t</tr>\n";
      $x++;
    }

In the above sheet[0] is used to read cells from the first work sheet, you can change it according to your needs. Now in sheets[0]['cells'], ['cells'] is a 2D array storing the data as shown in the above screen shot. After executing the entire source code, this is the output that is generated:


Best of luck. Download excel_reader.zip

:)

51 comments:

  1. nice dear i when i compile you source code it show following error please
    Deprecated: Assigning the return value of new by reference is deprecated in C:\wamp\www\e\reader.php on line 26

    ReplyDelete
    Replies
    1. i too got the error on line 261 as Deprecated: Assigning the return value of new by reference is deprecated

      Delete
    2. just removeng & character so the code :
      $this->_ole = new OLERead();

      Delete
    3. when i use other excel sheet it has 30 rows and 5 colums then compile error occured at line no 717, 718

      Delete
    4. i changed line 261 as Astimen FE mentioned....
      but at line 31 am having this problem...

      Message: require_once(OLERead.inc): failed to open stream: No such file or directory.

      can some one please help me resolve this...
      thank you...

      Delete
    5. rename oleread.inc into OLERead.inc

      Delete
    6. $this->_ole =& new OLERead();
      Just remove & sign and the error will be solved.
      $this->_ole = new OLERead();

      Delete
  2. Great! it works.. Nice thanks

    ReplyDelete
  3. Excellent piece of work. Thanks for posting.

    ReplyDelete
  4. Thank's a lot.It's working good. Awesome codeing....

    ReplyDelete
  5. its working fine....

    But how can we read a date from from excel file because its does not return the exact date.

    Thanks

    ReplyDelete
  6. Thank you sooooooooo much!!! Works Great!!!

    ReplyDelete
  7. Hi,
    Thank for the code, it's working good,

    Can you please help me to identify the headers in excel sheet, like name, country......

    ReplyDelete
  8. Hello, it is working fine with the excel file you attached with the code, but if i try it with xlsx format, it is saying it is not readable.

    Can you help me with this.

    ReplyDelete
    Replies
    1. The vendor which is used here, does not supports xlsx. Best way would be to convert the xlsx to xls and then use the code. I will try to post one for xlsx soon.

      Delete
  9. hey please specify the path to define for the class in the projects!!!
    Would b a great help

    ReplyDelete
  10. Is there any way to get cell address as "A1" if i have row number and column number also?

    ReplyDelete
    Replies
    1. You probably need to write a separate script for finding that out. It is not a part of this package. But I am sure it cannot be that difficult.

      Delete
  11. hi

    Let me say this is a great work! But I found a weird thing today.

    I have an excel which gets parsed perfectly only if i remove the last row. Any idea if the excel end marker is screwing the read function? I am not sure. I have been breaking head for a while now and any help in this regard would be much appreciated.

    Thanks !

    ReplyDelete
    Replies
    1. I guess you have checked whether special characters are stuffing things up. Try adding a fresh row replacing the one causing the issue and check.

      Delete
  12. Ya its work but how make file name dynamic ?
    thanx!

    ReplyDelete
  13. to avoid the deprecate error on line261 just add error_reporting(0);
    on the very frst line of your php script so that the errors will get suppressed and u will get only the output.

    ReplyDelete
  14. Hi how can i read excel file which has external link..
    like http:sitename.co/filename?
    like the document from dropbox.

    ReplyDelete
    Replies
    1. I am not sure whether this package is capable of reading external links. But a quick solutions would be to read the file using fopen() and save it in your server, and then use the above given code of that local version file. Hope this helps!!!

      Delete
  15. Great work. Thanks

    ReplyDelete
  16. thanku so much, its working....

    ReplyDelete
  17. how can i edit excel sheet??

    ReplyDelete
  18. Is it possible to amend this code to make it work to read .xlsx files?

    ReplyDelete
    Replies
    1. No, this API is specfic to older version, .xls

      Delete
  19. how to inser value in sheets please guide me

    ReplyDelete
  20. works great, thanks !

    ReplyDelete
  21. Thanks..it works..

    ReplyDelete
  22. hey really its great...... but i have a problem with .xlsx file. do you have any idea for this????

    ReplyDelete
    Replies
    1. hi
      im using noxls api http://noxls.net/apis/xls-parser and dont think about xls formats

      Delete
  23. nice, it's really great,what if i want to get the latest file of xls on the directory

    what code should i add? do you have any reference?

    thanks in advance

    ReplyDelete
  24. For me the oleread.inc had to be renamed to OLERead.inc to make this work. Also, for anybody trying to read .xlsx: this new format is in fact a collection of zipped xml files. Just extract them as if they are a zip file, and then you can just read as xml. You will need a xml-reader for this, not an Excel reader since it has nothing to do with Excel specifically really.

    ReplyDelete
  25. Works Perfectly for .xls file. Is any code to read .xlsx file?

    ReplyDelete
  26. Hi any help me

    Call to a member function read() on a non-object in /homepages/30/d502391960/htdocs/currimbhoys.com/newsite/csv_import/excel_reader/reader.php on line 381

    ReplyDelete
  27. have added OLeRead.inc and reader.php and included in my import.php.And its working fine in my local server but in server it shows a blank screen...what is the issue

    ReplyDelete
  28. I have added Oleread.inc and reader.php in my excel folder and included reader.php file in my import.php...In local it is working fine..but in server it is showing blank screen ...what is the issue?? please help

    ReplyDelete
  29. Nice..It Works. Thank you.

    ReplyDelete
  30. Hi ...
    I am using this tool ..to read a file with about 5000 rows .. but I found issues with some numbers to read .. ( 90% of them are correct ) but 10% of lines where read cell(number) are converted into a huge number e.g. -777234 is read as 1024344456
    can anyone give sime support .. thanks

    ReplyDelete