Programming PHPProgramming PHPSearch this book

15.3. Interfacing with COM

COM allows you to control other Windows applications. You can send file data to Excel, have it draw a graph, and export the graph as a GIF image. You could also use Word to format the information you receive from a form and then print an invoice as a record. After a brief introduction to COM terminology, this section shows you how to interact with both Word and Excel.

15.3.1. Background

COM is a Remote Procedure Call (RPC) mechanism with a few object-oriented features. It provides a way for the calling program (the controller) to talk to another program (the COM server, or object), regardless of where it resides. If the underlying code is local to the same machine, the technology is COM; if it's remote, it's Distributed COM (DCOM). If the underlying code is a DLL, and the code is loaded into the same process space, the COM server is referred to as an in-process, or inproc , server. If the code is a complete application that runs in its own process space, it is known as an out-of-process server, or local server application.

Object Linking and Embedding (OLE) is the overall marketing term for Microsoft's early technology that allowed one object to embed another object. For instance, you could embed an Excel spreadsheet in a Word document. Developed during the days of Windows 3.1, OLE 1.0 was limited because it used a technology known as Dynamic Data Exchange (DDE) to communicate between programs. DDE wasn't very powerful, and if you wanted to edit an Excel spreadsheet embedded in a Word file, Excel had to be opened and run.

OLE 2.0 replaced DDE with COM as the underlying communication method. Using OLE 2.0, you can now paste an Excel spreadsheet right into a Word document and edit the Excel data inline. Using OLE 2.0, the controller can pass complex messages to the COM server. For our examples, the controller will be our PHP script, and the COM server will be one of the typical MS Office applications. In the following sections, we will provide some tools for approaching this type of integration.

To whet your appetite and show you how powerful COM can be, here's how you start Word and add "Hello, World" to the initially empty document:

<?php
 $wp= new COM("Word.Application") or die ("Cannot open Word");
 $wp->visible=1;
 $wp->Documents->Add( );
  
 $wp->Selection->Typetext("Hello, world.");
?>

15.3.2. PHP Functions

PHP provides an interface into COM through a small set of function calls. Most of these are low-level functions that require detailed knowledge of COM that is beyond the scope of this introduction. Two classes that we will make heavy use of, however, are COM and VARIANT.

An object of the COM class represents a connection to a COM server:

$word = new COM("Word.Application") or die("Cannot start MS Word");

An object of the VARIANT type represents COM data values. For example:

$vrows = new VARIANT(0, VT_I4|VT_BYREF);

This creates a reference (VT_BYREF) to a 32-bit integer (VT_I4) with an initial value of 0. PHP can pass strings and numbers to COM servers automatically, but VARIANT COM types are required whenever you need to pass arguments by reference.

For most OLE automation, the most difficult task is that of converting a VB method call to something similar in PHP. For instance, this is VBScript to insert text into a Word document:

Selection.TypeText Text:="This is a test"

The same line in PHP is:

$word->Selection->Typetext("This is a test");

It is important to note two quirks in PHP's present COM support. First, you cannot pass parameters in the middle of an object method. So instead of writing a method as:

$a->b(p1)->c(p2) 

you must break up the method as:

$tmp=$a->b(p1);$tmp->c(p2);

Second, PHP is unaware of default parameters from Microsoft OLE applications such as Word. This simply means that you must explicitly pass all values to the underlying COM object.

15.3.3. Determining the API

To determine object hierarchy and parameters for a product such as Word, you might visit the Microsoft developer's site at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbawd10/html/wotocObjectModelApplication.asp and search for the specification for the Word object that interests you. Another alternative is to use both Microsoft's online VB scripting help and Word's supported macro language. Using these together will allow you to understand the order of parameters, as well as the desired values for a given task.

