Reading or Parsing Excel Spreadsheet using PHP

In this tutorial we will see, how we can easily parse or read an Excel sheet and display the parsed data to user. Even though we are going to display the output, the other extension of this technique is that we can parse an excel sheet filled with data, in set format, and convert it into SQL statement and insert the data into database. Now this is an easy task on an windows based server, to do the same thing in Linux we need to use some other libraries.

This tutorial assumes that you know PHP.

So let’s get into the details, PHP-ExcelReader is used for parsing the excel sheet.



First of all download the PHP-ExcelReader from SourceForge, if you PEAR configured properly or you can download the files Excel Reader from which I have removed the PEAR dependency (note: this is a bad practice, I only use this in my DEV environment for simple parsing and testing, please do not use this file in production environment).

require_once 'reader.php';

I just included the class file.

Now we will use following function for parsing the file, This function basically returns the data parsed from the excel sheet in an associative array. Off-course you have to remember that it is just a quick function to get the job done in DEV environment and you should not use it in production environment, without suitable modifications.

function parseExcel($excel_file_name_with_path)
{

   $data = new Spreadsheet_Excel_Reader();
   $data->setOutputEncoding('CP1251');
   $data->read($excel_file_name_with_path);

   $colname=array('NAME','SKU','PROD GROUP','WAREHOUSE CODE','DATE','LENGTH','WIDTH','COLOR','WEIGHT LB','PACKAGING','COST','RETAIL','WHOLESALE','SHIPPING COST','QUANTITY','MIN INV','MAX INV','DESC');
   $startloging=false;
   $k=0;

   for ($i = 1; $i <= $data->sheets[0]['numRows']; $i++) {

       for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++) {

           if($data->sheets[0]['cells'][$i][$j]=='NAME')
           {
               $startloging=true;
               break;
          }
          if($startloging)
           {
               $product[$k-1][$j-1]=$data->sheets[0]['cells'][$i][$j];
               $product[$k-1][$colname[$j-1]]=$data->sheets[0]['cells'][$i][$j];

           }

       }

       if($startloging)
       {

           $k=$k+1;

       }

   }

   return $product;

}

In first line we initialize the Spreadsheet_Excel_Reader, then we set the output encoding.

Finally we read the Excel file using read method of Spreadsheet_Excel_Reader class. This basically completes the process of reading the excel spreadsheet. Simple isn’t it?

Rest of the logic is basically parsing the output array of spreadsheet class, to more user friendly and easy to understand array.

The first FOR loop is counting the rows in spreadsheet, while second FOR loop is counting the number of columns in each row.

if($data->sheets[0]['cells'][$i][$j]=='NAME')

This condition is used to find the right row and column while parsing the data array. This ensures that we only return the relevant data from the excel sheet and not everything. In your case replace ‘NAME’ with the name of the first column in your spreadsheet.

Once we figure out the correct row( from where we need to pick the data) we save it in both the numeric array and also as associative array.

Here is a variation of this function, where our excel sheet does not have any column name mentioned.

function parseExcel($excel_file_name_with_path)
{

   $data = new Spreadsheet_Excel_Reader();

   // Set output Encoding.

   $data->setOutputEncoding('CP1251');

   $data->read($excel_file_name_with_path);
   $colname=array('id','name');

   for ($i = 1; $i <= $data->sheets[0]['numRows']; $i++) {
         for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++) {

               $product[$i-1][$j-1]=$data->sheets[0]['cells'][$i][$j];
               $product[$i-1][$colname[$j-1]]=$data->sheets[0]['cells'][$i][$j];

       }

   }

   return $product;

}

If you know some other alternatives please share.