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
xlInsideHorizon tal xlInsideVertica l);

$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 = "xlContinuo us";
$workseet->Range($range )->Borders("xlEdg eTop)->LineStyle = "xlContinuo us";
$workseet->Range($range )->Borders()->LineStyle->Value = "xlContinuo us";
$workseet->Range($range )->Borders("xlEdg eTop)->LineStyle->Value =
"xlContinuo us";
....

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]

Re: PHP COM Excel Obj

[color=blue]
> 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).[/color]
[color=blue]
> My problem is the Borders. I have tried numerous combinations without
> luck. Such as:[/color]
[color=blue]
> $workseet->Range($range )->Borders()->LineStyle = "xlContinuo us";
> $workseet->Range($range )->Borders("xlEdg eTop)->LineStyle = "xlContinuo us";
> $workseet->Range($range )->Borders()->LineStyle->Value = "xlContinuo us";
> $workseet->Range($range )->Borders("xlEdg eTop)->LineStyle->Value =
> "xlContinuo us";[/color]
[color=blue]
> 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.[/color]

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( "xlContinuo us", 1 );

---
Steve

[krigare]

Re: PHP COM Excel Obj

>[color=blue]
>
> 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( "xlContinuo us", 1 );
>
> ---
> Steve
>[/color]

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.appl ication") or Die ("Could not connect to Excel");

//Ignore Alerts
$XL->DisplayAlert s = 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("xlEdgeT op" , 8);
DEFINE("xlEdgeB ottom" , 9);
DEFINE("xlEdgeR ight" , 10);
DEFINE("xlEdgeL eft" , 7);
DEFINE("xlDiago nalUp" , 6);
DEFINE("xlDiago nalDown" , 5);
DEFINE("xlInsid eHorizontal", 12);
DEFINE("xlInsid eVertical" , 11);

// XlLineStyle
DEFINE("xlConti nuous", 1);
DEFINE("xlDash" , -4115);
DEFINE("xlDot", -4118);
DEFINE("xlDashD ot", 4);
DEFINE("xlDashD otDot", 5);
DEFINE("xlDoubl e", -4119);
DEFINE("xlSlant DashDot", 13);
DEFINE("xlLineS tyleNone", -4142);

// XlBorderWeight
DEFINE("xlHaire line", 1);
DEFINE("xlMediu m" , -4138);
DEFINE("xlThick " , 4);
DEFINE("xlThin" , 2);

// XlVAlign
DEFINE("xlVAlig nBottom" , -4107);
DEFINE("xlVAlig nCenter" , -4108);
DEFINE("xlVAlig nDistributed", -4117);
DEFINE("xlVAlig nJustify" , -4130);
DEFINE("xlVAlig nTop" , -4160);

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

$cells = array("B2","D2" ,"F2","H2","B4" ,"D4","F4","H4" );
$cell_data = array("Continuo us","Dash","Das hDot","DashDotD ot",

"Dot","Double", "SlantDashDot", "None");
$cell_line = array(xlContinu ous,xlDash,xlDa shDot,xlDashDot Dot,

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 ")->HorizontalAlig nment = xlCenter;

//Save your new excel file
$XL->ActiveWorkbo ok->SaveAs($xl_fil e);

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

?>