Posts Tagged ‘COM’

Excel & PHP (again…)

Monday, August 24th, 2009

Damn! It’s been a day and I just keep finding that although some of the COM functions for Excel-PHP are similar to those of ASP there are some that still are hard to get.
First of all to set the value of a cell we have 3 different ways:
Setting the Value property, setting the Formula and setting the FormulaR1C1.
The thing is that when trying to set the FormulaR1C1 with a value that uses de RC syntax the thing throws an unknown exception!!!
This is useless since I can’t figure what the hell is going on!
Also there’s little or none info about Excel COM & PHP on the web…. crap!
As soon as I find whats wrong Ill post it here….

Found something interesting.
A formular1c1 declared like “=SUM(R[-2]C:R[-1]C)” in common VB should look like “=SUM(F(-2)C:F(-1)C)”… weird but I think Im getting closer to a final solution.
Now suppose I have Excel and I have a spanish version where the formula SUM should be SUMAformular1c1 should work with SUM but doing:

$cell->FormulaR1C1 = “=SUM(F(-2)C:F(-1)C)”;

Does not work…. Like Poco I’ll keep on tryin’ 😉

Excel & PHP

Sunday, August 23rd, 2009

These past days I’ve been working with some integrations of some applications and PHP (Crystal reports, Excel, Word, etc).
A few days I found a question in a mailgroup asking somethin about Excel & PHP.
I’ve done something like this but didn’t publish an explanation of how I did it.
I did this through COM objects. I could recommend you to read the manual so that you may learn how to do it but unfotunately there’s few info about this.
So here it is, a sample code with the basic functions.
I learned a lot while searching through Google so do the same, document everything and more important: SHARE!

What you’ll need:
1. Com objects
2. PHP
3. An Excel document.
4. A web server with the proper dll’s or excel installed on it (this I didn’t dig too much so NE comments would be appreciated)

$path = “C:\\Documents and Settings\\Spiro\\My Documents\\sitios\\reto\\cotizaciones\\importados\\”; //set the path to the document

$encabezados = array(); //set a headers array

