PHP COM Excel Obj

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
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  

Here is a snippet of PERL code that works:

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 =  

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!!

Re: PHP COM Excel Obj

Quoted text here. Click to load it

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

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 );


Re: PHP COM Excel Obj

Quoted text here. Click to load it

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!


// 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

//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",
$cell_line = array(xlContinuous,xlDash,xlDashDot,xlDashDotDot,
$cell_border = array(xlEdgeTop, xlEdgeBottom, xlEdgeRight, xlEdgeLeft);

for ($i=0; $i<count($cells); $i++)
         $cell = $WS->Range($cells[$i]);
         $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 =  

//Adjust column widths

//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

//Clean up and close, quit, release


Site Timeline