the journey


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

With the vision so big I have in mind, I need to review my priorities and missions in life. I am aware that I am neither my company, nor my environment. So, wherever I am, the burden of life is on my shoulders always. There is no place better if I could not find happiness in the work I do, the relationship I have, and the responsible I hold. I am the only person responsible for my success.

There is no age too old to learn, however there will be regret when we leave this life without legacies. The way I define success determines whether I am achieving it or not. The way I define failure determines how fast my pace toward my goal. Continuously take the steps closer to your dream. And, do take them boldly.

 

Marcel Wijaya 20110804

 

Of the people seen from the way they do the effort, there are 4 types of them:
The Quitters: They who had the opportunity to act, but too lazy to start.
The Campers: They who act, but satisfy too soon on little achievement, and stop trying
The Climbers: They who focused constantly to achieve to the top and consistent in their efforts.
The Extraordinary: The climbers who always challenge themselves for higher peaks after reaching the top.

Some says that life is continuous gifts, some says that life is suffering, some says that life is a show theater, but most of them agree that life is unrevealed mystery.

Happy and sad drama around us that often shown about birth and death of ordinary people make the idea of the mystery of life even bolder. We saw that human beings just bow to a common rule of birth,growing old, sick and finally pass away. Life needs a requirements of the suffering of birth, life gives an un-reject-able gifts of being old and sick suffer, then the final destination is the suffering of death. Human came to life empty handed, left this life journey also empty handed. If coming and going in the same emptiness, what does our life mean, then? More evenly, what does life for?

Having seen Gus Dur passed away, I no longer see that life is a mystery. Life rather is a distinct path you will leave for your next generation. Life will be more meaningful when your juniors can take more from what you left. Mean your life second by second, and you will see that the life is more than just meaningful. Life will be as clear as crystal, as bright as sunlight, as useful as opened book. And on that very day, you will need not to ask what life means, because the people gathering will tell you and the world what your life mean to them.

The mystery is what lies after life, to which you go when the time has come.

Marcel Wijaya

20100101

This writing is dedicated for the grief of West Sumatra September 30, 2009.

For more than 1,000 lives were not saved, thousands more injured, and millions lose their homes, Indonesia is mourning again.

Disaster seems not giving any chance for you to run away, be tough my friends, be strong my brothers.

With HIS holiness’ permission only will the things on this world happen,

When you see no blessings, please remember that HE is too good to be unkind.

When you find only hurt and tears, behold that images about HIM you see clearer in these times…

Dear Lord, Father, The Creator of all things,

In this little heart, in this small knowledge, in this limited mind, in this powerless body.

I gather all my attention right here, right now to bow down and kneel toward your Greatness.

Giving thanks completely as YOU grant this opportunity to me,

Glorifying YOUR Holiness even though YOU do not need it,

Asking forgiveness for my faults and errors as this may be my last chance to do so,

Praying for the souls YOU pulled back, please receive them in YOUR kingdom,

Hoping the following days be better and brighter for us that remain,

I want to pray also for the natural disaster victims in Samoa and Philippine, war victims in Middle East, hunger victims and all beings that are in suffer. Please YOUR kindness to take away their suffering.

Please grant me wisdom to always work in line with YOUR will,

Please use me as YOUR tools to glorify YOUR name,

Please deliver me away from the evil and bad purpose beings.

Amen.