$exlObj = new COM(“Excel.Application”) or Die (“Did not connect”); //Here we stablish a COM object connection, you may want to set a try – catch to handle any error
$exlObj->Workbooks->Open(“$path$fnombre”); //We open the file set in $fnombre
$exlBook = $exlObj->ActiveWorkBook; //Open the workbook
$exlSheets = $exlBook->Sheets;
$cuantas_hojas = $exlSheets->Count; //how many sheets?
/*In my case there was a need to locate a named range so you may use these part of the script to locate the sheet where the range is.*/
try {
$donde_rango = $exlObj->Range(‘db’)->Worksheet->Name;
catch(Exception $e) {
$donde_rango = ”;
$log_errores[] = “El documento no contiene el rango ‘DB'”; //Info for error log
if ( $donde_rango != ” or true ) {
$sheet = $exlBook->Worksheets($donde_rango); //move to the sheet where the range is
$exlRange = $sheet->Range(‘DB’); //Get the range
$firstAddress = $exlRange->Address; //Get the range address – something like B3:X27 – dont mind about the letters 😉
//Get the upper left corner
$tokstart = strtok($firstAddress,”:”);
//Get the lower right corner
$tokend = strtok(“:”);
//This is why you dont bother about the range having letters
$range_start = $sheet->range($tokstart);
$colstart = $range_start->column;
$rowstart = $range_start->row;
$range_end = $sheet->range($tokend);
$colend = $range_end->column;
$rowend = $range_end->row;
//in case you wish to print the excel document as a nice html table
echo “<table>”;
echo “<thead>”;
echo “<tr>”
//suppose the firs line is where the headers are defined
$fila = $rowstart;
for ( $columna = $colstart; $columna <= $colend; $columna++ ) {
$valor = $sheet->Cells($fila, $columna);
echo “<th>$valor</th>”;
echo “</tr>”;
echo “</thead>”;
//start printing the content
echo “<tbody>”;
for ( $fila = $rowstart + 1; $fila <= $rowend; $fila++ ) {
echo “<tr>”;
for ( $columna = $colstart; $columna <= $colend; $columna++ ) {
$valor = $sheet->Cells($fila, $columna);
echo “<td>$valor</td>”; //print cell content
echo “</tr>”;
echo “</tbody>”;
echo “</table>”;
//close everything


So as you may see its not that difficult once you know where to start.
Take notice that this script only reads an Excel file, if you would like to write one then you should consider find info on Google.

The quickest term to find Google results is:

Hint: notice that you’ll be using COM objects so the code is very similar for ASP and PHP

Crystal Reports & PHP

Sunday, August 23rd, 2009

Just a few months from now the company I work for accepted to migrate a client-server application to a web environment.
It didn’t look so hard till we got to the reports issue. The client uses Crystal Reports to generate several reports needed for its processes. To be even more precise the client didnt even know he was using CR since the application just displays a screen where the client selects wether he wanted the report in PDF or XLS format.
The first approach was to migrate this reports to web and generate the through the pdf and xls libs that exist for PHP.
As time went by and there was less time for us to migrate the reports we became aware that if we were to migrate the reports from CR to pure PHP scripts it would take us a lot of time we didnt have anymore.
So we decided to use COM object in PHP to interact with the rpts files.
The first challenge was to set everything so that PHP may connect with CR.
The firs obstacle we found is that, for reasons unknown CR 2008 no longer supports COM. Shit! trying to buy a license for a Xi, 9 or 8 version was a real pain in the ass. We finally got our hands in a X distro and installed it on the server.
By the way everything is on WAMP plataform.
Now to the scripts. We tried to find example codes that show us how the hell to use PHP COM objects to interact with CR. Nothing!!!!! we found like 3 examples with 2 or 3 lines of code like this:

$ObjectFactory = new COM (“CrystalReports10.ObjectFactory.1”) or die (“Error on load”);
$crapp = $ObjectFactory->CreateObject(“CrystalDesignRunTime.Application”);

Even these 2 lines of code didnt run becase the second line should read:

$crapp = $ObjectFactory->CreateObject(“CrystalRunTime.Application”);

To be honest it was a real PITA, all doc was for ASP or VB.
So when we finally accomplished our goal we decided this info shouldnt be kept for just a few and here are som code lines that may help you in your coding.

Be aware that this code is for CR 10.

//Create the object, if theres an error catch it and display message
try {
$ObjectFactory = new COM (“CrystalReports10.ObjectFactory.1”) or die (“Error on load”);
$crapp = $ObjectFactory->CreateObject(“CrystalRunTime.Application”);
} catch (Exception $e) {
echo “Error message”;

$ruta = dirname( __FILE__ );
$archivo = $ruta . “/crrpts/CEAnalisisUnUSD.rpt”; //be aware that here goes your rpt file
//Open an rpt file if fail, display an error message
try {
$creport = $crapp->OpenReport($archivo, 1);
} catch (Exception $e) {
echo “File not found”;

//store formula object
$formulas = $creport->FormulaFields;

foreach ($formulas as $CRXFormulaField ) {
//display each formula name=value
/*if need to set a formulafield then u should type something like this:
if ($CRXFormulaField->name == “{@formulaname}”) {
$CRXFormulaField->text = “\”” . $_POST[‘ID_Cotizacion’] . “\””;
//Notice that when assigning text you must escape the double quotes.
echo $CRXFormulaField->name . ” = ” . $CRXFormulaField->text . ”

//Script to export to a pdf file

//finally close resources
$creport = null;
$crapp = null;

That was about it. Some important things to take in consideration:
1. Get your hands on the infamous “Crystal Reports Developers Help.chm” to have pretty guidelines as to where to start.
2. Get yourself a copy of CR10 and run it on the server.
3. Make the proper modifications to the rpt files in case you change your database source or other significant changes have occur.