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. #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}
" ;
print "Loaded version: {$ex->Application->version}
"; #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}
"; #Print the value of the cell:10000
$cell->value = 15000; #Change it to 15000
print "New value = {$cell->value}
";#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.
";
#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
";
#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: $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: $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: $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 ( 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 ets 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:
As for the Word example above, study the code with the help from the Visual Basic Editor ObjectBrowser for Excel. #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}
" ;
print "Loaded version: {$ex->Application->version}
"; #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}
"; #Print the value of the cell:10000
$cell->value = 15000; #Change it to 15000
print "New value = {$cell->value}
";#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.
";
#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
";
#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: $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: $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: $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 ( 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 ets 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:
Alain M. Samoun
Introduction
The built-in COM functionality of PHP4 is quite attractive for some of us programming in the win32 environment. So far, there is not much documentation on the subject. This short article will explain how to use COM in real PHP4 programming with three examples using MS office 2000 Word and Excel programs and the Adobe Distiller program. The COM technology has been developed by Microsoft for several years, under different names. As far as this article is concerned, the words OLE, OLE Automation, ActiveX and COM are all the same: They designate an encapsulated piece of code (the Object) that performs some functions for a windows application. PHP4 COM connects to the object (Instantiate the object) and uses its methods and properties.
If you want to reproduce the following examples, here is my configuration:
Windows 98 - MS Office 2000
Apache 1.3.9 Windows
PHP4.02 Dev (08-20-00) Running as CGI
COM tags in PHP4
Lets start with the specific information to use the COM functions with PHP4. To instantiate a component, one needs the "new" operator and the "OLE programmatic identifiers" of the object: $instance = new COM("$identifier");?>
Since COM is a reserved class name in PHP4, it passes the object's identifier to the constructor. Now that we have instantiated the component, we can easily reach its methods and properties, using the OOP class. For example: $instance->[Object]->[method1]->[method2]->..->[property];?>
It's that simple!
There are two tag functions for PHP4 COM that are used when the OOP construct doesn't work. (In the case of PHP syntax problems, with the names and values of properties with invalid characters, like dot or parenthesis): bool com_set(class com_object, string property name, string property_value);mixed com_get(class com_object, string property_name);?>
Finally, PHP4 also supports DCOM to create an instance of an object on a remote computer: $Instance = new COM(string "Component name", string "remote_server_address");?>
Note: there is a DCOM directive to set in the PHP configuration. PHP developers may add DCOM support for Unix in the future. That's all, there are no other functions to remember!
Identifiers, methods and properties.
identifiers are strings like:
For MS Word: "Word.Application" or "Word.Application.9"
MS Excel: "Excel.Application" or "Excel.Sheet"
ADOBE Acrobat: "Exch.application" or "PdfDistiller.PdfDistiller"
As the last identifier name indicates, it is not always easy to know the right name for the object. If you do not have access to a VBA doc, you can look at the windows registry (Start - Run regedit) and look in the HKEY_CLASSES_ROOT folder: Scan down until the end of the extensions list, you will then reach the Application names. The COM Identifiers available in your machine, are the folders with the CLSID subfolders.
The application program should document its COM methods and properties. In the case of Office 2000, start the application, then open the visual basic editor with the short cut key and select the Objects Editor . Enter a name of method or properties for the application's library. Then, select a class or member and right click on the member name in the next window below. You will get the description for the class or member by selecting help. You can also consult MSDN. An example for Excel is: http://msdn.microsoft.com/library/officedev/off2000/xltocobjectmodelapplication.htm
Using PHP4 COM functions with MS Word
Now, we have all we need to start with the first code example: #*********************************************************
# This example, slightly modified from the Zend site,
# will open an instance of word with a new
# document with the name "Useless test.doc" and the line:
# "This is a test2..." typed inside.
#*********************************************************#Instantiate the Word component.$word = new COM("word.application") or die("Unable to instantiate Word"); #Get and print its versionprint "Loaded Word, version {$word->Version}
"; #Another way to get the version using com_get$testversion = com_get($word->application,version);print "Version using Com_get(): $testversion
";#Make it visible in a window$word->Visible = 1; #Open a new document $word->Documents->Add(); #Write something$word->Selection->TypeText("This is a test..."); #Now save the document$word->Documents[1]->SaveAs("Useless test.doc"); #Comment next line if you want to see the word document,
#then close word manually$word->Quit();
#Comment if you want to see the word document, then close ?>
If you study this example for a few minutes using the OLE documentation that comes with Word, you will learn practically all you need to write your own program.
Apache 1.3.9 Windows
PHP4.02 Dev (08-20-00) Running as CGI
PHP4中的COM标记 现在让我们开始吧，用PHP4的COM来实例化一个组件，需要 new 操作符和对象的 "OLE 程序标识"：
$instance = new COM("$identifier"); ?> 因为COM是一个PHP4的保留字，它传送这个对象的标识给一个构造函数，现在得到了这个组件的一个实例，根据OOP类的性质，我们可以很容易地访问它的方法与属性。 例如：
$instance->[Object]->[method1]->[method2]->..->[property]; ?> 就是这么简单！ OOP的结构在PHP下不能工作，（由于PHP语法的问题，属性的名字.值是非法字符，如点和圆括号等），所以PHP4提供了两个相应的函数：
bool com_set(class com_object, string property name, string property_value); mixed com_get(class com_object, string property_name); ?> 最后，PHP4也支持DCOM技术，可以在远程计算机创建一个对象实例。
$Instance = new COM(string "Component name", string "remote_server_address"); ?> 注意：这是用DCOM指令来设置PHP。在将来，PHP开发者提供Unix下对DCOM的支持。 标识、方法和属性 标识是一个如下的字串： MS Word: "Word.Application" or "Word.Application.9"
MS Excel: "Excel.Application" or "Excel.Sheet"
ADOBE Acrobat: "Exch.application" or "PdfDistiller.PdfDistiller" 对于最后一个标识，我要指明的是，获得正确的对象标识名不是一件容易的事。如果你不能访问VBA文档，你可以查找一下windows的注册表，在 HKEY_CLASSES_ROOT 中寻找一下，你就可以得到一些应用程序的名字。在你的机器上有效的对象标识放在 CLSID 子文件夹下。 应用程序一般会提供文档说明它的COM方法和属性。在office2000中，你可以运行程序，打开VBA编辑器 ，选择对象编辑器。输入应用程序库中的一个方法名或属性名，然后，在下面的窗口中用鼠标右键选择一个类或成员名称，点帮助，你就会得到关于这个类或成员的描述。你也可以参考 MSDN。一个 Excel 的例子如下： http://msdn.microsoft.com/library/officedev/off2000/xltocobjectmodelapplication.htm
用COM函数操作 MS Word 现在，我们开始第一个例子吧：
#*********************************************************
# 本例来自Zend站点，略有改动
# 打开一个word实例，并新建一个文档Useless test.doc
# 输入一行文字 "This is a test2..."
#********************************************************* #实例化一个对象 $word = new COM("word.application") or die("Unable to instantiate Word"); #取得并显示版本 print "Loaded Word, version {$word->Version}
"; #另一种方法去取得版本 $testversion = com_get($word->application,version); print "Version using Com_get(): $testversion
"; #使其可见 $word->Visible = 1; #创建新文件 $word->Documents->Add(); #写字符 $word->Selection->TypeText("This is a test..."); #保存 $word->Documents[1]->SaveAs("Useless test.doc"); #关闭 $word->Quit(); ?>