Using PHP's COM object with Office 2010 (Excel.Application)
I recently coded a script in PHP using the COM object to strip data out of cells in an Excel 2007 spreadsheet into an array. This was all done locally on an XP system with Office 2007. Once I'd enabled the Apache server to interact with the desktop the script worked like an absolute dream, allowing me to populate my HTML with the array values via PHP.
Now we've just upgraded to Win 7 x64 and Office 2010 32bit - The same script now throws me a COM_exception:
'Microsoft Excel cannot access' the xml/xmlx file (it worked for both previously). "There are several possible reasons:
- The file name or path does not exist.
- The file is being used by another program.
- The workbook you are trying to save has the same name as a currently open workbook"... apparently.
I've disabled UAC thinking that was the culprit and of course allowed Apache to interact with the desktop, but an Excel process doesn't even attempt to start. I'm guessing Windows 7 isn't allowing the script to interact with Excel at all. There are other scripts available using classes (eg PHPExcel) however I would rather avoid writing the recipient code, plus I don't even know if the classes would work for Excel 2010.
How can I overcome this com_exception?
Code:
<?php
error_reporting(E_ALL);
function getDataFromExcel($file, $sheet, $rows, $cols)
{
// COM CREATE
fwrite(STDOUT, "----------------------------------------\r\n");
$excel = new COM("Excel.Application") or die ("ERROR: Unable to instantaniate COM!\r\n");
$excel->Visible = true; // so that we see the window on screen
fwrite(STDOUT, "Application name: {$excel->Application->value}\r\n") ;
fwrite(STDOUT, "Loaded version: {$excel->Application->version}\r\n");
fwrite(STDOUT, "----------------------------------------\r\n\r\n");
// DATA RETRIEVAL
$Workbook = $excel->Workbooks->Open($file) or die("ERROR: Unable to open " . $file . "!\r\n");
$Worksheet = $Workbook->Worksheets($sheet);
$Worksheet->Activate;
$i = 0;
foreach ($rows as $row)
{
$i++; $j = 0;
foreach ($cols as $col)
{
$j++;
$cell = $Worksheet->Range($col . $row);
$cell->activate();
$matrix[$i][$j] = $cell->value;
}
}
// COM DESTROY
$Workbook->Close();
unset($Worksheet);
unset($Workbook);
$excel->Workbooks->Close();
$excel->Quit();
unset($excel);
return $matrix;
}
// define inputs
$xls_path = "D:\\xampp\\htdocs\\path_to_document\\test.xls"; // input file
$xls_sheet = 1; // sheet #1 from file
$xls_rows = range(3, 20, 1); // I want extract rows 3 - 20 from test.xls with 1 row stepping
$xls_cols = array("B", "D", "E", "G"); // I want to extract columns B, D, E and G from the file
// retrieve data from excel
$data = getDataFromExcel($xls_path, $xls_sheet, $xls_rows, $xls_cols);
?>
<html>
<pre>
<?php print_r ($data);?>
</pre>
</html>
Answer
I think your problem is most likely that Apache doesn't have rights to the DCOM component. As admin, open DCOMCNFG.EXE. Browser to Computers -> My Computer -> DCOM Config and locate the "Microsoft Excel Application" component. Right click this and select "Properties", then go to the Security tab.
I'm a bit lazier than I probably should be, so I tend to grant the httpd permissions to all three of the options provided, rather than bother to understand when each one applies. Put the radio button to "Customize", and hit the "Edit" button, the follow the standard Windows dialogs for adding permissions to the user/role your httpd runs as.