This tutorial is aimed for those who are searching for live data feed connection to PHP like you have from DDE (Dynamic Data Exchange) supporting applications. This tutorial will show you not PHP – DDE connection, but the approach PHP can use to produce such a similar result.

DDE is about inter-process communication within Windows where the DDE client can get notified every time the data at DDE server is changed without the application do any disk operation (i.e. file saving). The problem of using read file extension for PHP is we cannot get the updated data before the file is altered on the disk. The main idea of approaching the solution is: to make a one-time data request to the running process, get a reply, close the connection, and then repeat the process at any particular time we need to see the update. And by using the other, PHP supported, inter-process communication; we could do just that, even beyond!

In PHP, we use COM object to refer to any windows processes. It can be Ms Word, Powerpoint, Lotus Notes or Excel as we will discuss here. We will use this php function:

com_get_active_object("Excel.Application")

But, before we continue, to give you the greater look, let us review the philosophy of this COM object first (for those who prefer to skip, I do not mind). The typical usage of COM object in PHP is to create a new object to refer to a new process, as it is listed below:

<?php
$file = "C:\\test.xls";

// Create new COM object – excel.application
$excel = new COM("Excel.application") or die ("ERROR: Unable to instantiate COM!\n");
echo "Application name: {$excel->Application->value}<br>" ;
echo "Loaded version: {$excel->Application->version}<br>";

// Retrieve data
$Workbook = $excel->Workbooks->Open($file) or die("ERROR: Unable to open " . $file . "!\r\n");
$Worksheet = $Workbook->Worksheets("Sheet1");
$Worksheet->Activate;
$cell = $Worksheet->Range("A1");
$cell->activate();
$value = $cell->value;
echo "<br>>>".$value."<<";

// Close and quit
$Workbook->Close();
unset($Worksheet);
unset($Workbook);
$excel->Workbooks->Close();
$excel->Quit();
unset($excel); 
?> 

Notice the command at line 5, which will create a new process running on Windows that will be controlled by $excel object.  What does this mean? This means:

  1. Just like any objects in our programs, object $excel has methods and properties we can use to manipulate the real process. Line 14 is an example of method usage, and line 15 is an example getting the object’s property.
  2. BUT, the script above is just like any “read file” operation which will not react to any unsaved changes on the real excel process. That is why I marked with bold font the words “new process” above.

That is where the function “com_get_active_object()”  will get in our way. We want those unsaved, rapid changes in data be reported as they are in “real-time”. That is why we are not going to “create new process”, but rather “point to an already running process”. With a little modification to the above script, we get:

<?php
// Create COM object which points to a running process
$excel = com_get_active_object("Excel.application") or die ("ERROR: Unable to instantaniate COM!\n");
echo "Application name: {$excel->Application->value}<br>" ;
echo "Loaded version: {$excel->Application->version}<br>";

// Retrieve data
$Worksheet = $excel->Worksheets("Sheet1");
$Worksheet->Activate;
$cell = $Worksheet->Range("A1");
$cell->activate();
$value = $cell->value;
echo "<br>>>".$value."<<";

// Close connection
unset($Worksheet);
unset($excel); 
?>

Make sure your excel application is running, if not, you will get a fatal error message as the script dies. If more than one workbook is opened, the script will connect to just any one of them, usually the one which is initiated earlier.

Notice that we have replace the “new COM()” function with “com_get_active_object()”. We do not need “open()” , “close()”, and “quit()” function as well because we want the excel application (which is supplying us the live feed) to keep running.

Congratulations! You have the PHP script that update its output according to the value that is given in the excel cell “A1” anytime you refresh the web page.

For your convenience, rather than making the script die each time the exception is born, you might want to use try-catch nest instead. With try-catch nest you can make the script to do any alternative procedures when the exception occurred.

<?php
try{ 
    // Create COM object which points to a running process 
    $excel = com_get_active_object("Excel.application"); 
    if (!$excel) throw new Exception("ERROR: Unable to instantiate COM!"); 
    … 
    … 
} 
catch (Exception $e){ 
    // any alternative procedures 
} 
?>

You can also make auto refresh function in Ajax to be used together with our script above. Here is the example of 2 seconds auto refresh, suppose the above script is saved as “excelcom.php”:

<html>
  <head>
	<title>excelCOM</title>
	<meta name="author" content="Marcel Wijaya">
	<link href="style.css" rel="stylesheet" type="text/css">
	<script type="text/javascript">
		function Ajax(page){
			var xmlHttp;
			try{	
				xmlHttp=new XMLHttpRequest(); // Firefox, Opera 8.0+, Safari
			}
			catch (e){
				try{
					xmlHttp=new ActiveXObject("Msxml2.XMLHTTP"); // Internet Explorer
				}
				catch (e){
					try{
						xmlHttp=new ActiveXObject("Microsoft.XMLHTTP");
					}
					catch (e){
						alert("No AJAX!?");
						return false;
					}
				}
			}
			xmlHttp.open("GET",page,true);
			xmlHttp.send(null);
			xmlHttp.onreadystatechange=function(){
				if(xmlHttp.readyState==4){
					document.getElementById('ReloadThis').innerHTML=xmlHttp.responseText;
					setTimeout('Ajax(page)',2000);
				}
			}
		}
		window.onload=function(){
			page = "excelcom.php";
			Ajax(page);
		}
	</script>
  </head>
	<body >
		<h1 align="center">excelCOM</h1>
		<div id="ReloadThis"></div>
	</body>
</html>

N.B.:

  1. As it has been warned in PHP page at php.net, this function is not supposedly be used on a Web server with more than 1 client! Most COM/OLE applications are not designed to handle more than one client concurrently, even (or especially!) Microsoft Office[i]. Please read http://php.net/manual/en/function.com-get-active-object.php and http://support.microsoft.com/kb/257757 for more details.

External links:

  1. http://msdn.microsoft.com/en-us/library/windows/desktop/aa365574(v=vs.85).aspx
  2. http://msdn.microsoft.com/en-us/library/ms648774.aspx