Convert VB's Format() into PHP

Question

In PHP, I'm trying to read an Excel file using COM():

$rrc_app = new COM("Excel.application");
$rrc_workbook = $rrc_app->Workbooks->Open($filename);
$rrc_worksheet = $rrc_workbook->Worksheets('Top sheet');
$rrc_worksheet->activate;

I tried to extract the value of a cell containing a "Time" value:

$review_time = $rrc_worksheet->Range("C30")->value;

However, it returns a decimal number:

0.604166666667

I know I can use the Format() function of VB, but I don't know from what object in PHP to call it from. The following:

$review_time = Format($rrc_worksheet->Range("C30")->value, "hh:mm:ss");

Gives:

Fatal error: Call to undefined function Format() in C:\xampplite\htdocs\pmc\index.php on line 40

Do you happen to know how I can call this Format() function using PHP?

Thanks in advance

Answer 1

Format() looks like a Visual Basic function. It is not necessarily available to you throught the Excel COM object you are communicating with - only if that object declares it somewhere. The VB function range is not imported automatically into PHP when talking to a COM object.

Can you try $rrc_app->Format()?

If nothing works, I recommend figuring out what the time value stands for (I'm sure you can find out here on SO) and converting it in PHP.

Answer 2

Format is a function of the VBA.String module, so it's not part of the Excel COM library and I'm not sure if it is accessible via COM at all.

However, you can use the Text property instead of Value: This returns a formatted string (according to the cell format in Excel) rather than the underlying value:

$review_time = $rrc_worksheet->Range("C30")->Text;

---

EDIT: If the cell does not have the correct format yet, you can change the format before reading the Text property (untested):

$rrc_worksheet->Range("C30")->Select();
$rrc_app->Selection->NumberFormat = "hh:mm:ss";
$review_time = $rrc_worksheet->Range("C30")->Text;