For instance, assuming we want to understand how a simple find and replace works, we can do the following:

  1. Open Word and create a new document containing some sample text. For example:

    "This is a test, 123" 
  2. Record a macro to find the text "test" and replace it with the text "rest". Do this by selecting Tools Figure Macro Figure Record New Macro from Word's menu bar. Once recording, use search and replace to create the macro. We will use this macro, shown in Figure 15-2, to determine the values of parameters that we will pass in our PHP COM method.

    Figure 15-2

    Figure 15-2. Using Word's macro language to expose OLE COM objects and parameters

  3. Use Word's object browser to determine the calling syntax for all parameters in this example. Press Alt-F11 to access Word's VBScript online help, then type in the assumed syntax for the object method (in our case, Selection.Find.Execute( )). Then right-click in the parameter area to bring up the list of all parameters for the method, as shown in Figure 15-3.

    Figure 15-3

    Figure 15-3. Gleaning syntax from Word's online help

  4. Values not in bold are optional in Word macros. PHP requires all values to be passed explicitly, however.

  5. Finally, convert the VBScript to corresponding PHP COM function calls, as shown here:

    <?php 
     $word=new COM("Word.Application") or die("Cannot start MS Word"); 
     print "Loaded Word version ($word->Version)\n"; 
     $word->visible = 1 ; 
     $word->Documents->Add( ); 
     $word->Selection->Typetext("This is a test"); 
     $word->Selection->Typetext(" 123"); 
     $word->Selection->Find->ClearFormatting( ); 
     $word->Selection->Find->Execute("test", False, False, False, False, False, 
     True, wdFindContinue, False, "rest", wdReplaceAll, False,
     False, False, False); 
    ?> 

In this code, we open up Word as an application. We then create a new document and set visible to 1 to make it easier for us to debug. ClearFormatting ensures that unwanted formats aren't included as criteria in a find or replace operation. Selection->Find->Execute performs our search and replacement, replacing all values of "test" with "rest".

15.3.4. Completing a Word Document

Because of the many versions of Word, and PHP's evolving COM support, the previous example isn't guaranteed to work in your environment. One way to work around this is to move as much of the automation as possible into the OLE application.

So let's assume we have the invoice shown in Figure 15-4 that we wish to fill in with data from PHP.

Figure 15-4

Figure 15-4. A sample invoice created with Microsoft Word

The basic idea is that we want to traverse the document and fill in the appropriate data. To accomplish this, we will use Word's bookmarks to move to key locations in the document.

To place a bookmark, simply open an existing document, place the cursor in the desired location, and select Insert Figure Bookmark. In the pop-up window, type in a name for the bookmark and press the Add button. Create bookmarks on the customer address line and in the delivery, item, and total fields. The names of those bookmarks should be customer, delivery, item, and total, respectively.

To move to a bookmark directly in PHP, we can use:

$word->Selection->Goto(what, which, count, name);

Using Word's macro language to determine the desired parameters for this method, we find that what requires the value wdGoToBookmark and that name refers to the name that we gave to our bookmark. With a little digging through Microsoft documentation, we also find that count indicates which instance of the bookmark in the document and that which is a navigational parameter, of which our desired value is wdGoToAbsolute.

Rather than do the positioning from PHP, though, we can create a macro to perform the find directly:

Sub BkmkCustomer( )
    Selection.GoTo What:=wdGoToBookmark, Name:="customer"
End Sub

This macro, which we've named BkmkCustomer, places the cursor at the bookmark named customer. Using this macro directly avoids any potential errors introduced in passing multiple parameters from PHP to Word. The PHP COM method for this is:

$word->Application->Run("BkmkCustomer");

We can repeat this process for each named bookmark in the invoice.

To reduce the number of bookmarks required, we can create a Word macro for moving to the next cell in a table:

Sub NextCell( )
    Selection.MoveRight Unit:=wdCell
End Sub

Now we can complete the invoice with data we get from an HTML form. We also want to print the form, though.

If we only wanted to save an electronic copy, it would be as simple as:

$word->ActiveDocument->SaveAs("c:/path/to/invoices/myinvoice.doc");

This has the side effect of setting the ActiveDocument->Saved flag to True, which lets us close the application without being prompted to save the modified invoice.

If we want to print the document, there are three steps: print, mark the document as saved so we can quit without a dialog box, then wait until the printing has finished. Failure to wait means the user will see a "Closing this application will cancel printing" warning. Here's the code for doing this:

$word->Application->Run("invoiceprint");
  
$word->Application->ActiveDocument->Saved=True;
while($word->Application->BackgroundPrintingStatus>0){sleep (1);}

