Monday, 9 September 2013

Read an Excel file using PHP
Ajinkya Mandhare9:55 pm 8 comments

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.

Have queries?? Get them resolved...

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
About The Author Ajinkya Mandhare Passionate about Web Development and Programming, and an Andriod maniac, this blog of mine is an effort to share whatever I learn new, to help those who may be looking out for similar things. Hope that this blog helps you to find all your requirements with regards to Programming stuff!! Keep visiting. Facebook and Twitter

8 comments:

  1. very useful article thank you for post

    ReplyDelete
  2. Hi, What if I don't want to import the data to database and just display rows and columns on webpage? What will I have to change? Also, can you provide with steps to be done on import.php in case we need to connect to database. Naive here. :)

    ReplyDelete
  3. I am getting this error after clicking on submit button- "The filename C:\wamp\tmp\php87C9.tmp is not readable" what should I do?

    ReplyDelete
  4. To answer your first question, create an object ('$data' in our example) of Spreadsheet_Excel_Reader class, and then use this object as $data->sheets[]['cells'][][] to access the (row,column)th entry of the corresponding sheet number in the excel.

    I'm not sure what sort of error you are getting. I had used xampp, and it worked seamlessly for me. Can you give more insight on what exactly you are doing and what error you got?

    ReplyDelete
  5. we have PHPExcel library() also which one should i use whether PHPExcel or spreadsheet library.

    ReplyDelete