How to Read Excel Files with PHP

If are not familiar with PHPExcel then this tutorial can be very helpful to learn basics things and basically reading excel files with PHP. PHPExcel helps you to read read and create excel files but you will have to try some php excel reader examples and it completely depends on type of your application.
First of all you will need to download and install phpexcel and load file in php excel. Let's do it step by step :-

Download and Install PHP Excel

You can use compose and if you are a beginner and found it new then you should be familiar with it. You can take a look about it here and can use it for managing dependency in your projects.
First create a composer.json file :-

{

"require": 

{ 

"phpoffice/phpexcel": "dev-develop" 

} 

}

Now type following command to install following dependencies and wait for a while, it takes a few minutes.

Composer.phar install

A vendor/autoload.php file will be auto generated when library files will be download to project_root/vendor/phpexcel with project_root/vendor/compose paths and this file will take care of automated loading of library class.
Or you can do it with PEAR install

$shell> pear install Spreadsheet_Excel_Writer

Load excel file in PHP Excel

You can basically choose one of the two ways to load files in PHPExcel. First way to read excel files in PHP excels is specifying types of file manually and in second case you can let library determine the file type by it self. You can try these following codes for above methods and types of file which are supported by PHPExcel :-

require_once "vendor/autoload.php";>

$fileName = "sample.xlsx";



/** automatically detect the correct reader to load for this file type */

$excelReader = PHPExcel_IOFactory::createReaderForFile($fileName);



/** Create a reader by explicitly setting the file type.

// $inputFileType = 'Excel5';

// $inputFileType = 'Excel2007';

// $inputFileType = 'Excel2003XML';

// $inputFileType = 'OOCalc';

// $inputFileType = 'SYLK';

// $inputFileType = 'Gnumeric';

// $inputFileType = 'CSV';

$excelReader = PHPExcel_IOFactory::createReader($inputFileType);

*/

Reader Options

There are a few options but I will go straight to simple one :-

//if we dont need any formatting on the data


$excelReader->setReadDataOnly();


//load only certain sheets from the file


$loadSheets = array('Sheet1', 'Sheet2');


$excelReader->setLoadSheetsOnly($loadSheets);


//the default behavior is to load all sheets


$excelReader->setLoadAllSheets();

This is very simple method to load sheet that you need and you can also set Read data only method to speed up process and loading of data in cells without special excel formatting. Big file can use lot of memory and all things completely depends on type of your work. There will be lots of changes you have to face while using these codes like, date formatting (They will be loaded as numbers) and many more. If you are doing some database import then its good for you because it only allows loading of certain columns and row.
Here is a example of both filters :-

class SampleReadFilter implements PHPExcel_Reader_IReadFilter { public function readCell($column, $row, $worksheetName = '') { // Read rows 1 to 10 and columns A to C only if ($row >= 1 && $row <= 7) { if (in_array($column,range('A','C'))) { return true; } } return false; } }
After creating filters you will need to pass this to reader:-
$sampleFilter = new SampleReadFilter();
$objReader->setReadFilter($chunkFilter);

Loading and Displaying the data

This is very easy to do and there are lot’s of stuff you can change to render data, it totally depends on your requirements. You can use following command to load data in PHP :-

$excelObj = $excelReader->load($fileName);

This will produce a excel page in php format but you can format it with your need or covert it to an array with following codes.

 $excelObj->getActiveSheet()->toArray(null, true,true,true); 

It will load data in current active sheet and recent one, you can choose to switch between sheets and get contents. Better you can automate these process and get all sheets in one array :-

//get all sheet names from the file 


$worksheetNames = $excelObj->getSheetNames($fileName); 


$return = array(); 


foreach($worksheetNames as $key => $sheetName){ 


//set the current active worksheet by name 


$excelObj->setActiveSheetIndexByName($sheetName); 


//create an assoc array with the sheet name as key and the sheet contents array as value 


$return[$sheetName] = $excelObj->getActiveSheet()->toArray(null, true,true,true); 


} 


//show the final array 


var_dump($return); 

Loading data in chunks

This method works fine in most cases but in case of requirement of loading some external information in a database and you don’t need to load entire excel file at once it will be much faster to split it in chunks and work with each individual chunk at a time. To do that we need a simple read filter:

<?php 


class ChunkReadFilter implements PHPExcel_Reader_IReadFilter 


{ 


private $_startRow = 0; 


private $_endRow = 0; 


/** Set the list of rows that we want to read */ 


public function setRows($startRow, $chunkSize) { 


$this->_startRow = $startRow; 


$this->_endRow = $startRow + $chunkSize; 


} 


public function readCell($column, $row, $worksheetName = '') { 


// Only read the heading row, and the configured rows 


if (($row == 1) || ($row >= $this->_startRow && $row < $this->_endRow)) { 


return true; 


} 


return false; 


} 


} 


/** Define how many rows we want to read for each "chunk" **/ 


$chunkSize = 2048; 


/** Create a new Instance of our Read Filter **/ 


$chunkFilter = new chunkReadFilter(); 


/** Tell the Reader that we want to use the Read Filter **/ 


$excelReader->setReadFilter($chunkFilter); 


/** Loop to read our worksheet in "chunk size" blocks **/ 


for ($startRow = 2; $startRow <= 65536; $startRow += $chunkSize) { 


/** Tell the Read Filter which rows we want this iteration **/ 


$chunkFilter->setRows($startRow,$chunkSize); 


/** Load only the rows that match our filter **/ 


$excelObj = $excelReader->load($inputFileName); 


$data = $excelObj->getActiveSheet()->toArray(null, true,true,true); 


// Do some processing here - the $data variable will contain an array which is always limited to 2048 elements regardless of the size of the entire sheet 


}

_{Code source}