Read Excel file data in PHP - PhpExcelReader

Php-mysql Course

- 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:


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 => 'https://coursesweb.net/',
 3 => '5000',
 4 => '9800'
 ),
 6 => array (
 1 => 'Courses Games and Anime',
 2 => 'https://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.

<ul>
 <li>http://coursesweb.net/html/</li>
 <li>http://coursesweb.net/css/</li>
</ul>

.some_class {
  display: list-item;
}

var obj = {
 "courses": ["php", "javascript", "ajax"]
};
var jsonstr = JSON.stringify(obj);
alert(jsonstr);    // {"courses":["php","javascript","ajax"]}

$strhtml = '<body><div id="dv1">CoursesWeb.net</div></body>';
$dochtml = new DOMDocument();
$dochtml->loadHTML($strhtml);
$elm = $dochtml->getElementById("dv1");
echo $elm->nodeValue;    // CoursesWeb.net