How to Read an XLS File in PHP

Save

PHP works often with databases and, as such, data files. Excel is commonly used in offices to store tables of data. However, making the two work together is a tough task. Excel files follow a proprietary data format set by Microsoft, and PHP is built by a vast team of volunteers. So there is nothing built in to PHP to help in reading Excel files. Fortunately, the Open Source community has taken the time to figure out how to solve this problem and created a good solution for reading XLS (Excel) files in PHP.

Things You'll Need

  • PHP Web hosting
  • Microsoft Excel

Install PHP Excel Reader

  • Go to the PHP Excel Reader download site, and download the package called "php-excel-reader" (see Resources).

  • Unzip the downloaded file by double-clicking on it.

  • Upload the file called "excel_reader2.php" to your website. Use FTP or your Web hosting service's file upload service to do this. The file should be placed in a folder where your PHP scripts can find it. You can put it in the same folder as the PHP script you wish to read Excel files in, or you can put it in a folder that is in the "include path." The "include path" is a set of folders where you can put files that your PHP script will always be able to find, even if your script is in a totally different place. Your website might have a folder called "includes" that is in the include path. If it does, put "excel_reader2.php" there. If it doesn't, put it in the same folder as the PHP scripts with which you wish to use it.

Read an XLS File

  • Upload an Excel file to use as an example. Create a simple Excel file, and save it as "example.xls". Open the file and fill in the first three columns of the first row with the words "column 1", "column 2" and "column 3". Then on the second row, fill the first three columns with the text "1-1", "2-1" and "3-1". Then fill the first three columns of the third row with the text "1-2", "2-2" and "3-2".

    Save the file and upload it to the same folder where you uploaded excel_reader2.php.

  • Open your favorite text editor, and save a text file called "example.php". This will be your PHP script for this tutorial.

  • Write your PHP script to open the Excel file, read the table of data and then print the data out in the user's Web browser. Use this code to do it:

    <?php

    $xls = new Spreadsheet_Excel_Reader('example.xls', false); //open PHP Excel Reader and your Excel file

    echo "<pre>"; //print HTML that helps formatting

    for ($r = 1; $r <= $xls->rowcount(); $r++) { //walk through each row

    for ($c = 1; $c <= $xls->colcount(); $c++) { //walk through each column

    echo $xls->raw($r, $c) . "       ";            //Display the data in that cell

    }

    echo "\n"; //make a new line at the end of each row

    }

    echo "</pre>"; //close the HTML we made earlier

    ?>

  • Save and upload your PHP file to your website. If you were able to put PHP Excel Reader in the include path, you may put your PHP file wherever you wish. If not, put your PHP file in the same folder as PHP Excel Reader.

  • To check your work, visit example.php in your Web browser. You should see the table of data from your Excel file printed in your browser window.

Tips & Warnings

  • Use this tutorial and the documentation at the PHP Excel Reader website as a base for doing more complicated work with Excel files.

References

  • Photo Credit in office for computer image by Anatoly Minkov from Fotolia.com
Promoted By Zergnet

Comments

Resources

You May Also Like

  • How to Convert MS Excel to PHP

    Microsoft Excel is a commonly used spreadsheet program. It combines a user-friendly interface with strong calculating and graphing capabilities which makes it...

  • How Do I Create a Microsoft Excel Script?

    Actions can be automated in Microsoft Excel using both macros and VBA scripting, and set to activate on-command or when a condition...

Related Searches

Check It Out

Geek Vs Geek: Robot battles, hoverboard drag race, and more

M
Is DIY in your DNA? Become part of our maker community.
Submit Your Work!