Reading Excel Documents from PHP applications

In this article we will learn on how we can read Microsoft Excel Sheet in PHP. To achieve this we will be using Open Source Tool PHPExcelReader. It provides us with necessary API that allow us to read the Excel Sheet in PHP.

Directory Structure for ExcelReader:

When you will unzip the excelreader, you will find example.php that shows us how to read excel sheet using php code. And the most important thing is the Excel folder, it contains all the necessary files and API that will actually perform your operation. Keep the Excel folder as it is as files are included in the API code as per the directory structure.

Example:

Well the included example.php is sufficient to understand the functionality of the ExcelReader. But i am showing the same code here with slight modification.

< ?php
  
require_once 'Excel/reader.php';
$data = new Spreadsheet_Excel_Reader();
 
// Set output Encoding.
$data->setOutputEncoding('CP1251');
 
/***
* if you want you can change 'iconv' to mb_convert_encoding:
* $data->setUTFEncoder('mb'); * 
 
** / 
 
 
 /***
* By default rows & cols indeces start with 1
* For change initial index use: 
*/
* $data->setRowColOffset(0); * 
*
 
/***
*  Some function for formatting output.
* $data->setDefaultFormat('%.2f'); 
 columns with unknown formatting * * $data->setColumnFormat(4, '%.3
* setDefaultFormat - set format fo rf'); * setColumnFormat - set format for column (apply only to number fields) * 
 
** / 
 
 
 $data->read('test.xls'); //Passing the excel sheet to be read from PHP
error_reporting(E_ALL ^ E_NOTICE);
 
for ($i = 0; $i < = $data->sheets[0]['numRows']; $i++) {
 echo $data->sheets[0]['cells'][$i][1]."<br />";
} 
  
?>

The most important thing here is:
 
Description
Source Code
 
Accessing Specific Sheet object inside excel document
$data->sheets[0](Accessing 1st sheet)
 
Accessing total rows inside specific sheet
$data->sheets[0]['numRows']
 
Accessing individual cells data
$data->sheets[0]['cells'][0][1] (Here i am accessing 1st sheet cell[0][1] i.e. A1 cell)
 
If we know this basic code than we can read the data inside the excel sheet from php.