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, "----------------------------------------
");
$excel = new COM("Excel.Application") or die ("ERROR: Unable to instantaniate COM!
");
$excel->Visible = true; // so that we see the window on screen
fwrite(STDOUT, "Application name: {$excel->Application->value}
") ;
fwrite(STDOUT, "Loaded version: {$excel->Application->version}
");
fwrite(STDOUT, "----------------------------------------
");
// DATA RETRIEVAL
$Workbook = $excel->Workbooks->Open($file) or die("ERROR: Unable to open " . $file . "!
");
$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>
