Posted by krigare on 06/01/06 23:56

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

?>

[Back to original message]