- PhpExcelReader is a free PHP class that can be used to read Excel file data without Microsoft Office. It supports both XLS and XLSX types.

Download PhpExcelReader.

This class is useful for small and medium excel file size (a few MB), because it reads the entire spreadsheet at once, and if you've got a large spreadsheet, the memory is exhausted. In this case it is better to convert the excel data in CSV format, then use it in PHP.


The PhpExcelReader class stores excel file data into a multidimensional array, in the $sheets property, accessed with:
$objectClass->sheets
- $objectClass is the object instance of the class.

For example, this excel sheet:
Excel Sheet

To read this excel file with the PhpExcelReader class, use this code:
<?php
include 'excel_reader.php';       // include the class
$excel = new PhpExcelReader;      // creates object instance of the class
$excel->read('excel_file.xls');   // reads and stores the excel file data

// Test to see the excel data stored in $sheets property
echo '<pre>';
var_export($excel->sheets);
echo '</pre>';
The $sheets property will store this array:
array (
 0 => array (
  'maxrow' => 0,
  'maxcol' => 0,
  'numRows' => 8,
  'numCols' => 4,
  'cells' => array (
    2 => array ( 2 => 'Web sites data' ),
     4 => array (
      1 => 'Title',
      2 => 'Url',
      3 => 'Visitors',
      4 => 'Accesses'
    ),
    5 => array (
     1 => 'Web Programming Courses',
     2 => 'http://coursesweb.net/',
     3 => '5000',
     4 => '9800'
    ),
    6 => array (
     1 => 'Courses Games and Anime',
     2 => 'http://www.marplo.net/',
     3 => '6000',
     4 => '22000'
    ),
    7 => array (
     1 => 'PHP: Hypertext Processor',
     2 => 'http://php.net/',
     3 => '30000',
     4 => '92000'
    ),
    8 => array (
     1 => 'Yahoo!',
     2 => 'http://yahoo.com/',
     3 => '100000',
     4 => '650000'
    ),
   ),
   'cellsInfo' =>  array (
     5 => array (
      3 => array (
      'raw' => 5000,
      'type' => 'unknown'
     ),
     4 => array (
      'raw' => 9800,
      'type' => 'unknown'
     ),
    ),
    6 => array (
     3 => array (
      'raw' => 6000,
      'type' => 'unknown'
     ),
     4 => array (
      'raw' => 22000,
      'type' => 'unknown'
     ),
    ),
    7 => array (
     3 => array (
      'raw' => 30000,
      'type' => 'unknown'
     ),
     4 => array (
      'raw' => 92000,
      'type' => 'unknown'
     ),
    ),
    8 => array (
     3 => array (
      'raw' => 100000,
      'type' => 'unknown'
     ),
     4 => array (
      'raw' => 650000,
      'type' => 'unknown'
     ),
    ),
    2 => array (
     2 => array ( 'colspan' => 3 ),
    ),
   ),
 )
)
- $excel->sheets[0]['numRows'] contains the number of rows with data in first sheet.
- $excel->sheets[0]['numCols'] contains the number of columns with data in first sheet.
- $excel->sheets[0] contains multidimensional array with data of the first sheet in excel file. The data is stored in 'cells' and the meta-data is stored in an array called 'cellsInfo'.
$sheets[index]  -->  'cells'  -->  row --> column --> Interpreted value
             -->  'cellsInfo' --> row --> column --> 'type' (Can be 'date', 'number', or 'unknown')
                                                --> 'raw' (The raw data that Excel stores for that data cell)

- The $objectClass->boundsheets[index_sheet]['name'] contains the name of the excel sheet having the index order of "index_sheet".


You can traverse the $objectClass->sheets array to add data into a HTML table.

- Here is an example that creates and outputs HTML table with excel data from each sheet.
<?php
include 'excel_reader.php';     // include the class

// creates an object instance of the class, and read the excel file data
$excel = new PhpExcelReader;
$excel->read('test.xls');

// this function creates and returns a HTML table with excel rows and columns data
// Parameter - array with excel worksheet data
function sheetData($sheet) {
  $re = '<table>';     // starts html table

  $x = 1;
  while($x <= $sheet['numRows']) {
    $re .= "<tr>\n";
    $y = 1;
    while($y <= $sheet['numCols']) {
      $cell = isset($sheet['cells'][$x][$y]) ? $sheet['cells'][$x][$y] : '';
      $re .= " <td>$cell</td>\n";  
      $y++;
    }  
    $re .= "</tr>\n";
    $x++;
  }

  return $re .'</table>';     // ends and returns the html table
}

$nr_sheets = count($excel->sheets);       // gets the number of worksheets
$excel_data = '';              // to store the the html tables with data of each sheet

// traverses the number of sheets and sets html table with each sheet data in $excel_data
for($i=0; $i<$nr_sheets; $i++) {
  $excel_data .= '<h4>Sheet '. ($i + 1) .' (<em>'. $excel->boundsheets[$i]['name'] .'</em>)</h4>'. sheetData($excel->sheets[$i]) .'<br/>';  
}

echo $excel_data;      // outputs HTML tables with excel file data

- In the archive with PhpExcelReader class you'll find a "test.xls" file for test, and two examples, and comments with details in code.
• This class is a version for PHP 5.3+ of the Spreadsheet_Excel_Reader class, from: PHP-ExcelReader Web Site.

Daily Test with Code Example

HTML
CSS
JavaScript
PHP-MySQL
Which attribute is used in <a> tag for the address of the link?
src href rel
<a href="http://coursesweb.net/" title="CoursesWeb.net">CoursesWeb.net</a>
Which CSS property sets the type of the text font?
font-family text-decoration font-size
h2 {
  font-family:"Calibri",sans-serif;
}
What instruction selects all the <div> tags with class="cls"?
querySelector("div.cls") getElementsByTagName("div") querySelectorAll("div.cls")
var elm_list = document.querySelectorAll("div.cls");
var nr_elms = elm_list.length;       // number of selected items
alert(nr_elms);
Indicate the function that can be used to get the sum of values in an array.
array_sum() array_diff() array_shift()
$arr = array(1, 2, 3, 4);
$arr_sum = array_sum($arr);
echo $arr_sum;       // 10
Read Excel file data in PHP - PhpExcelReader

Last accessed pages

  1. Zuma Games (9545)
  2. RegExp - Regular Expressions in ActionScript (2039)
  3. Force Download files with PHP (1037)
  4. Multiple Select Dropdown List with AJAX (12125)
  5. Multiple Drop-Down Select Lists Creator (3298)

Top accessed pages

  1. Courses Web: PHP-MySQL JavaScript Ajax HTML CSS Flash-AS3 (47990)
  2. Read Excel file data in PHP - PhpExcelReader (36111)
  3. PHP-MySQL free course, online tutorials PHP MySQL code (35689)
  4. Get Attribute (ID, Class, Name, Title, Src) with jQuery (32182)
  5. PHP PDO - exec (INSERT, UPDATE, DELETE) MySQL (29461)