How to read excel files in php

In this tutorial I’m going to show you how you can use the php library called PHP Excel Reader to read excel files using php. Yes, things like excel readers for php has already been written by smart people in the past so I’m not going to show you how to build an excel reader from scratch. It’s not quite my level yet, I still consider myself a beginner. Anyway, let’s get to the main topic.

 

Requirements

• php excel reader v2.2.1
• Microsoft Excel (Optional)

 

Sample Spreadsheet

First you need to have a sample spreadsheet that you could  work on. Just make it simple, preferably a 2-column sheet with a  few data on it.

 

Setup Working Directory

If you haven’t already downloaded the php excel reader library go ahead and download it and then setup your working directory. Also put your sample excel file on that directory for easy access.

 

Modifying the Library

The php excel reader is a pretty old library. You need to open up the excel_reader2.php file and modify line 916. From this:

$this->_ole =& new OLERead();

To this:

$this->_ole = new OLERead();

You’ll get an error that assigning the return value of new by reference is deprecated especially if you’re using PHP Version 5.3 and above.

 

Main Story

Okay so let’s get to the main story. The documentation for this library is actually pretty useful so if you want to do some tinkering then you might as well read the official documentation. Almost all of the methods that I’ll be talking about is based on the documentation and the example that comes with the library. I recommend that you check out the example first before continue reading. If you can’t figure something out or if you want to do something that isn’t mentioned in the documentation then come back here. I cannot promise that I have the answers but I can assure you that I will cover the basic stuff that you may want to know on reading excel files.

php excel reader documentation

The example.php file is a good place to start, go ahead and make a backup of it so that you can safely modify your copy.

First, this line tells us that this file is going to use the php excel reader library:

require_once 'excel_reader2.php';

It then creates a new object of the php excel reader class:

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

You need to specify the file name as the argument. If the php file is in the same directory with the excel file that you’re trying to read then the example above is going to work fine.

Lastly, you need to turn off error reporting for notices. Try to remove this line and you’ll see some orange stuff in your page.

error_reporting(E_ALL ^ E_NOTICE);

Now that you’re done importing the needed files in your php file. It’s now time to show you some of the methods that you can use.

• val() – This method requires 2 arguments and 1 optional argument. The first argument is the row number. The row number starts with the number 1. Row number 1 in most cases is the custom header name(Eg. student name, course, school, etc.) so the starting index for the row number is 2. The second argument is the column name. It is standard in excel (Eg. A, B, C, etc.), this is really useful because we can just put the column names in the php code. The third is the optional argument sheet index.  Sheet is zero-indexed. First sheet is 0, second index is 1 and so on.

$data->val(row_number, column_name, sheet_index);

• dump() – This method is a beast. What it does is just to dump the whole content of the first sheet if you don’t specify the third argument. The first argument requires a boolean data type, true if you want to output the row numbers(1, 2, 3, etc.) and false if you don’t want it. The second argument is whether to display the column letters, just set it to true if you want it to be displayed. The third argument is the sheet index, by default it’s 0 and you don’t even need to specify it explicitly.

$data->dump(row_numbers, column_letters, sheet_index);

• rowcount() – This method returns the total number of rows of the sheet.

$data->rowcount();

• colcount() – This method returns the total number of columns of the sheet.

$data->colcount();

 

Now that you know the methods that you can use, you might as well try to experiment a bit.
Dumping the whole excel sheet is pretty useful but what if you want to do something with the data that’s in it? For that we could use a while loop to loop though all of the data in the current sheet.

First you need to know the total row count. I added 1 because the row is not zero-indexed, if I do not add 1, the loop that we will be creating later is just going to read up to the second to the last record:

$num_row = $data->rowcount() + 1;

Specify the index that were going to start with, as I have said earlier the row count starts with 2.

$index = 2;

Then the table heading, since the first row in our spreadsheet is the header we will just have to use it as the header for our table:

<table>
<tr>    <th><?php echo $data->val(1, 'A'); ?></th>    <th><?php echo $data->val(1, 'B'); ?></th>
</tr>

After that, use a while loop to repeat the table row as long as the index is not equal to the total row count.

<?php while($index != $num_row){ ?>
   //table rows
<?php } ?>

And for the table rows the only thing that’s changing is the index. If you want to do something with the data later on, this is the perfect time to be storing them in an array which you could loop through later on. If you’re going to save them into the database, you can also execute the query here.

<tr>
   <td><?php echo $data->val($index, 'A'); ?></td>
   <td><?php echo $data->val($index, 'B'); ?></td>
</tr>

Finally, increment the index after creating the table row:

$index++;

 

Output

Here’s the sample output. The one on the top is the output for the dump, and the one below is the output for the while loop that we created:

 

Conclusion

I guess that’s it for this tutorial. In this tutorial you’ve learned how to use the php excel reader library to read excel files in php. Making use of this library will make life easier for you if you need to do some operations to the data in the excel file like saving them into the database our outputting them in a customized fashion. If you have questions, suggestions for this tutorial, or if there are other details that you think I have missed please feel free to use the comments section below thanks for reading!