CODEWITHSUNDEEP
phpcomexcelvba
Obay
Obay

Reputation: 3205

Convert VB's Format() into PHP

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

Upvotes: 2

Views: 636

Answers (2)

Heinzi
Heinzi

Reputation: 172230

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;

Upvotes: 2

Pekka
Pekka

Reputation: 449415

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.

Upvotes: 1

Related Questions