PHP Excel Reader

Posted by Matt at 24 December 2008

Category: Programming, utils

I recently created a project on Google Code for PHP Excel Reader. This is a PHP library for reading MS Office Excel files that has existed on Sourceforge for quite a while but was no longer being maintained or enhanced. I needed to use it for a work project, so I put in some time to fix it and enhance it to make it more usable. I decided to put it up on Google Code in the hopes that others would find it useful.

[ PHP Excel Reader Project Home ]

Example

Microsoft Excel

<?php
require_once 'excel_reader2.php';
$xls = new Spreadsheet_Excel_Reader("example.xls");


echo '<table border="1">';


  for ($row=1;$row<=$xls->rowcount();$row++) {


  echo '<tr>';


  for ($col=1;$col<=$xls->colcount();$col++) {


    echo '<td>' . $xls->val($row,$col) . ' </td>';


  }


  echo '</tr>';


}


echo '</table>';


?>






HTML Output




Usage




$data = new Spreadsheet_Excel_Reader("test.xls"); 

Retrieve formatted value of cell (first or only sheet): 

  $data->val($row,$col) 

Or using column names: 

  $data->val(10,'AZ') 

From a sheet other than the first: 

  $data->val($row,$col,$sheet_index) 

Retrieve cell info: 

  $data->type($row,$col);
  $data->raw($row,$col);
  $data->format($row,$col);
  $data->formatIndex($row,$col); 

Get sheet size: 

  $data->rowcount();
  $data->colcount(); 

$data->sheets[0]['cells'][$i][$j] - data from $i-row $j-column 

$data->sheets[0]['numRows'] - count rows
$data->sheets[0]['numCols'] - count columns 

$data->sheets[0]['cellsInfo'][$i][$j] - extended info about cell
$data->sheets[0]['cellsInfo'][$i][$j]['type'] = "date" | "number" | "unknown"
$data->sheets[0]['cellsInfo'][$i][$j]['raw'] = value if cell without format
$data->sheets[0]['cellsInfo'][$i][$j]['format'] = Excel-style Format string of cell
$data->sheets[0]['cellsInfo'][$i][$j]['formatIndex'] = The internal Excel index of format 

$data->sheets[0]['cellsInfo'][$i][$j]['colspan']
$data->sheets[0]['cellsInfo'][$i][$j]['rowspan']



							


		


	


	

		




5 Comments



    
	
	
  1. 
		
    
			
    
				
    
				kirk837 says (11.02.2009 at 6:37 pm):
    
				
    
			
    
			
    
								
    “some problem with translating times”:  Are you in the Eastern time zone (-5)?
    
This looks like either a problem with a GMT default for display, or a server-client time difference.  Or perhaps there is some problem with translating times, but doesn’t seem likely, does it?
    

    I’m just starting getting familiar with your AJAX toolbox — no real experience yet, but it sure looks good.
    
Thanks,
    
Kim Kirkpatrick
    
					
			
    
		
    

    2. 

	
  2. 
		
    
			
    
				
    
				Template Cascade says (22.02.2009 at 10:11 pm):
    
				
    
			
    
			
    
								
    Do you know of or recommend anything similar to this for .CSV files?  Might cut down on some development time for a little project of mine.  Thanks
    
					
			
    
		
    

    3. 

	
  3. 
		
    
			
    
				
    
				microtrash says (17.04.2009 at 11:50 am):
    
				
    
			
    
			
    
								
    template cascade:
    

    use fgetcsv() builtin to php
    
					
			
    
		
    

    4. 

	
  4. 
		
    
			
    
				
    
				gtg489w says (18.05.2009 at 4:47 am):
    
				
    
			
    
			
    
								
    Matt, you can replace _ with   which is interpreted by browsers as non breaking spaces
    

    Line 864:
    
// In Excel formats, “_” is used to add spacing, which we can’t do in HTML
    
$pattern = preg_replace(”/_./”,”",$pattern);
    
					
			
    
		
    

    5. 

	
  5. 
		
    
			
    
				
    
				Matt says (18.05.2009 at 9:38 am):
    
				
    
			
    
			
    
								
    @gtg489: I considered that, but _ is actually meant to take up the amount of space as the character that follows it. Which is why you often see _) in formats that contain (0.0) for negatives. Making every _ a nbsp would make some too wide and kind of defeat the purpose, IMO. Anyway, suggestions are best made on the Google Group for the library, as there are other people working on improving it now also.
    
					
			
    
		
    

    6.