在 Office 2010 中使用 PHP 的 COM 对象（Excel.Application）
我最近用 PHP 编写了一个脚本，使用 COM 对象将 Excel 2007 电子表格中的单元格中的数据剥离到数组中。这一切都是在装有 Office 2007 的 XP 系统上本地完成的。一旦我启用 Apache 服务器与桌面交互，脚本的工作就像一个绝对的梦想，允许我通过 PHP 用数组值填充我的 HTML。
现在我们刚刚升级到 Win 7 x64 和 Office 2010 32 位 - 相同的脚本现在向我抛出 COM_Exception：
“Microsoft Excel 无法访问”xml/xmlx 文件（以前对这两个文件都有效）。 “有几个可能的原因：
- 文件名或路径不存在。
- 该文件正在被另一个程序使用。
- 您尝试保存的工作簿与当前打开的工作簿同名”...显然。
我已经禁用了 UAC，认为这是罪魁祸首，当然允许 Apache 与桌面交互，但 Excel 进程甚至不尝试启动。我猜测 Windows 7 根本不允许脚本与 Excel 交互。还有其他可以使用类的脚本（例如 PHPExcel），但是我宁愿避免编写接收者代码，而且我什至不知道这些类是否适用于 Excel 2010。
我怎样才能克服这个 com_exception ？
代码：
<?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>
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>
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我认为你的问题很可能是Apache没有DCOM组件的权限。以管理员身份打开 DCOMCNFG.EXE。浏览器到计算机 ->我的电脑 -> DCOM 配置并找到“Microsoft Excel 应用程序”组件。右键单击它并选择“属性”，然后转到“安全”选项卡。
我比我应该做的要懒一些，所以我倾向于向所提供的所有三个选项授予 httpd 权限，而不是费心去了解每个选项何时适用。将单选按钮设置为“自定义”，然后单击“编辑”按钮，然后按照标准 Windows 对话框向您的 httpd 运行的用户/角色添加权限。
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.