PHP COM Excel Obj

[krigare]

Hope this is the right place to post :-)

I am trying to use an Excel COM object via PHP. I am able to read/write
data to cells, use AutoFilter, and AutoFit on columns. I can even set
the cell background color.

However, I am having problems with setting borders on cells and making a
column have centered text. I am able to do this with PERL. So, I am
looking for a an expert to tell me how to do it in PHP (I am a noob with
PHP).

Here is a snippet of PERL code that works:

$worksheet->Columns("c")->{ColumnWidth}=56;
my @edges = qw (xlEdgeBottom xlEdgeLeft xlEdgeRight xlEdgeTop
xlInsideHorizontal xlInsideVertical);

$range = "b1:c56";
foreach my $edge (@edges)
{
with (my $Borders =
$worksheet->Range($range)->Borders(eval($edge)), LineStyle
=>xlContinuous, Weight => xlThin, ColorIndex => 1);
}
My problem is the Borders. I have tried numerous combinations without
luck. Such as:
$workseet->Range($range)->Borders()->LineStyle = "xlContinuous";
$workseet->Range($range)->Borders("xlEdgeTop)->LineStyle = "xlContinuous";
$workseet->Range($range)->Borders()->LineStyle->Value = "xlContinuous";
$workseet->Range($range)->Borders("xlEdgeTop)->LineStyle->Value =
"xlContinuous";
....

Nothing seems to work. I am sure it is the PERL array and how I am
trying to lay the syntax out in PHP, but I am at a loss.

Thanks for your help!!

[Steve]

I am trying to use an Excel COM object via PHP. I am able to read/write
data to cells, use AutoFilter, and AutoFit on columns. I can even set
the cell background color.

However, I am having problems with setting borders on cells and making a
column have centered text. I am able to do this with PERL. So, I am
looking for a an expert to tell me how to do it in PHP (I am a noob with
PHP). My problem is the Borders. I have tried numerous combinations without
luck. Such as: $workseet->Range($range)->Borders()->LineStyle = "xlContinuous";
$workseet->Range($range)->Borders("xlEdgeTop)->LineStyle = "xlContinuous";
$workseet->Range($range)->Borders()->LineStyle->Value = "xlContinuous";
$workseet->Range($range)->Borders("xlEdgeTop)->LineStyle->Value =
"xlContinuous"; Nothing seems to work. I am sure it is the PERL array and how I am
trying to lay the syntax out in PHP, but I am at a loss.

This is probably more of an MSExcel question, but I suppose it
overlaps...

xlEdgeTop, xlContinuous are VBA constants, and you used them correctly
in your PERL sample. You are using them incorrectly in your PHP sample.
Correcting other typos (so I assume this is not a cut-and-paste from
the actual code - tut tut!)

$worksheet->Range($range)->Borders()->LineStyle = xlContinuous;

This assumes you have previously DEFINEd xlEdgeTop and xlContinuous
somewhere, such as:

' XlBordersIndex enumerated constants
DEFINE( "xlEdgeTop", 8 );
' XlLineStyle enumerated constants
DEFINE( "xlContinuous", 1 );

---
Steve

[krigare]

>

This is probably more of an MSExcel question, but I suppose it
overlaps...

xlEdgeTop, xlContinuous are VBA constants, and you used them correctly
in your PERL sample. You are using them incorrectly in your PHP sample.
Correcting other typos (so I assume this is not a cut-and-paste from
the actual code - tut tut!)

$worksheet->Range($range)->Borders()->LineStyle = xlContinuous;

This assumes you have previously DEFINEd xlEdgeTop and xlContinuous
somewhere, such as:

' XlBordersIndex enumerated constants
DEFINE( "xlEdgeTop", 8 );
' XlLineStyle enumerated constants
DEFINE( "xlContinuous", 1 );

---
Steve

Thanks Steve!! That got me pointed in the right direction. I now have
things working properly. Here is an example code in case anyone is
curious. I had to look at the PERL again to see how the hash was setup
when setting the LineStyle. Once I got that syntax correct in PHP and
got the Constants defined, worked like a charm.

Thanks again!

<?php

// Example in using Excel COM Object

//Set this to where you wish to save
$xl_file = "c:/tmp/my_test.xls";

//Create new object
$XL = new COM("Excel.application") or Die ("Could not connect to Excel");

//Ignore Alerts
$XL->DisplayAlerts = 0;

//Make Excel Visible
$XL->Visible = 1;

//Create a new workbook
$WB = $XL->Workbooks->Add;

//Go to worksheet number 1
$WS = $WB->Worksheets(1);

//Make sure that worksheet is active
$WS->activate;

//Give the worksheet name
$WS->Name = "My Test";

// XlBordersIndex
DEFINE("xlEdgeTop" , 8);
DEFINE("xlEdgeBottom" , 9);
DEFINE("xlEdgeRight" , 10);
DEFINE("xlEdgeLeft" , 7);
DEFINE("xlDiagonalUp" , 6);
DEFINE("xlDiagonalDown" , 5);
DEFINE("xlInsideHorizontal", 12);
DEFINE("xlInsideVertical" , 11);

// XlLineStyle
DEFINE("xlContinuous", 1);
DEFINE("xlDash", -4115);
DEFINE("xlDot", -4118);
DEFINE("xlDashDot", 4);
DEFINE("xlDashDotDot", 5);
DEFINE("xlDouble", -4119);
DEFINE("xlSlantDashDot", 13);
DEFINE("xlLineStyleNone", -4142);

// XlBorderWeight
DEFINE("xlHaireline", 1);
DEFINE("xlMedium" , -4138);
DEFINE("xlThick" , 4);
DEFINE("xlThin" , 2);

// XlVAlign
DEFINE("xlVAlignBottom" , -4107);
DEFINE("xlVAlignCenter" , -4108);
DEFINE("xlVAlignDistributed", -4117);
DEFINE("xlVAlignJustify" , -4130);
DEFINE("xlVAlignTop" , -4160);

// Range/Column data alignment
DEFINE("xlLeft", 2);
DEFINE("xlCenter", 3);
DEFINE("xlRight", 4);

$cells = array("B2","D2","F2","H2","B4","D4","F4","H4");
$cell_data = array("Continuous","Dash","DashDot","DashDotDot",

"Dot","Double","SlantDashDot","None");
$cell_line = array(xlContinuous,xlDash,xlDashDot,xlDashDotDot,

xlDot,xlDouble,xlSlantDashDot,xlLineStyleNone);
$cell_border = array(xlEdgeTop, xlEdgeBottom, xlEdgeRight, xlEdgeLeft);

for ($i=0; $i<count($cells); $i++)
{
$cell = $WS->Range($cells[$i]);
$cell->activate;
$cell->Value = $cell_data[$i];
$cell->Interior->ColorIndex = "36";
$cell->Font->FontStyle = "Bold";
foreach ($cell_border as $cb)
{
$WS->Range($cells[$i])->Borders($cb)->LineStyle =
$cell_line[$i];
}
}

//Adjust column widths
$WS->Columns("A:H")->AutoFit;

//Cell data is considered text not numeric
$WS->Columns("A:H")->NumberFormat = "@";

//Center cell data for columns
$WS->Columns("A:H")->HorizontalAlignment = xlCenter;

//Save your new excel file
$XL->ActiveWorkbook->SaveAs($xl_file);

//Clean up and close, quit, release
$WB->Close;
unset($WS);
unset($wB);
$XL->Workbooks->Close();
$XL->Quit();
unset($XL);

?>