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);