Wouldn’t it be grand if you could read data from a Microsoft Excel spreadsheet into an array? Well folks, life is grand! With the PHPExcel classes, we can read and even write to xls and xlsx spreadsheets. How cool is that?

In this article, I’m going to keep it short and sweet. I could write for hours on what PHPExcel can do, and spend minutes explaining what it can’t do. For now, we’ll stick with understanding how to read data from a spreadsheet into an array, which we will then display to the end user, namely, you.

To begin with, you will need to download PHPExcel from their website. That wasn’t so hard, was it? Now you can simply extract the directories and files and plug them into your framework. I won’t try to explain where, since your framework probably doesn’t match my framework. Though if I have to explain that, this may be a tad advanced for you. Moving on…

In the code snippet below, let’s pretend that we have a spreadsheet with one sheet, and with seven columns. Using the form provided in the code snippet, the user will simply upload their spreadsheet, which the code will read into a php array and display to the user. Be warned… This code snippet is a dumbed-down version of some code that I wrote for an internal application. In this case, the spreadsheet will always have seven columns. If there were to be fewer, php would throw a tantrum; if there were to be more, relevant data may not be processed.

Key Bits of Code

This little gem MUST be included if you are to do anything with the PHPExcel Class:

require_once dirname(__FILE__) . '\PHPExcel\IOFactory.php';

If you aren’t reading a file, what are you doing? I suggest you read the file into a php object:

$inputFile = $_FILES['spreadsheet']['tmp_name'];
$inputFileType = PHPExcel_IOFactory::identify($inputFile);
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($inputFile);

It would help to know when to end our loop! This tells us when to end it:

$sheet = $objPHPExcel->getSheet(0); 
$highestRow = $sheet->getHighestRow(); 
$highestColumn = $sheet->getHighestColumn();

Lastly, there is the loop and the referencing of the data of individual columns:

for ($row = 2; $row <= $highestRow; $row++){
    // set the array of data from the indexed row
    $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
    // push the data into an array (note: a-g and 0-7 reference all of the column iterations - you would need to call a and 1, through g and 7)
    $column[$index]['COLUMN_A-G'] = $rowData[0][0-7];
}
<style>
	th {
		background-color: #bbb;
	}
	th, td {
		font-size: 11px;
	}
	.tiny {
		width: 77px;
	}
	.small {
		width: 98px;
	}
	.medium {
		width: 140px;
	}
	.large {
		width: 280px;
	}
	.huge {
		width: 420px;
	}
	.dark {
		background-color: #ccc;
	}
	.light {
		background-color: #ddd;
	}
</style>
<div class="container-fluid">
    <div class="xlsToArray">
        <div class="row-fluid well">
        	<h3>Load Spreadsheet</h3>
        	<div class="row">
        		<div class="col-md-1">
				<form method="post" enctype="multipart/form-data" action="yourFile.php">
					Upload File: <input type="file" name="spreadsheet"/>
					<input type="submit" name="submit" value="Submit" />
				</form>
		        </div>
        	</div>

			<?php
			/////// Include PHPExcel_IOFactory
			require_once dirname(__FILE__) . '\PHPExcel\IOFactory.php';
			//Check valid spreadsheet has been uploaded
			if(isset($_FILES['spreadsheet'])){
				if($_FILES['spreadsheet']['name']){
					if(!$_FILES['spreadsheet']['error']) {
						$inputFile = $_FILES['spreadsheet']['name'];
						$extension = strtoupper(pathinfo($inputFile, PATHINFO_EXTENSION));
						if($extension == 'XLSX' || $extension == 'ODS') {
							// Read spreadsheeet workbook
							try {
								$inputFile = $_FILES['spreadsheet']['tmp_name'];
								$inputFileType = PHPExcel_IOFactory::identify($inputFile);
								$objReader = PHPExcel_IOFactory::createReader($inputFileType);
								$objPHPExcel = $objReader->load($inputFile);
							} catch(Exception $e) {
								die($e->getMessage());
							};
							// Get worksheet dimensions
							$sheet = $objPHPExcel->getSheet(0); 
							$highestRow = $sheet->getHighestRow(); 
							$highestColumn = $sheet->getHighestColumn();
							echo '<h4>Sheet Data (', number_format($highestRow), ' Records):</h4>';
							echo '<table><tr>
									<th class="tiny">COLUMN A</th>
									<th class="tiny">COLUMN B</th>
									<th class="tiny">COLUMN C</th>
									<th class="tiny">COLUMN D</th>
									<th class="huge">COLUMN E</th>
									<th class="large">COLUMN F</th>
									<th class="small">COLUMN G</th>
									</tr>';
							// Loop through each row of the worksheet in turn
							$column = array();
							$index = 0;
							for ($row = 2; $row <= $highestRow; $row++){ 
								// Read a row of data into an array
								$rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
								// Insert into array
								$column[$index]['COLUMN_A'] = $rowData[0][0];
								$column[$index]['COLUMN_B'] = $rowData[0][1];
								$column[$index]['COLUMN_C'] = $rowData[0][2];
								$column[$index]['COLUMN_D'] = $rowData[0][3];
								$column[$index]['COLUMN_E'] = $rowData[0][4];
								$column[$index]['COLUMN_F'] = $rowData[0][5];
								$column[$index]['COLUMN_G'] = $rowData[0][6];
								// Display data from array
								if ($row % 2 == 0) {
									echo '<tr class="dark">';
								} else {
									echo '<tr class="light">';
								}
								echo '<td class="tiny">', $column[$index]['COLUMN_A'], '</td>';
								echo '<td class="tiny">', $column[$index]['COLUMN_B'], '</td>';
								echo '<td class="tiny">', $column[$index]['COLUMN_C'], '</td>';
								echo '<td class="tiny">', $column[$index]['COLUMN_D'], '</td>';
								echo '<td class="huge">', $column[$index]['COLUMN_E'], '</td>';
								echo '<td class="large">', $column[$index]['COLUMN_F'], '</td>';
								echo '<td class="small">', $column[$index]['COLUMN_G'], '</td>';
								echo '</tr>';
								$index = $index + 1;
							};
							echo '</table>';
							unset($index);
							unset($column);
						} else {
							echo "Please upload an XLS or XLSX file";
						};
					} else {
						echo $_FILES['spreadsheet']['error'];
					};
				};
			};
			?>

        </div>
    </div>
</div>