Using the PHP4 COM functions with MS Excel
As for the Word example above, study the code with the help from the Visual Basic Editor ObjectBrowser for Excel.
<?php
#Set the workbook to use and its sheet. In this example we use a spreadsheet that
#comes with the Excel installation called: SOLVSAMP.XLS
$workbook = "C:\Program Files\Microsoft office\Office\Samples\SOLVSAMP.XLS";
$sheet = "Quick Tour";
#Instantiate the spreadsheet component.
$ex = new COM("Excel.sheet") or Die ("Did not connect");
#Get the application name and version
print "Application name:{$ex->Application->value}<BR>" ;
print "Loaded version: {$ex->Application->version}<BR>";
#Open the workbook that we want to use.
$wkb = $ex->application->Workbooks->Open($workbook) or Die ("Did not open");
#Create a copy of the workbook, so the original workbook will be preserved.
$ex->Application->ActiveWorkbook->SaveAs("Ourtest");
#$ex->Application->Visible = 1; #Uncomment to make Excel visible.
# Read and write to a cell in the new sheet
# We want to read the cell E11 (Advertising in the 4th. Quarter)
$sheets = $wkb->Worksheets($sheet); #Select the sheet
$sheets->activate; #Activate it
$cell = $sheets->Cells(11,5) ; #Select the cell (Row Column number)
$cell->activate; #Activate the cell
print "Old Value = {$cell->value} <BR>"; #Print the value of the cell:10000
$cell->value = 15000; #Change it to 15000
print "New value = {$cell->value}<BR> ";#Print the new value=15000
#Eventually, recalculate the sheet with the new value.
$sheets->Calculate; #Necessary only if calc. option is manual
#And see the effect on total cost(Cell E13)
$cell = $sheets->Cells(13,5) ; #Select the cell (Row Column number)
$number = Number_format($cell->value);
print "New Total cost =\$$number - was \$47,732 before.<BR>";
#Should print $57,809 because the advertising affects the Corporate overhead in the
# cell formula.
#Example of use of the built-in functions in Excel:
#Function: PMT(percent/12 months,Number of payments,Loan amount)
$pay = $ex->application->pmt(0.08/12,10,10000);
$pay = sprintf("%.2f",$pay);
print "Monthly payment for $10,000 loan @8% interest /10 months: \$ $pay<BR>";
#Should print monthly payment = $ -1,037.03
#Optionally, save the modified workbook
$ex->Application->ActiveWorkbook->SaveAs("Ourtest");
#Close all workbooks without questioning
$ex->application->ActiveWorkbook->Close("False");
unset ($ex);
?>
This example should get you going with the Excel COM and PHP. Of course there are many more objects to use. Writing an OOP wrapper for the principal functions will make access to the excel objects even easier.
Using PHP COM with Adobe Distiller
This last example is for a non-MS program: If your program has produced a PostScript document, it may be interesting to transform it (Distill it) to a PDF document. Adobe has a program called Distiller with a windows version that can be instantiated, with the following code:
<?php
$pdf = new COM("pdfdistiller.pdfdistiller.1");
?>
Note that the OLE Identifier name is not obvious, especially when the distiller documentation (Adobe's Technical Note #5158) refers to it as "pdfdistiller."
The principal method to distill a document is:
<?php
$pdf->FileToPdf ($psfile, strOutputPDF '', strJobOptions "");
?>
Where $psfile is the name of the PostScript file, strOutputPDF is the name for the output PDF file. StrJobOptions is the name of the parameters file for Distiller. The two last parameters of the method can be left blank to use the same name, the PS file for the PDF file and to use the default Job options file. For example:
<?php
$pdf->FileToPdf ($psfile, "", "");
#Where $psfile could be Myfile.ps and the result file: Myfile.pdf
?>
There are more methods and properties that can be used with Distiller. If you are interested, look at the Adobe's technical note.
Caveats/Possible problems
If there are some errors in your code, you may instantiate the object and your program may not close before it times out. Worst of all, the application may retentively be instantiated. As a result, several copies may lay around in your programs list and interfere after you have corrected the problem. The solution: After fixing the bug, clean up (<CTRL+ALT+Delete> and End Task) all the instances in the program list before you restart. For this same reason, always close the application at the end of your code and unlink the instance.
You may experience some oddities with com_get and com_set. For example: $Version = Com_get($instance->Application,"Version"); Works with Word, but produces an error with Excel.
Some Objects won't be instantiated by PHP4, it appears that these objects need a custom interface that PHP4-COM doesn't support.
Why use it?
Hopefully, these three examples have shown you the ropes. PHP COM allows the connection to many Windows programs inside a PHP script. The code is simpler than ASP's and can be integrated with the rest of PHP's powerful database functions. Microsoft markets the COM technology everywhere and under different names and architectures, like COM+(Combine COM with Microsoft Transaction Server MTS), ADO, OLE DB, OWC, Windows DNA, etc. PHP and Apache, working together, are now offering an open source solution to this confusion.
http://phpclasses.upperdesign.com/browse.html?package=86
--Alain
PS: See the EXCEL class using the COM interface at:
