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

:)

41 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
  2. Great! it works.. Nice thanks

    ReplyDelete
  3. Thanks a lot. Awesome code.....

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

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

    ReplyDelete
  6. its working fine....

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

    Thanks

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

    ReplyDelete
  8. 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
  9. 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
  10. hey please specify the path to define for the class in the projects!!!
    Would b a great help

    ReplyDelete
    Replies
    1. Sure. Will keep that in mind in future :)

      Delete
  11. 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
  12. 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
  13. Ya its work but how make file name dynamic ?
    thanx!

    ReplyDelete
  14. 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
  15. 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
  16. Great work. Thanks

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

    ReplyDelete
  18. how can i edit excel sheet??

    ReplyDelete
  19. 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
  20. how to inser value in sheets please guide me

    ReplyDelete
  21. works great, thanks !

    ReplyDelete
  22. Thanks..it works..

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

    ReplyDelete
  24. 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
  25. 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
  26. Works Perfectly for .xls file. Is any code to read .xlsx file?

    ReplyDelete