Read an Excel file using PHP

Reading an excel file using some server-side scripting language is something that should be in the ammo of any developer. Now, Excel is something which I have always stayed away from...blame it on the insipid UI, or lack of various other features which databases like Oracle, MySql etc provide. However, one cannot deny the fact that Excel is rampantly used to maintain records, and at some point or other, you may have to use excel as an input to your code.

I had faced such a situation recently, and after some googling, I managed to find a way to read the excel sheet and insert the corresponding entries to a MySql database. So, this post of mine will help you in reading an excel sheet using PHP. Once it is read and you have the values of the entries with you, its up to you as to what you like to do with it. In my case, I simply inserted those values in a MySql database.

What you need to get started:
PHPExcelReader
SpreadsheetExcelWriter

Download the above two packages. Extract both the packages. Now, in the PHPExcelReader package, find the oleread.inc. Copy this file, and paste it to Spreadsheet\Excel\Reader folder in the SpreadsheetExcelWriter package. It does not have the Reader folder by default. Create that folder and paste the oleread.inc file in it. Now, save the oleread.inc file in the same folder as OLERead.php.
Now, copy the entire Spreadsheet folder from SpreadsheetExcelWriter package, and paste it in the Excel folder in PHPExcelReader package. This 'Excel' folder in the PHPExcelReader package is what we will be using for reading from an excel file using PHP. Once you have done all this copying and pasting, you are all set to read an Excel sheet!!

Step I: Create a simple HTML file 'importexcel.html' to take the excel sheet as an input:

<form action="import.php" enctype="multipart/form-data" method="post">
File Name: <input type="file" name="file" id="file">

<input type="submit" name="Submit" value="Submit" />
</form>

Step II: Create a PHP file 'import.php', where we write the action part for the above form:

<?php

mysql_connect(hostname,username, password) OR DIE ('Unable to connect to database! Please try again later.');
mysql_select_db(dbname);
include 'Excel/reader.php';
$data = new Spreadsheet_Excel_Reader();
$data->setOutputEncoding('CP1251');
$data->read($_FILES["file"]["tmp_name"]);

//columns:
$sql = "INSERT INTO `TABLENAME` (";

for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++)
{
$sql .= "`" . mysql_escape_string($data->sheets[0]['cells'][1][$j]) . "`,";
}
$sql = substr($sql, 0, -1) . ") VALUES\r\n";
//cells
for ($i = 2; $i <= $data->sheets[0]['numRows']; $i++)
{
  $sql .= "(";
  for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++)
  {
  $sql .= "" . mysql_escape_string($data->sheets[0]['cells'][$i][$j]) . ",";
  }
$sql = substr($sql, 0, -1) . "),\r\n";
}
$sql =  substr($sql, 0, -3) . ";";
echo $sql;

mysql_query($sql);

?>

Make sure that you have the 'Excel' folder in the root directory.

If you look at the output of the code, it will print the INSERT query, which is then used in mysql_query to execute the query.

Whats a take away from this tutorial is that, you can access the entries from the Excel sheet by creating an object ('$data' in our example) of Spreadsheet_Excel_Reader class, and then use this object as $data->sheets[<sheet number>]['cells'][<row number>][<column number>] to access the (row,column)th entry of  the corresponding sheet number in the excel.
Once you can access these entries, you can use them in whichever way you want....creating a table in html, or inserting those entries in a database, or any other processing.

Thats it!! Simple it was, wasn't it? Now, go ahead...use this code to read an Excel sheet....njoy!!

Have Queries?? Post it on our Discussion Forum Compild

Kindly Bookmark and Share it: