Read an excel file in PHP

August 31, 2013 | 3263 views | Comments

Use PHPExcel to read data from an excel file in PHP.

For example

Read and display all records of an excel file in PHP.

Note: It is the best practice to make some necessary file checking before reading and parsing the given excel file, more importantly if the file is uploaded by a user. Create a check point and confirm, Is it really an excel file? Setting a max file size limit is also nice.

Screenshot of the excel file.

Please mouse over here to view the image.

Code.

include_once('PHPExcel_1.8.0_doc/Classes/PHPExcel/IOFactory.php');

$inputFileName = 'read-an-excel-file-in-php.xlsx';

/*check point*/

$inputFileType = PHPExcel_IOFactory::identify($inputFileName);
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($inputFileName);

$data = array(1,$objPHPExcel->getActiveSheet()->toArray(null,true,true,true));

//print the result
echo '<pre>';
	print_r($data);
echo '</pre>';

//another option to display the data
//go over the result and parse the records, then make it more readable
if($data[0]==1){
	foreach($data[1] AS $row){
		foreach($row AS $column){
			echo $column . ', ';
		}
		echo '<br />';
	}
}

The result is in an array form, the first value determines if the attempt to read the excel file is successful or not. A returned value of 1 indicates a successful result, otherwise there's an error.

Result:

Array
(
    [0] => 1 //successful
    [1] => Array
        (
            [1] => Array
                (
                    [A] => Last Name
                    [B] => First Name
                    [C] => Age
                    [D] => Sex
                    [E] => Location
                )

            [2] => Array
                (
                    [A] => Jackson
                    [B] => Barbara
                    [C] => 27
                    [D] => F
                    [E] => Florida
                )

            [3] => Array
                (
                    [A] => Kimball
                    [B] => Andrew
                    [C] => 25
                    [D] => M
                    [E] => Texas
                )

            [4] => Array
                (
                    [A] => Baker
                    [B] => John
                    [C] => 28
                    [D] => M
                    [E] => Arkansas
                )

            [5] => Array
                (
                    [A] => Gamble
                    [B] => Edward
                    [C] => 29
                    [D] => M
                    [E] => Virginia
                )

            [6] => Array
                (
                    [A] => Anderson
                    [B] => Kimberly
                    [C] => 23
                    [D] => F
                    [E] => Tennessee
                )

            [7] => Array
                (
                    [A] => Houston
                    [B] => Franchine
                    [C] => 25
                    [D] => F
                    [E] => Idaho
                )

            [8] => Array
                (
                    [A] => Franklin
                    [B] => Howard
                    [C] => 24
                    [D] => M
                    [E] => California
                )

            [9] => Array
                (
                    [A] => Chen
                    [B] => Dan
                    [C] => 26
                    [D] => M
                    [E] => Washington
                )

            [10] => Array
                (
                    [A] => Daniel
                    [B] => Carolyn
                    [C] => 27
                    [D] => F
                    [E] => North Carolina
                )

            [11] => Array
                (
                    [A] => Englert
                    [B] => Grant
                    [C] => 25
                    [D] => M
                    [E] => Delaware
                )

        )

)
Last Name, First Name, Age, Sex, Location, 
Jackson, Barbara, 27, F, Florida, 
Kimball, Andrew, 25, M, Texas, 
Baker, John, 28, M, Arkansas, 
Gamble, Edward, 29, M, Virginia, 
Anderson, Kimberly, 23, F, Tennessee, 
Houston, Franchine, 25, F, Idaho, 
Franklin, Howard, 24, M, California, 
Chen, Dan, 26, M, Washington, 
Daniel, Carolyn, 27, F, North Carolina, 
Englert, Grant, 25, M, Delaware,