Reading Excel files with PHPExcel

Microsoft Excel is a very powerful application and now we’ve finally got a powerful API written in PHP5 to read/write/convert Excel files also directly from Linux (not using COM, etc). It’s far from perfect at the moment, but it is working in 90% of cases. Software I’m talking about can be found here and it’s called PHP Excel.

First, we need to create a reader for our file. PHP Excel makes this easy for us, as we can use PHPExcel_IOFactory::createReaderForFile() method. It will try to detect filetype by analyzing the extension of the provided file. It can read XLS, XLSX, CSV, PDF and probably even more file formats. So to create a reader, use this code:

require('PHPExcel.php'); // found when you download the PHPExcel
$Reader = PHPExcel_IOFactory::createReaderForFile($file);
$Reader->setReadDataOnly(true); // set this, to not read all excel properties, just data

Reader is ready and I’ve also specified setReadDataOnly() to read only data, not all the properties around it. Usually we don’t need all the properties and this API is quote memory expensive, so try to read as little as possible to get the job done.
Next step is to load the file – you do that by calling load() method:

$objXLS = $Reader->load($file);

To read a value from cell A1 in sheet 0 (first sheet) use this code:

$value = $objXLS->getSheet(0)->getCell('A1')->getValue();
// or to get calculated value, if there is a formula, etc
$value = $objXLS->getSheet(0)->getCell('A1')->getCalculatedValue();

API is huge, so please check their not-so-well written documentation about it on their web page.

As I’ve written before, this API is using quite some memory to read in the Excel files, so if you have a long running script, it’s a good practice to unload it as soon as possible.
You can do that by calling disconnectWorksheets() method.

$objXLS->disconnectWorksheets();
unset($objXLS);

Tags: , , , , , ,

 
 
 

5 Responses to “Reading Excel files with PHPExcel”

  1. Igor Karpov says:

    this solution is good only for small xls files

    but for reading more than 10 000 rows Im using noxls api http://noxls.net/apis/xls-parser

  2. php says:

    how do i get a row count while reading the excel

  3. Piyush Ballal says:

    how to read an activeX control button from excel sheet using php???

  4. […] talk about how to write/create Excel files with PHP Excel. If you need to read Excel files, check How to read excel files with PHP article.We’ll create a simple Excel sheet with the following values:ProductQuanityPriceTotal […]

Leave a Reply