Read an Excel document using PHP

2011-09-09 16:59:00 CET - kafoso

In this article I will describe how to read data from an Excel (2003) spreadsheet using PHP. Script files and usage examples are included.

Table of contents

  1. Download and test the PHP Excel Reader
  2. Files

Passing data between a database and PHP has been done for decades - well, almost - since 1995. Most commonly PHP is used with MySQL, but other relational database management systems may also be used with PHP, such as Microsoft Access and MSSQL; however, MSSQL support was deprecated in PHP versions 5.3.0 and above, and was replaced by SQL Server (SQLSRV), also engineered by Microsoft.

The structure of a standard spreadsheet resembles the structure of a database table quite well. And so, an obvious use for a spreadsheet is a form of “open” database – open in the sense that everyone can access the spreadsheet on the web server, unless access to the document is somehow restricted. XML documents are widely used for arranging and storing data and spreadsheet are roughly the same, however, the data in spreadsheets may be modified through an interface using spreadsheet applications like Microsoft Excel and/or OpenOffice.org Calc. This enables a wider, non-XML expert audience to handle and modify the data, without the need to implement systems for displaying data in a way that makes sense to the average human brain.

In this article I will give a quick example of how to easily read data from an Excel 2003 spreadsheet into a PHP variable, and then display the contents of the spreadsheet in a browser.

For testing the PHP Excel Reader you need a PHP environment
Read more here on how to set up a PHP environment locally on your computer. Alternatively, you can upload the PHP Excel Reader files to a website which supports PHP.

Download and test the PHP Excel Reader

  1. Firstly, you must download php_excel_reader.zip file which contains the following script files:
    • default.php
      Contains the calls to the reader and a usage example
    • data.xls
      A simple Excel 2003 spreadsheet containing test data
    • excel_reader/excel_reader.php
      Data access class
    • excel_reader/reader.php
      Data conversion, encoding and decoding
    • excel_reader/oleread.inc
      Data stream from the XLS-file

     
    The files "reader.php" and "oleread.inc" were originally developed by Vadim Tkachenko under the name PHPExcelReader (http://sourceforge.net/projects/phpexcelreader).

  2. Unpack the ZIP-file to your local PHP environment or upload the files to a web server using FTP.

  3. Open the "data.xls" file and verify the test data is there, and perhaps add some content to this file yourself.

  4. Run the "default.php" script in an Internet Browser and a table containing the data from the XLS-file is displayed.

Now, obviously you can do a lot more than just displaying the contents of the spreadsheet, e.g. input numbers in the spreadsheet and use these numbers as coordinates to draw geometric shapes.


Files

Download: php_excel_reader.zip

PHP Excel Reader.