In this code, we've created a macro, InvoicePrint, with our desired printer settings. Once we call the macro, we loop until the value of BackgroundPrintingStatus is set to 0.

Example 15-2 shows the complete PHP program to complete and print the invoice using Word.

Example 15-2. Completing and printing a Word invoice from PHP

<?php
 // the skeletal Word invoice with macros
 $invoice="C:/temp/invoice.doc";
  
 // fake form parameters
 $customerinfo="Wyle Coyote
 123 ABC Ave.
 LooneyTune, USA 99999";
 $deliverynum="00001";
 $ordernum="12345";
 $custnum="WB-beep";
  
 $shipdate="11 Sep 2001";
 $orderdate="11 Sep 2001";
 $shipvia="UPS Ground";
  
 $item[1]="SK-000-05";
 $desc[1]="Acme Pocket Rocket";
 $quantity[1]="2"; 
 $cost[1]="$5.00";
 $subtot[1]="$10.00";
 $total="$10.00";
  
 // start Word 
 $word=new COM("Word.Application") or die("Cannot start MS Word");
 print "Loaded Word version ($word->Version)\n";
 $word->visible = 1 ;
 $word->Documents->Open($invoice);
  
 // fill in fields
 $word->Application->Run("BkmkCustomer");
 $word->Selection->TypeText($customerinfo);
  
 $word->Application->Run("BkmkDelivery");
 $word->Selection->TypeText($deliverynum);
 $word->Application->Run("NextCell");
 $word->Selection->TypeText($shipdate);
 $word->Application->Run("NextCell");
 $word->Selection->TypeText($shipvia);
 $word->Application->Run("NextCell");
 $word->Selection->TypeText($orderdate);
 $word->Application->Run("NextCell");
 $word->Selection->TypeText($custnum);
 $word->Application->Run("NextCell");
 $word->Selection->TypeText($ordernum);
 $word->Application->Run("NextCell");
  
 $word->Application->Run("BkmkItem");
 $word->Selection->TypeText($item[1]);
 $word->Application->Run("NextCell");
 $word->Selection->TypeText($desc[1]);
 $word->Application->Run("NextCell");
 $word->Selection->TypeText($quantity[1]);
 $word->Application->Run("NextCell");
 $word->Selection->TypeText($cost[1]);
 $word->Application->Run("NextCell");
 $word->Selection->TypeText($subtot[1]);
  
 $word->Application->Run("BkmkTotal");
 $word->Selection->TypeText($total);
  
 // print it
 $word->Application->Run("invoiceprint");
  
 // wait to quit
 $word->Application->ActiveDocument->Saved=True;
 while($word->Application->BackgroundPrintingStatus>0){sleep (1);}
  
 // close the application and release the COM object
 $word->Quit( );
 $word->Release( );
 $word = null;
?>

15.3.5. Reading and Writing Excel Files

Controlling Excel is similar to controlling Word—research the APIs and use a combination of macros and COM. The hierarchy of objects is: the Application can have multiple Workbooks, each of which can have multiple Sheets. A Sheet is what you probably think of as a spreadsheet—a grid of cells.

Example 15-3 creates a new Excel spreadsheet and a new worksheet within it, stores "Hello, world" in cell A1, then saves the result to c:\temp\demo.xls.

Example 15-3. Writing to Excel from PHP

<?php
 $ex = new COM("Excel.sheet") or Die ("Did not connect");
 $ex->Application->Visible = 1;
 $wkb = $ex->Application->Workbooks->Add( );
 $sheet = 1;
  
 excel_write_cell($wkb, $sheet, "A1", "Hello, World");
  
 // write a value to a particular cell
 function excel_write_cell($wkb,$sheet,$c,$v) {
   $sheets = $wkb->Worksheets($sheet); 
   $sheets->activate;                  
   $selcell = $sheets->Range($c);
   $selcell->activate;
   $selcell->value = $v;
 }
?>

You can read the value in a cell with this function:

function excel_read_cell($wkb,$sheet,$c) {
  $sheets = $wkb->Worksheets($sheet); 
  $sheets->activate;                  
  $selcell = $sheets->Range($c);
  $selcell->activate;
  return $selcell->value;
}


Library Navigation Links

Copyright © 2003 O'Reilly & Associates. All rights reserved.