Thursday, January 08, 2009

(0) Comments

PHP and Excel

welcome to infomix.blogspot.com

Many of my customers hold data in Excel files. Then, when they decide to build a web based, database driven project controlled by PHP, ask me to move their Excel data into the MySQL. Whether it’s possible and how to do it, will be the content of this post.

Since the PHP allows you to create an OLE compatible COM object with its methods and properties, the solution is more than easy. All you need is take a brief look into the PHP COM Manual Pages to be able to understand the following explanatory example. I wrote this code as a PHP CLI script which seems to me more usable for interaction with other applications.


Before we start, let’s introduce input parameter of the function which is responsible for data retrieve from an Excel file and output it as a matrix, representing the Excel table.

  • $file - (string) absolute path to the Excel data file
  • $sheet - (int) order number of the sheet which data we want to extract
  • $rows - (array) rows selected from the sheet
  • $cols - (array) columns selected from the sheet

The names of variables were selected to represent their meaning (semantic names) and facilitate the understanding of script work. But if you’re still confused of input parameters or output, don’t be affraid, following examples will clarify it more. So, let’s move forward to the PHP and Excel interaction.

function getDataFromExcel($file, $sheet, $rows, $cols)
{
   // COM CREATE
   fwrite(STDOUT, "----------------------------------------\r\n");
   $excel = new COM("Excel.application") or die ("ERROR: Unable to instantaniate COM!\r\n");
   fwrite(STDOUT, "Application name: {$excel->Application->value}\r\n") ;
   fwrite(STDOUT, "Loaded version: {$excel->Application->version}\r\n");
   fwrite(STDOUT, "----------------------------------------\r\n\r\n");

   // DATA RETRIEVAL
   $Workbook = $excel->Workbooks->Open($file) or die("ERROR: Unable to open " . $file . "!\r\n");
   $Worksheet = $Workbook->Worksheets($sheet);
   $Worksheet->Activate;
   $i = 0;
   foreach ($rows as $row)
   {
       $i++; $j = 0;
       foreach ($cols as $col)
       {
           $j++;
           $cell = $Worksheet->Range($col . $row);
           $cell->activate();
           $matrix[$i][$j] = $cell->value;
       }
   }

   // COM DESTROY
   $Workbook->Close();
   unset($Worksheet);
   unset($Workbook);
   $excel->Workbooks->Close();
   $excel->Quit();
   unset($excel);

   return $matrix;
}

Now, when the key function is defined we can fire an extraction and insertion process:

// define inputs
$xls_path = "C:\\Users\\Johny\\Documents\\Business\\excel_data.xls"; // input file
$xls_sheet = 1; // sheet #1 from file excel_data.xls
$xls_rows = range(2, 270, 1); // I want extract rows 2 - 270 from excel_data.xls with step 1 row
$xls_cols = array("A", "B", "C", "D", "E", "F"); // I want to extract columns A - F from excel_data.xls

// initiate MySQL connection
mysql_connect("server", "username", "password") or die("Unable to connect MySQL server!");
mysql_select_db("database") or die("Unable to select requested database!");

// retrieve data from excel
$data = getDataFromExcel($xls_path, $xls_sheet, $xls_rows, $xls_cols);

// insert retrieved data into database
foreach ($data as $line)
{
   $i = 0;
   foreach ($line as $col => $entry)
   {
       // create the SET string for INSERT query
       $i++;
       $string .= "`" . $col . "` = '" . $entry . "'";
       if ($i < count($line))
           $string .= ", ";
   }
   mysql_query("INSERT INTO `table` SET " . $string . "");
}

The stated above example is simplified to emphasize the core of process, not necessary details may lead to unclear interpretation. It is supposed that readers have at least basic knowledge of PHP and MySQL.

So, as you can see there’s pretty simple way how to import data from excel file directly to database using PHP. The PHP COM interface allows you to do the same with many other types of application (Word, PowerPoint, etc.).


0 Responses to "PHP and Excel"

Post a Comment